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.
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.