Cache

Handy Query to look at amount of data in Buffer Cache

Here’s a handy query which I’ve written which I’ve used to see how much of each table is loaded into the SQL Server Buffer Cache.  It shows you how much data is in memory, how much space is used on disk, and the percentage of data in memory compared to the amount of data on disk.

SELECT sys.tables.name TableName,
 sum(a.page_id)*8 AS MemorySpaceKB,
 SUM(sys.allocation_units.data_pages)*8 AS StorageSpaceKB,
 CASE WHEN SUM(sys.allocation_units.data_pages) <> 0 THEN SUM(a.page_id)/CAST(SUM(sys.allocation_units.data_pages) AS NUMERIC(18,2)) END AS ‘Percentage Of Object In Memory’
FROM (SELECT database_id, allocation_unit_id, COUNT(page_id) page_id FROM sys.dm_os_buffer_descriptors GROUP BY database_id, allocation_unit_id) a
JOIN sys.allocation_units ON a.allocation_unit_id = sys.allocation_units.allocation_unit_id
JOIN sys.partitions ON (sys.allocation_units.type IN (1,3)
  AND sys.allocation_units.container_id = sys.partitions.hobt_id)
 OR (sys.allocation_units.type = 2 AND sys.allocation_units.container_id = sys.partitions.partition_id)
JOIN sys.tables ON sys.partitions.object_id = sys.tables.object_id
 AND sys.tables.is_ms_shipped = 0
WHERE a.database_id = DB_ID()
GROUP BY sys.tables.name

When you run this code, some of the smaller objects will show more pages in memory than on disk.  It appears that this is normal.  My guess is that SQL Server is preallocating data pages so that if data is added to these pages there is space allocated to them already in order to save time when adding data to the table.

 I’ve removed the system objects from this query, as you can’t do anything to optimize them.

Denny

Article: SQL Server memory configurations for procedure cache and buffer cache

I’ve just published another tip over on SearchSQLServer.com.  This one is titled SQL Server memory configurations for procedure cache and buffer cache.

To determine how much memory your SQL Server database uses for buffer cache and procedure cache, factor in the SQL version you’re using, whether it’s x86, x64 or Itanium and the amount of memory allocated to the SQL Server instance. I talk about how the system configures memory and how it allocates the amount of buffer and procedure cache that’s available.Denny

EMC World 2008 Day 3

Wednesday at EMC World was a session packed day to be sure.

I started my morning with CLARiiON Rebuild Settings and Data which was an in depth look at how exactly the CLARiiON systems handle rebuilds, and how long various rebuilds take.  In addition we went into detail as to how the CLARiiON will not see that a disk is going to fail, and instead of waiting for it to fail and then having to rebuild it will actually copy the data from the disk ahead of time to a hot-spare then mark the disk as failed so that it can be replaced before the disk actually fails there by providing you with no point in time where your data is unprotected.

 Another session went through the changes to the EMC PowerPath product and all the new features they are building into PowerPath such as the encryption of data when it leaves the server on its way to the disk.

Another session went through some tuning tips an tricks for getting the best performance from the EMC CLARiiON product.  These include the strip size, cache settings at both the LUN and SP level. 

The night ended with the Billy Crystal performance.  I figured that the show would be funny.  I was wrong, it was hilarious.  Like everyone else I’ve seen Billy Crystal on TV and in Movies and thought he was funny, but in person he was probably the best comedy performance that I had ever seen.

Denny

Checking the cached execution plans

As we all know SQL Server, will for good or bad, cache execution plans.  Up until now it’s been very tough to see the cached execution plans.  You pretty much had to grab the query or procedure (with the parameters) and run it with Query Analyzer and have it return the execution plan.  The only other option was to use SQL Profiler to capture the query along with the execution plan which it used.

 Starting in SQL Server 2005 you now have the ability to query the system catalog and see what query plans are stored in the cache, as well as the queries which trigger that plan to be used, as well as how many times that cached plan has been used.

