Kalen Delaney : DMVs

Geek City: More statistics info available!

Kalen Delaney — Wed, 10 Apr 2013 01:39:00 GMT

I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives us more info about distribution statistics. It returns info about the last statistics update date (which is also available with a function STATS_DATE())....(read more)

Geek City: Space Used By Worktables

Kalen Delaney — Wed, 26 Nov 2008 17:08:00 GMT

Today, a reader asked me the following:

"How can I find the amount of space occupied by a worktable?. Using SET STATISTICS IO ON, I can only see the number of reads using the worktable, not the amount of space taken."

What is a worktable?

I always like to think of it as a temp table that SQL Server builds without being asked. While preparing to write this post, I decided to see if I could find a formal definition. Books Online for SQL Server 2005 gives the following definition in the topic "Worktables":

Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan.

Then I went to one of my favorite whitepapers, "Working with tempdb in SQL Server 2005", which I strongly suggest you take a look at, if you're at all interested in keeping track of your tempdb database. It had a slightly different definition:

Work tables are temporary objects and are used to store results for query spool, LOB variables, and cursors.

So there is some overlap, in that both definitions mention spools.

Prior to SQL Server 2005, the best we could do was watch the STATISTICS IO value, and look at the page reads for any worktables created in the query, but, as my reader mentions, those values show us the number of reads, not the total size of the tables . There were/are some Performance Monitor counters that let us see how many worktables were created, but they don't mention the size.

SQL Server 2005 provides us a couple of DMVs that can be helpful.

The first, sys.dm_db_file_space_usage, has a name that seems like it will provide information about all your databases, but it turns out it just provides information for tempdb. I usually use this view to keep track of the version store space, but it also tells me how much space is used for user objects (explicit temp tables) and internal objects (which include worktables).

The second, sys.dm_db_session_space_usage, reports information for each session, so you can filter it by the session_id you are interested in . For you current session, you can look at @@spid:

SELECT * FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid;

During testing, the above can be useful, to look at the values before you run a test, and then look at the values afterwards, and compute the difference. This still doesn't give you the exact size of your worktables, but it can give you some ideas. In fact, the above mentioned whitepaper states that there is no way to get the number of pages used by any specific internal object in tempdb.

The whitepaper gives you code to create a table called tempdb_space_usage and a stored procedure called sp_sampleTempDbSpaceUsage to populate the table. It also provides half a dozen queries to examine the data collected.

You should be able to get a much better handle on what is using your tempdb space by following the guidelines in the whitepaper and running some of the provided queries.

Have fun!

~Kalen

Geek City: Why I still need Sysprocesses

Kalen Delaney — Mon, 30 Jun 2008 03:48:00 GMT

I've said many times that my favorite new feature in SQL Server 2005 is the new metadata, in particular the new Dynamic Management Objects. When I have to do troubleshooting on a SQL Server 2000 system, it is worse than painful, not to have my favorite tools like sys.dm_tran_locks, sys.dm_exec_cached_plans and
sys.dm_exec_query_plan. By now, on the eve of the release of the version AFTER SQL Server 2005, my transition to the new metadata is almost complete.

With one notable exception...

I have always used sysprocesses constantly for troubleshooting information. Although most of the useful information that allows me to see what sessions are using lots of memory, or performing lots of I/O, can found in sys.dm_exec_sessions, there is one piece of information that isn't there. Sysprocesses contains a columns called open_tran which reflects the transaction nesting of each session. If a session issues four BEGIN TRAN commands, with no COMMITs or ROLLBACKs, their session will have an open_tran value in sysprocesses of 4. Any open_tran value greater than 0 might mean that a transaction is holding locks and blocking other processes, or it might be keeping the transaction log from being cleared. If you ever notice open_tran values in higher than 2 or 3, it's a pretty good indication that a developer doesn't know much about SQL Server transaction management. I use this value all the time, and once had to troubleshoot a system where dozens of connections had open_tran values in the 20s and 30s! (For more details about nesting transactions, see my earlier post.

So imagine my surprise when I discovered that the sys.dm_exec_sessions view, which is supposed to 'replace' sysprocesses in SQL Server 2005, has no column to provide this information! Another view, sys.dm_exec_requests, has a column called open_transaction_count, which you might think would be the same thing. And it actually is the same information, but the sys.dm_exec_requests view only returns rows for sessions that are currently active, i.e. currently running a query. The need for examining the open_tran value is greatest when looking at those sessions that aren't doing anything, but are just sitting there with an open transaction. Those are the processes that you need to troubleshoot. I had felt that the omission of open_tran (or open_transaction_count) from sys.dm_exec_sessions was just an oversight, and it would be 'fixed' in the next release. But it appears I am to be disappointed. I just checked sys.dm_exec_sessions in RC0, and there is still no open_transaction_count column.

