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.
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?
In A Previous Installment… our heroine (that’s me) rediscovered CTEs, specifically in the recursive style. That was in my post “Filling in Data Potholes with Recursive CTEs.”
SQLPass unfortunately can’t last forever, but happily it’s still going strong. Here’s some highlights from my Day #2.
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.
Sometimes tempdb is filling up. 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.
Update: improved/more recent version of queries for this are here.
It can be pretty difficult to manage statistics in data warehouses, or even OLTP databases that have very large tables.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.