SQL Server Error Handling

Good error handling can be critical to writing robust code and ensuring predictable behavior should things go wrong. We always want to be certain that our database will be left in a clean and desirable state, which can be challenging if, say, a stored procedure errors part way through a series of inter-dependent steps.

As a basic starting point for basic handling of problem scenarios it's good practice to contain code within transactions:

BEGIN TRAN

--DO SOMETIHNG

COMMIT

If multiple steps are contained within the transaction, and any one should fail, then all the steps will be rolled back, leaving the database in the same state it was before the transaction began.

With SQL 2005, TRY..CATCH blocks were introduced. Using this feature we can greatly enhance the error handling. We can now “try” to do our data manipulation and should any step fail, we can “catch” the error and perform further "clean up" steps as a result.

A simple example might be to open a transaction in the TRY block, roll it back in the CATCH, else commit it:

BEGIN TRY

BEGIN TRAN

--DO SOMETHING

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK

END CATCH

IF @@TRANCOUNT > 0

COMMIT

For more details on useful global variables such as @@TRANCOUNT see my post on SQL Server Global Variables.

The above code allows us to cleanly handle error scenarios and greatly improve the robustness of our code and therefore our database. However, the above example does not actually let us know any details about the error that has occurred. To do that, we need to capture the error information:

BEGIN TRY

SELECT 10/0

END TRY

BEGIN CATCH

SELECT

ERROR_MESSAGE() AS ErrorMessage,

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_LINE() AS ErrorLine

END CATCH

In the above script I have forced a “divide by zero” error in the TRY block. When this error is thrown, the code will jump into the CATCH block and execute the select statement:

ErrorMessage ErrorNumber ErrorSeverity ErrorState ErrorLine

------------------------------------- ----------- ------------- ----------- -----------

Divide by zero error encountered. 8134 16 1 3

Note that the actual error has not been thrown, and the code has completed successfully without any problems. We are simply displaying the results of the various error functions.

We can make this even more powerful if we use the RAISERROR function. This will allow us to actually throw an error message back to the calling application. The advantage this gives us is that we can capture the error from the TRY, perform remedial action in the CATCH, and then let the calling application know that there was an error we had to clean up:

DECLARE @ErrorMsg VARCHAR(500)

BEGIN TRY

BEGIN TRAN

SELECT 10/0

END TRY

BEGIN CATCH

SELECT @ErrorMsg = 'Error: ' + ERROR_MESSAGE()

+CHAR(13) +'Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(100))

+CHAR(13) +'State: ' + CAST(ERROR_STATE() AS VARCHAR(100))

+CHAR(13) +'Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(100))

+CHAR(13) +'Line: ' + CAST(ERROR_LINE() AS VARCHAR(100))

IF @@TRANCOUNT > 0

ROLLBACK

RAISERROR(@ErrorMsg, 16,10)

END CATCH

IF @@TRANCOUNT > 0

COMMIT

-----------

(0 row(s) affected)

Msg 50000, Level 16, State 10, Line 21

Error: Divide by zero error encountered.

Severity: 16

State: 1

Number: 8134

Line: 5

The result is that the error is captured and handled - the transaction is rolled back. Using the RAISERROR function, however, we have build our own custom error message to return to the calling application. In the example above, this message lets us know even the exact line that caused the error. This message can be enriched to include as much information as you wish, which can greatly ease debugging of the problem.

(It's worth noting that the severity of the error I have set to 16. Different severity numbers can have different effects in different places, and typically 11-18 are the range for user defined errors. See the MSDN link at the end of this article for more details.)

Finally, RAISERROR can be used to throw msg_id's, and this is the more standard SQL behaviour. The list of standard error messages that can be thrown are contained in the sys.messages

table. You can insert your own (with an id >50000) and then use RAISERROR to throw these when required.

Error handling can be used to powerful effect using the techniques described. If you have any techniques you use or ideas on how to improve on the methods described here, please feel free to share them.

MSDN - RAISERROR:

http://msdn.microsoft.com/en-us/library/ms178592.aspx

CheckSum, HashBytes and Slowly Changing Dimensions

A recent requirement for a DW was to implement a Type 2 Slowly Changing Dimension across all attributes in the dimension.  Unfortunately, the dimension had many, many attributes (30+), which meant that a comparison of all attribute values would be needed for each new record that arrived.

To recap:

Type 2 Slowly Changing Dimension (SCD2)
SCDs are methodologies for tracking historical changes to a data row in a dimension table over time. While SCD1 is just an overwrite when there is a change (no history), SCD2 involves entering a new record into the table with the new changed data.  A new surrogate key is generated for the new record, while maintaining the natural key to allow the new record to be linked to the old one.  Typically, there would also be either an IsCurrent flag, to identify which is the current version of the data, and/or Start and End Date columns to indicate the order and time span of the changes:

Surrogate_Key	Natural_Key	CustName	Country	Start_Date	End_Date
100	375	Guy	Scotland	01-Jan-2002	31-Jul-2005
101	375	Guy	England	01-Aug-2005	31-Dec-9999

In the above example the Country has changed and so a new record with the new country and new surrogate key has been inserted.  As each new row arrives, it's country is checked against the existing records country to see if it has changed or not.

This is straightforward if there is only the one attribute being tracked.  A simple string comparison will quickly identify the change.  However, if there are many attributes being compared then the query doing this comparison can become quite long and perform poorly,  particularly if you have large numbers of records arriving.

In order to solve this problem it was decided to hashcode the relevant (tracked) columns together and save the output to a new column.  If we hashcode the incoming records on the same columns as well, then all we have to do is compare the two hash columns to see if there the record has changed. 

There are a number of ways to do this:

CHECKSUM()

From BOL:

 CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator...... If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change.

In fact a quick test using CHECKSUM() to hashcode 100,000 GUIDs resulted in up to 3 repeated hashcodes ( "collisions").  The CHECKSUM is very fast, but this rate of collision is not tolerable in this scenario.

HASHBYTES()

HASHBYTES() performs a similar function but the rate of collision is much much lower. It's not zero, but to date I have never been able to generate a collision.
To use this function, an algorithm needs to be specified. Thomas Kesjer has done a detailed comparison of the various algorithms' performance, and produced this graph:

The performance of the HASHBYTES algorithms are largely similar (poorer than CHECKSUM), with the exception of MD2, which is much slower.  Typically I use MD5.  The other point to note is that HASHBYTES requires a single string parameter to be passed, not multiple columns eg:

SELECT HASHBYTES('MD5', Country) FROM DummyTable

In order to hashcode multiple columns together we can either concatenate them together as strings, or an even neater way would be to convert it to an XML string using FOR XML:

SELECT HASHBYTES('MD5', (SELECT CustName, Country FOR XML RAW))

Using this technique we can hashcode all the existing records in the table, and as each new record arrives we can look at its hashcode and compare it (joining on the Natural Key).  If the code is different then the record has changed, and is inserted. If not, it is ignored.

This should be much simpler to maintain and perform much better than doing individual comparisons of the all the changing fields.

SQL to Drop All Non-System Objects From Master DB

We've all been there - well I have anyway. You've just created your script to generate all the required objects for your database.  You run the script in the new environment.  But wait, you forgot to set the database!  All the objects have been created in the master db instead!

The number of times this has happened to me and to others I have worked with is infuriating.  I've sat there watching people spend ages deleting one by one all the db objects using Management Studio, stumbling when there are dependencies, and generally wasting a lot if time.

To solve this problem I have put together a short script to generate all the required drop statements. Fortunately, the sys.objects table contains a handy "is_ms_shipped" column that makes life much simpler:

SELECT

'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+Name+']''))'+ CHAR(10)+ 'DROP '

+ CASE WHEN  type = 'U' THEN 'TABLE '

       WHEN  type = 'P' THEN 'PROCEDURE '

       WHEN  type ='FN' THEN 'FUNCTION '

       WHEN  type = 'V' THEN 'VIEW '

  END

+ Name + CHAR(10) + 'GO' + CHAR(10)

FROM  Master.sys.objects

WHERE is_ms_shipped <> 1

AND TYPE IN ('U','P','FN','V')

ORDER BY

CASE  WHEN type = 'P'  THEN 1

      WHEN type = 'V'  THEN 2

      WHEN type = 'FN' THEN 3

      WHEN type = 'U'  THEN 4

END

This script will generate all the DROP statements for the user-defined objects in the master db.  These need to be copied into a new window (easiest if you have been outputted the results to text rather than to grid in SSMS) and executed.

If there are any foreign key constraints, however, the above script will generate an error.  To handle this either execute all the drop statements over and over until the errors cease (each subsequent execution of the drop statements will delete additional tables as each foreign key table is dropped), or, prior to executing the above script, execute the following:

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(fk.parent_object_ID) + '] DROP CONSTRAINT ' + fk.name

+ CHAR(10) + 'GO' +CHAR(10)

FROM sys.foreign_keys fk

WHERE is_ms_shipped = 0

This will generate the necessary statements that need to be run in order to drop all the constraints. Copy the output to a new query window and execute.

SQL Server Parameter Sniffing - Slow Running Queries

Parameter sniffing is a problem that can occasionally creep into code, just when everything seems fine. A stored procedure that yesterday ran nice and fast, today is taking much longer. The next day it is fast again. Nothing has changed on the server / db. It can be difficult to track down why.

The answer could be Parameter Sniffing.

When a stored procedure is compiled, it compiles using ( or "sniffing") the parameter values set ​​at the time of the invocation. It uses those parameter values ​​to determine the optimal execution plan for the proc. Simply put, if you later call the proc with a different parameter value, it will still use the execution plan determined by the first set of values. It will do this regardless of whether a better execution plan would have been more appropriate for the new value.

For example, if I have a table containing all the values ​​from 1 to 100, and then 100 rows with a value 999:

/ * Create table * /

CREATE TABLE DummyData ( col1 INT ​​)

DECLARE @Counter INT

/ * Insert data 1-100 * /

SET @Counter = 0

WHILE @Counter < 100

BEGIN

INSERT INTO DummyData ( col1 ) VALUES ( @Counter )

SET @Counter = @Counter + 1

END

/ * Insert 100 data rows with value 999 * /

SET @Counter = 0

WHILE @Counter < 100

BEGIN

INSERT INTO DummyData ( col1 ) VALUES ( 999 )

SET @Counter = @Counter + 1

END

Next create a procedure to select values from this table:

CREATE PROC SelectDummyData ( @DummyValue INT )

AS

BEGIN

SELECT * FROM DummyData WHERE col1 = @DummyValue

END

Now generate the Execution Plan for this procedure, passing a parameter of 999, and look at the "estimated number of rows":

EXEC SelectDummyData @DummyValue = 999

The row count is 100, which is what we would expect. But re-run the execute statement, this time with @DummyValue = 5, and the Estimated Number of Rows is still 100, when we would expect it to be 1.

The reason it is still expecting 100 rows is because the execution plan was determined when the proc was first compiled, when we used a value of 999.  It is still using the same execution plan, and therefore the same number of rows is expected, regardless of the input value supplied.

The impact of this oddity in this example is negligible, but for a complex query it can result in significantly slower performance.

If your query is complex it might be easier to generate the execution plan in XML ( SET SHOWPLAN_XML ON ) and then search the XML for something like "ParameterCompiledValue =".

To avoid parameter sniffing issues from occurring there are a number of options that can be considered:

1. Recompile the stored proc every time it is executed:

EXEC SelectDummyData @DummyValue =  1
WITH RECOMPILE 

This will ensure that the proc uses the supplied  parameters to build the best execution plan every time, but the overhead is the constant recompilation. This may be acceptable if there is sufficient gain in query performance.

Generating the execution plan on the above script, using various parameters, shows the correct number of rows being estimated each time.

If the parameter sniffing  relates to a set of queries in particular, then  recompilation can be specified individually for queries:

SELECT * FROM DummyData WHERE col1 = @DummyValue

OPTION ( RECOMPILE )

2. Optimize the query/ies for a specific parameter value:

ALTER PROC SelectDummyData ( @DummyValue INT )

AS

BEGIN

SELECT * FROM DummyData WHERE col1 = @DummyValue

OPTION ( optimize FOR ( @DummyValue = 1 ))

END

With this option we can force the query plan to be based on the same known value every time it is compiled (eg During maintenance tasks). This option may work if we know the optimal value for our system. The downside, however, is that it may still perform poorly for other values, but that may well be tolerable.

3. Avoid sniffing altogether, by using a local variable inside the proc:

ALTER PROC SelectDummyData ( @DummyValue INT )
AS BEGIN DECLARE @LocalDummyValue INT SET @LocalDummyValue = @DummyValue
SELECT * FROM DummyData WHERE col1 = @LocalDummyValue
END 

Here there is no possibility of parameter sniffing, and the execution plan will be based on the statistics. Note, however, that the plan will be built using statistic densities instead of statistic histograms, which are less accurate. Therefore it is not guaranteed to be the best plan for all possible values.

Conclusion
Parameter sniffing has the potential to cause queries to run with widely varying performance. In order to mitigate this problem consider modifying the code based on the options presented above. Hopefully this will help keep the queries and stored procedures performing well wherever they are used.

More useful info on this topic is available here:

Degremont Michel

http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Turgay Sahtiyan
http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

SQL Server: Dynamically Check if SQL Agent Job is Running

It can be helpful to have a SQL script to determine if a SQL Agent job is currently running or not. In SSMS this check can be done using the tools and GUIs provided, but it may be necessary to embed this check in script.  For example you may have a series of scripts, some scheduled through jobs and some triggered by, say, a button click on an interface.  If you need to ensure that the button job does not run at the same time as the Agent Job, you'll need to dynamically check the agent job status. Here's how to do it.

First let's create a simple SQL Agent job with two steps:

In the above image I have created a new job called TestDelay, containing two steps, WAIT 1 and WAIT 2.  The first step simply waits for 20 seconds before completing successfully, while the second step waits for a further 5 seconds before doing the same.

The information relating to this job and it's execution is contained in the following tables in the msdb database:

dbo.sysjobs
dbo.sysjobsteps
dbo.sysjobactivity

From the first table we can find out the job_id, which is key for joining the 3 tables together:

SELECT job_id FROM msdb.dbo.sysjobs

WHERE name LIKE 'TestDelay'

Then from the second table we can find out "step" information:

SELECT step_id, step_name, subsystem

FROM msdb.dbo.sysjobsteps

WHERE job_id = (SELECT job_id FROM MSDB.dbo.sysjobs

                        WHERE name LIKE 'TestDelay')

step_id     step_name     subsystem
----------- ---------------------------
1           WAIT 1        TSQL
2           WAIT 2        TSQL

(2 row(s) affected)

Here we can see the two steps in our job and their step_ids. Using the third table we can run the following query for our job:

SELECT     start_execution_date,

           last_executed_step_id,

           last_executed_step_date

FROM       msdb.dbo.sysjobactivity a

INNER JOIN msdb.dbo.sysjobs j

ON   j.job_id = a.job_id

WHERE      j.name LIKE 'TestDelay'

start_execution_date    last_executed_step_id last_executed_step_date
----------------------- --------------------- -----------------------
2014-04-28 15:20:23.000 2                     2014-04-28 15:20:43.000

(1 row(s) affected)

We can see that on it's most recent execution, the job finished it's second and final step at 15:20:43. If the job were still running and had not completed even the first step yet,  then the last_executed_step_id and last_executed_step_date fields would be null for this row.
If, however, the first step has completed but the second has not, the two fields will be populated but the step_id will be 1, not 2.

So to know if our job is still running we really need to combine these three tables into a single query, checking whether the last_execution_step_id field is null, and if not, if it is at it's maximum value or not.

SELECT DISTINCT

            start_execution_date,

            last_executed_step_id,

            last_executed_step_date,

CASE  WHEN  last_executed_step_id = MAX(step_id) THEN 'Job Complete'    

      ELSE  'Job Still Running'

END         AS result

FROM        msdb.dbo.sysjobactivity a

INNER JOIN  msdb.dbo.sysjobsteps s

      ON    a.job_id=s.job_id

INNER JOIN  msdb.dbo.sysjobs j

      ON    j.job_id = a.job_id

WHERE       j.name LIKE 'TestDelay'

GROUP BY    start_execution_date,

            last_executed_step_id,

            last_executed_step_date

In the above query, the "result" field will say either "Job Still Running" or "Job Complete" depending on the logic stated above.  Alternatively the script could be rewritten in the form of a stored procedure that accepts a parameter containing the job name and returns a 1/0 depending on whether it is running or not.

It is worth examining the rest of the fields in these three tables as they can offer additional options for extracting information about the jobs set up on the server.