February 16, 2015 by Kenneth Fisher
Nov 12, 2014
They asked for help again. Every now and again everything slows down and they see a lot of timeouts. They have tried rebooting the SQL instance and that fixes the problem but it’s only a temporary solution. Of course last time they asked for my help they never implemented my suggestions so I don’t hold out a lot of hope.
Nov 14, 2014
Great day! I found the problem! It even has a simple fix! (can you tell I’m excited?) Turns out there is a SP that runs hundreds of times a minute. Every now and again it’s getting compiled with a value that’s a bit skewed. It means the SP runs just a little bit slower but that makes all the difference. I suggested adding OPTIMIZE FOR UNKNOWN to the queries in the SP and it should fix the problem.
Nov 17, 2014
No word on the changes yet but I’m sure they will be done testing soon. It’s not like the changes can cause the data to be different. It shouldn’t be all that hard to test. I can’t wait. It will be awesome to not have to worry about this problem anymore.
Nov 28, 2014
Still no word on the code changes I suggested. Last time this happened they decided it was to much work to test and they wouldn’t bother.
Dec 18, 2014
I got a response back. And I quote “I tested it but didn’t get a performance improvement. Here are the changes we will put in.”
They ^#*$(% added NOLOCK to each of the table references.
Dec 19, 2014
Oh for the love of …. Not only did they add NOLOCK everywhere they already had SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of the SP. Good thing I’m going on vacation in the next few days and can cool off.
Jan 10, 2015
I just went back and looked and they are setting the isolation level to READ UNCOMMITTED at the beginning of 90% of the code in this @*#&$ database. I’m even finding NOLOCK on temporary tables! These people have absolutely no idea what they are doing.
Jan 20, 2015
This will fix them! From now on they won’t be able to create or alter any code with NOLOCK or UNCOMMITTED in them. The HAL 0001 was just for fun. But now, I give you .. The HAL 0002!
CREATE TRIGGER HAL0002 ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_FUNCTION, ALTER_FUNCTION, CREATE_VIEW, ALTER_VIEW, CREATE_TRIGGER, ALTER_TRIGGER AS IF EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)', 'NVARCHAR(255)') != 'HAL0002' BEGIN IF EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','nvarchar(max)') LIKE '%NOLOCK%' BEGIN PRINT 'No, you may not use NOLOCK. No, not even for that.' ROLLBACK END IF EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','nvarchar(max)') LIKE '%UNCOMMITTED%' BEGIN PRINT 'READ UNCOMMITTED is just as bad as NOLOCK. No, just no.' ROLLBACK END END GO