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 writing queries against the cache using DMOs. Why don’t I just pull it using the DMOs I had just been using? Worked like a charm.

SELECT  dest.text
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   deqs.last_execution_time > '5/19/2011 11:00'
        AND dest.text LIKE 'WITH%';

My query was much more complicated, but this was all I needed. I was able to filter out the junk in cache by only selecting stuff with an execution time within about 15 minutes of when I had closed SSMS, and I supplied the start of the query, a CTE. That was all I needed. I got back my query. Took a little formatting work, but it was immediately available and all was right with the world.

Just posting this in case you hit the same situation. You too can get your query back, easily and quickly. Then again, maybe I’m the only one stupid enough to do that.

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_* set. Here’s a complete listing of those procs and whether or not they’re available to you in SQL Azure:

SELECT  *
FROM    sys.dm_exec_requests AS der
--available

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_query_plan(der.plan_handle)
--available

SELECT  *
FROM    sys.dm_exec_requests der
        CROSS APPLY sys.dm_exec_sql_text(der.sql_handle)
--available

SELECT  *
FROM    sys.dm_exec_query_stats AS deqs
--available

SELECT  *
FROM    sys.dm_exec_cached_plans AS decp
--invalid object

SELECT  *
FROM    sys.dm_exec_connections AS dec
--available

SELECT  *
FROM    sys.dm_exec_cursors AS dec
--invalid object

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_plan_attributes(der.plan_handle) AS depa
--invalid object

SELECT  *
FROM    sys.dm_exec_procedure_stats AS deps
--invalid object

SELECT  *
FROM    sys.dm_exec_query_memory_grants AS deqmg
--invalid object

SELECT  *
FROM    sys.dm_exec_query_optimizer_info AS deqoi
--invalid object

SELECT  *
FROM    sys.dm_exec_query_resource_semaphores AS deqrs
--invalid object

SELECT  *
FROM    sys.dm_exec_sessions AS des
--available

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_text_query_plan(der.plan_handle, 0, -1) AS detqp
--available

SELECT  *
FROM    sys.dm_exec_trigger_stats AS dets
--invalid object

SELECT  *
FROM    sys.dm_exec_xml_handles(@@SPID)
--invalid object

The good news, most everything you need is available so you’re really going to be able to go to town on using DMOs as part of your query tuning. The bad news, “most everything” doesn’t include sys.dm_exec_query_optimizer_info is not on the list. This does take away a tool. It’s not a vital tool, but it’s one that allows you to understand some of what’s happening on the system. I’m not going to cry and rend my clothing because I don’t have it, but I will be somewhat disappointed.

This is great news! The tools you’re learning and using (and you are using DMOs, right?) will continue to be available in SQL Azure.

Greetings. Welcome once more to the Miskatonic University branch of SQL University. Does anyone know where to buy some camping equipment, cheap? I’ve been tagged to go an expedition to Antarctica and I need some cold weather gear a bit more substantial than my LL Bean boots. Evidently the last expedition found some caves in some mountains down there. Sounds like the perfect place to get away from all the crazy stuff that goes on here at Miskatonic. I mean, what could happen?

Anyway, our last several talks have all been about indexes and indexing. One of the things that we haven’t talked about is how to tell if, how or when your indexes are being used. Starting with SQL Server 2005, and continuing to 2008 and R2, there has been a wonderfully easy way to do just this through Dynamic Management Objects (DMOs). Specifically three DMOs: sys.dm_db_index_usage_stats, sys.dm_db_index_operational_stats(), and sys.dm_db_index_physical_stats().

sys.dm_db_index_usage_stats

The first, sys.dm_db_index_usage_stats is a fascinating Dynamic Management View (DMV). It returns information that shows how many times various operations have occurred on an index, and when the last time it was done. The information is stored since the last time the server was started, or when a database is attached or restored. If a database is detached or closed, the data for that database is removed from the DMV. It breaks the data down so that it shows user information separately from system information. This means you can see the number of times a user has updated the index as opposed to when the system did an update as part of maintenance. You can use this to get an idea of which indexes are used within your system and which ones are not.

Since it’s a DMV, it’s incredibly easy to use. Here’s a query that shows the number of times that user access have occurred on the SalesOrderDetail clustered index:

SELECT  ddius.user_lookups,
        ddius.user_scans,
        ddius.user_seeks,
        ddius.user_updates,
        ddius.index_id
