SQL Server has amazing instrumentation. Dynamic management views and functions give you great insight into what’s happening in your SQL Server and what’s slowing it down. Interpreting these views and functions takes a lot of time, but with practice and skill you can use them to become great at performance tuning.
But nothing’s perfect. Some of these DMVs and DMFs have bugs or column names that can be misleading. If you take them at face value, you can end up with egg all over your face.
sys.dm_os_sys_info – hyperthread_ratio column
This DMV is great for quickly checking the last startup time of your SQL instance and finding out if it’s virtualized. But don’t trust sys.dm_os_sys_info to tell you whether or not hyperthreading is enabled on your processors.
The “hyperthread_ratio” column is simply an indication that you have multiple cores per processor and does NOT tell you whether or not they are using hyperthreading.
sys.dm_index_usage_stats – the whole concept of “usage”
This DMV is trying to tell you the truth, but almost nobody understands what it means. This DMV tells you how many times a query used an index in its execution plan– but it doesn’t tell you exactly how many times the index was accessed. Only the number of operators referencing it when the plan was run.
A simple example: I run a query that does a nested loop lookup to retrieve a value from the index PK_Address_AddressID. The query runs once, and in that one run it executes the lookup 30 times. This is counted as one “use” of PK_Address_AddressID, even though 30 lookups were executed.
If a single index appears more than once in an execution plan (due to a subquery, union, etc), then it’ll be counted once for each operator in the plan.
TLDR; index_usage_stats does NOT show you the number of “reads” or “writes” an index is really serving up.
sys.dm_exec_trigger_stats – execution_count and last_execution_time columns
Which triggers on your server are running the most and using the most resources? The sys.dm_exec_trigger_stats DMV seems like it’s perfect to answer that question, but beware.
There’s a bug where execution_count increments due to completely unrelated actions that wasn’t fixed until SQL Server 2012. (I’ve seen the info from this DMV be wonky on 2008R2, and I’ve validated I can’t reproduce this on SQL Server 2014, but I haven’t personally tested on 2012.)
Side note: isn’t it cool that Microsoft makes the Connect bug data public? I’m not sure that I ever would have figured out what contributes to inflating the execution counts on my own!
sys.sysindexes – Rowmodctr
It can sometimes be useful to estimate how many changes have occurred since statistics were last updated on a column or index. This gives you a quick way to guestimate if those stats are maybe not-so-fresh.
Good news on this one– the column isn’t perfectly true, but Books Online has a great rundown of its issues. It lets you know that SQL Server doesn’t really use this counter for its own purposes, and that it’s only roughly accurate at best.
And then it lets you know that SQL Server doesn’t expose column modification counters, so this maybe-pretty-close-guestimate counter is still perhaps better than nothing.
sys.dm_os_performance_counters
I’m a huge fan of SQL Server’s performance counters, wheter you’re querying them via sys.dm_os_performance_counters or looking at them with perfmon.exe.
But there’s a whole host of misleading and just plain outdated counters that will lead you astray. Learn which perf counters to beware in Jeremiah’s post on perfmon.
SQL Server’s Instrumentation is Great
And honestly, so is SQL Server’s documentation. The challenge is that there’s a massive amount to document– and Books Online can’t cover all the nuances of everything. Keep using those DMVs– just keep an eye out for the gotchas.
21 Comments. Leave new
Hey…. I like my Windows Phone…. can we filter out this row from sys.Lies? Add an “is_debatable” column?
At least it’s not Blackberry.
LOL– I was so torn between making a Windows Phone joke and a Siri joke. (I’m an Adroid user now.)
What’s an Adroid? 🙂
Windows Phone 8(WP8) OS Rocks.
LOL. If I get more fans I may have to make an editor’s correction on the article. 🙂
Please, do so! I can’t live without my Windows Phone anymore! 😉
Aside from the other bits of awesomeness in this article, thanks for the sys.dm_os_sys_info tip on uptime. I’ve used the tempdb create date before (an approach I -really- don’t like for whatever reason) and more recently used a powershell script that queried WMI for server (not instance) uptime, this is a handy way to access simply via T-SQL, specific to instances. It was right there all along, sqlserver_start_time. Grazie!
…as a brief continuation of my little rabbit trail, I noted this morning that sqlserver_start_time does not exist in the 2005 DMV, so for those of us supporting mixed environments still we may be stuck using crdate for tempdb, a little while longer.
I did notice (in 2005 and up) the ms_ticks column is a useful one…if you want to know when your server was booted rather than when the instance service last started, this is handy and can be computed as follows (note I convert to seconds because if you use milliseconds, you can overflow the second DATEADD() parameter (an int) if your server has been online for a while):
SELECT DATEADD(SECOND, ms_ticks*-0.001, GETDATE()) FROM sys.dm_os_sys_info
When I run this on my SQL Server 2012:
SELECT [server_start_time] = DATEADD(SECOND, ms_ticks*-0.001, GETDATE()), [sqlserver_start_time] FROM sys.dm_os_sys_info;
I get:
server_start_time sqlserver_start_time
2013-11-24 09:40:15.287 2013-11-24 08:59:15.707
BTW, I verified the server boot time using wmic OS GET CSName,LastBootUpTime, which returned 20131124090447.608969-300. I also used systeminfo | find “System Boot Time”, which returned 11/24/2013, 9:04:47 AM.
The formula you provided comes up with 9:40 am, not 9:04 am, so I think there is something wrong there.
Even if the server booted at 9:04, how can SQL Server have last started 5 minutes *before* the last time the server boot?
Hey now! I’m with Jeff and Sree, I love my Windows phone.
First of all, Apologize for taking this post Off topic…
Yep, Those Windows Phone 7 and 7.5/Mango, “No Apps” days are gone long ago. I switched from Android after using it two and a half years to WP8(Nokia Lumia 920) an year ago. WP8 is Much Much Robust and secure OS compared to any android OS out there in the market, and hey, all I am having is rock solid experience with my gorgeous Windows phone(Never had a Frozen screen or a lag till date which I used to have almost every week while using Android(I had both samsung and LG devices)).
IMHO…WP8 phones are Cool, cooler and the coolest.
I think you’re misinterpreting the index usage stats. The fact it only logs when a plan first touches an index is in my mind correct. Say you have a query that runs once a quarter and touches the index 300 times that could be misconstrued as 300 separate plans that have hit the index when that’s not the case. I’m pretty sure if you ask MS they’ll confirm its working as per the design
Regards Perry
Hey Perry,
I agree that the index usage stats DMV is working as designed. The issue is that the word “usage” implies something to people than what the DMV actually records, and Books Online isn’t very clear about how it actually works.
I’m not suggesting that the DMV needs to change its functionality, it can just be helpful for people to understand what it means by “usage”.
–Kendra
Windows Phone rules.
Another thing with sys.dm_db_index_usage_stats is that in 2012 it resets itself when you rebuild an index. So although they state that this is from the last time the database started, it’s not.
It’s documented as a bug that they won’t fix here, with Kendra being the first comment on it: http://connect.microsoft.com/SQLServer/feedback/details/739566/rebuilding-an-index-clears-stats-from-sys-dm-db-index-usage-stats
BTW, things not to talk about at work or professional blogs: Religion, Politics, Phone OS. Not to complain, but you asked for excessive comments here!!!
Kendra … great article. Thanks. BTW, even though aliens haven’t landed, I did dissuade alien leader Bahumba from invading. I introduced him to a triple Americano at Starbucks and he said he would return back to his planet XuXor until he was able to duplicate it. So techically, sys.Lies is still accurate. 🙂
@Steve Hood
that link details a different issue as follows
“Rebuilding an index clears any existing row entry from sys.dm_db_index_usage_stats for that index. This was not the behavior in SQL Server 2008. ”
The post above talks about index usage not issues with the usage stats affected by index rebuilds
Regards Perry
Perry,
Yes, the link I attached does speak about a different issue with the DMV returning different results than expected. I added this here as another way that this DMV doesn’t give you what you think it should, and not related to the issue of it only incrementing once for a use of any scale.
The intent of Kendra’s post here was to warn you about a DMV’s results varying from what a user may consider as obvious, in which case this is definitely pertinent.
Thanks,
Steve
Thanks Kendra !!
Great article to know finer destails
Regards
Neeraj
While my comment comes more than a year after this post, I thought others might like to know that the execution counter bug mentioned for dm_exec_trigger_stats is not confined to just this DMV. There are also similar problems with dm_exec_procedure_stats, though they’re much more elusive and harder to repeat. Below is a transcript of one occurrence, but it doesn’t happen reliably. I’ve not been able to narrow down the specific circumstances that make this repeatable.
C:\Users\smcintosh>sqlcmd -Uscott.mcintosh -PZZZZZZ -S tcp:XXXXXXX,PPPP -dDDDDDD -w 1000
1>
2> IF EXISTS (SELECT * FROM sys.objects WHERE type=’U’ AND schema_id=1 AND name=’SCOTT_1′)
3> DROP TABLE SCOTT_1
4> GO
1> CREATE TABLE SCOTT_1 (f int identity, g int not null)
2> GO
1>
2> IF EXISTS (SELECT * FROM sys.objects WHERE type=’P’ AND schema_id=1 AND name=’SCOTT_P’)
3> DROP PROCEDURE SCOTT_P
4> GO
1> CREATE PROCEDURE SCOTT_P AS
2> BEGIN
3> DECLARE @c int
4> SELECT @c=count(*) FROM sys.objects WHERE type=’U’ AND schema_id=1;
5> INSERT INTO SCOTT_1 (g) SELECT TOP 30 convert(int,rand()*100) FROM sysobjects WHERE type=’U’
6> END
7> GO
1>
2> EXEC SCOTT_P
3> GO
(30 rows affected)
1>
2> SELECT obj_name=convert(varchar(32),object_name(object_id,database_id)), execution_count FROM sys.dm_exec_procedure_stats WHERE object_name(object_id,database_id)=’SCOTT_P’
3> GO
obj_name execution_count
——————————– ——————–
SCOTT_P 6199
(1 rows affected)
1> SELECT rowcnt=count(*) FROM SCOTT_1
2> GO
rowcnt
———–
30
(1 rows affected)
1> select @@version
2> go
—————————————————————————————————————————————————-
Microsoft SQL Server 2008 R2 (SP2) – 10.50.4033.0 (X64)
Jul 9 2014 16:04:25
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
(1 rows affected)
1>