The most common performance mistake SQL Server developers make:
SQL Server estimates memory requirement for queries at compilation time. This mechanism is fine for dynamic queries that need memory, but not for queries that cache the plan. With dynamic queries the plan is not reused for different set of parameters values / predicates and hence different amount of memory can be estimated based on different set of parameter values / predicates. Common memory allocating queries are that perform Sort and do Hash Match operations like Hash Join or Hash Aggregation or Hash Union. This article covers Sort with examples. It is recommended
to read Plan Caching and Query Memory Part II after this article which covers Hash Match operations.
When the plan is cached by using stored procedure or other plan caching mechanisms like sp_executesql or prepared statement, SQL Server estimates memory requirement based on first set of execution parameters. Later when the same stored procedure is called with different set of parameter values, the same amount of memory is used
to execute the stored procedure. This might lead
to underestimation / overestimation of memory on plan reuse, overestimation of memory might not be a noticeable issue for Sort operations, but underestimation of memory will lead
to spill over tempdb resulting in poor performance.
This article covers underestimation / overestimation of memory for Sort. Plan Caching and Query Memory Part II covers underestimation / overestimation for Hash Match operation. It is important
to note that underestimation of memory for Sort and Hash Match operations lead
to spill over tempdb and hence negatively impact performance. Overestimation of memory affects the memory needs of other concurrently executing queries. In addition, it is important
to note, with Hash Match operations, overestimation of memory can actually lead
to poor performance.
To read additional articles I wrote click here.
In most cases it is cheaper
to pay for the compilation cost of dynamic queries than huge cost for spill over tempdb, unless memory requirement for a stored procedure does not change significantly based on predicates.
The best way
to learn is
to practice.
To create the below tables and reproduce the behavior, join the mailing list by using this link: www.sqlworkshops.com/ml and I will send you the table creation script. Most of these concepts are also covered in our webcasts: www.sqlworkshops.com/webcasts
Enough theory, let’s see an example where we sort initially 1 month of data and then use the stored procedure
to sort 6 months of data.
Let’s create a stored procedure that sorts customers by name within certain date range.
--Example provided by www.sqlworkshops.com
create proc CustomersByCreationDate @CreationDateFrom datetime, @CreationDateTo datetime as
begin
declare @CustomerID int, @CustomerName varchar(48), @CreationDate datetime
select @CustomerName = c.CustomerName, @CreationDate = c.CreationDate from Customers c
where c.CreationDate between @CreationDateFrom and @CreationDateTo
order by c.CustomerName
option (maxdop 1)
end
go
Let’s execute the stored procedure initially with 1 month date range.
set statistics time on
go
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-01-31'
go
The stored procedure took 48 ms
to complete.
The stored procedure was granted 6656 KB based on 43199.9 rows being estimated.
The estimated number of rows, 43199.9 is similar
to actual number of rows 43200 and hence the memory estimation should be ok.
There was
no Sort Warnings in SQL Profiler.
Now let’s execute the stored procedure with 6 month date range.
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-06-30'
go
The stored procedure took 679 ms
to complete.
The stored procedure was granted 6656 KB based on 43199.9 rows being estimated.
The estimated number of rows, 43199.9 is way different from the actual number of rows 259200 because the estimation is based on the first set of parameter value supplied
to the stored procedure which is 1 month in our case. This underestimation will lead
to sort spill over tempdb, resulting in poor performance.
There was Sort Warnings in SQL Profiler.
To monitor the amount of data written and read from tempdb, one can execute select num_of_bytes_written, num_of_bytes_read from sys.dm_io_virtual_file_stats(2, NULL) before and after the stored procedure execution, for additional information refer
to the webcast: www.sqlworkshops.com/webcasts.
Let’s recompile the stored procedure and then let’s first execute the stored procedure with 6 month date range.
In a production instance it is not advisable
to use sp_recompile instead one should use DBCC FREEPROCCACHE (plan_handle). This is due
to locking issues involved with sp_recompile, refer
to our webcasts for further details.
exec sp_recompile CustomersByCreationDate
go
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-06-30'
go
Now the stored procedure took only 294 ms instead of 679 ms.
The stored procedure was granted 26832 KB of memory.
The estimated number of rows, 259200 is similar
to actual number of rows of 259200. Better performance of this stored procedure is due
to better estimation of memory and avoiding sort spill over tempdb.
There was
no Sort Warnings in SQL Profiler.
Now let’s execute the stored procedure with 1 month date range.
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-01-31'
go
The stored procedure took 49 ms
to complete, similar
to our very first stored procedure execution.
This stored procedure was granted more memory (26832 KB) than necessary memory (6656 KB) based on 6 months of data estimation (259200 rows) instead of 1 month of data estimation (43199.9 rows). This is because the estimation is based on the first set of parameter value supplied
to the stored procedure which is 6 months in this case. This overestimation did not affect performance, but it might affect performance of other concurrent queries requiring memory and hence overestimation is not recommended. This overestimation might affect performance Hash Match operations, refer
to article Plan Caching and Query Memory Part II for further details.
Let’s recompile the stored procedure and then let’s first execute the stored procedure with 2 day date range.
exec sp_recompile CustomersByCreationDate
go
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-01-02'
go
The stored procedure took 1 ms.
The stored procedure was granted 1024 KB based on 1440 rows being estimated.
There was
no Sort Warnings in SQL Profiler.
Now let’s execute the stored procedure with 6 month date range.
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-06-30'
go
The stored procedure took 955 ms
to complete, way higher than 679 ms or 294ms we noticed before.
The stored procedure was granted 1024 KB based on 1440 rows being estimated. But we noticed in the past this stored procedure with 6 month date range needed 26832 KB of memory
to execute optimally without spill over tempdb. This is clear underestimation of memory and the reason for the very poor performance.
There was Sort Warnings in SQL Profiler. Unlike before this was a Multiple pass sort instead of Single pass sort. This occurs when granted memory is too low.
Intermediate Summary: This issue can be avoided by not caching the plan for memory allocating queries. Other possibility is
to use recompile hint or optimize for hint
to allocate memory for predefined date range.
Let’s recreate the stored procedure with recompile hint.
--Example provided by www.sqlworkshops.com
drop proc CustomersByCreationDate
go
create proc CustomersByCreationDate @CreationDateFrom datetime, @CreationDateTo datetime as
begin
declare @CustomerID int, @CustomerName varchar(48), @CreationDate datetime
select @CustomerName = c.CustomerName, @CreationDate = c.CreationDate from Customers c
where c.CreationDate between @CreationDateFrom and @CreationDateTo
order by c.CustomerName
option (maxdop 1, recompile)
end
go
Let’s execute the stored procedure initially with 1 month date range and then with 6 month date range.
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-01-30'
exec CustomersByCreationDate '2001-01-01', '2001-06-30'
go
The stored procedure took 48ms and 291 ms in line with previous optimal execution times.
The stored procedure with 1 month date range has good estimation like before.
The stored procedure with 6 month date range also has good estimation and memory grant like before because the query was recompiled with current set of parameter values.
The compilation time and compilation CPU of 1 ms is not expensive in this case compared
to the performance benefit.
Let’s recreate the stored procedure with optimize for hint of 6 month date range.
--Example provided by www.sqlworkshops.com
drop proc CustomersByCreationDate
go
create proc CustomersByCreationDate @CreationDateFrom datetime, @CreationDateTo datetime as
begin
declare @CustomerID int, @CustomerName varchar(48), @CreationDate datetime
select @CustomerName = c.CustomerName, @CreationDate = c.CreationDate from Customers c
where c.CreationDate between @CreationDateFrom and @CreationDateTo
order by c.CustomerName
option (maxdop 1, optimize for (@CreationDateFrom = '2001-01-01', @CreationDateTo ='2001-06-30'))
end
go
Let’s execute the stored procedure initially with 1 month date range and then with 6 month date range.
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-01-30'
exec CustomersByCreationDate '2001-01-01', '2001-06-30'
go
The stored procedure took 48ms and 291 ms in line with previous optimal execution times.
The stored procedure with 1 month date range has overestimation of rows and memory. This is because we provided hint
to optimize for 6 months of data.
The stored procedure with 6 month date range has good estimation and memory grant because we provided hint
to optimize for 6 months of data.
Let’s execute the stored procedure with 12 month date range using the currently cashed plan for 6 month date range.
--Example provided by www.sqlworkshops.com
exec CustomersByCreationDate '2001-01-01', '2001-12-31'
go
The stored procedure took 1138 ms
to complete.
2592000 rows were estimated based on optimize for hint value for 6 month date range. Actual number of rows is 524160 due
to 12 month date range.
The stored procedure was granted enough memory
to sort 6 month date range and not 12 month date range, so there will be spill over tempdb.
There was Sort Warnings in SQL Profiler.
As we see above, optimize for hint cannot guarantee enough memory and optimal performance compared
to recompile hint.
This article covers underestimation / overestimation of memory for Sort. Plan Caching and Query Memory Part II covers underestimation / overestimation for Hash Match operation. It is important
to note that underestimation of memory for Sort and Hash Match operations lead
to spill over tempdb and hence negatively impact performance. Overestimation of memory affects the memory needs of other concurrently executing queries. In addition, it is important
to note, with Hash Match operations, overestimation of memory can actually lead
to poor performance.
Summary: Cached plan might lead
to underestimation or overestimation of memory because the memory is estimated based on first set of execution parameters. It is recommended not
to cache the plan if the amount of memory required
to execute the stored procedure has a wide range of possibilities. One can mitigate this by using recompile hint, but that will lead
to compilation overhead. However, in most cases it might be ok
to pay for compilation rather than spilling sort over tempdb which could be very expensive compared
to compilation cost. The other possibility is
to use optimize for hint, but in case one sorts more data than hinted by optimize for hint, this will still lead
to spill. On the other side there is also the possibility of overestimation leading
to unnecessary memory issues for other concurrently executing queries. In case of Hash Match operations, this overestimation of memory might lead
to poor performance. When the values used in optimize for hint are archived from the database, the estimation will be wrong leading
to worst performance, so one has
to exercise caution before using optimize for hint, recompile hint is better in this case.
I explain these concepts with detailed examples in my webcasts (www.sqlworkshops.com/webcasts), I recommend you
to watch them. The best way
to learn is
to practice.
To create the above tables and reproduce the behavior, join the mailing list at www.sqlworkshops.com/ml and I will send you the relevant SQL Scripts.
Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2008 and SQL Server 2005 Performance Monitoring & Tuning Hands-on Workshop in London, United Kingdom during March 15-17, 2011, click here
to register / Microsoft UK TechNet.These are hands-on workshops with a maximum of 12 participants and not lectures. For consulting engagements click here.
Disclaimer and copyright information:This article refers
to organizations and products that may be the trademarks or registered trademarks of their various owners.
Copyright of this article belongs
to R Meyyappan / www.sqlworkshops.com. You may freely use the ideas and concepts discussed in this article with acknowledgement (www.sqlworkshops.com), but you may not claim any of it as your own work.
This article is for informational purposes only; you use any of the suggestions given here entirely at your own risk.
R Meyyappan
[email protected]
LinkedIn: http://at.linkedin.com/in/rmeyyappan