FROM    sys.dm_db_index_usage_stats AS ddius
WHERE   database_id = DB_ID(N'AdventureWorks2008R2')
        AND object_id = OBJECT_ID('AdventureWorks2008R2.Sales.SalesOrderDetail')
        AND index_id = 1;

You can combine this with other queries and other DMOs to put together interesting information. But the key is to remember that you can only rely on this information to a certain degree. You can’t simply assume that the information here will tell you precisely which indexes have been used and which have not. You will have to assume that it might not cover all possible uses of indexes in your system because of a reboot on your server. If your server and the database that you’re interested in have been continuously online for over a year and you don’t have 18 month queries or something else, maybe. Otherwise, you’re taking a chance to simply use this to help clean up your system. However, it can help you clean up your system. Just do so with the knowledge of what this represents.

sys.dm_db_index_operational_stats

The next object is sys.dm_db_index_operational_stats(). This is a Dynamic Mangement Function (DMF) which means you must pass it parameters. With this DMF you can see current locking for every table or index. The call looks something like this:

SELECT  *
FROM    sys.dm_db_index_operational_stats(DB_ID('AdventureWorks2008R2'),
      OBJECT_ID('Sales.SalesOrderDetail'),
      NULL, –IndexID
      NULL –PartitionID
      ) AS ddios

The way I have it configured, I’m looking at all indexes for the table Sales.SalesOrderDetail. If I substituted NULL for the OBJECT_ID, I could see all access to tables within the database. Again, if I substituted NULL for the DB_ID I could see all active access across the system. Conversely, if I wanted to drill down, I would also add the IndexID value so that I would only see the activity of a given index and the same thing with the PartitionID.

But to really see it at work, let’s get a transaction going. I can do it very simply. If I open a second connection to the database and run this query:

BEGIN TRAN

SELECT * FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID = 42;

And then go back and run the original query. I have three rows for the three indexes on the table:

This returns all sorts of information about the locks and waits that currently occurring. In this instance you can see that a single page lock was taken out and that there were a pair of page latch waits, all on the first index, index_id = 1, the cluster. This would be expected for a query that is performing a seek on the clustered index.

sys.dm_db_index_physical_stats

The last object is sys.dm_db_index_physical_stats(). This gives you information about your indexes, how deep they are, how many pages, how fragmented those pages are, etc. It’s extremely useful for determining when to defragment your indexes, but it also helps you understand what is being accessed when, why the optimizer chose to access a given index and more. The key to this particular DMF is that it can be very expensive to run. It has three modes; LIMITED, SAMPLED, and DETAILED. You’re going to get varying amounts of data back, but the DETAILED mode will scan the whole index and this can lead to blocking issues on a production system. Be cautious. Calling this DMF is not that different from the others:

SELECT  *
FROM    sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'),
      OBJECT_ID('Sales.SalesOrderDetail'),
      NULL, NULL, 'LIMITED') AS ddips

Running the query in this manner can be used to show general information about the index and it’s level of fragmentation. If you’re interested in understanding more about the index, such as the average amount of space left on a page, you can move to SAMPLED or DETAILED. SAMPLED and DETAILED return the same data, but the SAMPLED information is taken from a 1% sample of the index. Although, if the index is less than 10,000 pages in size, DETAILED is used instead of SAMPLED, so that’s something to be aware of.

Running the query above would return a data set similar to this one:

This is all the data returned from a LIMITED scan, which only hits the tree structure above the leaf or the PFS & IAM pages of a heap. You can see the index types, if they store data outside of the row, the depth, average fragmentation in percentages, fragment counts, fragment size in pages and page count.

Conclusion

This is just an overview of what’s possible with these various DMOs. You can combine them with other DMOs to arrive at use information about your systems and the performance there.

Thanks for stopping by Miskatonic University. Please stop by the gift shop on your way out. Just remember to keep those meteor shards they sell there out of your water supply.

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:

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

When I create this procedure and run it, you can see the general performance of the query being run by pulling data from the sys.dm_exec_procedure_stats DMO like this:

SELECT deps.type_desc,
deps.last_execution_time,
deps.execution_count,
deps.total_logical_reads,
dest.encrypted AS EncryptedText,
dest.text,
deqp.query_plan,
deqp.encrypted AS EncryptedPlan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) as deqp
WHERE dest.text LIKE 'CREATE PROCEDURE dbo.GetSalesDetails%'

Now, to modify the procedure so that it’s encrypted I’m going to recreate it with a slight modifcation:

CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT)
WITH ENCRYPTION...

