SQL Server is a powerful technology that never ceases to amaze me. Every day, I discover new and interesting information by querying the Dynamic Management Views (DMV). These DMVs provide valuable insights into what’s happening under the hood of SQL Server, allowing us to optimize performance and improve overall efficiency.

One of the hidden gems of DMV that I frequently use is the sys.dm_exec_query_optimizer_info. This DMV keeps a record of most of the operations performed by the Query Optimizer, giving us access to a wealth of information that can be utilized to enhance server performance.

Let’s take a look at a sample query:

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' )

By executing this query, we can retrieve a plethora of vital information. For example, we can determine how many times the Optimizer was triggered and the average time it took to optimize our queries. We can also identify the number of times update, insert, or delete statements were optimized.

One practical application of this DMV is identifying the overuse of Query Hints. By analyzing the results, I was able to quickly identify that a client was relying heavily on Query Hints, which can sometimes hinder performance rather than improve it.

If you’ve been following my blog, you might be familiar with my series on SQL Server Views and their limitations. With the help of sys.dm_exec_query_optimizer_info, I can easily determine how many times Views were used in various solutions within a query.

Furthermore, this DMV allows us to gauge the impact of optimizations on server tuning. For instance, we can calculate the fraction of time a View was referenced in total optimizations. This information can be valuable in understanding the role of Views in our database environment.

Here’s an example query that calculates the fraction of time a View was referenced:

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

It’s important to note that the sys.dm_exec_query_optimizer_info DMV includes both system Views and DMVs, so the numbers may vary depending on your specific environment.

By leveraging the power of DMVs like sys.dm_exec_query_optimizer_info, we can gain valuable insights into the inner workings of SQL Server and make informed decisions to optimize performance and improve overall efficiency.