Nov 12, 2014

Dear Diary,

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

Dear Diary,

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

Dear Diary,

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

Dear Diary,

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

Dear Diary,

(*#*&%^(#$(%)^#*@

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

Dear Diary,

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

Dear Diary,

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

Dear Diary,

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)[1]',  'NVARCHAR(255)') != 'HAL0002'
BEGIN
IF EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','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)[1]','nvarchar(max)') LIKE '%UNCOMMITTED%'
BEGIN
PRINT 'READ UNCOMMITTED is just as bad as NOLOCK.  No, just no.'
ROLLBACK
END
END
GO

Continued in Part 2

Filed under: DBA Humor, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: ddl triggers, Humor, microsoft sql server, T-SQL, triggers