Tribal SQL

I am honored to announce that a chapter I wrote has been published in a book. Nope – it’s not the additional Appendix that I sent the author of Everyone Poops, but is instead Tribal SQL. Edited and produced by the infamous Jen McCown and published by Red Gate the books is on sale now.

If you are attending PASS Summit you can pick up a free copy at the Red Gate booth October 16th 6 – 8PM, and you can meet some of the authors Friday October 18th at 10:45AM.

My chapter carries the title Guerrilla Project Management for DBAs and offers advice on how a DBA can use some simple techniques to better communicate what they do, how effective they are, and why they should be given huge raises.

Here is an excerpt from the book:

A DBA’s Place in the Organization

Organizational skills matter more to a DBA than many other technical jobs due to the diverse nature of their workload. In addition, this diversity exposes the DBA to a greater surface area of the organization than is typical for most technical roles. As part of our jobs, we talk to many people:

  • Vendor’s technical resources – mainly to ask why they need ‘sa’ access, but also to request updated pictures of them that are coincidentally the size of a dartboard
  • Internal development resources – mainly to ask politely why they decided that a homegrown triple-nested cursor implementation of GROUP BY was a good idea, but also to enquire, for no particular reason at all, about their food allergies.
  • Business stakeholders – mainly to discuss capacity-planning decisions, but also to find out how to prevent an in-ear Bluetooth headset from affecting one’s golf swing.
  • Internal semi-technical resources (project managers, product managers, business analysts) – mainly to help with decisions about performance, capacity, and future goals for each product that needs database resources, but also to spread misinformation (“What, you didn’t know that using the first column in Excel has been outlawed as part of the Patriot Act?”)
  • Internal IT resources – mainly to work with them in configuring and securing the hardware and software platforms on which the DBMS infrastructure depends, but also to trade Magic: The Gathering playing cards
  • Management resources – to complain about all the above people

Since we talk to so many people, we are at higher risk of finding ourselves in the cross hairs of the blame gun.

Reasons you should buy this book:

  1. All author royalties go the the worthy charity Computers 4 Africa.
  2. There are many other great chapters from my fellow authors including:
    • What changed? Auditing solutions in SQL Server
    • Agile Database Development
    • Verifying backups using statistical sampling
    • Taming Transactional Replication
    • SQL Injection: How it Works and How to Thwart it
    • Building Better Reports

Hit it up Tribal SQL on Amazon to purchase:


5 minute book review: The Art of SQL

I have a simple algorithm for picking books: I buy until my wife tells me I have to sleep outside I try to read books that take a practical and interesting approach.

For technical books about databases the quality is really uneven. You have your query tuning books which are good overviews of how DBMS X query engine works, some examples of the different selectors, and some practical advice followed by some vendor copy about how the next version of DBMS X will solve everything including your inability to feel as if you deserve true love. Other books are theoretical modeling exercises that walk you through how to build an order system so that it makes a college professor stroke his beard and half-smile.

The Art of SQL is a combination query tuning/performance book, modeling book, and practical advice book. Think of it as Design Patterns for SQL – it could have easily been called the title of one of its chapters: “The Nine Situations” as it goes over common situations you will face in getting a relational database to run quickly. The book is written in the “Art of War” style and uses (surprisingly effective) war metaphors throughout the chapters – it calls performance monitoring tools the “employment of spies” and begins each chapter with a war quote that also applies to database management (In a chapter on planning: “It is the first step that reveals genius in all wars”)

This book isn’t vendor specific but still has practical examples including some advice on how to handle SQL generated by code (with a realistic PHP/MySQL example – name another database book with non-ironic PHP in it). Reading through this book was sort of painful for me since it talked about things that you should avoid which I did not in any way avoid in the past and offered practical advice on dealing with things that I had run around like a man in a padded room before discovering.  Just look at these chapters:

  • Representing Trees in a SQL Database
  • Distributed Systems
  • To Be or Not to Be, or to Be Null
  • The Difficulties of Historical Data
  • Considering Indexes as Data Repositories
  • Holy Simplicity

I’d recommend The Art of SQL as a good “second book” for developers looking to understand databases, why DBAs don’t like NULLs, why linked servers should be avoided, why your code to generate SQL makes DBAs beards turn white overnight, and how to best plan out your database structure.

 

Out of space error when moving tempdb

Quirk in SQL Server to do with sizing tempdb that I ran into today: SQL Server checks the current location when you move tempdb to see if there is enough space not the new location.

From: Configuring Database Files for Optimal Performance

The below script will move TempDB from its current location to a folder on the T drive. Change the drive letter and folder location to suit your system. The script only uses a 1gb file size because of an odd behavior in SQL Server that checks the current file location to see if there”s enough space – instead of checking the new file location. If the user specifies a 100gb TempDB data file on the T drive (which does have 110gb of free space), SQL Server checks the current location (C) for 100gb of free space. If that space doesn”t exist, the script will fail. Therefore, use a small 1gb file size first, then after SQL Server restarts, alter the file to be the full desired size.

