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
