The SQL Server Profiler is a very powerful diagnostic tool that helps you take a look at what’s happening inside of SQL Server when you execute a query. I hope that you are finding the 10-part tutorial series on the SQL Server Profiler (http://beyondrelational.com/modules/12/tutorials/631/getting-started-with-sql-server-profiler.aspx?tab=tutorials&bs=57&ts=46) useful. The aim of the tutorial is to help you get started with the SQL Server profiler and debug any database issues within your application with ease.

However, the more you explore, the more queries and questions you come up with and attempting to answer them is perhaps the best way to learn.

One of the kind readers asked me a question the other day via the ASK Module (http://beyondrelational.com/ask/nakul/default.aspx) related to the SQL Server Profiler (http://beyondrelational.com/ask/nakul/questions/1557/sql-server-profiler-trace-query.aspx), the answer to which I will discussing in today’s post.

The Question

Assume that one of your applications is executing the following query to get a list of all salaried employees for AdventureWorks Cycles.

USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Employee WHERE SalariedFlag = 1

However, when looked at from the SQL Server Profiler, you see that instead of the value “1”, you see a parameterized version of the query if the “SQL:StmtStarting” or “SQL:StmtCompleted” events are captured:

SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1

The actual parameter value (“1”) is available when the “RPC:Completed” event is captured. The question therefore is:

Why do “SQL:StmtStarting” and “SQL:StmtCompleted” not show the parameter values? Is there a way by which they can show the parameter values, so that additional events don’t need to be traced?

The Answer

The question is very interesting, and the answer lies in the internals of how SQL Server prepares itself for multiple executions of the same query in the future. However, let’s answer the second part of the question first. The answer to the second part of the question is quite simply, no. To the best of my knowledge, there is no way that we can have both the parameterized version of the query and it’s values captured the same event – “SQL:StmtStarting” or “SQL:StmtCompleted”.

That being said, let’s look at “why” the parameterized version of the query is seen.

One of the first things that SQL Server’s database engine does is to create a query plan for execution. Preparing a query plan is an expensive operation and therefore, SQL Server caches this plan for future re-use. Subsequent executions of the query will re-use the already available query plan and not spend time in creating a new plan – the specific values used in the comparisons may differ from instance to instance, but the plan itself remains the same.

We are therefore looking at two different components to the execution of a query:

1. A generic, parameterized query plan – which is what is seen in the “SQL:StmtStarting” and “SQL:StmtCompleted” events
2. A set of instance-specific variables/parameter values – which is what is seen in the instance specific “RPC:Completed” event

The following is an easy set of queries for better understanding of the concept.

/* WARNING */
/*
This query is provided "as-is" and without warranty.
The query is provided for demonstration purposes only.

PLEASE RUN THIS ON A DEVELOPMENT ENVIRONMENT ONLY.
EXECUTING THIS ON A QA/PRODUCTION ENVIRONMENT MAY RESULT IN SERIOUS PERFORMANCE COMPLICATIONS.
*/
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

USE AdventureWorks2008R2
GO
DECLARE @sqlstring NVARCHAR(100)
DECLARE @paramDefinition NVARCHAR(50)
DECLARE @paramValue BIT

SET @sqlstring = N'SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1'
SET @paramDefinition = N'@p1 BIT'
SET @paramValue = 1

EXEC sp_executesql @sqlstring, @paramDefinition, @p1 = @paramValue

--Check if the SQL Server is re-using the query plan
-- USECOUNTS value should be 1
SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
    	text like '%SELECT * FROM HumanResources.Employee%'
ORDER BY usecounts DESC;

/********* RESULTS ****************/
/* (Compressed for sake of brevity)
usecounts   cacheobjtype	objtype	     text
  1         Compiled Plan	Prepared     (@p1 BIT)SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1
  1         Compiled Plan	Adhoc        DECLARE @sqlstring ...............
*/

We can see that the version SQL Server has cached is the one with the generalized parameters – not the one with the parameter values.

Let us execute the same query again – this time with a different parameter value.

--Execute the query again, but with a different value
SET @paramValue = 0
EXEC sp_executesql @sqlstring, @paramDefinition, @p1 = @paramValue

--Check if the SQL Server is re-using the query plan
-- USECOUNTS value should be 2
SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
    	text like '%SELECT * FROM HumanResources.Employee%'
ORDER BY usecounts DESC;

/********* RESULTS ****************/
/* (Compressed for sake of brevity)
usecounts   cacheobjtype	objtype	     text
  2         Compiled Plan	Prepared    (@p1 BIT)SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1
  1         Compiled Plan	Adhoc       DECLARE @sqlstring ...............
*/
GO

The results show that the SQL Server was able to reuse the generic query plan.

Reference:

• Query Plan Re-use – http://beyondrelational.com/modules/2/blogs/77/posts/11346/query-plan-re-use-write-your-t-sql-queries-with-the-proper-case-for-plan-re-use-and-better-query-per.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Experiences teach us a lot of things – and one of them is to always take some more time when developing code to make sure it is isolated from future changes that one may make. Today’s post is based on one such incident that recently took place at the office.

How do you pass parameters to your stored procedures?

Stored procedures can be called in multiple ways, but when we talk about passing parameters to a procedure, there are essentially only two ways:

1. Using ordinal position, for e.g. EXEC proc_MyProcedure 1,2
2. Using parameter names, for e.g. EXEC proc_MyProcedure @param1 = 1, @param2 = 2

Now, we know that using ordinal positions is not a recommended best practice when using them in a query. Yet, I am quite sure that a large portion of the code currently being used production uses ordinal positions to pass parameters to a stored procedure. I will honestly admit here that I used to be one of the many who are victims of this bad practice – but all it takes is one experience to understand the importance of doing things right the first time around.

An example

Let’s take an example to understand why passing parameters using ordinal positions is not a recommended practice. We will create a simple procedure as shown below.

USE AdventureWorks2012
GO

CREATE PROCEDURE proc_GetEmployeesBySalariedFlagAndOrganizationalLevel
            @salariedFlag BIT,
            @organizationLevel TINYINT
AS
    BEGIN
        SET NOCOUNT ON

        SELECT Employee.BusinessEntityID,
               Employee.OrganizationLevel,
               Employee.SalariedFlag,
               Employee.NationalIDNumber,
               Employee.BirthDate,
               Employee.JobTitle,
               Employee.SickLeaveHours,
               Employee.VacationHours
        FROM HumanResources.Employee
        WHERE Employee.OrganizationLevel = @organizationLevel
          AND Employee.SalariedFlag = @salariedFlag
    END
GO

As you can see, our demo procedure has 2 parameters – a flag indicating whether we want salaried employees or not and the other to indicate the organizational level of the employees that we need to fetch the data for.

Passing parameters using ordinal positions

In order to execute this stored procedure, we can simply use the ordinal positions as shown below:

--Executing using ordinal positions
USE AdventureWorks2012
GO
EXEC proc_GetEmployeesBySalariedFlagAndOrganizationalLevel 0, 4

Execution of the stored procedure returns us some 187 odd records.

Passing parameters using parameter names

In order to execute the procedure using parameter names, we can use the following statement (takes more number of key strokes to write, but will reap benefits in the future):

--Executing using parameter names
USE AdventureWorks2012
GO
EXEC proc_GetEmployeesBySalariedFlagAndOrganizationalLevel @salariedFlag = 0, @organizationLevel = 4

Executing this also returns us the same 187 records.

What can go wrong?

As part of a review, assume that the architect asks the developers to swap out the two parameters – the stored procedure remains the same, but the architect believes that the organization level holds a higher priority from a business perspective and therefore requests the change.

USE AdventureWorks2012
GO

ALTER PROCEDURE proc_GetEmployeesBySalariedFlagAndOrganizationalLevel
            @organizationLevel TINYINT,
            @salariedFlag BIT
AS
    BEGIN
        SELECT Employee.BusinessEntityID,
               Employee.OrganizationLevel,
               Employee.SalariedFlag,
               Employee.NationalIDNumber,
               Employee.BirthDate,
               Employee.JobTitle,
               Employee.SickLeaveHours,
               Employee.VacationHours
        FROM HumanResources.Employee
        WHERE Employee.OrganizationLevel = @organizationLevel
          AND Employee.SalariedFlag = @salariedFlag
    END
GO

Which of the two methods to you think would continue to work after the change? Let’s run them and see:

--Executing using ordinal positions
USE AdventureWorks2012
GO
EXEC proc_GetEmployeesBySalariedFlagAndOrganizationalLevel 0, 4

--Executing using parameter names
USE AdventureWorks2012
GO
EXEC proc_GetEmployeesBySalariedFlagAndOrganizationalLevel @salariedFlag = 0, @organizationLevel = 4

Result:

The execution using ordinal positions now returns only 1 result, whereas the execution mode with the parameter names continues to return 187 records.

Conclusion

As a matter of fact, if one would observe the prepared statements being fired against a database through a data adapter or via any automated code generator, one would observe that they are always parameterized using parameter names, not using ordinal positions.

In conclusion, all I would say is that using ordinal positions can lead to troubles in case the underlying object changes sometime in the future. Named referencing and parameterization is always the safer approach, and while it takes a lot more key strokes (unwelcome with the development community), they save a lot of rework when the change is actually to be made.

This experience was enough to make me use named parameterization when making stored procedure calls always.

How do you execute your stored procedures – I am interested to know. Do share your observations/thoughts/approaches before you leave.

Until we meet next time,

Be courteous. Drive responsibly.

I have worked for a product sustenance team for the better part of my 7+ years of experience and hence, I can closely relate to the problem at hand today. The challenges with sustenance teams are many, and one of them is understanding and building upon code written by other teams – developers of whom may not even be around in the organization. In addition to this the code that needs to be extended may be legacy code which was written years ago and may not confirm to the newer coding standards. To reduce the impact, most sustenance teams therefore do not spend the time to re-engineer or re-write a piece of code just because it is not up to standards, but instead work on extending the functionality. They leave it to the new development teams to re-write the code as part of the system refresh cycles.

The Scenario

This took the better of one of the engineers recently. An existing procedure (written in the old-fashioned way to use CURSORs) had to be extended. It was upto this poor developer to extend the stored procedure to incorporate some additional logic and release it out to QA within a considerably tight time-frame.

This stored procedure used the slower approach of using CURSORs to iterate through the list of employees. This cursor was to be re-used after another set of business operators to achieve the new requirement. The engineer therefore decided to add the additional piece of code after the old code ended – which is where he made a fatal mistake that cost him a couple of hours to notice.

The Example

To establish a parallel with the requirement, let us assume that a procedure exists to increment the VacationHours of all employees of a certain grade with AdventureWorks Cycles by 1 hour. We will extend this stored procedure by attempting to revert back the change to VacationHours, leaving a small error in doing so.

USE AdventureWorks2012
GO
IF OBJECT_ID('HumanResources.proc_DemoDellocateCursorIssues') IS NOT NULL
BEGIN
    DROP PROCEDURE HumanResources.proc_DemoDellocateCursorIssues
END
GO
CREATE PROCEDURE HumanResources.proc_DemoDellocateCursorIssues
    @orgLevel INT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @businessEntityId INT 

    DECLARE employeeCursor INSENSITIVE CURSOR
    FOR (SELECT e.BusinessEntityID
         FROM HumanResources.Employee AS e
         WHERE e.OrganizationLevel = @orgLevel
        )

    OPEN employeeCursor 

    BEGIN TRANSACTION outerTran
        BEGIN TRY
            FETCH NEXT FROM employeeTran INTO @businessEntityId

            WHILE (@@FETCH_STATUS <> -1)
            BEGIN
                WHILE (@@FETCH_STATUS <> -2)
                BEGIN
                    UPDATE e
                    SET VacationHours += 1
                    FROM HumanResources.Employee AS e
                    WHERE BusinessEntityID = @businessEntityId
                END
                FETCH NEXT FROM employeeTran INTO @businessEntityId
            END

            --IF @@TRANCOUNT > 0
            --    COMMIT TRANSACTION outerTran
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION outerTran
        END CATCH

        CLOSE employeeCursor
        DEALLOCATE employeeCursor

        --OLD CODE ENDS HERE!!! ADDITIONAL CODE BEGINS

        --This part did not exist before
        --Some more business logic added here

        --Re-open the cursor and begin processing
        OPEN employeeCursor
        BEGIN TRY
            FETCH NEXT FROM employeeTran INTO @businessEntityId

            WHILE (@@FETCH_STATUS <> -1)
            BEGIN
                WHILE (@@FETCH_STATUS <> -2)
                BEGIN
                    UPDATE e
                    SET VacationHours -= 1
                    FROM HumanResources.Employee AS e
                    WHERE BusinessEntityID = @businessEntityId
                END
                FETCH NEXT FROM employeeTran INTO @businessEntityId
            END
            IF @@TRANCOUNT > 0
                COMMIT TRANSACTION outerTran
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION outerTran
        END CATCH

        CLOSE employeeCursor
        DEALLOCATE employeeCursor
END
GO

The Error

All those who have noticed the error, no further reading of this post is necessary. All those who have not found the error yet, please read on…

Executing the procedure raises the following error:

USE AdventureWorks2012
GO
EXEC HumanResources.proc_DemoDellocateCursorIssues @orgLevel = 4
GO

Msg 16916, Level 16, State 1, Procedure proc_DemoDellocateCursorIssues, Line 40
A cursor with the name ’employeeCursor’ does not exist.
Msg 16916, Level 16, State 1, Procedure proc_DemoDellocateCursorIssues, Line 67
A cursor with the name ’employeeCursor’ does not exist.
Msg 16916, Level 16, State 1, Procedure proc_DemoDellocateCursorIssues, Line 68
A cursor with the name ’employeeCursor’ does not exist.

The error comes simply because the CURSOR – employeeCursor has been DEALLOCATEd before being used again. Let’s focus on the part where the old code ends and new extension was added:

        CLOSE employeeCursor
        --Notice the DEALLOCATE from the older implementation here. The solution is to comment this DEALLOCATE statement
        --DEALLOCATE employeeCursor

        --OLD CODE ENDS HERE!!! ADDITIONAL CODE BEGINS

        --This part did not exist before
        --Some more business logic added here

        --Re-open the cursor and begin processing
        OPEN employeeCursor

It was a simple typographical error on the part of the developer, but resulted in his spending an hour or two in debugging the issue and ultimately approaching me for help. A 10-minute study of the code from a different pair of eyes (mine) helped him resolve realize the miss and resolve the issue.

Moral of the story

The moral of the story is not whether we should use Cursors or not, but there is a more important behavioural pattern that I would like to highlight.

After being sure that we have done our level best in writing a piece of code, most of us tend to keep troubleshooting the code for hours at an end if it happens to develop an error. All I urge is that if you are sure that you have done due diligence from your end, do not be afraid to ask for peer reviews – they help because they involve a different pair of eyes on the same problem. The difficult part is not making it a habit by having others resolve all your problems for you.

Until we meet next time,

Be courteous. Drive responsibly.

It is generally a bad practice to have execution sequence dependent business logic inside of triggers. They should be inside stored procedures, and not triggers. But, a lot of us maintain databases designed & developed by external teams – members of which may not be with the organization anymore. What I am sharing today comes from a Sunday afternoon long time ago from when I was on-call during a customer on-premise deployment.

Building the Scenario

The application I was working on used triggers which had custom logic embedded into them. Apparently, one of the areas required that a record in another table be updated by an update trigger before another trigger to send out a notification of the change to an external system is executed – and that was the source of our problems.

It is difficult to recreate the scenario because the trigger order is by default, controlled by the SQL Server database engine. However, here goes – the script below creates a table with two very simple insert triggers on it.

USE tempdb
GO

--Safety Check
IF OBJECT_ID('triggerSequenceTest') IS NOT NULL
BEGIN
    DROP TABLE triggerSequenceTest
END
GO

CREATE TABLE triggerSequenceTest (RowId INT IDENTITY(1,1),
                                   RowValue NVARCHAR(20)
                                  )
GO

CREATE TRIGGER dbo.trig_UpdateTrig01
ON dbo.triggerSequenceTest
FOR INSERT
AS 
BEGIN
    DECLARE @insertedValue NVARCHAR(20)
    
    SELECT @insertedValue = inserted.RowValue from inserted
    
    PRINT 'Trigger 01: ' + @insertedValue
END
GO

CREATE TRIGGER dbo.trig_UpdateTrig02
ON dbo.triggerSequenceTest
FOR INSERT
AS 
BEGIN
    DECLARE @insertedValue NVARCHAR(20)
    
    SELECT @insertedValue = inserted.RowValue from inserted
    
    PRINT 'Trigger 02: ' + @insertedValue
END
GO

--Perform the INSERT
INSERT INTO triggerSequenceTest (RowValue) VALUES ('Microsoft')

The output that we (normally) receive is:

Trigger 01: Microsoft

Trigger 02: Microsoft

In my case, the trigger 02 was the replication trigger and was designed such that it was expecting a change made by trigger 01 to be in place. However, the trigger 02 was executed before trigger 01 causing all sorts of weird behavioural issues within the application and failures being logged into integration logs.

The Solution

In the very rare case when SQL Server fails to set the correct trigger order, one can control the execution to a certain extent. We can control which trigger needs to be executed first, and which one the last through a system stored procedure – sp_settriggerorder.

While the long term solution was to re-engineer the application to move the required logic into a stored procedure, the solution that we ended up using that day was forcibly setting the trigger order for the integration related trigger to last. As a demo, let’s move the trigger 01 to the last in the execution sequence:

--Change the trigger order
USE tempdb;
GO
sp_settriggerorder @triggername= 'dbo.trig_UpdateTrig01', @order='Last', @stmttype = 'INSERT';
GO

An attempt to insert some data now yields:

--Perform the INSERT again
INSERT INTO triggerSequenceTest (RowValue) VALUES ('SQL Server')

Trigger 02: Microsoft

Trigger 01: Microsoft

IMPORTANT: One of the key points to remember is that server scoped triggers are always executed first.

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

Until we meet next time,

Be courteous. Drive responsibly.

Recently, one of my friends (new to SQL Server) was attempting to the deploy the AdventureWorks2012 versions of the sample databases available for download from the CodePlex community on his laptop for study purposes, but was facing an error and hence called me up.

When I reached to his place, he told me that he had downloaded the AdventureWorks2012 database from the following website: http://msftdbprodsamples.codeplex.com/releases/view/55330#DownloadId=165399

When attempting to restore the database, he was facing the following error:

A quick glance at the screenshot shows us the error –

Unable to open the physical file “E:SQL DatabasesAdventureWorksAdventureWorks_Log.ldf”. Operating system error 2: “2(The system cannot find the file specified.)”. (Microsoft SQL Server, Error: 5120)

Clearly the error is because the sample databases available on the CodePlex website are just the data files. A transaction log file is not supplied by CodePlex. To restore a database that comes without a data file, one can use the option to rebuild the transaction log when running the CREATE DATABASE command. For example:

USE [master]
GO

CREATE DATABASE [AdventureWorks] 
ON  PRIMARY ( NAME = N'AdventureWorks_Data', 
              FILENAME = N'E:SQL DatabasesAdventureWorksAdventureWorks_Data.mdf', 
              SIZE = 3072KB,
              MAXSIZE = UNLIMITED,
              FILEGROWTH = 1024KB )
FOR ATTACH_REBUILD_LOG
GO

Some finer points about ATTACH_REBUILD_LOG:

• When rebuilding the transaction log, a collation name cannot be specified to change the collation of the database
• This option is only available for read/write databases
• Because the transaction log file is being rebuilt, we do not have control over the location, initial size and file growth increment values. The new log file will be created at the default log path location and will have a size of 1MB
• Naturally, this operation breaks the log chain

You can also find this script with a lot of other SQL Server, C# and XML scripts at the Scripts Module on BeyondRelational.com: http://beyondrelational.com/modules/30/scripts.aspx?s=stream&tab=scripts

Until we meet next time,

Be courteous. Drive responsibly.