jump to navigation

Optimising SQL Server for ad hoc workloads August 10, 2012

Posted by fofo in Sql Server, SQL Server 2008, SQL Server 2012.
Tags: , ,
1 comment so far

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!!!!

Looking into the contents of Plan Cache in SQL Server August 9, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: , ,
3 comments

In this post I will be demonstrating with hands-on examples how to look into the contents of Plan Cache.  I will be explaining what the Plan Cache (Procedure Cache) is, the process of query optimization and the generation of execution plans in detail. Then I will use some DMVs to create a custom view that will return the contents of the plan cache.

I will be using SQL Server 2012 Developer edition in this post but feel free to use SQL Server 2005,2008 versions.I will be using the AdventureWorks2012 database.You can download it from the Codeplex website.

Let me talk a bit about query optimisation and T-SQL. T-SQL is declarative by nature. When we write T-SQL statements in a query window in SSMS and execute them,we just say to SQL Server “I want these results back”. We do not provide any details on how the results will be returned.If there was nothing else between our T-SQL code and the SQL Server Database engine, we simply would not get any results back.Luckily for us there is a very important component, the Query Optimizer that generates an imperative plan. By saying imperative I mean detailed. This plan that is called execution plan is what is actually executed by the relational engine.The query optimiser will not look for a perfect plan.It is a cost-based optimiser that must find an efficient plan.The optimiser when deciding upon the execution plan will take in to consideration the type of operations,statistics (must always be up to date),indexes,hardware resources (number of CPUs ,available memory),SQL Server edition,number of active concurrent connections and query hints.

The following steps  take place upon query receival from SQL Server:

  • Parser
  • Algebriser
  • Optimiser
  • Execution plan generation – caching
  • Query execution = execution plan execution

The Parser checks the an incoming query validating it for correct syntax.The query is terminated if a syntax error is detected.On validating a query the parser generates the parse tree.

The Algebriser takes the parse tree and resolves all the names of the different objects and data types.The output of all these verifications and resolutions is a binary set of data called a query processor tree.

Based on the query tree the Optimiser finds the optimal solution to execute the query.

Plans are then cached and reused so you do not have to create a new plan every time a query is executed.That will improve the performance significantly since execution plans are costly in both time and resources.So, the first time a query is executed the plan is created,compiled and placed in the procedure-plan cache. When there are subsequent requests for that query,the query plan will be used.

Connect to an instance of SQL Server. Type (copy paste) the code below and execute it. Make sure you include the execution plan ( CTRL+M )


USE [AdventureWorks2012]

GO

SELECT  Sales.SalesOrderDetail.OrderQty ,
Sales.SalesOrderDetail.SpecialOfferID ,
Sales.SalesOrderDetail.UnitPrice ,
Sales.SalesOrderDetail.UnitPriceDiscount ,
Sales.SalesOrderHeader.Status ,
Sales.SalesOrderHeader.AccountNumber
FROM    Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
ORDER BY Sales.SalesOrderDetail.UnitPrice DESC

Ηave a look at the picture below to see the results returned with the execution of the statement above along with the execution plan.

I will not be looking into the various components of this execution plan and what they mean. You can hover over each of these components and get information like IO cost and CPU cost.

Most SQL Server developers and administrators I know, strongly believe that DMVs are the best feature that has been added to SQL Server since 2005.I feel exactly the same way.Please have a look at this post of mine that I talk about new DMVs and DMFs in SQL Server 2008 SP1 and SQL Server 2012.

Wouldn’t it be nice to be able to look into the contents of the cache?We can do that by using various DMVs.

In a new query window type the following.Do not execute anything yet.


USE AdventureWorks2012
GO
--DO NOT USE THIS COMMAND IN A PRODUCTION SYSTEM

DBCC FREEPROCCACHE

SELECT * FROM sys.dm_exec_cached_plans

SELECT * FROM Person.Person

SELECT * FROM sys.dm_exec_cached_plans

select * from sys.dm_exec_query_plan(0x060005003A0E56049009B9FF0100000001000000000000000000000000000000000000000000000000000000);

select * from sys.dm_exec_sql_text(0x060005003A0E56049009B9FF0100000001000000000000000000000000000000000000000000000000000000)

Execute line 1-5 first. I clear the contents of plan cache with the DBCC FREEPROCCACHE command.

Then (in line 7) I use the sys.dm_exec_cached_plans dynamic view that returns a row for each query plan that is cached by SQL Server.

Please have a look in this link for more information about the sys.dm_exec_cached_plans view.

At this time I do not expect to see anything in the plan cache.Then in line 9 I execute a simple SELECT statement..

An execution plan is generated. I run again the sys.dm_exec_cached_plans (line 11) view and I get back a row for this cached plan. I find out the relevant handle.

In my case is “0x060005003A0E56049029B9FF0100000001000000000000000000000000000000000000000000000000000000″

In your case it will be different so make sure you use yours instead.

Now I want to find the actual execution plan.We can use the sys.dm_exec_query_plan dynamic view.This view returns the plan in XML format for the batch specified by the plan handle.

Please have a look at this link for more information about the sys.dm_exec_query_plan view.I need to provide the handle as an input parameter.

I execute line 13 and I get the query plan in XML format for the sql query “Select * from Person.Person”

Then I want to find the SQL statement that created that plan.I will use the sys.dm_exec_sql_text dynamic view.

Please have a look at this link for more information about the sys.dm_exec_sql_text dynamic view.I need to provide the handle as an input parameter.

I execute the statement in line 15.The results I get is something like this

dbid    objectid    number    encrypted    text
5              NULL          NULL                0              SELECT * FROM Person.Person

So I managed to get the handle of the SQL query and then get the execution plan for this query and finally the text for the query itself.

Well that seems like a lot of work and I will demonstrate how to combine all these views into a custom-helper view.

In the same query window type (copy paste) and execute the following.


CREATE FUNCTION ExecPlanAndSqlText2 (@planhandle varbinary (MAX))
RETURNS TABLE
AS
RETURN SELECT EST.text,ECP.objtype,ECP.cacheobjtype,ECP.usecounts,ECP.size_in_bytes,EQP.query_plan FROM sys.dm_exec_sql_text(@planhandle) AS EST CROSS JOIN
sys.dm_exec_query_plan(@planhandle) AS EQP
JOIN sys.dm_exec_cached_plans AS ECP
ON ECP.plan_handle = @planhandle;
GO

SELECT * FROM ExecPlanAndSqlText(0x060005003A0E56049029B9FF0100000001000000000000000000000000000000000000000000000000000000);

I create a helper function first using all the dynamic management views and their relevant columns and then I pass as an input parameter the handle.

When I run the last SELECT statement I get the following results.

As you can see now I have all the information I need in one function.

In the same query window type (copy and paste) the code below.Execute the code


CREATE VIEW LookIntoPlanCache
AS
SELECT  EPAST.*
FROM    sys.dm_exec_cached_plans AS ECP
CROSS APPLY ExecPlanAndSqlText(ECP.plan_handle) AS EPAST

SELECT * FROM LookIntoPlanCache

I create a view (LookIntoPlanCache) that collects all the plan handles and passes them to our user defined function. Finally I execute a simple SELECT to see the contents of the view.

When I do that  I get the following results.

Finally I have the SQL query text,the execution plan and the times this plan was used in one view.

Make sure you use this view to look into the contents of your Plan Cache.It works for every system.

Hope it helps!!!!

Stored procedure compilation and caching July 17, 2012

Posted by fofo in general .net, Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: , ,
1 comment so far

In this post I will explain when the actual caching (the stored procedure is placed in the procedure cache ) takes place.

Many people think that the execution plan of a stored procedure is cached in the procedure cache when the stored procedure is created. That is a mistake.

You can use any edition of SQL Server of 2012/2008/2005 you want for this example. SQL Server Express edition will do just fine.Download the Express edition, here .

In my case I will use Developer edition of 2012 for these example.

I connect to the SQL Server instance.

Type (copy and paste but make sure you review it) and execute the code below


USE AdventureWorks2012
GO

DBCC FREEPROCCACHE
GO

CREATE PROCEDURE PeopleinSeattle
AS
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
FROM [AdventureWorks2012].[Person].[Address]
WHERE [City] = 'Seattle'

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

Let me explain what I do with the code above. I clear the procedure cache in line 4.Do not do that in a production environment.

Then in lines 8-17 I create a stored procedure.In lines 19-28 I use 1 DMV and 2 DMFs to get information from the procedure cache.

I get information such as the Plan Type,Use counts(how many times the plan was used),the actual query and its query plan.

When I execute lines 8-17 I get the result you see in the picture below.

It is clear that there is no stored procedure in the cache.Let’s execute the stored procedure and run the code with the DMO (DMV+DMFs) again


exec PeopleinSeattle

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

The result follows in the picture below

Only when the stored procedure is executed the Query optimiser generates a plan, compiles it and places it in the procedure cache.No plan is generated when the stored procedure is created.

Execute the code below


exec PeopleinSeattle
GO 40

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

We execute the stored procedure 40 times and we check again to see the contents of the procedure cache.

Have a look at the picture below to see the results

Now we can see that the compiled plan that was compiled and placed in the procedure cache for this particular stored procedure will be used every time this procedure is executed.That is great performance boost.

That is one of the main advantages of stored procedures.

Hope it helps!!!

Follow

Get every new post delivered to your Inbox.

Join 1,793 other followers