execution plan

How to Find Queries Using an Index (and Queries Using Index Hints)

Sometimes you know a query is out there, but it’s hard to find the exact query. SQL Server stores query execution plans in cache, but it can be difficult to query the XML it stores. And there’s always a chance that the query plan won’t be there, due to memory pressure, recompile hints, or the plan cache being cleared by setting changes or other administrative actions. I can’t guarantee that your query can always be in the plan cache. But I can make it a bit easier to find the query you’re looking for. In this post I give example code to find queries using a specific index, or using an index hint. But you’ll find that it’s pretty easy to adapt these queries for whatever you’re looking for. How I like to Search the Plan Cache If I’m looking in SQL Server’s Execution Plan Cache, I like to use the sys.dm_exec_text_query_plan…
Read More

How to Tune Indexes for a Stored Procedure

You’ve got an important stored procedure that you think needs index help– but it runs in environment with lots of other queries. How do you focus in and discover exactly what indexes need tuning for that procedure? The best way to tune indexes in a stored procedure The best way is to run the stored procedure yourself to generate and save an “actual” execution plan, which contains the estimates SQL Server used when it generated the plan as well as actual rowcounts, actual memory granted, etc. It will also contain a green tooltip with a “missing index request” if SQL Server thinks an index would help. Tips on how to tune procedures with actual execution plans: If you execute the stored procedure from a free tool like SQL Sentry Plan Explorer, you can even save off the plans (which will have green missing index requests where SQL Server thinks they would help),…
Read More

Filling in Data Potholes Redux: Tally Tables vs CTEs

In A Previous Installment Re-Ducks … our heroine (that’s me) rediscovered CTEs, specifically in the recursive style. That was in my post “Filling in Data Potholes with Recursive CTEs.” To recap: I was working on a problem with gaps in temporal data. The basic scenario was: Imagine that you are writing a script that looks at data grouped by the minute. You notice that there are no rows for some minutes, and you’d like to display a value when that is the case, probably showing a count of zero. For the particular problem I was looking at, I was using small datasets and generating a list of all the valid dates with a recursive CTE performed well for me. From the Comments The best thing about blogging is not really sharing what you know: it’s getting to learn more. You get to learn from the process of writing the blog,…
Read More

SQLPASS Day 2- Optimization Timeouts and All about TLogs

SQLPass unfortunately can’t last forever, but happily it’s still going strong. Here’s some highlights from my Day #2. Paul Randal Knows Exactly What’s Going on in Your Transaction Log… A definite highlight of day 2 was attending Paul Randal‘s session on Logging and Recovery in SQL Server. I’ve read Pauls’ blog posts on this topic and attended his classes before, but even being familiar with the material I find I always learn something from his talks. You just can’t beat being strong on the basics! I took a lot of notes in the session, this is my favorite excerpt from my notes: SQL Server must reserve space in the TLOG so that it can roll back the active transactions, if needed. Once a VLF no longer contains log records that are required, it can be cleared This is done by a log backup in full or bulk_logged recovery models, or…
Read More

SQL PASS Day 1: To Free or Not To Free the Proc Cache?

Yesterday was day 1 of SQL PASS 2009. I am attending a variety of sessions on execution plans this year, and along the way I heard three very different opinions yesterday on managing the procedure cache in presentations. Rule of Thumb: The “it depends” answer is usually right. Opinion 1: Never Ever Clear the Proc Cache on a Production Server This first opinion came in a good, solid presentation on using execution plans for troubleshooting. There were some good examples of when you want sql to look at the statistics and trigger generating a new plan, and when you don’t. (AKA when parameter sniffing is a good or a bad thing.) But the speaker was wholeheartedly against clearing the proc cache in production. While I can definitely see this being true for some systems, I have definitely seen advantages of clearing the proc cache on others (more to come below),…
Read More

Who’s Using All that Space in tempdb, and What’s their Plan?

This post contains a script that I adapted from the fantastic SQL Server Storage Engine Blog.

It comes in handy in my job all the time! Sometimes tempdb is filling up, but sometimes I just want to monitor the amount of tempdb and check out execution plans of heavy tempdb users while watching performance on a server. It just really comes in handy more frequently than I would have thought before I started using it.

Read More