|
Sort in Tempdb Download Truly Level 400 SQL Server Performance Monitoring and Tuning
Webcast 3 Part
1:
SQLWebcast3Part1of3.wmv(103MB), Part
2:
SQLWebcast3Part2of3.wmv(116MB) & Part
3:
SQLWebcast3Part3of3.wmv(69.1MB)
- FREE Download. Webcast
3 released on February 10, 2010 (Box.net download counters as of February 14, 2012: 39,568 downloads, sum of all
3 Parts). Firewall might block downloads, if you have issues
downloading let us know, we will provide you with alternate download
links.
When to place tempdb in RAM (or Solid State Disk) or to use
query rewrite workarounds to improve ORDER BY performance:
About the article:
This is an excerpt from the
3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop; I explain the concepts in the
workshop with examples. I try to do the best while writing this
article, but it is not the same!!
This article discusses when to use Solid State
Disk (or RAMDISK) or to use query rewrite workarounds to improve
ORDER BY performance.
If you find this article helpful, give me feedback, this
motivates me to write some more articles. I also encourage you to attend the
3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop.
Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2012 and 2008 Performance Monitoring & Tuning Hands-on Workshop, for workshop
schedule click
here. These are hands-on workshops
with a maximum of 12 participants and not lectures.
For consulting engagements click
here.
To read additional articles I wrote
click
here.
This article helps you to understand when to use Solid State
Disk (or RAMDISK) or to use query rewrite workarounds to improve
ORDER BY performance.
The purpose of this article is to help you
better understand SQL Server Performance Monitoring & Tuning. The purpose of this article is not to discuss whether
this is a bug or not.
Lubor Kollar, Customer Advisory Team, SQL Server Development,
Microsoft Corporation:
I have attended the first two days of Ramesh’s Performance
Monitoring and Tuning Hands-on Workshop in Prague on Oct. 15 and 16.
My main goal was to learn in detail the contents of Ramesh’s
class so that I can suggest his class to other Microsoft
customers I’m working with in the role of SQL Server Customer
Advisory Team manager. I was very pleased both with the content
and delivery of Ramesh’s class. He is providing useful, deep and
very accurate information that will help our customers when
developing, testing, tuning and deploying their applications using SQL Server or upgrading existing applications from
previous SQL Server releases. Ramesh showed extraordinary
attention to detail and he explained in depth the internals of
the engine.
Description:
When a customer asks you: I have a query that does ORDER BY, the
query is slow, how can I improve the performance of the query ?
One limitation that I have is I cannot create index for all
possible combination of ORDER BY as users choose which columns
to ORDER BY on the fly. This article will help you answer the
question.
When a query contains ORDER BY and if there is
a supporting index then SQL Server can use the index to avoid a
sort. When there is no supporting index SQL Server has to sort
the rows, especially when sorting over columns from different
tables on a join, sort can be in-memory or it could spill to
tempdb. By running the query in isolation and observing tempdb
activity one can tell if the sort is spilling to tempdb. It is
possible to monitor sort spilling to tempdb from the profiler
event ‘Sort Warnings’.
If the sort is spilling to tempdb – the first
thing to check is if the statistics are up to date. SQL Server
estimates memory grants for sort based on statistics, when
statistics are out of date, the estimation might be low leading
to low memory grants which could be a reason for the sort
spilling to tempdb. The other reason for sort spilling to tempdb
is that the server does not have enough (workspace) memory
available.
One way to speed up the sort is to keep tempdb
on a faster disk, like Solid State Disk or RAMDISK. The other
way is to avoid sort spilling to tempdb in the first place, this
could be achieved by rewriting the query (in such a way that
more memory would be granted for this query avoiding sort
spilling to tempdb).
In some cases it is not possible to create an
index to optimize ORDER BY as the columns to ORDER BY could be
from different tables that are joined (like in the below
example).
Example:
This SQL Server example needs tables that you have to create. The script to
create the table is located at the end of this article(www.sqlworkshops.com/sort_in_tempdb).
Based on your server configuration, it might be necessary to
alter the test query predicate (5000 / 7000) to observe the
stated behavior.
1. Save tempdb data file statistics before executing the test
query:
--example provided by
www.sqlworkshops.com
--save old tempdb data file statistics
select
vfs.*
into dm_io_virtual_file_stats_start
from
sys.master_files mf
inner
join
sys.dm_io_virtual_file_stats(NULL,
NULL) vfs
on (mf.database_id
= vfs.database_id
and mf.file_id
= vfs.file_id)
where mf.type
= 0 and
mf.database_id
= 2
go
2.1: Sort having enough granted memory does not spill to tempdb:
--example provided by
www.sqlworkshops.com
--update statistics to have better estimates
update
statistics tab71
with
fullscan
--set statistics time to see how long the query took to complete
set
statistics
time on
--set rowcount 1 to stop the results after 1 row has been
output, to avoid filling-up the query window
set
rowcount 1
--query that does the below sort has enough memory that it does
not spill to tempdb
select
* from
tab71 where c1
<= 5000
order by c2
option
(maxdop 1)
set
rowcount 0
set
statistics
time
off
go
The above
query completes in about 15ms (depends on your server).
2.2: Sort without having enough granted memory spills to tempdb:
--example provided by
www.sqlworkshops.com
--update statistics to have better estimates
update
statistics tab71
with
fullscan
--set statistics time to see how long the query took to complete
set
statistics
time on
--set rowcount 1 to stop the results after 1 row has been
output, to avoid filling-up the query window
set
rowcount 1
--query that does the below sort does not have enough memory and
it spill the sort to tempdb
select
* from
tab71 where c1
<= 7000
order by c2
option
(maxdop 1)
set
rowcount 0
set
statistics
time
off
go
The above
query completes in about 190ms (depends on your server).
Notice that we did update statistics to have better estimates.
The reason for this query taking longer than the first test
query is that this query spills the sort to tempdb.
The amount
of data written to tempdb is about 8MB during this test
query execution. You can find this yourself if you follow step 3
& 4.
I was able
to rewrite the second test query to achieve 28ms
execution time. This was done by rewriting the query(without
changing the schema or adding indexes) in such a way SQL Server
grants more memory avoiding sort spilling to tempdb. Solid State
Disk or RAMDISK can also be used to improve performance of the
above query, query rewrite will get the best execution time in
this case as we are well below workspace memory limit.
2.3: Top 500 sort without having enough granted memory spills
to tempdb:
--example provided by
www.sqlworkshops.com
--update statistics to have better estimates
update
statistics tab71
with
fullscan
--set statistics time to see how long the query took to complete
set
statistics
time on
--set rowcount 1 to stop the results after 1 row has been
output, to avoid filling-up the query window
set
rowcount 1
--query that does the below top 500 sort does not have enough
memory and it spill the sort to tempdb
select
top 500
* from tab71
where c1
<= 7000
order by c2
option
(maxdop 1)
set
rowcount 0
set
statistics
time
off
go
The above
query completes in about 500ms (depends on your server).
Notice that we did update statistics to have better estimates.
The reason for this query taking longer than the first test
query is that this query spills the top sort to tempdb.
The amount
of data written to tempdb is about 11.5MB during this
test query execution. You can find this yourself if you follow
step 3 & 4. The high tempdb usage is due to multiple passes SQL
Server makes over sorted output.
I was able
to again rewrite the third test query to again achieve 28ms
execution time. This was done by rewriting the query(without
changing the schema or adding indexes) in such a way SQL Server
grants more memory avoiding sort spilling to tempdb. Solid State
Disk or RAMDISK can also be used to improve performance of the
above query, query rewrite will get the best execution time in
this case as we are well below workspace memory limit.
2.4:
Sort on a join without having enough granted memory spills to
tempdb:
--example provided by
www.sqlworkshops.com
--update statistics to have better estimates
update
statistics tab71
with
fullscan
--set statistics time to see how long the query took to complete
set
statistics
time on
--set rowcount 1 to stop the results after 1 row has been
output, to avoid filling-up the query window
set
rowcount 1
--query that does the below sort on a join does not have enough
memory and it spill the sort to tempdb
select
* from
tab71 t1
inner
join tab71 t2
on (t1.c1
= t2.c1)
where t1.c1 <= 7000
order
by t1.c2,
t2.c2
option (maxdop
1)
set
rowcount 0
set
statistics
time
off
go
The above
query completes in about 300ms (depends on your server).
Notice that we did update statistics to have better estimates.
The reason for this query taking longer than the first test
query is that this query spills the top sort to tempdb.
The amount
of data written to tempdb is about 14MB during this test
query execution. You can find this yourself if you follow step 3
& 4.
I was able
to again rewrite the fourth test query to again achieve 67ms
execution time. This was done by rewriting the query(without
changing the schema or adding indexes or indexed views) in such
a way SQL Server grants more memory avoiding sort spilling to
tempdb. Solid State Disk or RAMDISK can also be used to improve
performance of the above query, query rewrite will get the best
execution time in this case as we are well below workspace
memory limit.
3. Save tempdb data file statistics after executing the test
query:
--example provided by
www.sqlworkshops.com
--save old tempdb data file statistics
select
vfs.*
into dm_io_virtual_file_stats_end
from
sys.master_files mf
inner
join
sys.dm_io_virtual_file_stats(NULL,
NULL) vfs
on (mf.database_id
= vfs.database_id
and mf.file_id
= vfs.file_id)
where mf.type
= 0 and
mf.database_id
= 2
go
4. Calculate tempdb data file activity that occurred during for
the test query:
--example provided by
www.sqlworkshops.com
--delta tempdb data file statistics
select
(sum(t2.num_of_reads)
-
sum(t1.
num_of_reads))
as num_of_reads,
(sum(t2.num_of_writes)
-
sum(t1.
num_of_writes))
as num_of_writes,
(sum(t2.num_of_bytes_read)
-
sum(t1.
num_of_bytes_read))
/ 1024.0
as num_of_bytes_read_KB,
(sum(t2.num_of_bytes_written)
-
sum(t1.
num_of_bytes_written))
/ 1024.0
as num_of_bytes_written_KB
from
dm_io_virtual_file_stats_start t1 inner
join
dm_io_virtual_file_stats_end t2 on
(t1.file_id
= t2.file_id)
go
Tempdb on Solid State Disk or RAMDISK:
When sort spills to tempdb, performance can be significantly
worse. There are cases where sort can spill to tempdb even when
you have lot of (workspace) memory and statistics are up to date
(like in the above example). In this case placing tempdb on
Solid State Disk or RAMDISK will provide significant performance
improvement.
Placing tempdb on RAM or Solid State Disk is
ideal when your query needs more that the available workspace
memory, when you are below this limit, you can workaround with
query rewrite.
Query Rewrite:
In most
cases, without using Solid State Disk or RAMDISK, one can
workaround the issue by rewriting the query to have extra memory
granted to avoid sort spilling to tempdb (up to workspace memory
limitation). I have found ways to achieve this even for TOP N
queries.
Query rewrite procedure is now explained in the free webcasts: www.sqlworkshops.com/webcasts.
Download Truly Level 400 SQL Server Performance Monitoring and Tuning
Webcast 2 Part 1:
SQLWebcast2Part1of3.wmv(35.1MB), Part
2:
SQLWebcast2Part2of3.wmv(34.9MB) & Part
3:
SQLWebcast2Part3of3.wmv(30.6MB)
- FREE Download.
Webcast 2 released on January 20, 2010 (Box.net download
counters as of February 14, 2012: 23,953
downloads, sum of all 3 Parts).
Download Truly Level 400 SQL Server Performance Monitoring and Tuning
Webcast 1 Part 1:
SQLWebcast1Part1of3.wmv(29.1MB), Part
2:
SQLWebcast1Part2of3.wmv(47.5MB) & Part
3:
SQLWebcast1Part3of3.wmv(37.4MB)
- FREE Download.
Webcast 1 released on November 21, 2009 (Box.net download
counters as of February 14, 2012: 36,079
downloads, sum of all 3 Parts).
To receive example SQL
Scripts used in webcasts:
Sign up for Email Updates.
Please take a few minutes and pass on the webcast link to your friends and local
user community. Feedback from a Microsoft Employee: Your
webcast rocked. Your free webcast was very good and informative.
Additional Information:
If you have attended my SQL 2005 workshop(s) in the past
two and a half years you know how to monitor tempdb activity and
when to place tempdb on a Solid State Disk or RAMDISK.
I encourage you to attend the
3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop, where you will gain real practical
knowledge.
This level 400 workshop requires participants to have basic prior SQL Server experience, specifically knowledge about Cluster, Heap and Non-cluster index, Index Tuning Wizard and SQL Profiler - to avoid repetition. The workshop covers specifically SQL Server Performance Monitoring & Tuning
topics - doesn't cover Disaster Recovery topics on the side –
to keep the focus.
Have you been to a workshop where the
instructor explains the difference between Clustered and Heap
index (or demonstrates SQL Profiler) and you felt like having an
another coffee break ?
Have you been to a workshop where the instructor explains deeply
about DBCC CHECKDB and then 2 years later you felt like you
could have spent that time on a vacation because it didn’t make
a dent in your career ?
Well this workshop will be an entirely different
experience. This truly level 400 workshop gives you an opportunity to really
gain practical knowledge and use that knowledge to advance
your career.
Microsoft Employees (including senior members
of Microsoft Consulting Services, Support and SQL Server Product
Development team) have participated in the 3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshops delivered by R Meyyappan. SAP AG’s IT Department DBAs
in Walldorf, Germany have participated in Level 300 Microsoft SQL Server Administration & Tuning for SAP Customers Hands-on Workshops delivered by R Meyyappan.
If you find this article helpful, give me feedback, this
motivates me to write some more articles. I also encourage you to attend the
3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop.
Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2012 and 2008 Performance Monitoring & Tuning Hands-on Workshop, for workshop
schedule click
here. These are hands-on workshops
with a maximum of 12 participants and not lectures.
For consulting engagements click
here.
Charges: 2100 Euro (VAT excl.). For Agenda click here.
For Workshop schedule and registration information 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.
Table:
--example provided by
www.sqlworkshops.com
create
table tab71
(c1 int
primary
key clustered,
c2 int,
c3 char(1000))
go
begin
tran
go
declare
@i int
set
@i = 1
while
@i <= 10000
begin
insert
into tab71(c1,
c2, c3)
values
(@i, @i,
'a')
set
@i = @i
+ 1
end
commit
tran
go
Webcast Agenda:
Webcast 1: Explains Memory allocation
issues with sort. Demonstrates ways to identify sort spills
to tempdb. Provides query rewrite procedure to avoid sort
spills to tempdb. Demonstrates cases where 1 tempdb date
file per core might not be ideal for all implementations.
Webcast 2: Explains high CXPACKET waits are
NOT a direct result of delays associated with inefficiencies
of parallel processing. Provides example to scale queries
over many CPU cores without reducing MAXDOP settings.
Webcast 3: Recommends not using stored
procedure or other plan caching mechanism like using
sp_executesql and Prepared Statement using ADO.NET or OLEDB
based executions for memory allocating queries. Common
memory allocating queries are that perform Sort and do Hash
Match operations like Hash Join or Hash Aggregation or Hash
Union.
With examples provides ways to identify queries performing
Hash match operations that spill to tempdb. Using SQL
Profiler: Hash Warnings (Hash Recursion and Hash Bailout).
Using sys.dm_exec_query_memory_grants: Granted Memory, Used
Memory and Maximum Used memory. Explains how SLEEP_TASK wait
type is associated with Hash Warnings.
Explains with examples how sp_recompile can block and bring
an application to a standstill and recommends using DBCC
FREEPROCCACHE (plan_handle) instead.
Explains with examples how stored procedure or other plan
caching mechanism affects queries that sort (perform order
by). Explains how IO_COMPLETION wait type is associated with
Sort Warnings.
Demonstrates why rollbacks and database restore could wait
on IO_COMPLETION wait type and if needed how this wait can
be reduced.
Explains how parallel query performance is significantly
affected by a CPU intensive query executing on one of the
CPU cores. Explains the reason for the performance issue and
how to identify the issue with SLEEP_TASK wait type. With
example explains the reason for the observed query execution
time when the child thread executes on the CPU core where
the CPU intensive query executes and also when the
coordinator executes on the CPU core where the CPU intensive
query executes. Recommends ways to avoid this parallel sort
query performance issue and also demonstrates that only
certain queries will be affected by this and not all
parallel queries.
Explains sys.dm_os_waits_stats.signal_wait_time_ms does not
indicate system wide CPU pressure, just at a CPU core level.
Explains the prefetch mechanism and how it can affect query
performance. Provides ways to force prefecth. Demonstrates
cases where Avg. Disk Sec / read, the disk latency,
PAGEIOLATCH_SH, Avg. disk queue length is very high and the
query executes fast.
Explains why Plan Guides (Plan Freezing!) cannot help with
prefetch or in few other cases.
Ramesh's consulting customers include Austria:
Allgemeines Krankenhaus Linz, BMD Systemhaus,
Bundesrechenzentrum, bwin, C07 IT Operations, CMC Markets,
derStandard, ERGO Insurance Service, Fabasoft Austria,
Greentube, IAEA (UN), Interwetten, INTERSPORT, Kapsch, Linz
AG, Mayr-Melnhof Karton, NÖM, ÖBB, OMV, Österreichische
Post, RACON West Software, Raiffeisen Bankengruppe,
Raiffeisen Informatik, S.Spitz, Salzburg AG, Siemens, TIWAG,
UC4, Vinzenz Gruppe, Wiener Zeitung, Wirtschaftskammer
Österreich; Bulgaria: MobilTel;
Czech Republic: Česká pojišťovna, ČSOB Bank, GTS
Novera, Telefónica O2, Vodafone; Denmark:
Danfoss, Egmont, NNIT; Finland: Cap Gemini,
Kemira, Pohjola; Germany: JAM Software, SAP
AG; Netherlands: De Nederlandsche Bank;
Norway: Acta, Aker Solutions, Det Norske
Veritas(DNV), Komplett; Russia: Mediatel;
Serbia: Tarkett; Slovakia:
Adastra Corporation, VÚB Banka; Slovenia:
Bankart, Iskratel, KDD, Krka, Mobitel, Pošta Slovenije,
Sava, SRC.SI; South Africa: De Beers,
Kumba, Sasol; Spain: CIE Automotive, Gas
Natural, ITP; Sweden: Sony Ericsson;
Switzerland: F. Hoffmann-La Roche, Philip
Morris International; UAE: Dubai Customs.
For past consulting engagement feedbacks click
here.
COPYRIGHT AND TRADEMARK NOTICES:
The copyright in all material provided on this site is held
by SQLWorkshops.com or by the original creator of the material.
Except as stated herein, none of the material may be copied,
reproduced, distributed, republished, downloaded, displayed,
posted or transmitted in any form or by any means, including,
but not limited to, electronic, mechanical, photocopying,
recording, or otherwise, without the prior written permission of
SQLWorkshops.com or the copyright owner. Permission is granted
to download the webcast on this Site for personal,
non-commercial use only, provided you do not modify the webcast
and that you retain all copyright and other proprietary notices
contained in the webcast. You also may not, without
SQLWorkshops.com permission, "mirror" any webcast contained on
this Site on any other server. This permission terminates
automatically if you breach any of these terms or conditions.
Upon termination, you must immediately destroy any downloaded
webcast. Any unauthorized use of any webcast contained on this
Site may violate copyright laws, trademark laws, the laws of
privacy and publicity, and communications regulations and
statutes.
Microsoft is a registered trademark of Microsoft Corporation in
the United States and/or other countries. SQL Server is a
trademark of Microsoft Corporation in the United States and/or
other countries. This webcast may refer to organizations and
products that may be the trademarks or registered trademarks of
their various owners.
You may freely use the ideas and concepts discussed in this
webcast, but you may not claim any of it as your own work. You
may quote freely, with acknowledgements to SQLWorkshops.com.
This article is for informational purposes only; you use any of
the suggestions given here entirely at your own risk.
|