Now, if I execute the procedure and rerun the select statement against the DMO, I won’t get any data. Why? Because of the WHERE clause. The text of the procedure is no longer available in the sys.dm_exec_procedures_stats DMO. Encryption has worked. I can’t see the SQL and I can’t see the execution plan. I will however, see values in the EncryptedText and EncryptePlan columns, showing that despite the encryption, rows for the procedure in question do exist in the appropriate DMOs.

There’s the answer to the question.

EDIT: Fixed the spelling of Johan’s name. Sorry Johan!

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_os_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 of the key. Oh, and he opened an incident on Connect, which seems to be closed, but vote on it anyway, I did. %%lockres%% is also covered in Kalen Delaney’s excellent book on SQL Server 2008 Internals and even warrants a bit of discussion in Professional SQL Server 2008, but that was written by James Rowland-Jones, so I’m not sure it counts.

In the meantime, while I was investigating this stuff, evidently the development team was looking into it on their own. They came to the same set of resources and decided to use the virtual column as part of their real-time, transactional application. Yeah, an undocumented “virtual” column going into a major application. Since I would probably be unable to do anything about this, I decided to at least look into how this thing behaves so I can be aware of what types of problems I might run into.

First, a simple query:

SELECTa.City
–,%%lockres%%
FROM Person.Address AS a
WHERE a.AddressID = 432

If you run this query and take a look at the execution plan you’ll see a nice clean clustered index seek, just as you would suspect. If you take away the comment and run it again, the execution plan is identical. On the version of AdventureWorks2008 currently installed on my machine, I get two page reads, regardless of whether or not I include %%lockres%% or not. With the comments removed, it returns the hash of the primary key: (b0004e040bc2). This looks pretty painless, free even.

If we want to see %%lockres%% in action, it’s not too difficult:

BEGIN TRAN
UPDATE Person.Address
SET City = ‘dude’
WHERE AddressID = 432;
–ROLLBACK TRAN

Obviously this will put a key lock on that row in the table. If I just select against sys.dm_os_tran_locks, the data returned looks like this:

resource_type   resource_description   resource_associated_entity_id   request_mode
KEY                       (b0004e040bc2)            72057594043564032                      X 

The original request from the development team was for a way to get the key value back when you know that a table is locked, such as the case here. I wrote this simple query to make that happen:

SELECT a.AddressID
FROM person.address(NOLOCK) AS a
JOIN sys.dm_tran_locks AS dtl
ON a.%%lockres%% = dtl.resource_description
WHERE dtl.resource_type = ‘KEY’

This query works and returns our key value of 432 just as you would want. But, take a look at the execution plan:

Yes, that’s a clustered index (or table, same thing) scan followed by a Sort followed by a merge join, processing 19614 rows to return one. But hey, it was only 341 reads. To say the least, I’m not excited about seeing this in a production system. This was explicitly cautioned in Kalen Delaney’s book. While it appears that the remote scan operator, which is how the DMV is accessed in this case, is 59% of the operation, that’s the estimated cost and has been pointed out before, isn’t the best measure of real cost in the system.

The development team went off and developed their own query, they had said they were looking for the key value, but evidently they were looking for who was holding the lock on a particular key value:

SELECT s.nt_user_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s
on l.request_session_id = s.session_id
inner join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
where OBJECT_NAME(p.object_id) = ‘Address’ and
l.resource_description in (select %%lockres%%
from person.Address(NOLOCK) a WHERE a.AddressID = 432)

I actually had to adjust their query just a bit to get it to work correctly, but basically they had the right idea. Here’s the final execution plan:

This was still not terribly inspiring a thing to think about running in a production system although it only had one scan and seven reads. Whether or not putting this in a transactional system is a good idea, it certainly adds yet another tool, albeit an undocumented one, to the tool belt.

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 the SQL string. Using these values you can retrieve an individual statement out of a stored procedure that has multiple statements.

But… Here’s where things get tricky. Try this on your machine:

SELECT SUBSTRING(dest.text, (der.statement_start_offset ) + 1,
(der.statement_end_offset - der.statement_start_offset) + 1)
,LEN(dest.text) AS CharLength,
der.statement_start_offset,
der.statement_end_offset
FROM sys.dm_exec_query_stats AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.statement_end_offset > -1

You might get an error or you might get a bunch of really odd looking statements in the first column, starting part way into TSQL and cutting off after they’re done or before they’re over. It’ll look odd. But what’s the deal? The SUBSTRING function should work. Logically it’s configured correctly. Here’s the problem.