Triggers Part 3: FAQ and FOP Continued

This is part of a 3 part epic mini-series on triggers, view Part 1 and Part 2.

How many triggers should you have per table?

Ideally zero.  If you have any then there should be one.  There is no guarantee on the ordering of trigger firings, they normally fire based on their age – newly-added triggers fire last.  So if you have two triggers that both run on update, you could get into a recursion situation.  If you do have more than one, you have to set the trigger order via a call to sp_settriggerorder to avoid this recursion, or rewrite the trigger

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Test trigger recursion
IF object_id('TestTriggerRecursion', N'U') > 0
	DROP TABLE TestTriggerRecursion
IF object_id('tr_TestTriggerRecursionUpdatedDate', N'TR') > 0
	DROP TRIGGER dbo.tr_TestTriggerRecursionUpdatedDate
IF object_id('tr_TestTriggerRecursionUpdatedBy', N'TR') > 0
	DROP TRIGGER dbo.tr_TestTriggerRecursionUpdatedBy
GO
 
CREATE TABLE TestTriggerRecursion
(
	KeyID INT PRIMARY KEY clustered,
	Payload VARCHAR(MAX) NOT NULL,
	LastUpdatedDate datetime NULL,
	LastUpdatedBy sysname NULL
)
GO
 
CREATE TRIGGER dbo.tr_TestTriggerRecursionUpdatedDate ON dbo.TestTriggerRecursion
FOR INSERT, UPDATE
AS
	print 'tr_TestTriggerRecursionUpdatedDate ran'
	UPDATE t SET LastUpdatedDate = getdate()
	FROM INSERTED i
	JOIN TestTriggerRecursion t ON t.KeyID = i.KeyID
GO
 
CREATE TRIGGER dbo.tr_TestTriggerRecursionMaintainAuditField ON dbo.TestTriggerRecursion
FOR INSERT, UPDATE
AS
	print 'tr_TestTriggerRecursionUpdatedBy ran'
	UPDATE t SET LastUpdatedBy = SUSER_NAME()
	FROM INSERTED i
	JOIN TestTriggerRecursion t ON t.KeyID = i.KeyID
GO
 
-- delete from TestTriggerRecursion
INSERT INTO TestTriggerRecursion VALUES (1, 'test1', NULL, NULL)
INSERT INTO TestTriggerRecursion VALUES (2, 'test2', NULL, NULL)
INSERT INTO TestTriggerRecursion VALUES (3, 'test3', NULL, NULL)
SELECT * FROM TestTriggerRecursion
 
-- fails with
/*(0 row(s) affected)Msg 217, Level 16, State 1, Procedure tr_TestTriggerRecursionUpdatedBy, Line 6Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).*/
 
-- exec sp_settriggerorder @triggername = 'tr_TestTriggerRecursionUpdatedBy', @order='First'

Do triggers try to run ON UPDATE even if now rows have been affected?

Yes, which is why you should always bail if no rows are affected like so: IF @@ROWCOUNT = 0 RETURN As you see below, your entire trigger will fire even on a failed update.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Test trigger update behavior with zero rows
IF OBJECT_ID('TestTriggerUpdateBehavior', N'U') > 0
	DROP TABLE TestTriggerUpdateBehavior
IF OBJECT_ID('tr_TestTriggerUpdateBehaviorUpdatedDate', N'TR') > 0
	DROP TRIGGER dbo.tr_TestTriggerUpdateBehaviorUpdatedDate 
 
CREATE TABLE TestTriggerUpdateBehavior
(
	KeyID INT PRIMARY KEY CLUSTERED,
	Payload VARCHAR(MAX) NOT NULL,
	LastUpdatedDate DATETIME NULL,
	LastUpdatedBy SYSNAME NULL
)
GO 
 
CREATE TRIGGER dbo.tr_TestTriggerUpdateBehaviorUpdatedDate ON dbo.TestTriggerUpdateBehavior
FOR UPDATE
AS
	PRINT 'tr_TestTriggerUpdateBehaviorUpdatedDate ran'
	UPDATE t SET LastUpdatedDate = GETDATE()
	FROM INSERTED i
	JOIN TestTriggerUpdateBehavior t ON t.KeyID = i.KeyID
GO 
 
INSERT INTO TestTriggerUpdateBehavior VALUES (1, 'test1', NULL, NULL)
UPDATE TestTriggerUpdateBehavior SET Payload = 'testImpossibleUpdate'
WHERE 1 = 2 
 
SELECT * FROM TestTriggerUpdateBehavior

