sql-server-swap-meet

Why You Should Switch in Staging Tables Instead of Renaming Them (Dear SQL DBA Episode 26)

Over the years, I’ve come across a pattern fairly frequently: an application in an OLTP database periodically creates new tables, loads some data into them and fixes it up, then does a switcheroo and replaces old tables with the new tables. This can cause major problems with blocking if anyone else is querying the table. […]

tsql-xevents-histogram-target

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

cots-database-holidays

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

shift-key

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

baguettes-tempdb-files-france

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

matrix-95-percent-computed-columns

Columnstore Indexes and Computed Columns in SQL Server 2016

You can’t do everything with a columnstore index — but SQL Server’s optimizer can get pretty creative so it can use a columnstore index in ways you might not expect. You can’t put a computed column in a columnstore index If you try to create a nonclustered columnstore index on a computed column, you’ll get error message 35307: […]

deadlock-manager-sql-server

Tracing Deadlock Graphs: Extended Events or Server Side Trace

Deadlock graphs are incredibly helpful for figuring out why queries are getting automatically killed off by SQL Server. But it can be tricky to trace deadlocks in SQL Server. You might be tempted to use Profiler, but the application has lots of baggage. You’re better off using either Extended Events (which may be confusing), or a […]