Object Qualification

I came across an interesting issue recently with NHibernate, now it is widely known I despise ORM’s, in my experience they do a pretty mediocre job at best and at times can be absolutely horrific.  The issue was that the statements being fired at an instance of SQL Server from an application using NHibernate were not schema qualified.  Now this is not a rant at ORM’s as the issue I will show below is experienced with stored procedures, ad-hoc sql and any T-SQL you execute against SQL Server for that matter.  In fact I will be using a stored procedure in the example ;)

Now for those of you that don’t know SQL Server has to do an awfull lot of work before a statement is actually executed, here I want to show you the performance improvements that can be achieved by schema qualifying your objects.  The below quote is from Microsoft and will set the scene for the rest of the post.

"If user "dbo" owns object dbo.mystoredproc, and another user "Harry" runs this stored procedure with the command "exec mystoredproc," the initial cache lookup by object name fails because the object is not owner-qualified. (It is not yet known whether another stored procedure named Harry.mystoredproc exists, so SQL cannot be sure that the cached plan for dbo.mystoredproc is the right one to execute.) SQL Server then acquires an exclusive compile lock on the procedure and makes preparations to compile the procedure, including resolving the object name to an object ID. Before it compiles the plan, SQL Server uses this object ID to perform a more precise search of the procedure cache and is able to locate a previously compiled plan even without the owner qualification.

 If an existing plan is found, SQL Server reuses the cached plan and does not actually compile the stored procedure. However, the lack of owner-qualification forces SQL to perform a second cache lookup and acquire an exclusive compile lock before determining that the existing cached execution plan can be reused. Acquiring the lock and performing lookups and other work that is needed to get to this point can introduce a delay that is sufficient for the compile locks to lead to blocking. This is especially true if a large number of users who are not the stored procedure's owner simultaneously run it without supplying the owner name. Note that even if you do not see SPIDs waiting on compile locks, lack of owner-qualification can introduce delays in stored procedure execution and unnecessarily high CPU utilization."

Script

To demonstrate this I used the below script, I am running SQL Server 2008 R2 developer edition on my local instance and used the AdventureWorks2008R2 databasewhich is available here.

The script creates a schema called Chris in the AdventureWorks2008R2 database, a user SQL365\Chris is created for the login SQL365\Chris with the default schema of Chris.  Finally a procedure called dbo.spGetSalesOrderHeader is created that returns every record from AdventureWorks2008R2.dbo.SalesOrderHeader.

/*

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

      Object Qualification

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

   

      For more SQL resources, check out SQLServer365.blogspot.com

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

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

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

*/

-- Set Database Context

USE AdventureWorks2008R2;

GO

-- Declare variable

DECLARE @SQL VARCHAR(255)

-- Set variable

SET @SQL = 'CREATE SCHEMA Chris AUTHORIZATION dbo'

-- Create Schema

IF NOT EXISTS ( SELECT  1

                FROM    sys.schemas

                WHERE   name = 'Chris' )

    BEGIN

        EXEC (@SQL)

    END

GO

-- Create user mapped to login with default schema of the above created schema

IF NOT EXISTS ( SELECT  1

                FROM    sys.database_principals

                WHERE   name = 'SQL365\chris' )

    BEGIN

        CREATE USER [SQL365\Chris] FOR LOGIN [SQL365\Chris] WITH DEFAULT_SCHEMA = Chris;

    END

GO

-- Create procedure in dbo schema to be executed by the above user

IF EXISTS ( SELECT  1

            FROM    sys.objects

            WHERE   [object_id] = OBJECT_ID(N'[dbo].[spGetSalesOrderHeader]')

                    AND type IN ( N'P', N'PC' ) )

    DROP PROCEDURE [dbo].[spGetSalesOrderHeader]

GO

CREATE PROCEDURE dbo.spGetSalesOrderHeader

AS

    BEGIN

        SELECT  SalesOrderID ,

                RevisionNumber ,

                OrderDate ,

                DueDate ,

                ShipDate ,

                [Status] ,

                OnlineOrderFlag ,

                SalesOrderNumber ,

                PurchaseOrderNumber ,

                AccountNumber ,

                CustomerID ,

                SalesPersonID ,

                TerritoryID ,

                BillToAddressID ,

                ShipToAddressID ,

                ShipMethodID ,

                CreditCardID ,

                CreditCardApprovalCode ,

                CurrencyRateID ,

                SubTotal ,

                TaxAmt ,

                Freight ,

                TotalDue ,

                Comment ,

                rowguid ,

                ModifiedDate

        FROM    sales.SalesOrderHeader

    END

GO

I use a great tool SQLQueryStress developed by Adam Machanic (B - T) quite frequently when testing the effects of changes under load, it is ingeniously simple to use and I love it.  I used SQLQueryStress to record the results of executing the procedure and without schema qualification and with schema qualification, I used 4 threads (the number of cores in my laptop) and ran a thousand iterations to get a good average.  Results of which are included below;

Non Schema Qualified

Schema Qualified

As you can see the results are pretty damn conclusive every metric measured by SQLQueryStress saw a performance improvement by schema qualifying objects.

Run Time - 27.01% Improvement

ClientSeconds/Iteration (Avg) - 11.99% Improvement

CPU Seconds/Iteration (Avg) - 1.13% Improvement

Actual Seconds/Iteration (Avg) - 12.54% Improvement

There is no excuse for not schema qualifying your objects, performance improvements like this just cannot be ignored.