So, when should you use triggers?
You should use triggers:

  • When you have a clear understanding of how they work
  • You have no other option
  • You have performance tested your code thoroughly
  • You have informed your storage folks, DBA folks, and your mother
  • You have prayed about it

In all honesty I”ve only seen a few clean uses for triggers:

  • audit trigger: Audit mechanism for straight up insert/delete/update calls.  You have tableA, and you want to log all changes to auditTableA – a “copy trigger” does this quite well.
  • refactor trigger: A temporary bridge between two phases of a database refactor project.  You are migrating data from schema A to schema B, but that last pesty bit of code hasn”t been changed.  This release changed 80% of the code and put in a trigger to maintain the data or log to new tables for later testing the remaining 20%.  The discipline required to push through the 20% and remove the trigger is rare, so this is sometimes dangerous.
  • trap trigger: In a crisis, log where updates are coming from to a specific table.  Remove trigger quickly thereafter.
  • evil trigger: A trigger created for evil.

Where can I find more information about triggers?

Books online: Triggers

Triggers Part 2: Facts and Frequently-Occuring-Problems (FOP)

This is part of a 3 part epic mini-series on triggers, view Part 1 and Part 3.

When are triggers fired, and how many times?

For your standard missionary position trigger, they are fired once per batch. So if you have an update statement that affects 57 rows, then the DML AFTER trigger fires *once* but the deleted and inserted magic tables have 57 rows in them. One common anti-pattern is not handling multiple values in the inserted/deleted tables. Code to prove the firing behavior of triggers:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- Test trigger firing cardinality
IF OBJECT_ID('TestTriggerFiring', N'U') > 0
	DROP TABLE TestTriggerFiring
IF OBJECT_ID('TestTriggerAudit', N'U') > 0
	DROP TABLE TestTriggerAudit
IF OBJECT_ID('tr_TestTriggerFiring', N'TR') > 0
	DROP TRIGGER dbo.tr_TestTriggerFiring
 
CREATE TABLE TestTriggerFiring
(
	KeyID INT PRIMARY KEY CLUSTERED,
	Payload VARCHAR(MAX) NOT NULL,
	LastUpdatedDate DATETIME NULL,
	LastUpdatedBy sysname NULL
)
 
CREATE TABLE TestTriggerAudit
(
	TriggerName SYSNAME,
	DateFired DATETIME NOT NULL,
	ActionType VARCHAR(10) NOT NULL,
	KeyID INT NOT NULL,
	Stamp UNIQUEIDENTIFIER
)
GO
 
CREATE TRIGGER dbo.tr_TestTriggerFiring ON dbo.TestTriggerFiring
FOR INSERT, UPDATE
AS
 
DECLARE @MyGuid uniqueidentifierset @MyGuid = NEWID()
 
INSERT INTO TestTriggerAudit (TriggerName, DateFired, ActionType, KeyID, Stamp)
SELECT 'tr_TestTriggerFiring', GETDATE(), 'inserted', i.KeyID, @MyGuid
FROM INSERTED i
GO
 
/*
DELETE FROM TestTriggerFiring
DELETE FROM  TestTriggerAudit
*/
 
INSERT INTO TestTriggerFiring
SELECT TOP 3 object_id, name, NULL, NULL
FROM sys.objects
GO
 
INSERT INTO TestTriggerFiring VALUES (-1, 'test4', NULL, NULL)
WAITFOR DELAY '00:00:001'
GO
 
INSERT INTO TestTriggerFiring VALUES (-2, 'test5', NULL, NULL)
WAITFOR DELAY '00:00:001'
GO
 
INSERT INTO TestTriggerFiring VALUES (-3, 'test6', NULL, NULL)
WAITFOR DELAY '00:00:001'
GO
 
SELECT * FROM TestTriggerAudit

What are the inserted/deleted magic tables? How do they work?

The inserted and deleted tables hold the date being changed. Depending on what you are doing they hold different data:

Insert

Inserted – holds the new data

Deleted – is empty

Update

Inserted – holds new data

Deleted – holds old data

Delete

Deleted – data being deleted

Inserted – is empty

The inserted and deleted tables are not indexed, so take care in querying them in the wrong way. Given the logic above, if possible it is normally cleaner to not combine an insert/update trigger. If you have to, the below if my template for doing so:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
IF OBJECT_ID('tr_Example_Update', 'TR') > 0
	DROP TRIGGER dbo.tr_Example_Update
GO
 
CREATE TRIGGER dbo.tr_Example_Update ON dbo.Test
FOR UPDATE, INSERT
AS 
 
IF @@ROWCOUNT = 0
RETURN
 
SET NOCOUNT ON
 
IF UPDATE(Field1) OR UPDATE(Field2)
BEGIN
	-- Update
	IF (SELECT COUNT(1) FROM DELETED) > 0
	BEGIN
		PRINT 'Update logic'
	END
	ELSE
	BEGIN
		PRINT 'Insert logic'
	END
