May 1 2008 9:00AM GMT
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
Apr 7 2008 11:00AM GMT
Posted by: mrdenny
SQL,
Query tuning,
SQL Server 2008
Normally when running a query against a table and using a CAST or CONVERT function against a datetime field any index is made useless. In SQL Server 2008 this problem is fixed. Microsoft has come up with a way for SELECT statements which use CAST or CONVERT against a column of the datetime datatype to continue to use the index. Now keep in mind that this only works for the datetime datatypes and not other data types.
I believe that this feature showed up in CTP 5 (November).
Denny
Mar 27 2008 8:21PM GMT
Posted by: mrdenny
SQL,
Query tuning
Not all filter commands are created equal. Different filtering operations should be used at different times to get the best performance our of your SQL Server.
While the JOIN, EXISTS and IN filters can give you the same results the way that SQL Server gets to the data is very different and can lead to poor system performance. Also when doing a select vs. a delete these same operators will give different execution plans.
I’ll refer you do this file which will provide you with some sample code which can be run against the AdventureWorksDW sample database. Run each query with the execution plan being displayed. You’ll see that the IN and EXISTS both produce the same plan, while the JOIN produces a better plan when it comes to selecting data. However when it comes to deleting the data the EXISTS and IN produce a better plan than the JOIN command does. (Don’t worry, these delete scripts won’t actually remove any data from the table. The data these scripts try to delete doesn’t actually exist. We are looking for execution plans here, not actual deletes).
Denny
Mar 21 2008 6:11AM GMT
Posted by: mrdenny
Service Broker,
In Person Events,
Query tuning
I’d like to thank the San Diego SQL Server User Group for having me come and speak to them tonight. I had a great time presenting both my SQL Server Query Tuning and SQL Server Service Broker presentations. You can grab the slide deck and sample code from those two links.
I was happy to fill in on short notice for them when there scheduled speak cancelled on them. Hopefully the members liked the presentations as much as I liked giving them. Hopefully the San Diego SQL Server User Group will invite me back in the future.
Denny
Mar 18 2008 10:24AM GMT
Posted by: mrdenny
In Person Events,
Query tuning,
Service Broker
Below you will find links to download file files with the MS PowerPoint slide decks and sample files which I’ll be using at this weeks presentation at the San Diego SQL Users Group Meeting.
There is a lot of sample code in the Query Tuning file. We’ll look through as much of it as we can depending on the amount of time that we have.
SQL Server Query Tuning
SQL Server Service Broker
I’ve included the Service Broker slide deck even though I’m not sure that we will get to it. If there isn’t time to get to the slide deck during the meeting I’ll be happy to answer questions on it after the meeting (or posted here of course).
See you Thursday night.
Denny
Mar 6 2008 3:12AM GMT
Posted by: mrdenny
In Person Events,
SQL,
Query tuning,
Resource Governor
The good folks of the San Diego SQL Server User Group has invited me to come speak to them on March 20, 2008. The meeting starts at 6PM.
If you are in the area I urge you to come and check it out. The topic of my talk will be Query Tuning as well as a bit of Query Governor. If you attended the SoCal Code Camp in January and weren’t able to catch my session then, now is your chance.
The slide deck will be a little different so I’ll be adjusting it a little bit. I’ll try and get the slide deck posted before the meeting, but no promises.
More information about the San Diego SQL Server User Group can be found on thier web site http://www.sdsqlug.org. See you there.
Denny
Jan 28 2008 11:56PM GMT
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