The first DMV to look at is sys.dm_exec_cached_plans.  This DMV gives you the size of the plan, the handle of the plan (which is binary), and the number of times the plan has been used.

The next DMV to look at is sys.dm_exec_requests.  This DMV gives you the currently running processes, wait information, all the session settings for the processes, etc.  It also includes the handle for all the plans which are currently being run.  If you join this sys.dm_exec_requests to sys.dm_exec_cached_plans you can see exactly which processes are using which plans.  Because this shows you the spid (the session_id column) and the plan_handle you can see what users are running which commands.

The next object is a system table function called sys.dm_exec_query_plan.  This function accepts a single input parameter of a plan handle.  There are several columns in the output, the most important of which is the query_plan.  This is the actual xml plan which is stored in the SQL Server.

The last object we’ll be looking at is the sys.dm_exec_plan_attributes table function.  This function also accepts a single input parameter of a plan handle.  This function returns the various session settings which were in place when the plan was created.  This is important information to have when working with query plans, as changing a single ANSI connection setting will force SQL to create a new execution plan.

Don’t forget how to view the XML execution plan in the UI.

Denny

New Article: Configuring SQL Server memory settings

I’ve published a new tip over on SearchSQLServer.com entitled “Configuring SQL Server memory settings“.  In it I talk about how to correctly setup the memory settings for SQL Server to get SQL setup correctly.

Denny

Slide Decks and Sample Code from SoCal Code Camp

The SoCal Code Camp was this last weekend.  I had a great time attending and speaking at the code camp this time around.   I hope that everyone who attended my sessions had as much fun attending the sessions as I did speaking at them.  I did my best to make them as much fun and interactive as I could.

As promised here are the slide decks and sample T/SQL code from the four sessions.  Everything is within a single ZIP file.  I’ve included two copies of each slide deck.  One in the Office 2007 format, and one in the Office 97-2003 format.  They are identical to each other, but I wanted to include both so that people with the older version of Microsoft Office can see the deck without having to download and install the patch which allows Office 2003 to view Office 2007 files.

The sample scripts which I’ve included are all run against the AdventureWorks database or the AdventureWorksDW database (check the USE commands at the top of the scripts).  If they need a different database they will create the new database.

Session 1 - SQL Server Query Tuning (SQL 2000+)

Session 2 - SQL Server 2008 Resource Governor (SQL 2008 CTP5+ only)

Session 3 - SQL Server Service Broker in the Real World (SQL 2005+) (I’ve fixed the problem with the single server script that we were having at the Code Camp.  Turns out I had left the route in place which is why the message never showed up.  The first script didn’t run correctly because I had run the server to server script on my virtual machine and the route was left by accident.)

Session 4 - SQL Server 2008 What’s on the Horizon (SQL 2008 CTP5+)

If you have any questions about these slide decks or sample code feel free to post a comment here, or drop me an email.

Denny

How SQL decides how much procedure cache and how much buffer cache to use.

There are two main types of cache which SQL Server deals with, the buffer cache and the procedure cache.  The procedure cache is where the execution plans for procedures and queries are stored.  The buffer cache is where the actual data is cached so that SQL Server doesn’t have to go to disk to get often accessed data.

The version of SQL Server that you are running will determine how SQL calculates the maximum size of the procedure cache.

SQL 2000 - 50% of the memory or 1 Gig which ever is lower

SQL 2005 RTM to SP1 - 75% of the first 8 Gigs of RAM + 50% of the next 56 Gigs of RAM + 25% of the ram over 64 Gigs.

SQL 2005 SP2 and up - 75% of the first 4 Gigs of RAM + 10% of the ram over 4 Gigs

As I understand the reason for the change the original settings were causing SQL Server to lockup for some customers as not enough RAM was left over for the buffer cache.

If you are using SQL 2005 in a Win32 platform these calculations change again as the procedure cache must remain within the first 2 Gigs of memory giving you a max of 2 Gigs of procedure cache no matter how much memory you install.

Denny