Jan 072013

Have you seen this type of an informational message in your SQL errorlog before?  If so, then I hope the timestamp associated with it was not during the middle of your peak processing time.  This is a good indication that someone has been tinkering with some of the SQL configuration settings.

SQL Server offers a lot of wonderful configuration options; many of which can be changed dynamically using sp_configure without the need of restarting the SQL Server service.  The downside to the dynamic changes is sometimes they can have a detrimental effect on performance, and that’s what this “informational” message is tell you.  Let’s look at an example.
First, let’s get our starting numbers for the cachestores.

      COUNT(*) AS ‘Total Cached Plans’
FROM sys.dm_exec_cached_plans;
       name AS ‘Cache Name’
FROM sys.dm_os_memory_cache_counters
WHERE name IN (‘Object Plans’,‘SQL Plans’,‘Bound Trees’);
As you can see, we have 160 total plans in cache using up the amount of single and multi-page allocations for each entry.
Now let’s say your system administrator came to you today and said he hot-added extra ram to the SQL box because of how much you had been complaining about memory shortages.  You are so excited about this, you login to the server and execute the following query to bump SQL’s maximum memory up to 4GB.

EXEC sys.sp_configure N’max server memory (MB)’,N’4000′;
Now you look in the SQL errorlog to valid the changes.
You are excited to see this message.
Configuration option ‘max server memory (MB)’ changed from 2000 to 4000. Run the RECONFIGURE statement to install.
And not so excited about these.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Bound Trees’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Let’s run our cachestore query again and look at the results.
Ah proof that we just cleared the entire procedure cache.  Having these cachestores cleared out means you just lost all compiled plans for stored procedures, functions, triggers, views, adhoc plans, prepared plans, etc.  The next time any query runs, its plan will have to be compiled costing extra CPU cycles and potentially degraded performance.
As it turns out, for certain configuration settings when you execute the RECONFIGURE command it essentially does what amounts to DBCC FREEPROCCACHE.

The whole procedure cache is cleared if one of the following server options is changed by the RECONFIGURE statement:

  • cross db ownership chaining
  • index create memory (KB)
  • remote query timeout (s)
  • user options
  • max text repl size (B)
  • cost threshold for parallelism
  • max degree of parallelism
  • min memory per query (KB)
  • query wait (s)
  • min server memory (MB)
  • max server memory (MB)
  • query governor cost limit

This is a very good example of why you should NOT make configuration changes during peak processing times.  You wouldn’t run DBCC FREEPROCCACHE during the middle of your peak processing, so why would you play around with configuration settings during that same time?
Oct 162012

You have 5 instances running.  How do you know which one is killing your CPU?

As you can see from Windows Task Manager, you have several instances of SQL installed and one of them is using nearly half of the CPU.  To figure this out, you’ll need to know the PID (Process ID) number.  

To view the PID number in Windows Task Manager, from the menu select View, then Select Columns, and then check the box for PID.  For our example, it’s Process ID 1212 that’s using nearly 50% of our CPU.

So how do you correlate that PID to the SQL instance, so you can start the performance investigation? There’s a couple of places you can find this information.

The first place is to look at the most recent SQL Error Log for each of your instances.  One of the very first entries will be the PID value.
Server process ID is 1212.
The other place is to open the SQL Server Configuration Manager, select SQL Server Services from the left hand side, then right click on an instance and click Properties.  Click the Service tab and you’ll see the PID value for that instance.

Both of these methods give show you which instance is our culprit.  Now you can drill into that instance to find out why it’s killing your CPU.

I prefer to look for this value in the SQL Error Log because I already have Management Studio open ready to investigate the issue.  Keep in mind the PID value will change every time your SQL instance is started.

Oct 042012
Have you ever needed an instance of SQL Server to listen on multiple TCP/IP port numbers but didn’t know how to implement it?  Well here’s a quick step-by-step guide that will work for SQL Server 2005, 2008, and 2012.

Open SQL Server Configuration Manager.

On the left hand side, expand SQL Server Network Configuration.

Select your instance name.

On the right hand side, right click on TCP/IP and select Properties.

Click the IP Addresses tab.

Scroll down to the IPALL section.

Make sure the TCP Dynamic Ports field is empty.

In the TCP Port field, enter each port number separated by commas.

Click OK.

A pop up message will appear stating the changes will not take effect until the service is restarted.  Just click OK to close the message.

In the SQL Server Configuration Manager, on the left hand side, select SQL Server Services.

On the right hand side, right click the SQL Server service and select Restart.

After the restart is complete, SQL Server will be listening on all the port numbers you specified.

The easiest way to verify this is to look in the SQL Server Errorlog.

You will see one entry for each port number.

Server is listening on [ ‘any’ <ipv4> 10000].
Server is listening on [ ‘any’ <ipv4> 10001].
Server is listening on [ ‘any’ <ipv4> 10002].

If you happen to pick a port number that is already in use, then you will see an error like this 
within the SQL Server Errorlog, and SQL Server will fail to start.

Server TCP provider failed to listen on [ ‘any’ <ipv4> 10000]. Tcp port is already in use.