The [text] column in sys.dm_exec_sql_text is of the datatype NVARCHAR(MAX). Unicode. If you look at the length of the text, it’ll tell you exactly how many characters you see in the string that called to your server. But, the statement_start_offset and statement_end_offset are measuring something different. They’re not measuring characters, they’re measuring unicode characters. Try this query instead:

SELECT SUBSTRING(dest.text, (der.statement_start_offset / 2) + 1,
(der.statement_end_offset - der.statement_start_offset) / 2+ 1),
LEN(dest.text) AS CharLength,
DATALENGTH(dest.text) AS DLength,
DATALENGTH(dest.text) / 2 AS HalfDLength,
der.statement_start_offset,
der.statement_end_offset
FROM sys.dm_exec_query_stats AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.statement_end_offset > -1

You can see that the character length is, whatever it’s supposed to be, but the DATALENGTH is twice that much. Unicode, as we all know, includes a byte to identify the character set. That’s included in the character count in statement_start_offset and statement_end_offset.  You need to take that into account when dealing with these “characters.”

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.

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.

This is my first pass at a modern (2005/2008) blocking monitoring script. I think it’s a decent blocking script to capture information about blocks as they are occurring. Filters can be applied and it wouldn’t be hard at all to add on other information such as execution plans, plan hash, etc.

SELECT  tl.request_session_id AS WaitingSessionID

       ,wt.blocking_session_id AS BlockingSessionID

       ,wt.resource_description

       ,wt.wait_type

       ,wt.wait_duration_ms

       ,DB_NAME(tl.resource_database_id) AS DatabaseName

       ,tl.resource_associated_entity_id AS WaitingAssociatedEntity

       ,tl.resource_type AS WaitingResourceType

       ,tl.request_type AS WaitingRequestType

       ,wrt.[text] AS WaitingTSql

       ,btl.request_type BlockingRequestType

       ,brt.[text] AS BlockingTsql

FROM    sys.dm_tran_locks tl

        JOIN sys.dm_os_waiting_tasks wt

        ON tl.lock_owner_address = wt.resource_address

        JOIN sys.dm_exec_requests wr

        ON wr.session_id = tl.request_session_id

        CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt

        LEFT JOIN sys.dm_exec_requests br

        ON br.session_id = wt.blocking_session_id

        OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) AS brt

        LEFT JOIN sys.dm_tran_locks AS btl

        ON br.session_id = btl.request_session_id;

I’m working on the chapter on blocking in the new book. Explaining blocking of course means explaining locks. Prior to 2005, to understand locks, you went to sp_lock. Not anymore. Now you can query sys.dm_tran_locks. It’s so much more sophisticated than the old system procedure.  Best of all, the information within it is simply a view into the internal locking infrastructure, so you’re not placing extra load or extra processing on the system to marshal this data. A simple query to get basic locking information would look like this:

  SELECT tl.request_session_id

            ,tl.resource_database_id

            ,tl.resource_associated_entity_id

            ,tl.resource_type

            ,tl.resource_description

            ,tl.request_mode

            ,tl.request_status

  FROM sys.dm_tran_locks tl

That just outputs roughly the same information as sp_lock. Lots more detail, not available in sp_lock, is available if you need it. Things like resource_lock_partition to identify which partition a lock is on or  request_reference_count which shows how often the same lock has been requested by a given resource. Then, armed with this data, you can go after other dmv’s. Take, for a GLARING example, sys.dm_os_waiting_tasks. Hmmm if we were to combine something that showed locks with something that showed tasks that were waiting, what might you arrive at? BLOCKING!

The BOL shows a neat little query for just such an occasion:

SELECT
        t1.resource_type,
        t1.resource_database_id,
        t1.resource_associated_entity_id,
        t1.request_mode,
        t1.request_session_id,
        t2.blocking_session_id
    FROM sys.dm_tran_locks as t1
    INNER JOIN sys.dm_os_waiting_tasks as t2
        ON t1.lock_owner_address = t2.resource_address;

Clearly there is more here to explore. You can even go on to combine these dmv’s with the one’s that show the procedure cache so you can capture the execution plan of queries that are blocking or being blocked. I know dmv’s have been featured in a lot of articles and presentations lately, but I still think lots of people are unaware of just how useful they are. You need to examine this resource further if you’re working in SQL Server 2005/2008. More to come on sys.dm_os_waiting_tasks.

UPDATE: Typo corrected in the first paragraph. Thanks Jack.
ANOTHER UPDATE: More Typo’s corrected in the first paragraph. Thanks Gail