Optimising SQL Server for ad hoc workloads August 10, 2012Posted by fofo in Sql Server, SQL Server 2008, SQL Server 2012.
Tags: ad hoc workloads, execution plan, Plan Cache
In this post I will be demonstrating with hands-on examples how to optimise your SQL Server for “ad hoc workloads“. I will be using the view LookIntoPlanCache that I created in this post .Make sure to read this post if you are not familiar with terms like Query Optimiser,execution plans and Plan Cache.
I will be using SQL Server 2012 Developer edition in this post but feel free to use SQL Server 2008/R2 versions.I will be using the AdventureWorks2012 database.You can download it from the Codeplex website.
There are times when the application code (high level code) includes ad-hoc queries/statements and there are no stored procedures in the code when it should.There are many cases when developers do not even use parameters. By that I mean that the developer code is written in a way that does not make use of execution plan reuse. Some people will say “Hey man, I do not care I have enough memory in my SQL Server box”.Well, SQL Server just loves memory and it is the one hardware resource that we must keep an eye on.
It is never a good argument to claim that there is “enough hard disk or memory in my system” so I do not care about fixing things…
Imagine that you have a reused plan in the plan cache and because of ad-hoc queries/workloads that plan is pushed out of the plan cache.That does not help the performance of the system.When you have many plans in the cache that are not reused at all that can lead to procedure cache bloat.In this case you have many entries (execution plans) in the plan cache that are used only one and if this happens far too often good plans (heavily reused plans) are forced out of the plan cache.
There is a setting that can be configured in SQL Server to approach such issues when we have lots of adhoc queries.It is called Optimize for Ad Hoc Workloads and you can enable it by using the system stored procedure sp_configure. By default this setting is off.When enabling this feature you must keep in mind that it will affect newly created plans and not the ones that are already placed in the plan cache.
Let me move on with my hands-on examples.
Connect to an instance of SQL Server. Type (copy paste) the code below.Do not execute anything yet.
USE AdventureWorks2012; GO IF OBJECT_ID ( 'Person.uspGetPersons', 'P' ) IS NOT NULL DROP PROCEDURE Person.uspGetPersons; GO CREATE PROCEDURE Person.uspGetPersons @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, Title FROM Person.Person WHERE FirstName = @FirstName AND LastName = @LastName; GO DBCC FREEPROCCACHE SELECT * FROM dbo.LookIntoPlanCache EXEC Person.uspGetPersons N'Miller', N'Dylan' GO EXEC Person.uspGetPersons N'Duffy', N'Terry'; GO EXEC person.uspgetpersons N'Brown', N'Kevin' GO SELECT * FROM dbo.LookIntoPlanCache
I execute line 1-2 to make AdventureWorks2012 the current database. Then I will create a simple stored procedure in lines 8-17.In lines 4-6 I just check to see if the stored procedure already exists on the system. Then in line 19 I clear the plan cache and use the LookIntoPlanCache view to see the contents of the plan cache.There will be no contents at this stage.
Then I execute the lines 23-28 .There are 3 calls to the stored procedure.Please note that I have typed the name of the procedure with 3 different ways.
Finally I execute the LookIntoPlanCache view to check the contents of plan cache once more.Have a look at the picture below to see the results I got.
The plan for the stored procedure is compiled, cached and reused.Having typed the stored procedure’s name in a different way does not matter .
In a new query window type (copy paste) the following t-sql code.Do not execute anything yet.
USE AdventureWorks2012 GO DBCC FREEPROCCACHE SELECT * FROM [AdventureWorks2012].[Sales].[Currency] GO SELECT * FROM [AdventureWorks2012].[Sales].[Currency]; GO SELECT * FROM [dbo].[LookIntoPlanCache] GO DBCC FREEPROCCACHE SELECT * FROM [AdventureWorks2012].[Sales].[Currency] SELECT * FROM [AdventureWorks2012].[Sales].[Currency]; SELECT * FROM [dbo].[LookIntoPlanCache] GO
Execute line 4.Once more I clear the contents of plan cache.Then I execute 2 simple SELECT statements.Please note that I in the second SELECT statement I have added a ‘;’.
Ηave a look at the picture below to see the results returned with the execution of the statements.
You can see that I get 2 execution plans for an identical query.The first thing to note here is that we should try and write T-SQL ad hoc queries in the same way. The second thing is that ad-hoc statements get different treatment compared to stored procedures.
Then in line 17 I clear the cache once more.Then I execute (line 19-22) the two SELECT statements in the same batch.Then I look into the contents of the plan cache using the LookIntoPlanCache view.
Have a look in the picture below to see what my results are.
It is one entry this time in the plan cache but the plan size is twice the size.
Let’s see now how we can optimise SQL Server for ad-hoc workloads
In a new query window type (copy – paste the following).Do not execute anything yet.
USE AdventureWorks2012 GO DBCC FREEPROCCACHE SELECT * FROM [AdventureWorks2012].[Production].[ProductModel] GO SELECT * FROM [AdventureWorks2012].[Purchasing].[ShipMethod] GO SELECT * FROM [AdventureWorks2012].[Sales].[Currency] GO SELECT * FROM [dbo].[LookIntoPlanCache] GO sp_configure 'show advanced options', 1; GO RECONFIGURE GO sp_configure 'optimize for ad hoc workloads', 1; GO RECONFIGURE SELECT * FROM [AdventureWorks2012].[Production].[ProductModel] GO SELECT * FROM [AdventureWorks2012].[Purchasing].[ShipMethod] GO SELECT * FROM [AdventureWorks2012].[Sales].[Currency] GO SELECT * FROM [dbo].[LookIntoPlanCache] GO
I execute line 1-2 to make AdventureWorks2012 the current database.Then I clear the cache once more and execute three SELECT statements (lines 6-16) (ad-hoc queries).
Then in lines 18-20 I look into the plan cache once more.
Have a look at the picture below to see the results I have got.
Those results were the ones I expected. If most of your workload or even 25-40% percent of your workload is ad-hoc queries you should optimise SQL Server differently.
Execute lines 23-29. That will enable SQL Server to optimise execution plans for ad-hoc queries.Then execute the same 3 SELECT statements (lines 31-41) as before.Finally execute the view (lines 43-45) to look into the contents of plan cache once more.
Have a look at the picture below to see the results I have got.
Instead of a compiled plan I have a Compiled Plan Stub.The size that each of these stubs is 272 bytes. That size is significantly less (150 times) than the size that the original compiled plans occupied. What will happen if I run again the 3 SELECT statements in lines 31-41? When I execute these lines again along with the view that looks the contents of the plan cache, I get the following results
As you can see now there are no more Compiled Plan Stubs for the 3 SELECT statements but compiled plans that take “full” size in the plan cache.So, when the same query is executed for a second time the full execution plan is generated.
To recap, make sure you write your code using stored procedures and parameterised queries. If you do not have access to the high level code and that code has been written by an “ad-hoc query” enthusiast make sure you optimise SQL Server accordingly,thus to optimise SQL Server for ad-hoc workloads.
Hope it helps!!!!