Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspxIn SQL Server, one of the most significant architectural legacies from Sybase is the use of a single globally shared tempdb database. Any time you have something globally shared in a highly concurrent system, the shared resource can become a huge impedimentenCommunityServer 2.1 SP2 (Build: 61129.1)re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16058Mon, 17 Aug 2009 06:35:59 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16058Sankar Reddy<p>Linchi,</p>
<p>One reason why UPDATE STATISTICS uses tempdb is because of a SELECT TOP 100 ... ORDER BY within a subquery.</p>
<p>Something like below.</p>
<p>SELECT StatMan([SC0], [SB0000]) FROM </p>
<p>(SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM </p>
<p>(SELECT [cEndDateTime] AS [SC0] FROM [dbo].[Tablename] </p>
<p> &nbsp; &nbsp;WITH (READUNCOMMITTED,SAMPLE 5.949706e+001 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] )</p>
<p> &nbsp; &nbsp; AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)</p>
<p>I created a connect item on this and as per MSFT team, this will be fixed sometime after SQL Server 2008 R2.</p>
<p>Here is the connect and my blog post.</p>
<p><a rel="nofollow" target="_new" href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=457024">https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=457024</a></p>
<p><a rel="nofollow" target="_new" href="http://sankarreddy.spaces.live.com/blog/cns">http://sankarreddy.spaces.live.com/blog/cns</a>!1F1B61765691B5CD!367.entry</p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16059Mon, 17 Aug 2009 12:33:17 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16059Linchi Shea<p>Thanks Sankar for the info! That's interesting. Do you know what are the conditions in which UPDATE STATISTICS would lead to the referenced SELECT TOP query? I did a quick check and did not see the query, but will check again when I have time in the evening.</p>
<p>Also the OPTION(MAXDOP 1) clause is worrisome.</p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16060Mon, 17 Aug 2009 13:35:02 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16060Joe Chang<p>the StatMan shows up in Trace Stored Procedures SP:StmtCompleted</p>
<p>unfortunately, neither the step_direction nor the StatMan functions can be executed by the user(?)</p>
<p>Also, in S2K, the statistics only kept header, vector and histogram information, of which the histogram was limited to 200 rows, containing the range_hi_key, eq_rows, range_rows, and distinct_range_rows, plus some housekeeping info, meaning each histogram row in in your above example consumes 300+ bytes, but for int and big int type, the statblob was tpyically 8K bytes</p>
<p>in S2K5 and on, additional info is keeping for substring distribution, and I have seen the Stats_Stream &nbsp;upward of 190K</p>
<p>try DBCC SHOW_STATISTICS (test,c2) WITH STATS_STREAM</p>
<p>and see how long the field is</p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16061Mon, 17 Aug 2009 13:58:55 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16061Linchi Shea<p>&gt; the StatMan shows up in Trace Stored Procedures SP:StmtCompleted</p>
<p>Yes, of course, Joe! I was thrown off by the fact that UPDATE STATISTICS showed up in SQL:batchStarting, but not in RPC:BatchStarting, and never bothered to check SP:StmtStarting. Anyway, for UPDATE STATISTICS WITH FULLSCAN, the traced call is as follows:</p>
<p>SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [c2] AS [SC0] FROM [dbo].[test] WITH (READUNCOMMITTED) &nbsp;ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL </p>
<p>This explains the use of tempdb. And it confirms that the FULLSCAN option is not limited to a single thread, i.e. OPTION(MAXDOP 1) is not present.</p>
<p>And if I run UPDATE STATISTICS without the FULLSCAN option, the SP statement call is as follows:</p>
<p>SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] &nbsp;FROM (SELECT [c2] AS [SC0] FROM [dbo].[test] TABLESAMPLE SYSTEM (1.307525e+000 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL &nbsp;OPTION (MAXDOP 1)</p>
<p>So the default sampling rate does limit the operation to a single thread.</p>
<p>I take the trace result to mean that UPDATE STATISTICS actually runs a SQL statement through the 'normal' query processing mechanisms. I had thought that UPDATE STATISTICS might internally bypass some of the regular query processing, and therefore could have less an impact on the shared resources.</p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16063Mon, 17 Aug 2009 14:31:44 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16063AaronBertrand<p>I just don't comprehend why the TOP / ORDER BY in the subquery in the first place. &nbsp;It seems like a sort for nothing. &nbsp;Granted that I do not know the code behind StatMan(), but still, how could this code behave differently than when just saying:</p>
<p>SELECT StatMan([SC0]) FROM (SELECT [c2] AS [SC0] FROM [dbo].[test] WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL</p>
<p>?</p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16064Mon, 17 Aug 2009 14:46:47 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16064Linchi Shea<p>Aaron;</p>
<p>Maybe, SQL Server has internal mechanisms to treat these queries differently than your regular user queries. Just a WAG on my part. But then, for statistics, SQL Server does need to sort the values, doesn't it? </p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16066Mon, 17 Aug 2009 15:18:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16066Linchi Shea<p>In addition, even UPDATE STATISTICS test WITH SAMPLE 99 PERCENT results in OPTION(MAXDOP 1), but FULLSCAN does not. </p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16236Mon, 24 Aug 2009 04:09:16 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16236gvphubli<p>for stats calculation and data evaluation it has to use some space somewhere, what is the other best place than the Tempdb ?</p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#16250Mon, 24 Aug 2009 16:34:47 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16250Linchi Shea<p>gvphubli;</p>
<p>I didn't mean to suggest it's necessarily wrong to use tempdb for updating stats. But this needs to be clearly documented because tempdb is a shared resource, and is often a bottleneck, espeically when many SQL Server apps are tempdb heavy. Ideally, for some operations, it would be nice for each database to have its own scratch space.</p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#50155Thu, 18 Jul 2013 02:42:52 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50155Allen Zhang<p>Sybase can have multiple tempdb a few years back. It will be good to see SQL Server can support multiple tempdb.</p>
re: Performance impact: tempdb and update statisticshttp://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx#59197Fri, 24 Jul 2015 05:46:38 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:59197Rajesh<p>Hi Linchi,</p>
<p>Please write some more blogs.Please</p>
<p>Regards</p>