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


How to Query Histogram Target XML in Extended Events

When I was recently testing tempdb file usage, I used an Extended Events session that used SQL Server’s histogram target to track sqlserver.file_read events in the tempdb database for a specific session. I like using the histogram target because it’s relatively lightweight — you can “bucket” results by what you’re interested in. In my case, […]


Administering COTS databases (ISVs / Third Party Vendors)

I recently received this question from a reader… I just moved from an in-house software development company to a new environment that most of the software used here are COTS (Commercial off-the-shelf) This is totally new to me. I’m a little bit lost since I don’t know anything on the applications, users (security), or the […]


Configure Fields and Predicates for Multiple X-Events

I’m not always the best at learning to use graphical interfaces. Maybe that’s why I often don’t like them? I do rather like the Extended Events GUI — at least better than I like writing my own TSQL for Extended Events. And I finally figured out something that wasn’t obvious to me at all: it’s very […]


Parallelism and tempdb data file usage in SQL Server

I’m sometimes asked if the number of CPU cores used by a query determines the number of tempdb files that the query can use. Good news: even a single threaded query can use multiple tempdb data files. First, tempdb is a tricksy place! My first version of this post used a flawed methodology: I configured […]