November 14, 2013 at 7:13 am
One of our prod server started behaving weirdly from last couple of days. Suddenly we are seeing a growth in tempdb size and it's filling up the drive in minutes. All the connections were failing. There were no new releases or change in queries from last 10 days. I ran the query to find out what causing the growth and it showed as internal objects means related to queries. I am still surprised what caused the sudden growth of tempdb. I just noticed the select queries running while increase in the tempdb. As said before these queries were running before with no issues, but there were in large number during this time.
Is the issue related to the select stmnt queries causing the increase in tempdb, when they hit the database in huge number?
November 15, 2013 at 4:46 am
Yes it can be, depending on the code in your stored procedures and the volume of data in your database(s).
For example if you have a stored procedure being called 1000 times per second and in the stored procedure it creates a temporary table that holds 10MB of data.
You are looking at a consistent usuage of 1000 X 10MB which depending on the size of your tempdb database it could be filling it very quickly.
MCITP SQL 2005, MCSA SQL 2012
November 15, 2013 at 4:56 am
Yes, and it's not just explicit use of temp tables and temp variables that use tempdb. Maybe the query optimizer will decide that it needs to materialise a part of a query. Maybe you are using one of the snapshot isolation levels. Maybe your indexes have SORT_IN_TEMPDB set to ON. That list isn't exhaustive - there's a lot of things that can touch tempdb. As for why it's only just happening, maybe your data is larger or differently distributed than it was before and that is causing greater use of tempdb.
John
November 15, 2013 at 6:38 am
John Mitchell-245523 (11/15/2013)
Yes, and it's not just explicit use of temp tables and temp variables that use tempdb. Maybe the query optimizer will decide that it needs to materialise a part of a query. Maybe you are using one of the snapshot isolation levels. Maybe your indexes have SORT_IN_TEMPDB set to ON. That list isn't exhaustive - there's a lot of things that can touch tempdb. As for why it's only just happening, maybe your data is larger or differently distributed than it was before and that is causing greater use of tempdb.John
The query looks similar to like this.............
select a,b,c,d,e,f from (select distinct top (1) a,b, c,d,e from table A and exist (select 1 from table b where xx) and exist ( select 1 from table c where xx ) order by xx outer appy( select a,b,c,d from (select a,b,c from table c where xx) pivot (max() for b in ()) as xx) as t
No temp tables in the listed query. the main table has around 1 million rows.
November 15, 2013 at 8:47 am
Check out this script from Kendra Little:
http://www.littlekendra.com/2009/08/27/whos-using-all-that-space-in-tempdb-and-whats-their-plan/
Also, check out this post too:
http://www.sqlservercentral.com/Forums/Topic1078769-391-1.aspx
It should help you find who is behind the growth in tempdb
November 15, 2013 at 3:30 pm
Many and/or large sorts can end up in tempdb once memory is exhausted.
So if you have an unusually great number of connections running that query at the same time, it is quite possible that they are causing tempdb to grow.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply