Ever have this conversation?

Dev: Hey, can you help me? The performance on my application is terrible all of a sudden.
DBA: Sure thing. Has any code changed recently?
Dev: No. Nothing’s changed at all. Everything is just suddenly slower.
DBA: Are you sure?

Want to bet some code has changed? Note: I’m not saying this is the only reason why performance suddenly plummets. There are a number of possibilities. This is just one that, while it should be really obvious, frequently isn’t. Someone changed a piece of code, it’s suddenly taking longer and/or consuming tons of resources. It may even be blocking other tasks from running. But unless you have a single developer (and sometimes even then) it’s not easy to find out what’s changed. Particularly when you don’t have source control.

Ok, so what’s a solution? Well, how about a DDL trigger to log changes? I don’t want to replicate source control but just a list of who changed what, when.

-- Table to store the data
CREATE TABLE WhatsChanged (
id INT NOT NULL IDENTITY(1,1),
event_type sysname,
object_id int,
object_name sysname,
change_date datetime,
changed_by sysname,
host_name nvarchar(128)
);
GO
-- Make sure there won't be any problems inserting
-- into the logging table.
GRANT INSERT ON WhatsChanged TO public;
GO
CREATE TRIGGER tr_WhatsChanged
ON DATABASE
FOR DDL_PROCEDURE_EVENTS,
    DDL_FUNCTION_EVENTS,
    DDL_VIEW_EVENTS,
    DDL_TRIGGER_EVENTS,
    DDL_TABLE_EVENTS
AS
BEGIN
DECLARE @option int = @@options
IF ( (16 & @option) <> 16 ) 
SET ANSI_PADDING  ON
INSERT INTO WhatsChanged (event_type, object_id, object_name, change_date, changed_by)
VALUES (EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',  'NVARCHAR(255)'),
EVENTDATA().value('(/EVENT_INSTANCE/ObjectId)[1]',  'INT'),
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
getdate(),
ORIGINAL_LOGIN(),
HOST_NAME()
);
IF ( (16 & @option) <> 16 ) 
SET ANSI_PADDING  OFF
END

A couple of notes before testing the code. The event groups I’m using will pull CREATE, ALTER and DELETE events for those objects. For a more complete list of events (you might want to add service broker events for example) go here. Also I’m using ORIGINAL_LOGIN because it will return who made the change even if they are impersonating someone else.

For my test, I created a user that only has db_DDLADMIN on the database. That means it can make DDL changes but can’t insert, update, delete or even run a select against any table in the database. That’s why I grant INSERT to public for the logging table.

-- Connection created as test user
CREATE PROCEDURE ChangeTest AS
PRINT '1';
GO
ALTER PROCEDURE ChangeTest AS
PRINT '1';
GO
DROP PROCEDURE ChangeTest;
GO

And here are the contents of the WhatsChanged table.

Warning: If ansi_padding is off then the trigger will fail and so will the change. Of course I’m not sure I consider that a bad thing.

Note: I’ve made a small modification to check for ANSI_PADDING being off and if it was turn it on, then back off again at the end.

Filed under: Microsoft SQL Server Tagged: ddl triggers, microsoft sql server