Querying Information from the Plan Cache, Simplified

SQL Server 2005, SQL Server 2008, TSQL
One of the great things about the Dynamic Management Objects (DMOs) that expose the information in plan cache is that, by their very nature, they can be queried. The plans exposed are in XML format, so you can run XQuery against them to pull out interesting information. For example, what if you wanted to see all the plans in cache that had a Timeout as the reason for early termination from the optimizer? It’d be great way to see which of your plans were less than reliable. You could so like this: WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), QueryPlans AS ( SELECT RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason, RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel, --dest.text, SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (deqs.statement_end_offset - deqs.statement_start_offset) / 2 + 1) AS StatementText, deqp.query_plan, deqp.dbid, deqs.execution_count, deqs.total_elapsed_time, deqs.total_logical_reads, deqs.total_logical_writes FROM…
Read More

Oh ****!

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQLServerPedia Syndication
Did you ever accidentally close SQL Server Management Studio? And, in closing SSMS, did you get the prompt that says “Save changes to the following items?” And did you, completely unthinkingly, with a query you had just been working on, hit Cancel? Yeah, me neither. What kind of idiot does that…. OK. I confess. I just did that. Silly thing it was, but I had just spent at least 1/2 an hour working on a query and now it was gone…. or was it? I had just run the query and had been looking at the results when I closed SSMS. Initially, I panicked and started thinking about how I could get the data back (somewhere there’s a file I’ve heard). Then it occurred to me, I had just been…
Read More

SQL Azure Query Tuning

Azure, SQLServerPedia Syndication
SQL Azure is still SQL Server at the end of the day. This means it is entirely possible to write queries against SQL Azure that really… what’s a good word… stink. So what do you do? It’s all in the cloud. You couldn’t possibly tune the queries, right? Wrong. Many of the same tools that you have available to you, such as execution plans and dynamic management objects, are still available in SQL Azure. Let’s talk DMOs for a second. First off, don’t make the mistake I did of trying to run these outside the context of a specific database on SQL Azure. You’ll get extremely inconsistent results, trust me on this. Anyway, I did a quick run-down on some of the most used DMOs for performance tuning, the sys.dm_exec_*…
Read More

Encryption and the Performance DMOs

SQLServerPedia Syndication, TSQL
Ever wonder what you can see in the performance oriented DMOs when stored procedures were encrypted? Me neither. But, I did get that question during my DMO presentation at the PASS Summit. I did not have an answer. I did get an answer from Johan Bijnens (twitter) from the audience, which I repeated without entirely knowing what I was saying. I decided that I ought to actually know the answer to that question, so here’s a little experiment. I'm going to create a simple stored procedure: [sourcecode language="sql"]CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT) AS SELECT soh.AccountNumber, sod.LineTotal FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @SalesOrderID[/sourcecode] When I create this procedure and run it, you can see the general performance of the query being…
Read More

Undocumented Virtual Column: %%lockres%

SQL Server 2005, SQL Server 2008, TSQL
One of my development teams needed a mechanism for identifying the value of a key that was part of a lock (don't ask). I'd never tried doing that before. Obviously if you hit the DMV sys.dm_tran_locks you can see the hash of the key in the resource_description column. But how to pull the value back. After some research, I first found this excellent article by the late, great, Ken Henderson (I really wish he was still around). The article outlined, among other things, the use of an undocumented "virtual" column called %%lockres%%. Some more searching then uncovered this great article by James Rowland-Jones, AKA Claypole. He described how, in a very high volume system, he used %%lockres%% to identify the source of a deadlock as the internal mechanisms that SQL Server uses to manage locks, the hash…
Read More

Characters

SCOM, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL
No, I'm not talking about a Dickens novel. I'm talking about the number of characters in a string. I had a painful time recently because of the word "characters."  If you take a look at the dynamic management view sys.dm_exec_sql_text you can get the queries that have been run on your system that are still in the cache. It's a great utility. Better still, you can get specific statements from the code that are actively running through sys.dm_exec_requests or ones that have run through sys.dm_exec_query_stats. To do this is very simple. Each of these DMV's has a pair of columns, statement_start_offset and statement_end_offset. These columns, and I'm quoting directly from books online measure the "number of character" offset from the beginning of the SQL string and from the end of…
Read More

When did this statement start?

SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL
UPDATE: This post is incorrect. Adam nailed it in the comments. I explain my mistake here. A question came up over at SQL Server Central where someone was wondering if it was possible to know when a given statement within a batch started. Immediately I thought, oh yeah, that's easy, use the sys.dm_exec_requests dynamic management view (DMV). Done. Wrong. The original poster pointed out that I had assumed that the values present in the DMV represented statement level values, but they show the batch. While the DMV shows a start_time, that time is the start of the current batch, not the statement within the batch. Now the question was, where else might I get this data? I next tried sys.dm_exec_sessions because it has the last_request_start_time value. Sure enough this worked. Don't…
Read More

MS Field Engineer's on Performance Troubleshooting

SQL Server 2005, SQL Server 2008, TSQL
Do you want to get a glimpse into how the Microsoft Field Engineers would go about troubleshooting performance issues on your server? Then go and read this blog entry by Denzil Ribeiro. Not only is this an excellent how-to on troubleshooting performance problems, but Mr. Ribeiro provides multiple links that describe the concepts he's dealing with further, making it a great piece of documentation. The MS Field Engineer blog is not terribly active, but what gets posted there is worth reading. If you don't have it on your feed list, you should.
Read More

Missing Index Information and Query Stats

SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL
So the goal was to find a way to pull information from sys.dm_exec_query_stats so that we could identify poor performing procedures that were in cache at the moment and combine it with missing index information from sys.dm_db_missing_index_details. We're combining these because we're working with a Microsoft Dynamics CRM database that is almost all ad hoc queries and lots of them are against tables with missing indexes. The hope was to identify necessary indexes merely by looking at the longest running queries. Unfortunately there is no way to combine data from the missing indexes set of DMV's and all the execution DMV's that show query stats, execution plan, etc. None of the missing index tables has a plan handle or a plan hash column that would allow you to combine that…
Read More

2008 Index Fragmentation

SQL Server 2008
I forgot all about this, but a script I wrote on using all the new functionality of dynamic management views & functions to do index defragmentation and rebuilds got published over at SQL Server Central. It could stand a bit of tweaking, but gets the job done on several of the systems I've tested it on so far.
Read More