Slow Running System the Other Day

A couple days ago I was working with a client, they called me they needed desperate help about a query that was running really slow in their system. It was an insert, inserting into a good sized table but not millions of rows by any means.

We started looking at it and thinking, so why is your insert statements slow? What we learned from that was they had a bunch of triggers on it. So we looked at the triggers to see if that was the problem. And none of the triggers were the issue (that was what they suspected at first).

So, then I looked again and thought, well what’s blocking. When you do an insert to a table, it needs to get an exclusive lock in order to insert those rows into the table. But if you have another query running that’s a select, and has a lock on that, a shared lock, then the insert is unable to get the exclusive lock.

So in a normal situation where all your SELECT statements are running really quick, it’s not going to block your inserts. But this was a really long running query that was keeping the Insert from happening. So what we found was the problem was not at all with the insert statement, but it was the query that was blocking which was causing the trouble.

And we know in SQL Server blocking occurs, and it’s a good thing because it keeps your data consistent. But it’s a bad thing because it slows things down.

We now offer a way to track blocking queries! We offer a script and installation instructions which will guide you though setting up email alerting!

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!