Since it’s Halloween I thought I would write about another SQL Server horror story. In fact, I based this on the morning after you resolve a zombie spid issue.
Which I discussed in my last Halloween themed post last year, and you can read in detail here.
Some of you who have attended one of my sessions previously as it’s based on something that can potentially happen. So, here we go.
If it helps, to start you can imagine that after you resolved a zombie spid issue by restarting the SQL Service that you go home.
Morning after the last horror story
However, the very next day you walk into the office to be told there are reports of slowness. On the very same server you fixed the day before.
So, you ask one of your new colleagues what’s going on.
They explain to you that once upon a time Microsoft asked them to run sp_msforeachdb and sp_msforeachtable together. With an update statistics with fullscan statement included with them.
For the benefit of those who don’t realise this. It will update all of the statistics, in all of the tables, in all of the databases using the fullscan sample rate.
So, based on that previous experience with Microsoft one of your new colleagues has taken it upon themselves to run it again now.
As you can imagine, on a server with very large databases (VLDB’s), this could take some time to do. Especially if it is causing another SQL Server horror story.
Paranoia after the last horror story
You ask your new colleague to stop the statement from running. However, they suggest the two of you discuss this with your boss as there’s a level of paranoia about the server after yesterdays issues.
Luckily, you convince your boss it’s OK to stop the statement whilst it’s running. So your colleague stops it and everything goes back to normal.
Alternatives during this horror story
Now, there a couple of alternatives you could have done here. For example, you could have let your colleague finish running the statement and put up with all the complaints.
However, even if it had improved the performance of some queries the chances of this alone preventing a zombie spid is slim.
Of course, if you were new to the company it might have been a good option to have a quiet word with your new boss to avoid upsetting a new colleague.
However, your new colleague could find out out afterwards you had talked to your new boss. Which would also upset them.
Reasons why things done in SQL Server
Just remember when somebody does something that causes problems a lot of the time it’s because they think they are doing something for a good reason.
For instance, here a colleague was just doing something they were advised to do years ago. In fact, they were actually trying to help. Just like a lot of people do in horror stories.
I hope this reminds you to be very careful when running sp_msforeachdb and sp_msforeachtable together.
Remember also to check the SQL Server technical documentation online here for alternatives. Which appears to have been rebranded again.
It looks like this is the new name for SQL Server Documentation, and was previously known as Books Online. I’m guessing a SQL Server 2019 announcement is due soon.
Of course, if in doubt ask a colleague.
Final word about this SQL Server horror story
Well I hope you enjoyed me bringing along some imagination to todays halloween themed post.
Since last years post has become more popular I thought readers would enjoy another SQL Server horror story.
Like I said earlier, some of you will recognise this from one of my sessions which I recently presented at SQL Saturday Sofia. Which you can read about in detail here.