So long live sysprocesses!

~Kalen

Did You Know? Fragmentation, revisited....

Kalen Delaney — Fri, 29 Feb 2008 01:00:00 GMT

A couple of questions arrived regarding my earlier post about fragmentation on the system tables, and since the questions really don't have anything to do with system tables, I felt they deserved a new post.

1) Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages). According to many source, this should also be actioned, seeing that this indicates "logical" fragmentation (meaning that the leaf pages are not in the order of the non-leaf keys any more). I cannot find anything conclusive on this. Where as the index level=0 occurrences, indicates extent fragmentation. We are using this dmv, to check fragmentation-levels first, and based on that, do the necessary re-index/reorg. Currently we are using the LIMITED option (which only shows leaf-level), but we seem to be missing indexes that needs to be re-indexed/re-orged.

There are two misunderstandings reflected in this question. Although there are several types of fragmentation, the value in the avg_fragmentation_in_percent represents only logical fragmentation, in any level of any index. Yes, there can be logical fragmentation in the upper levels, which may need to be addressed in some very large tables. Extent fragmentation is only reported for heaps, and once you have a clustered index, the table itself is just the leaf level of the index. So basically, there is no difference between the meaning of the avg_fragmentation_in_percent value no matter what level you are at. However, fragmentation is really only an issue if there are LOTS of pages. I usually say that if there are less than 100 pages, you should never worry about fragmentation. However, this this whitepaper suggests that a better cutoff would actually be 1000 pages:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

It would need to be a very big table to have upper levels of indexes with more than 1000 pages, and the sys.dm_db_index_physical_stats view does show you the number of pages at each level.

The second misunderstanding involves what LIMITED means. LIMITED does not ONLY show leaf level, in fact, just the opposite. LIMITED does not evaluate pages at the leaf level at all. It can still determine the logical fragmentation because of the pointers in the upper levels, but LIMITED will return no values that require actually examining the leaf level rows. This includes values like min, max and avg record size, ghost and forwarded records counts, and the value for avg_page_space_used_in_percent. If you just want to see logical fragmentation to know if defrag or reorg might help, LIMITED should be fine.

2) Secondly, the stats that this function return, does not seem to be reliable. I ran the function prior to an ALTER…. REBUILD, and then again thereafter. There were hardly any differences in the stats. Is it now a case of the stats being unreliable, or the REBUILD not being effective?

REBUILD doesn't seem to make a difference in the avg_fragmentation_in_percent value is when there are too few pages in the table.

Geek City: Fragmentation on the System Tables

Kalen Delaney — Fri, 18 Jan 2008 01:05:00 GMT

It's my first post of the new year. I hope it's starting out well for all of you! New year, but sometimes the same old questions. I got another email asking about defragging the system tables. It seems to be in the Hit Parade of FAQs.

First of all, WHY do you think you would need to defrag a system table? Fragmentation is only a problem when you are performing an in-order scan on a table of more than 100 pages or so, and how often do you do that to a system table that is that big? Rarely, I would assume, but let me know if you have seen an actual need for defragging a system table.

In SQL Server 2000, you can actually use a system table as a parameter to DBCC SHOWCONTIG to see the fragmentation values. One of my readers reported that he tried running a DBCC INDEXDEFRAG on SQL 2000 system tables and while sometimes it worked, sometimes it corrupted the table! It doesn't sound worth it to me.

And now, with SQL Server 2005 there is no way to see the fragmentation on the system tables. What about using the new Dynamic Management Object sys.dm_db_index_physical_stats? It requires an object_id as a parameter, and if you select from the metadata view sys.all_objects you can see the object_id for the real system tables, even if you're not using the DAC. Try this:

select * from sys.all_objects
where type = 'S'

You should see all the system tables in your current database, and their object IDs. So you could try passing the id to the management view. Here I'll try it for syshobts, which has an ID of 15:

select * from
sys.dm_db_index_physical_stats(1, 15, null, null, 'detailed')

No results are returned. If I replace the 15 with null, to indicate I want all objects, I do not see any objects with IDs less than 100, which means there are no system objects reported.