Enjoy!

Chris

A Script A Day - Day 24 - Object Qualification

Today’s script is one I use as an example to explain that there is method behind my standards that some people initially see as madness.  I’m sure some people think I am trying to make their job harder and that I get a kick out of telling them to “go away and do it again properly”.  The answer is yes I do get a kick out of it, but not because I am telling them they are wrong because it proves that the standards in place are providing value to the business by adding a layer of protection.

Let me explain the script below.  I create two schemas, two tables (one in each schema) and populate both with one record.  I then create two logins and two users, each user has a different default schema (one of each of the schemas created earlier).  These objects are to simply support the example.

Now lets assume that SchemaBlogUser1 is a developer and SchemaBlogUser2 is a DBA the process that I am trying to demonstrate is;

Developer creates and submits a script

DBA executes the script

Problems arrise with users reporting errors in the application

DBA investiagtes the issue

What the DBA ultimately finds is that because the script the developer submitted did not qualify the table with a schema in the check or the drop statement SQL Server used the default schema of the DBA which was different to that of the developer and ultimately dropped the wrong table!

It is important to remember that standards are there to protect everyone, yes they can make life more difficult in the now, but can save a lot of time and headaches in future, and that includes DBA’s!!!

/*

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

      Object Qualification

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

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

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

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

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

*/

USE SQLServer365

GO

-- Create test schemas

CREATE SCHEMA SchemaBlog1 AUTHORIZATION dbo;

GO

CREATE SCHEMA SchemaBlog2 AUTHORIZATION dbo;

GO

-- Create test tables

CREATE TABLE SchemaBlog1.Table1

(

      Table1ID INT IDENTITY (1,1),

      Column1 VARCHAR(50)

);

GO

CREATE TABLE SchemaBlog2.Table1

(

      Table1ID INT IDENTITY (1,1),

      Column1 VARCHAR(50)

);

GO

-- Insert test data

INSERT INTO SchemaBlog1.Table1

VALUES ('This table is in the SchemaBlog1 schema');

GO

INSERT INTO SchemaBlog2.Table1

VALUES ('This table is in the SchemaBlog2 schema');

GO

-- Create Logins

USE [master]

GO

CREATE LOGIN [SchemaBlogUser1] WITH PASSWORD=N'SchemaBlogUser1', DEFAULT_DATABASE=[SQLServer365], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

GO

CREATE LOGIN [SchemaBlogUser2] WITH PASSWORD=N'SchemaBlogUser2', DEFAULT_DATABASE=[SQLServer365], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

GO

-- Create users

USE [SQLServer365];

GO

CREATE USER [SchemaBlogUser1] FOR LOGIN [SchemaBlogUser1];

GO

ALTER USER [SchemaBlogUser1] WITH DEFAULT_SCHEMA=[SchemaBlog1];

GO

EXEC sp_addrolemember N'db_owner', N'SchemaBlogUser1';

GO

CREATE USER [SchemaBlogUser2] FOR LOGIN [SchemaBlogUser2];

GO

ALTER USER [SchemaBlogUser2] WITH DEFAULT_SCHEMA=[SchemaBlog2];

GO

EXEC sp_addrolemember N'db_owner', N'SchemaBlogUser2';

GO

-- SchemaBlogUser1 created the below script to drop table1 -- DO NOT RUN THIS PART!

IF EXISTS ( SELECT  1

            FROM    sys.objects

            WHERE   [object_id] = OBJECT_ID('Table1')

                    AND [type] = 'U' )

    DROP TABLE Table1;

GO

/*

      Connect to the instance of SQL Server as SchemaBlogUser2 to run the script

*/

-- Sets the execution context to SchemaBlogUser2 (to mimic the user executing the script)

EXECUTE AS USER = 'SchemaBlogUser2';

GO

-- Drop the table

IF EXISTS ( SELECT  1

            FROM    sys.objects

            WHERE   [object_id] = OBJECT_ID('Table1')

                    AND [type] = 'U' )

    DROP TABLE Table1;

GO

-- Revert the execution context (just to show the syntax)

REVERT;

GO

-- Set the execution context to SchemaBlogUser2 (to mimic the user executing the script)

EXECUTE AS USER = 'SchemaBlogUser2';

GO

-- Quick check of record count in SchemaBlog2.Table1

SELECT

      COUNT(*)

FROM  SchemaBlog2.Table1;

GO

/*

      Msg 208, Level 16, State 1, Line 1

      Invalid object name 'SchemaBlog2.Table1'.

*/

-- 0 Records

SELECT 

      *

FROM   

      sys.objects

WHERE  

      [object_id] = OBJECT_ID('Table1')

      AND [type] = 'U';

GO   

     

-- 1 Record

SELECT 

      *

FROM   

      sys.objects

WHERE  

      [object_id] = OBJECT_ID('SchemaBlog1.Table1')

      AND [type] = 'U';

GO

     

-- 0 Records - Wrong table has been dropped!   

SELECT 

      *

FROM   

      sys.objects

WHERE  

      [object_id] = OBJECT_ID('SchemaBlog2.Table1')

      AND [type] = 'U';

GO

     

-- The script should have read!

IF EXISTS ( SELECT  1

            FROM    sys.objects

            WHERE   [object_id] = OBJECT_ID('SchemaBlog1.Table1')

                    AND [type] = 'U' )

    DROP TABLE Table1;

GO

Enjoy!

Chris