END
 
SET NOCOUNT OFF
 
GO

Are triggers fired on bulk insert?

No by default, but you can turn them on: Controlling Trigger Execution When Bulk Importing Data

Tune in for Triggers Part 3: FAQ and FOP continued

Triggers Part 1: Introduction to madness, plus whores

This is part of a 3 part epic mini-series on triggers, view Part 2 and Part 3.

According to the standard developer canon about databases you should avoid triggers like you avoid wearing your “I love goto statements” t-shirt at the company Christmas party (again).  Knowledge of triggers is verboten, almost as bad as saying you love cursors.  Why are they such a bad idea, and why does the DBA let the air out of your Oldsmobile Firenza tires whenever you write one?  Let’s explore these ideas, and leave you being grown up and driving a Oldsmobile for another day. First, a brief overview of triggers if you aren’t familiar with them at all (lucky):

Triggers are blocks of T-SQL that are run upon the firing of certain events within SQL Server, the most common one being an insert, update, or delete to a rows in a table. They can also fire upon login, the creation of tables, and other system events. Triggers have certain constraints that they have to live with such as not being able to return results or perform certain operations such as CREATE DATABASE, and they are fed only certain input – @@ROWCOUNT, the inserted and deleted tables, the ambient transaction. They are typically complained at and about by DBAs and developers as being slow and hard to deal with.

Where do triggers come from?

When a whore and a demon love each other they make a baby, and that baby is a trigger with a cursor in it that handles it own transactions and savepoints whilst trying to send email via a call to the smtp service using xp_cmdshell.

Are triggers in the original relational model?

No, but neither are isolation levels, recovery models, or stored procedures. But never fear, very little that you have heard of is in the original relational model unless you married your sister and eat German chocolate bars in which case you are familiar with joined relations and relvars.

Why are triggers given such a bad name – aren’t they just a certain type of stored procedure that happens at a certain time?

Triggers are given a bad name for a few reasons:

What they are typically used for:

They are quite powerful and it is easy to overuse them for things that are normally better supported by the DBMS in other places. The classic example of this is constraint or foreign key enforcement – a trigger can stop the insert/update from occurring or check the updated data right before the modification. Business logic can live in a trigger, so that it runs “just in time”. Another common pattern is to use a trigger to keep two separate databases in sync – every update to one causes an insert into another across linked server, etc. Triggers are dark-alleyed shortcuts and there are normally better paths to build the functionality you want.

How hard they are to work with:

Most developers don’t know how to properly code a trigger – there I said it.  In addition, they are added T-SQL surface area that doesn’t show up in most tracing mechanisms for laymen (Activity Monitor, syscomments, indirectly in execution plans, a lot of source control systems, etc).

They are slow:

Triggers are inside a transaction doing an insert, update, delete.  This is the essential core of the database system – very close to the DBMS kernel in terms of your application usage. Adding a trigger that takes half a second can bring your system to a messy halt.  In addition, in the past triggers populated the inserted and deleted tables via building a view from reading the transaction log.  This lead many DBAs to see non sequential behavior in their storage mechanisms that were built for sequential writes leading to even greater slowdowns.  As of SQL Server 2005 triggers are implemented via row versioning which thus makes use of the tempdb instead of reading the log, but DBAs never forget.  In either event, the behavior of triggers is simply different than other types of statements, leading DBAs to be even more unhappy – and they are pretty much unhappy in DisneyWorld.

Tune in for Triggers Part 2 Facts and Frequently-Occuring-Problems (FOP)

Running SQL cross servers

If you work with more than one database and have the constant need to run the same query across multiple servers, the undocumented stored procedure sp_MSforeachdb can be useful; see below example comparing stored procedures across servers using a simple checksum to tell if they are the same exact contents. sp_msforeachtable is another oldie but goodie that can be used for do an action per table or examine table properties easily – such as examining table properties that are not exposed from sys.tables.If you are using SQL Server 2008 there is a more fully supported way to do this and many other nice admin features: Multiple Server Query Execution in SQL Server 2008. Example of sp_MSforeachdb to test if stored procedure contents are exactly the same across databases:

1
2
3
4
5
6
DECLARE @SQL VARCHAR(MAX), @procname sysnameset
@procname = 'cspExampleList'
SET @SQL = ' use ? select db_name(), sp.name, checksum(sc.text) from sys.procedures sp join syscomments sc on sc.id = sp.object_id where name = 'SET @SQL = @SQL + '''' + @procname + ''''-- select @sqlcreate table #Results (dbname varchar(max), procname varchar(max), chksum int)
INSERT INTO #Resultsexec sp_MSforeachdb @SQL
SELECT * FROM #Results
DROP TABLE #Results