Optimizing queries is the most fun when you don’t need to add indexes. There’s nothing quite so nice as finding a way to make reading data faster, without slowing down writes or creating new data structures that need to be maintained.
SQLPass unfortunately can’t last forever, but happily it’s still going strong. Here’s some highlights from my Day #2.
Update from Kendra (Nov 2018): I’m keeping this post for posterity, but instead of sp_who2, check out @AdamMachanic ’s sp_WhoIsActive, if you need a free tool to see what’s running in SQL Server.
I thought I would do a series of posts thinking through general troubleshooting steps I use for a wide variety of issues– everything from slow running performance, system crashes, or application failures where the source hasn’t been identified.
I needed to quickly report on what articles are being replicated from a database yesterday. It was helpful to include the size of the articles, number of rows, and the subscriber information.
The system tables make this pretty quick work.
There’s some pretty cool things about SSMS 2008– I am really pleased that execution plans open graphically by default now, and it’s easy to view the XML on demand. I had gotten pretty fast at saving things as .sqlplan, but it was a bit of a drag.
Update from Kendra (Nov 2018): I’m keeping this post for posterity, but I REALLY don’t recommend the script. You’d be much better off using a production monitoring tool that did this job, or @AdamMachanic ‘sĀ sp_WhoIsActive.
Maybe you’re a user in a reporting database running a long query in the read committed isolation level, merrily blocking a process which needs to load data.
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.