Get statistics on Query plan optimizer execution

As you might know PERFMON (SYSMON) and DMVs in SQL 2005 are very handy to know internal resource information about the SQL Server engine. So you might be thinking what might be the cause of this poor performance when you have a simple query to run and execution time differs on development platform as compared to production enviornment.

To go into details, the causes of these symptoms (poor performance & execution times) are not necessarily independent of each other. The poor choice of a query plan and over usage of system resources will cause an overall slowdown of the workload. Say for discussion if a large table is missing a useful index, or the query optimizer decides not to use it, this not only causes the query to slow down but it also puts heavy pressure on server. The consequences of this will be high pressure on I/O subsystem to read the unnecessary data pages and on the memory (buffer pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently running query can put pressure on the CPU and also this blog cause is applicable.

At the same time it will be a good option to find out how much time the SQL Server spends in optimizing a query that is performing poorly. You can take help of a DMV here in this case, Sys.dm_exec_query_optimizer_info. This DMV gets you a good idea of the time SQL Server spends optimizing.

Select * from sys.dm_exec_query_optimizer_info
where counter in ('optimizations','elapsed time','trivial plan','tables','insert stmt','update stmt','delete stmt')

The script above determines CPU resources that are required to optimize a query or plan. The above query gives total query plans, elapsed time on CPU and tables indicate average number of tables involved per query.You can also get the number of optimizations that are for INSERT/DELETE/UPDATE statements. Bear in mind the occurrence values are cumulative here and will only be set to 0 when a system restart occurs. For consistent information if you take two snapshots of this DMV, you can get a good feel for the time that is spent optimizing in the given time period.