Cache

May 1 2008   9:00AM GMT

Checking the cached execution plans

Posted by: mrdenny
Cache, Query tuning, SQL Server 2005, sys.dm_exec_cached_plans, sys.dm_exec_requests, sys.dm_exec_query_plan, sys.dm_exec_plan_attributes, Execution Plan

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

Feb 13 2008   10:37AM GMT

New Article: Configuring SQL Server memory settings

Posted by: mrdenny
SQL, Article, Cache, RAM

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

Jan 28 2008   11:56PM GMT

Slide Decks and Sample Code from SoCal Code Camp

Posted by: mrdenny
Cache, Config, In Person Events, CLR, SQL, Resource Governor, SQL Server 2005, SQL Server 2008, SSMS, Query tuning, Beta

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

Oct 22 2007   10:17AM GMT

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

Posted by: mrdenny
SQL, Config, Cache

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