Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working on reducing the amount of transactions doing key lookups on our SQL Servers.

First I want to define a baseline for the normal or tolerable number on that metric and as we constantly have releases in production, then, I want to create an automation based on it that notifies me when it goes south.

The problem that I am having is actually finding the right counter either on perfmon or a SQL DMV to get the data from.

I looked on the obvious object SQL Server, Access Methods Object yet, I could not find a specific one. I also tried other counters or SQL views but could not find it.

Any suggestions would be very appreciated!

share|improve this question

I don't know of a fully accurate and reliable way to track this.

One way to get at least something potentially useful is to keep snapshots of sys.dm_db_index_usage_stats, specifically the user_lookups column for index_id 0 (RID Lookup) and 1 (Key Lookup). This DMV can be reset for many reasons, including database or instance restarts. Rebuilding (but not reorganizing) and index also clears the related DMV entry on SQL Server 2012 and later, which can make things more difficult. You will need to capture information fairly regularly and use a heuristic to decide if the DMV reset between captures.

The index usage stats DMV also only returns a count of the number of times a plan containing a Lookup is executed. A plan that contains a single Key Lookup will increment the counter by 1, regardless of the number of lookups actually performed. It will also increment even if the Lookup is not executed at all.

The sys.dm_db_index_operational_stats DMV records the number of singleton lookups actually performed, but does not distinguish between singleton seeks on the index directly, and those that result from a Key or RID Lookup, so it is not useful for your purpose.

Singleton seeks are another name for Probe Scans as reported by the Access Methods Object. There is no way to distinguish between a 'normal' singleton seek on a unique index, and a singleton seek resulting from a Lookup. This means the Access Methods Probe Scans counter is not useful to you. The AM counters are very noisy anyway, and there's no way to correlate the counters with a particular index.

share|improve this answer

A good write-up by Michael J. Swart seems to indicate that a key lookup will count toward the Probe Scans/sec counter under the Access methods:

Key Lookup (Showplan Operator Documentation)

This operator is also known as a bookmark lookup operator. It always counts towards the Probe Scans/sec performance counter. It’s interesting that even though it gets a single record at a time, this operator is often seen as a symptom of poor performance. That’s because the number of executions can get big. Many executions can kill the performance of the query. If you focus on the performance counters, you’ll notice that each of these executions will count towards the Probe Scans performance counter.

Although using the performance counter is only going to give you a number. You would still have to dig into the plan cache to find the specific queries that have the key lookup. Which is possible.

Note: The link above to the showplan operator is what Michael linked to, but I happened to find the current documentation in MSDN that provides a bit more current information: Showplan Logical and Physical Operators Reference

share|improve this answer
2  
Unfortunately non-Lookup singleton seeks into a unique clustered index are also reported as Probe Scans, so this cannot be used to distinguish a Key/RID Lookup from an equality seek into a unique clustered index. – Paul White Jan 7 at 8:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.