Performance is bad. Did you change anything recently? No. Are you sure?

3

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)[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.

3 thoughts on “Performance is bad. Did you change anything recently? No. Are you sure?

  1. […] Kenneth Fisher has a simple database trigger to track certain data definition language events: […]

  2. Hiram says:

    You need the hostname to make it better.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: