Verifying SQL Server Instance workload

As a DBA, you might be supporting different-different clients. Basically, you would be dealing with thousand of SQL Servers, and it is tough to remember which SQL Server is having what kind of workload – OLTP or DSS (aka DW). If your client reports to you performance issue on one of the SQL Servers instance, and he informs that the instance is having OLTP kind of workload. How would you make sure that what kind of workload the SQL Server is truly having before you start troubleshooting the issue?

There is a DMV called sys.dm_exec_query_optimizer_info which was introduced in SQL Server 2005. It returns detailed statistics about the operation of the SQL Server query optimizer. You can use this system catalog view to verify the workload of SQL Server and It also helps to identify any potential query optimization problems. The DMV returns three columns are as follows:

SELECT * FROM sys.dm_exec_query_optimizer_info

image4

  • counter – Name of optimizer statistics event.
  • occurrence – Number of occurrences of optimization event for this counter
  • value – Average property value per event occurrence.

Note: – The “occurrence” column values are cumulative and they are set to 0 at system restart. The “value” column values are set to NULL at system restart.

If you are going to use this DMV on various version of SQL Servers, you will find all the version have same output count except SQL Server 2005. In SQL Server 2005, It has 38 rows count whereas other versions have 39 rows count. The missing counter is “merge stmt” which was introduced in SQL Server 2008 onwards.

countofexecqueryoptimizer

Most of the information of the DMV is still hidden secrets as it is not documented by Microsoft. But, if you monitor a few optimizer statistics events very closely against different-different SQL Server instances, you will be able to relate which instance has OLTP workload and which has mixed workload. To simulate the scenario, I have done testing on three different machines and below are the results;

SELECT *

FROM sys.dm_exec_query_optimizer_info

WHERE [counter] IN ( ‘optimizations’, ‘trivial plan’‘search 0’‘search 1’, ‘search 2’ ); —optimizer statistics events.

OLTP Workload

oltp-system

Mixed Workload

mixed-workload-system

Based on my testing and understanding, I have summarized the meaning of the optimizer events below.

– optimizations:
As per the below referenced link, the value for optimizations event equals to the sum of the trivial plan, search 0, search 1, and search 2. When I verified it on many busy systems, I found that the most of time the optimizations event’s value was close to sum of the trivial plan, search 0, search 1, and search 2 but not equals to. You can calculate the above snapshot values or your production instance optimizer values to reach a conclusion.

– trivial plan:
Obvious plan – there is no reason for SQL server to optimize the statement.

– search 0:
Transaction Processing – it means serial plan and very low estimated subtree cost <0.2

– search 1:
Quick search of plan – explore more rules, & alternate join ordering. If the best (cheapest) plan costs < 1.0

– search 2:
 Full Optimization  – explore all options, and opt for the cheapest plan after a limited number of explorations.

Conclusion:

When you see high values for the events “trivial plan”, “search 0”, and “search 1” on a SQL Server instance, It means the instance has OLTP bound of workload. When you see pretty high values for all four events “trivial plan”, “search 0”, “search 1”, and “search 2”, It means the instance has mixed workload.

Hope, you enjoyed learning a not much explored DMV!

Reference Link:
Professional SQL Server 2012 Internals and Troubleshooting

One thought on “Verifying SQL Server Instance workload

  • Narendra

    Good

    Reply

Leave a comment

Your email address will not be published. Required fields are marked *