Tracking Compiles with Extended Events

This post comes from a question by Tara Kizer on DBA StackExchange related to Compilations/Sec in PerfMon and using Extended Events to try and track the compilations that were happening in SQL Server for one of their clients.  There are a number of events in Extended Events that relate to plans in SQL Server, and […]

Implicit Conversions that cause Index Scans

Implicit conversions seem to be a growing trend in the SQL Server performance tuning work that I’ve been engaged in recently, and I’ve blogged in the past about ways to identify when implicit conversions are occurring using the plan cache. For those of you who don’t know, implicit conversions occur whenever data with two different […]

Finding Key Lookups inside the Plan Cache

This is actually a blog post I thought I’d written more than two years ago, but this morning when I went looking for it after receiving a question by email  I realized that I’ve never blogged about this before.  At PASS Summit 2010 I presented a session on performance tuning SQL Server by digging into […]

Identifying High Compile Time Statements from the Plan Cache

If you don’t already know it, I love query the plan cache in SQL Server to identify problems that might exist in a server.  I’ve blogged a number of ways to use plan cache data to identify and diagnose problems the last few years: Finding what queries in the plan cache use a specific index […]

Finding what queries in the plan cache use a specific index

In the last 48 hours I have seen two different people having the exact same problem so I thought that I would go about blogging some code I’ve had lying around for a while and been meaning to blog that would help them with finding the root cause of their problems.  In both cases, the […]

Tuning ‘cost threshold for parallelism’ from the Plan Cache

A good while ago I was asked if it was possible to consistently produce a parallel plan by Tom LaRock(blog/twitter), and I recalled from reading Grant Fritchey’s(blog/twitter) book on Execution Plans that it was possible to do this by adjusting the ‘cost threshold for parallelism’ sp_configure option from the default of five to a value […]

Finding Implicit Column Conversions in the Plan Cache

Last year Tibor Karaszi posted a blog post titled Match Those Types that talked about implicit data type conversions associated with mismatched data types between filter criteria and table column data types.  His blog post does a good job of covering the topic so I am not going to rehash it out here. However, at […]

Digging into the SQL Plan Cache: Finding Missing Indexes

This is one of those topics I planned to blog about a long time ago, but never actually got around to it, and consequently I keep having to hit search engines to find the code I provided in a forums post when I need to use it again.  A while back, I helped fellow SQL […]