Validating Instance-Level Index View and MERGE Optimization Activity

You may have read the blog posts a few weeks ago regarding indexed views and MERGE statement usage and incorrect results. If not, please see the following blog posts:

I won’t rehash what they have collectively already covered thoroughly – but just a quick tip about identifying index view and MERGE optimization activity via the sys.dm_exec_query_optimizer_info DMV…  The following query shows counter name and occurrences of optimizations for MERGE statements and indexed views having been matched since the SQL Server instance last restarted:

SELECT  [counter],
FROM sys.[dm_exec_query_optimizer_info]
WHERE counter IN
('merge stmt',
'indexed views matched');

I see this as a “first cut” check – but there are some key limitations to why this would only be a starting data point and not the “end all, be all” approach:

  • As mentioned earlier, occurrences are as of the last restart of SQL Server – and you could still be impacted by the issue – but a specific workload may not have been executed yet
  • The counters are instance level – so you will not know which databases were specifically associated with the activity and whether this is an actual MERGE + indexed view combo as described in the aforementioned blog posts

Even with the limitations, if you see non-zero values for the counters, this might accelerate your investigation and application of the appropriate cumulative update.  I prefer keeping up with serious issues in this case, but if you need to prioritize what gets patched in larger environments with thousands of SQL Server instances, this may help drive that prioritization.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.