Correct SQL Server TempDB Spills in Query Plans Caused by Outdated Statistics

转载 2016年08月31日 10:24:34

https://www.mssqltips.com/sqlservertip/4132/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics/


Problem

Statistics are an integral part of SQL Server and query performance. In short, the query optimizer uses statistics to create query plans that will improve the overall performance of the queries ran. Each statistic object is created on a list of one or more table columns and includes a histogram displaying the distribution of values in the first column. The histogram can have up to 200 steps, but no more regardless of the number of rows in the column or index. Unfortunately, I have seen a warning in my query plan that reads: "Operator used tempdb to spill data during execution with spill level 1". How do I address this warning?

Solution

In this tip we’ll take a look at one specific performance issue that you might find in an execution plan of a query which is "Operator used tempdb to spill data during execution with spill level 1". If you’ve ever noticed the following warning, then this tip is for you:

Operator used tempdb spill data during execution with spill level 1

Within the AdventureWorks2014 DB, I’ll use the following query for my example:

SELECT BusinessEntityID, FirstName, LastName, EmailPromotion
FROM [AdventureWorks2014].[Person].[Person]
WHERE EmailPromotion > 0
ORDER BY LastName
        

Looking at this query I can already tell contention may be present so I’ll go ahead and add a covering index:

CREATE NONCLUSTERED INDEX [IX_Person_EmailPromotion_INCLUDES]
ON [Person].[Person] ([EmailPromotion])
INCLUDE ([BusinessEntityID],[FirstName],[LastName])
        

When adding the index above, statistics were automatically created and updated. Since the addition of this index I’ve added a few thousand rows to the Person table.

Let’s run the query and make sure the “Include Actual Execution Plan” button is selected.

Include Actual Execution Plan in SQL Server Management Studio

After the query executes let’s take a look at the execution plan by clicking on the tab in the Results pane:

SQL Server Management Studio Execution Plan

One of the first things you’ll probably notice is the warning sign on the Sort operation. If we hover our mouse over this operator you’ll see more information including the “Warnings: Operator used tempdb to spill data during the execution with spill level 1”

SQL Server Sort operator that used tempdb instead of memory

These warnings were added to SQL Server Management Studio 2012, so if you’re using an older version you may not see this. The spill data to TempDB warning means that the query was not granted enough memory to finish the operation and spilled over into the TempDB to complete the operation. We all know reading from memory is much faster than reading from disk and this is exactly what is happening here. The query read as much as it could from memory before moving over to the TempDB disk.

Let’s look into this further. Hover over the arrow leading to the Sort operation to view the Actual Number of Rows and Estimated Number of Rows:

Estimated Number of Rows vs Actual Number of Rows has a large discrepancy

Aha! The SQL Server database engine only estimated this query would return 568,136 rows, but we actually returned 1,128,192 rows. Seeing this type of discrepancy usually means that statistics are out of date.

Let’s run DBCC SHOW_STATISTICS against our new index to view the histogram:

DBCC SHOW_STATISTICS ('Person.Person', IX_Person_EmailPromotion_INCLUDES)
        


DBCC SHOW_STATISTICS for the recently created index

As you can see from the previous screenshot, DBCC SHOW_STATISTICS returns 3 different result sets.

  • General information about the statistics object
  • Density vector
  • Histogram

We’re going to concentrate on the general information and histogram result sets for this tip. As you can see from the general information set, rows equal 319,552 and rows sampled equals 68,991

Density vector for the index with a discrepancy between the rows and rows sampled

When we run the following query you’ll notice that there are a lot more records than the general information results are displaying:

SELECT COUNT(EmailPromotion)
FROM [AdventureWorks2014].[Person].[Person]
        


Row count

Also, you can see from the column RANGE_HI_KEY, there are 177,518 records where the EmailPromotion column equals 0.

RANGE_HI_KEY values and the associated number of rows in the EQ_ROWS column

If you run the following query, however, you’ll see that 1,428,224 records exist where EmailPromotion is equal to 0:

SELECT COUNT(*)
FROM [AdventureWorks2014].[Person].[Person]
WHERE EmailPromotion = 0
        


All rows with the EmailPromotion of zero

Let’s update this statistic with FULLSCAN by running the following statement:

