July 26, 2017 by Kenneth Fisher
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)', 'NVARCHAR(255)'), EVENTDATA().value('(/EVENT_INSTANCE/ObjectId)', 'INT'), EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)', '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.