Confused by sp_who2 (Dear SQL DBA Episode 30)

This week’s ‘Dear SQL DBA’ question gets us down to the essentials Recently, when I was checking if there are any hanging transactions in my database via “sp_who2 ” procedure… A transaction is “AWAITING COMMAND” LastBatch date is more than a week ago ProgramName is “SQLAgent – Generic Refresher” Is the transaction hanging? Learn the […]


New online course: Troubleshooting Blocking & Deadlocks for Beginners (Free!)

I made y’all a SQL Server style Valentine’s day present: a new FREE online training course. Here’s a quick trailer: Course info: Troubleshooting Blocking & Deadlocks for Beginners You need to prove if blocking is killing the performance on your SQL Server. Learn to set up simple, lightweight monitoring using free tools and scripts to […]


Should I Learn Fulltext Indexing? (Dear SQL DBA Episode 29)

This week’s question is about a longstanding feature in SQL Server that sounds really cool: full-text search. If you’re learning performance tuning, how much time should you invest in researching and learning about full-text indexes? Watch this 18 minute video, or scroll on down to read the written scoop on full-text search. Dear SQL DBA… […]


Understanding Left vs. Right Partition Functions (with Diagrams)

You’re designing table partitioning, or you want to make a change to an existing partition function. It’s critical to understand the difference between how “left” and “right” partition functions behave, but the documentation is a bit confusing on this topic. Honestly, even after years of working with partitioning, it’s easy to get confused about left and […]


What’s that Garbage in my Execution Plan? (Dear SQL DBA Episode 27)

Today I was working on some code samples for a user question, and I hit a weird roadblock. There was a bunch of garbage in my execution plan that I couldn’t explain. And by ‘garbage’, I mean a nested loop to a whole branch of code that I hadn’t asked SQL Server to run — and […]


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 […]