Cleveland DBA

SQL Server administration in Cleveland, OH

Stuff I learned … yesterday – statistics

Yesterday was a pretty full day between training and the ONSSUG June meeting immediately afterwards, so I didn’t get a chance to blog.  Anyway, I did learn something yesterday, but it didn’t come from the class I’m taking.  Erin Stellato (blog | twitter) gave a great presentation at ONSSUG about statistics.  I really wish she’d had more time because it was simply crammed with good information. 

Anyway, two key things I learned:

  1. Multicolumn statistics – Just what they sound like, statistics over multiple columns.  These are created by default when you create a composite index, but you can also create them yourself without an index using the CREATE STATISTICS command.  Helpful when you have queries that filter on multiple columns where no index exists.
  2. Filtered statistics – You can also put a filter on your statistics.  This is useful when you have a very large table but normally only query on a small subset of rows.  If these rows are highly selective, creating filtered statistics will help the optimizer choose the best path.

For more information on these 2 features or on statistics in general, check out Books Online.


June 8, 2011 Posted by | Features, SQL Tuning | , | Leave a comment

Plan guides and parameterization

We’ve all had this experience. A third party application is running a piece of sql that simply isn’t choosing the best query plan, and if only it would use a hash join instead of a nested loop. Or maybe it’s blocking other processes and a nolock hint would help. The problem is, you can’t change the code.

Or can you?

SQL Server plan guides effectively allow you to optimize performance of queries you can’t modify directly by attaching hints to them. Let’s say you have an application running the following query:


And after some testing, you decide it will run much better if it were using MERGE JOINs.  So you add a hint.

plan_guides_with hint

That’s all well and good, but you can’t go into the application to add that hint.  And then there’s the added complication of the usage of literal values in the sql, instead of variables.  So the optimizer will see every execution of this query for ‘Manufacturing’ as completely different from an execution for ‘Quality Assurance’.  In order for a query plan to work for any value, you’ll have to parameterize the query.  Now, you could force parameterization at the database level, but that’s a mighty big hammer for this little nail.  So, instead we’ll create a plan guide using 3 steps.

  • Step 1: We’ll use sp_get_query_template to generate a parameterized version of this query
  • Step 2: We’ll force parameterization of any query that matches this form.  
  • Step 3: We can create the plan guide. 

Sound complicated?  It’s not.  Look:

/*    Step 1: Create a parameterized version of the query. */

DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template
N'SELECT E.EmployeeID, C.LastName, C.FirstName, D.GroupName, E.Title, P.PayFrequency, P.Rate
    HumanResources.Employee E 
    inner join Person.Contact C on E.ContactID = C.ContactID
    inner join HumanResources.EmployeePayHistory P on E.EmployeeID = p.EmployeeID
            AND P.RateChangeDate = 
                (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory P2
                    WHERE P2.EmployeeID = P.EmployeeID)
    inner join HumanResources.EmployeeDepartmentHistory DH on E.EmployeeID = DH.EmployeeID
            AND DH.EndDate IS NULL
    inner join HumanResources.Department D on D.DepartmentID = DH.DepartmentID
    D.GroupName = ''Manufacturing''    AND
    E.CurrentFlag = 1
@stmt OUTPUT,            --try selecting these 2 variables to see how they get parameterized
@params OUTPUT

/*    Step 2: Force parameterization of any query that matches this form.  So, any time this 
    sql statement is executed, regardless of the literal values used, it will be parameterized.  
    Therefore all iterations of the query will look the same to the optimizer. This is critical to
    using plan guides.  */
EXEC sp_create_plan_guide N'MyTemplateGuide', 

/*    Step 3: Now that we've parameterized the query, we can apply a plan guide to it.  In this
    instance, we're giving it a hint to use a MERGE JOIN */

EXEC sp_create_plan_guide N'MyPlanGuide', 


Pretty straightforward, right?  And please note that if your query is already using parameters, you don’t need to do steps 1 and 2.  Now, let’s try that original sql again.  Notice the original code is now executing as though it has the MERGE JOIN hint:



You can see what plan guides are in your current database by querying sys.plan_guides.



And you can disable/enable a particular plan guide using sp_control_plan_guide.

disable plan_guide

And that’s it.  You’re a hero.

February 9, 2011 Posted by | SQL Tuning, T-SQL | , , | Leave a comment