UPDATE STATISTICS Person.Person IX_Person_EmailPromotion_INCLUDES WITH FULLSCAN
        

Now when we run DBCC SHOW_STATISTICS we’ll notice that rows and rows sampled in the general information results are correct. We’ll also see that 1,428,224 records exist for the RANGE_HI_KEY 0. These are the results we were expecting to get.

Results of Rows and Rows Sampled UPDATE STATISTICS with FULLSCAN

Let’s run our query again and look at the execution plan:

Final query plan with the updated statistics

Yes! The warning is gone.

Next Steps
  • When a query is compiled SQL Server does not look at the data in the table or index. It uses statistics to estimate the number of rows that will be returned and that will determine the execution plan. This is why it’s important to keep statistics updated on a regular basis and included as part of your regular maintenance schedule.

SQL Server 2016新特性: tempdb增强(Multiple TempDB Files)

在数据处理的过程中,我们习惯使用SELECT INTO #Table这样的语句,用临时表来存放中间数据。大量使用临时表,会造成资源争夺,降低性能,因为在SQL Server 2012及之前的版本,t...
  • Burgess_Liu
  • Burgess_Liu
  • 2016年06月01日 16:43
  • 1098

SqlServer 一个查询语句导致tempdb增大55G

今天操作着服务器,突然右下角提示“C盘空间不足”! 吓一跳!~ 看看C盘,还有7M!!!这么大的C盘空间怎么会没了呢?搞不好等下服务器会动不了! 第一反应就想可能是日志问题,很可能是数据库日志问题 于...
  • kk185800961
  • kk185800961
  • 2015年02月05日 17:39
  • 2329

Sql Server tempdb原理-启动过程解析实践

Sql Server tempdb原理-启动过程解析实践 我们知道在SqlServer实例启动过程中数据库会进行还原(Redo,Undo)然后打开提供服务,但我们知道tempdb是不...
  • weizhiai12
  • weizhiai12
  • 2016年05月24日 16:36
  • 257

修改SQL Sever tempdb路径

原文: 点击打开链接 数据库规划时,很多人对tempdb的规划不够重视,或者根本没规划过tempdb,从而,在生产环境中会出现不少问题。 比如,空间暴增,数据库假死等等问题。 那...
  • yenange
  • yenange
  • 2014年04月02日 12:57
  • 4741

修改SQL Server的TEMPDB路径(含修改失败后,修复SQL Server)

SQL Server的TEMPDB默认存放在数据库安装路径,根据SQL Server性能或使用的需要,我们可能会需要移动TEMPDB存放的路径。 如下是描述如何移动TEMPDB及移动失败后,如何修复启...
  • wstoneh01
  • wstoneh01
  • 2016年01月21日 16:09
  • 907

认识Tempdb----配置最佳实践

一些问题需要改变配置,这里会整合所有的tempdb配置的最佳实践。主要包括:tempdb放置的位置,初始化大小及自动增长,配置多个文件。 Tempdb文件放置(File Placement) 众所...
  • Burgess_Liu
  • Burgess_Liu
  • 2014年02月28日 10:34
  • 3780

深刻的教训——SQL Server关于TempDB的使用

深刻的教训,SQL Server关于TempDB的使用。
  • Wentasy
  • Wentasy
  • 2014年04月19日 11:21
  • 5113

谈谈tempdb在系统中的重要作用

简介:        tempdb是SQLServer的系统数据库一直都是SQLServer的重要组成部分,用来存储临时对象。可以简单理解tempdb是SQLServer的速写板。应用程序与数据库都...
  • t134679
  • t134679
  • 2012年04月26日 22:19
  • 3625

丢失tempdb无法启动SQL Server2005的故障排除

最近服务器的出了些问题,将连接存储的服务器更换了之后,为了查找原因,需要重新启动SQL server,结果系统报错 Event Type: ErrorEvent Source: MSSQLSERVER...
  • cityeremite
  • cityeremite
  • 2009年07月28日 22:13
  • 1127

sql server execution plan - 执行计划的诡异 ( 二 )

sql server execution plan
  • wujiandao
  • wujiandao
  • 2015年05月04日 23:24
  • 726
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Correct SQL Server TempDB Spills in Query Plans Caused by Outdated Statistics
举报原因:
原因补充:

(最多只允许输入30个字)