Profiler: Deadlock Graph Event
Until recently I’d never used the deadlock graph event in Profiler. I’ve been pretty lucky and haven’t had to troubleshoot deadlocks much until now. The deadlock graph turns out to be quite nice!
Replication - Updates that Do Not Flow Through
This past week at work we found an instance where a replicated table (transactional push) was out of sync on the subscriber. In evaluating how to address the situation, we did some testing in pre-production and discovered the following (using profiler).
Finding Plans and Stats for Queries Based on Wildcard Text Matching
I often need to find a query plan in the cache for a process that has run long overnight. Typically I’ll be able to figure out from our logging some of the tables involved in the query. Sometimes I will have most of the executing text but won’t know exactly what dates or reference points were included.
A Script to Summarize Blocking
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.
Index Usage Statistics with ColumnList and Index Size
As an add on to my last post, here is what I currently do use to track index usage. This shows usage, columns in the index, and index size on disk. The size can be quite useful to know when evaluating how much an index is worth– typically if this index is large then you’re paying a fair amount on the inserts.
How Stale are my Statistics?
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.
Checking Permissions on Linked Servers
One reason I started this blog was just the idea of going through my catalog of scripts and reviewing them and sharing out what might be useful to people.
Here is a quick one I put together a while back. I was starting to work with a group of servers [at an unnamed company, always an unnamed company!]. Some of the instances had been configured long ago, and I found some linked servers where passwords had been hardcoded into the login mappings.
Everything About Your Indexes (well, almost)
I am going to post my monstrously big index query.
Why? Because it’s AWESOME. No really, it actually is awesome. At least, if you like that sort of thing. I use some variant of this almost daily, and I tweak it fairly regularly to suit the needs of whatever I’m working on. So it’s a work in progress, but I find it constantly valuable.
A Table Summarizing All Agent Jobs with Steps...
Each time I work with a new system, it can take a while to familiarize myself with what all the SQL Server Agent jobs do. Often there are quite a few jobs, and sometimes they have legacy names that either don’t describe what the job does very well anymore, or is just hard to understand.
SQL Agent Jobs: Checking for failed steps at the end of a job
I use the SQL agent a lot, and it is handy for a lot of things, but it can be frustrating to not be able to pass state information between steps.