Incredibly, SQL Server has so much information to share with us. Every single day, I am amazed with this SQL Server technology. Sometimes I find several interesting information by just querying few of the DMV. And when I present this info in front of my client during performance tuning consultancy, they are surprised with my findings. Today, I am going to share one of the hidden gems of DMV with you, the one which I frequently use to understand what’s going on under the hood of SQL Server.

SQL Server keeps the record of most of the operations of the Query Optimizer. We can learn many interesting details about the optimizer which can be utilized to improve the performance of server.

SELECT *
FROM sys.dm_exec_query_optimizer_info
WHERE counter IN ('optimizations', 'elapsed time','final cost',
'insert stmt','delete stmt','update stmt',
'merge stmt','contains subquery','tables',
'hints','order hint','join hint',
'view reference','remote query','maximum DOP',
'maximum recursion level','indexed views loaded',
'indexed views matched','indexed views used',
'indexed views updated','dynamic cursor request',
'fast forward cursor request')


All occurrence values are cumulative and are set to 0 at system restart. All values for value fields are set to NULL at system restart. I have removed a few of the internal counters from the script above, and kept only documented details. Let us check the result of the above query.

As you can see, there is so much vital information that is revealed in above query. I can easily say so many things about how many times Optimizer was triggered and what the average time taken by it to optimize my queries was. Additionally, I can also determine how many times update, insert or delete statements were optimized.

I was able to quickly figure out that my client was overusing the Query Hints using this dynamic management view. If you have been reading my blog, I am sure you are aware of my series related to SQL Server Views SQL SERVER – The Limitations of the Views – Eleven and more…. With this, I can take a quick look and figure out how many times Views were used in various solutions within the query.

Moreover, you can easily know what fraction of the optimizations has been involved in tuning server.

For example, the following query would tell me, in total optimizations, what the fraction of time View was “reference“. As this View also includes system Views and DMVs, the number is a bit higher on my machine.

SELECT (SELECT CAST (occurrence AS FLOAT)
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'view reference') /
(SELECT CAST (occurrence AS FLOAT)
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')
AS ViewReferencedFraction


Reference : Pinal Dave (http://blog.SQLAuthority.com)

Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL DMV, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology