Category Archives: T-SQL

SQL Server DML Triggers – Tips & Tricks – Part 1 (“Intro” & “Triggers activation order”)

This is the first blog post in a series of posts about Tips & Tricks of SQL Server DML Triggers.

Introduction

Triggers are often one of the main reason of quarrel between Developers and DBAs, between those who customize a database application and those who provides it. They are often the first database objects to be blamed when the performance degrades. They seem easy to write, but writing efficient Triggers is not easy. They have many side effects, but also a very important characteristic: they allow solving problems that cannot be managed in any other application layer.

A Trigger is a special type of stored procedure: it is not called directly, but it is activated on a certain event with special rights that allow you to access in-coming and out-coming data that are stored in special virtual tables called Inserted and Deleted. Triggers exist in SQL Server since the version 1.0, even before CHECK constraint. They always work in the same unit-of-work of the T-SQL statement that has called them. There are different types of Triggers: Logon Trigger, DDL Trigger and DML Trigger; the most known and used type is Data Manipulation Language Trigger, also known as DML Trigger. This series of posts treats only aspects related to DML Triggers.

There are many options that modify run time Triggers’ behavior, they are:

  1. Nested Triggers
  2. Disallowed results from Triggers
  3. Server Trigger recursion
  4. Recursive Triggers

Each of these options has, of course, a default value in respect to the best practices of Triggers development. The first three options are Server Level Options and you can change their default value using sp_configure system stored procedure, whereas the value of the last one can be set at the Database Level.

What do you think about Triggers? In your opinion, based on your experience, are they useful or damaging?

You will meet people who say: “Triggers are absolutely useful” and other people who say the opposite. Who is right? Reading the two bullet lists you will find the main reasons of the two different theory about Triggers.

People say that Triggers are useful because with them:

  • You can develop customize business logics without changing the user front-end or the Application code
  • You can develop an Auditing or Logging mechanism that could not be managed so efficiently in any other application layer

People say that Triggers are damaging because:

  • They are sneaky!
  • They can execute a very complex pieces of code silently!
  • They can degrade performance very much
  • Issues in Triggers are difficult to diagnose

As usual the truth is in the middle. I think that Triggers are a very useful tool that you could use when there are no other ways to implement a database solution as efficiently as a Trigger can do, but the author has to test them very well before the deployment on the production environment.

Triggers activation order

SQL Server has no limitation about the number of Triggers that you can define on a table, but you cannot create more than 2.147.483.647 objects per database; so that the total of Table, View, Stored Procedure, User-Defined Function, Trigger, Rule, Default and Constraint must be lower than, or equal to this number (that is the maximum number that will be represented by the integer data type).

Now, supposing that we have a table with multiple Triggers, all of them ready to fire on the same statement type, for example on the INSERT statement: “Have you ever asked yourself which is the exact activation order for those Triggers?” In other worlds, is it possible to guarantee a particular activation order?

The Production.Product table in the AdventureWorks2014 database has no Triggers by design. Let’s create, now, three DML Triggers on this table, all of them active for the same statement type: the INSERT statement. The goal of these Triggers is printing an output message that allows us to observe the exact activation order. The following piece of T-SQL code creates three sample DML AFTER INSERT Triggers on Production.Product table.

USE [AdventureWorks2014];
GO

-- Create Triggers on Production.Product
CREATE TRIGGER Production.TR_Product_INS_1 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_1';
GO

CREATE TRIGGER Production.TR_Product_INS_2 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_2';
GO

CREATE TRIGGER Production.TR_Product_INS_3 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_3';
GO

Let’s see all Triggers defined on Production.Product table, to achieve this task we will use the sp_helptrigger system stored procedure as shown in the following piece of T-SQL code.

USE [AdventureWorks2014];
GO

EXEC sp_helptrigger 'Production.Product';
GO

The output is shown in the following picture.

 

Picture 1 – All Triggers defined on Production.Product table

Now the question is: Which will be the activation order for these three Triggers? We can answer to this question executing the following INSERT statement on Production.Product table, when we execute it, all the DML INSERT Triggers fire.

USE [AdventureWorks2014];
GO

INSERT INTO Production.Product
(
  Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
  ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate,
  RowGUID, ModifiedDate
)
VALUES
(
  N'CityBike', N'CB-5381', 0, 0, 1000, 750, 0.0000, 0.0000, 0, GETDATE(),
  NEWID(), GETDATE()
);
GO

The output returned shows the default Triggers activation order.

Message from TR_Product_INS_1
Message from TR_Product_INS_2
Message from TR_Product_INS_3


As you can see in this example, Triggers activation order coincides with the creation order, but by design, Triggers activation order is undefined.

If you want to guarantee a particular activation order you have to use the sp_settriggerorder system stored procedure that allows you to set the activation of the first and of the last Trigger. This configuration can be applied to the Triggers of each statement (INSERT/UPDATE/DELETE). The following piece of code uses sp_settriggerorder system stored procedure to set the Production.TR_Product_INS_3 Trigger as the first one to fire when an INSERT statement is executed on Production.Product table.

USE [AdventureWorks2014];
GO

EXEC sp_settriggerorder
  @triggername = 'Production.TR_Product_INS_3'
  ,@order = 'First'
  ,@stmttype = 'INSERT';
GO

At the same way, you can set the last Trigger fire.

USE [AdventureWorks2014];
GO

EXEC sp_settriggerorder
  @triggername = 'Production.TR_Product_INS_2'
  ,@order = 'Last'
  ,@stmttype = 'INSERT';
GO

Let’s see the new Triggers activation order by executing another INSERT statement on Production.Product table.

USE [AdventureWorks2014];
GO

INSERT INTO Production.Product
(
  Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
  ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate,
  RowGUID, ModifiedDate
)
VALUES
(
  N'CityBike Pro', N'CB-5382', 0, 0, 1000, 750, 0.0000, 0.0000, 0, GETDATE(),
  NEWID(), GETDATE()
);
GO

The returned output shows our customized Triggers activation order.

Message from TR_Product_INS_3
Message from TR_Product_INS_1
Message from TR_Product_INS_2

In this first part you have learnt how to set the activation of the first and of the last Trigger in a multiple DML AFTER INSERT Triggers scenario. Probably, one question has come to your mind: “May I set only the first and the last Trigger?” The answer is: “Yes, currently you have the possibility to set only the first Trigger and only the last Trigger for each statement on a single table”; as a friend of mine says (he is a DBA): “You can set the activation only of the first and of the last Trigger because you should have three Triggers maximum for each statement on a single table! The sp_settriggerorder system stored procedure allows you to set the first and the last Trigger fires, so that the third one will be in the middle, between the first and the last”.

All examples in this post are based on AdventureWorks2014 database that you can download from codeplex website at this link.

How TOP wrecks performance (part 1)

The TOP keyword in the SELECT clause may not be ANSI standard, but I am sure we have all used it. But do you know that it comes with two optional keywords? And have you used them? They are PERCENT and WITH TIES. TOP Let’s first look at the based functionality of TOP, and how it affects performance – which in fact, contrary to the title of this post, is usually quite good for performance. Here is a simple example, using the AdventureWorks2012 sample database (which can be downloaded from Codeplex…(read more)

Database Mail … and then the SMTP Server changed

The database I inherited when I started my current job sends out lots of mails. It does so using Database Mail, and it has lots of mail accounts and mail profiles defined. I know that several of those profiles are used, I suspect that some are not, and I have no idea about the rest – one day I will find the time to clean up, but so far there have always been more pressing matters to attend to. But today the mail administrator told me that due to a change in architecture, SQL Server had to start using…(read more)

Geek City: Lost Identity

Way long ago, when Sybase first added the IDENTITY property, it took people quite a while to get the hang of using it. Along with being able to automatically generate sequential numbers for each row, we had to tune a configuration option with the geekiest of names: “Identity Burning Set Factor”. And figuring out how to use this value seemed to be one of the dark arts, so almost everyone left it at the default. (If you really want to know how to use the burning set, you can read the Sybase docs here…(read more)

Unexpected SPID change

A few time ago I had the opportunity to investigate about an unexpected SPID (Session Process ID) change. The inquiry began when a customer of ours starts to complain for locks, and lock time out error. These locks sometime were more frequent and sometimes not.

To investigate about this issue, I have taken two SQL Profiler trace files related of two execution of the Application functionality on which the customer has complained about the problem, with the same execution criteria. The only difference is that the first trace file was taken when the user complained locks and lock timeout error, while the second one was taken when no locks were occurred.

Comparing the two SQL Profiler trace files I have noticed an “unexpected” SPID change happened exactly when the Application has been locked; I have written “unexpected” because the Application uses always one connection to perform the queries captured by SQL Profiler. I have checked the piece of code that execute the queries shown in the following picture and I have verified no new connection was been opened explicitly.

The following picture shows the first execution in which you can see the unexpected SPID change.

Picture 1 – Execution with unexpected SPID change

Let me focus on the trace captured during the first execution. As you can see in the Picture 1, at certain point for the ClientProcessID number 192, there was been a SPID change from SPID number 111 to SPID number 110. The last query executed with SPID 110 has only the SP:StmtStarting event without SP:StmtCompleted event because this query was blocked from the previous SPID number 111 and for this reason the Application has been blocked.

The following picture shows the second execution.

Picture 2 – Execution without SPID change

Let me focus on the second execution of the same Application functionality, on the same client and with the same execution criteria, of course. As you can see in the picture 2 the SPID number is always the 68 for all queries performed in the same piece of code (as I expect).

Now the questions are: Which is the reason for this unexpected SPID change? Which are the conditions that force SQL Server to take the decision of changing the SPID number between two queries execution?

Talking about this issue with Erland Sommarskog he asked me “What API does the application use?” and my answer: “OLE DB”, so he replied me “We have the answer!”.

When you use OLE DB or something based on OLE DB and you perform a query on a connection which has not fully consumed the entire result-set of the previous, the default behavior is to open a new connection behind the scenes. This new connection will have a different SPID number, it is an attempt to be helpful, but it is not be ever helpful.

In particular, the result-set not fully consumed was in the last query executed by the SPID number 111, the same tables were been accessed from the last query with SPID 110 and then there was been the lock.

Thanks for the help Erland!

SQL Saturday #262 : Boston

Today I gave my T-SQL : Bad Habits & Best Practices session at SQL Saturday #262 at Microsoft in Cambridge. Fun event with a lot of friendly faces. A few mishaps along the way: had to wake up at 5:00 AM to take the only sensible commuter rail option to get me there on time; spilled, then proceeded to smear, bagel droppings all over my pants on said train; blew up the projector (literally!) pressing F5 during one of my demos; had to proceed with just hand-waving and a little interpretive dance…(read more)

Speaking this weekend at SQL Saturday 277 – Richmond

One of my favorite locations to speak or attend is when Richmond has a SQL Saturday . (though if you are an organizer of another SQL Saturday’s I have submitted to, note that I said “one of my favorites” :)). This will be the third time I go to Richmond. I like it for several reasons: The people – I have coworkers coming up from Virginia Beach to attend, and from Maryland, and our leader lives in Richmond; I have a cowriter who is also speaking ( Jessica Moss ), and other good friends who are amongst…(read more)

Performance Tuning with SQL Server Dynamic Management Views

From Red-Gate website you can download (for free) the ebook Performance Tuning with SQL Server Dynamic Management Views written by Louis Davidson (@drsql) and Tim Ford (@sqlagentman).

DMVs are objects that publish information about the health state of a SQL Server instance. They allow you to monitor the heart of the system to diagnose problems and measure performance. They represent a very good tool for DBAs and database Developer as well. Reading this book you will learn how to obtain information from the most frequently used DMVs in investigating query execution, index usage, session and transaction activity, disk I/O, and how SQL Server is using or abusing the operating system.

The editorial review is available here.

This book cannot miss in your digital library! And obviously you can buy the paperback on Amazon.

Capturing query and IO statistics using Extended Events

The commands

SET STATISTICS TIME ON
SET STATISTICS
IO ON

return information about query executions and are very useful when doing performance tuning work as they inform how long a query took to execute and the amount of IO activity that occurred as a result of that query.

These are very effective features however to my mind they do have a drawback in that the information they provide is not accessible in the actual query window from which the query was executed. This means the results cannot be collected, stored in a table, and then queried – such information would have to be manually copied and pasted from the messages pane into (say) a spreadsheet for further analysis.

This is dumb. I’m a SQL Server developer, I want my data available so that I can bung it into a table in SQL Server and issue queries against it. That is why, a couple of weeks ago, I submitted a request to Microsoft Connect entitled Access to STATS TIME & STATS IO from my query in which I said:

I recently was doing some performance testing work where I was evaluating the affect of changing various settings on a particular query. I would have liked to simply run my query inside a couple of nested loops in order to test all permutations but I could not do that because every time I executed the query I had to pause so I could retrieve the stats returned from STATISTICS IO & STATISTCS TIME and manually copy and paste (yes, copy and paste) the information into a spreadsheet.

This feels pretty dumb in this day and age. Why can we not simply have access to that same information within my query? After all, we have @@ROWCOUNT, ERROR_MESSAGE(), ERROR_NUMBER() etc… that provide very useful information about the previously executed statement, how about @@STATISTICS for returning all the IO & timing info? We can parse the text returned by that function to get all the info we need.
Better still, provide individual functions e.g.:
@@QUERYPARSETIME
@@QUERYCOMPILETIME
@@QUERYEXECUTIONTIME
@@SCANCOUNT
@@LOGICALREADS
@@PHYSICALREADS
@@READAHEADREADS

Ralph Kemperdick noticed my submission and correctly suggested that the same information could be accessed using Extended Events. Based on this I’ve written a script (below) that issues a series of queries against the AdventureWorks2012 sample database, captures similar stats that would be captured by SET STATISTICS then presents them back at the end of the query. Here are those results:

image

The information is not as comprehensive as what you would get from SET STATISTICS (no Read-Ahead Reads for example, and no breakdown of IO per table) but should be sufficient for most purposes.

You can adapt the script accordingly for whatever information you want to capture, the important part of the script is the creation of the XEvents session for capturing the queries, then reading and shredding the XML results thereafter.

Hope this is useful!

@Jamiet

–Create the event session
CREATE EVENT SESSION [queryperf] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
ADD TARGET package0.event_file(SET filename=N’C:tempqueryperf.xel’,max_file_size=(2),max_rollover_files=(100))
WITH MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,
            
MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,
            
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);

–Set up some demo queries against AdventureWorks2012 in order to evaluate query time & IO
USE AdventureWorks2012
DECLARE    @SalesPersonID INT;
DECLARE    @salesTally INT;
DECLARE    mycursor CURSOR FOR
SELECT
soh.SalesPersonID
FROM   Sales.SalesOrderHeader soh
GROUP  BY soh.SalesPersonID;
OPEN mycursor;
FETCH NEXT FROM mycursor INTO @SalesPersonID;
ALTER EVENT SESSION [queryperf] ON SERVER STATE = START;
WHILE @@FETCH_STATUS = 0
BEGIN
       DBCC
FREEPROCCACHE;
      
DBCC DROPCLEANBUFFERS;
      
CHECKPOINT;
      
SELECT @salesTally = COUNT(*)
      
FROM Sales.SalesOrderHeader  soh
      
INNER JOIN Sales.[SalesOrderDetail] sod        ON  soh.[SalesOrderID] = sod.[SalesOrderID]
      
WHERE SalesPersonID = @SalesPersonID
      
FETCH NEXT FROM mycursor INTO @SalesPersonID;
END
CLOSE
mycursor;
DEALLOCATE mycursor;
DROP EVENT SESSION [queryperf] ON SERVER;

–Extract query information from the XEvents target
SELECT q.duration,q.cpu_time,q.physical_reads,q.logical_reads,q.writes–,event_data_XML,statement,timestamp
FROM   (
      
SELECT  duration=e.event_data_XML.value(‘(//data[@name="duration"]/value)[1]’,‘int’)
       ,      
cpu_time=e.event_data_XML.value(‘(//data[@name="cpu_time"]/value)[1]’,‘int’)
       ,      
physical_reads=e.event_data_XML.value(‘(//data[@name="physical_reads"]/value)[1]’,‘int’)
       ,      
logical_reads=e.event_data_XML.value(‘(//data[@name="logical_reads"]/value)[1]’,‘int’)
       ,      
writes=e.event_data_XML.value(‘(//data[@name="writes"]/value)[1]’,‘int’)
       ,      
statement=e.event_data_XML.value(‘(//data[@name="statement"]/value)[1]’,‘nvarchar(max)’)
       ,      
TIMESTAMP=e.event_data_XML.value(‘(//@timestamp)[1]’,‘datetime2(7)’)
       ,       *
      
FROM    (
              
SELECT CAST(event_data AS XML) AS event_data_XML
              
FROM sys.fn_xe_file_target_read_file(‘C:tempqueryperf*.xel’, NULL, NULL, NULL)
              
)e
      
)q
WHERE  q.[statement] LIKE ‘select @salesTally = count(*)%’ –Filters out all the detritus that we’re not interested in!
ORDER  BY q.[timestamp] ASC
;

Parameterization and filtered indexes (part 2)

In my previous post , I demonstrated how the presence of a filtered index can get in the way of successful parameterization, possibly resulting in too much query compilations and procedure cache bloat. I suggest reading that first, because I will go straight where I left off. Use the Force, Luke If you read the previous post very carefully, you will have noticed that I wrote that if a plan is unstable, it is “unsafe for simple parameterization” – and that suggests that SQL Server will not have such…(read more)