I did notice one interesting behavior while playing around with this concept. If I use the old DBCC SHOWCONTIG and try to get a report for a system table, this command gives me an error:

dbcc showcontig ('syshobts')

Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "syshobts". Check the system catalog.

However, if I qualify the table name with the schema name, something different happens.

dbcc showcontig ('sys.syshobts')

This time, I don't get an error. I don't get a fragmentation report, but I don't get an error. I just get the message that DBCC execution completed. So it's obvious that there is a difference here. If I then intentionally misspell the object name

dbcc showcontig ('sys.syshobbits')

now I get the error that SQL Server cannot find the object. So it seems that in SQL Server 2005, DBCC SHOWCONTIG is deliberately filtering out the real system tables, and it was by design to not return fragmentation information. So even if you could run defrag on your system tables, you could never know what impact you had!

I think there's better ways to spend our time.

Have fun!

~Kalen

Geek City: HyperThreaded or Not?

Kalen Delaney — Sat, 08 Dec 2007 23:56:00 GMT

In almost all my classes, and every time I do any performance consulting, I get the question "How can I determine if our SQL Server is hyperthreaded?"

I was delighted when I found the DMV sys.dm_sys_info, which has a column called hyperthread_ratio. But soon after, I read a blog post from one of my favorite SQL Server bloggers, Buck Woody who indicated that the following query gives you the number of cores, but doesn't specifically tell you if the hyperthreading is enabled.

SELECT cpu_count/hyperthread_ratio AS sockets
FROM sys.dm_os_sys_info

Although he said this gives you the number of cores, that is not correct either, because one socket can contain a multi-core CPU. A computer with a single socket containing a dual-core CPU and no hyperthreading will have a cpu_count value of 2, and a hyperthread_ratio of 2, and a computer with a single socket single core CPU with hyperthreading will also have the same hyperthread_ratio. In both cases, dividing hyperthread_ratio by cpu_count will give 1, the number of sockets. If we have a single socket machine with a dual-core CPU and hyperthreading, cpu_count will be 4 and hyperthread_ratio will also be four, so the ratio again will be one. So the values for single socket possibilities are summarized here:

Number of Sockets	Number of Cores	Hyperthreaded?	sys.dm_os_info. cpu_count	sys.dm_os_info. hyperthread_ratio	cpu_count / hyperthread_ratio
1	2	NO	2	2	1
1	1	YES	2	2	1
1	2	YES	4	4	1

So even though Buck said his formula would show you the number of cores, the query itself tells the truth and the output column is named 'sockets'. The ratio of cpu_count to hyperthread_ratio shows the number of sockets, not the number of cores, and there is no way to tell the difference between hyperthreading and dual-core.

I have heard that this relationship and the values returned can be different on Vista or later operating systems, but since I am not running Vista, I have no way to test it out for myself. But maybe you can.

:-)

Have fun!

~Kalen

Did You Know? The Best SQL Server 2005 Feature

Kalen Delaney — Wed, 05 Dec 2007 05:44:00 GMT

This post really should have a topic of "Pet Peeve" but I really didn't want to sound quite so negative.

It seems like almost every time I see a clever solution in a newsgroup response, or in a blog post, about how to extract some very useful troubleshooting information using the Dynamic Management Views, someone will ask for an equivalent query in SQL Server 2000. It just happened again as I was doing a google search and came across a post by Kevin Kline right here on sqlblog about using the new metadata to get information about space usage in tempdb. It was actually referring to a query developed by Aaron Bertrand, who also posts here. Someone asked if there was a way to do the same thing in SQL 2005, but we assume they meant SQL 2000. Our own Adam Machanic responded that the CROSS APPLY operator was not available in SQL 2000, but that is the least of our worries. The query refers to seven different Dynamic Management Objects (6 views and a function), most of which have no equivalent in SQL Server 2000.

It just seems that some people seem to think that anything we can do in one version should be doable in a previous version. If that were true, why would you upgrade? Do you only upgrade for performance benefits? I don't think so.

In my opinion, the BEST SQL Server 2005 feature is the new system metadata. It is absolutely worth the price of admission. There are so many things that you couldn't even begin to find out about with the metadata from SQL 2000, and the only way to get that level of internal detail is to UPGRADE!

If you're running SQL 2005, the Dynamic Management Objects can provide hours and hours of fun and exciting explorations!

~Kalen