May 20, 2013 at 5:50 am
My tempdb is getting full within 2 hours , there are some executing SP that are using temporary tables, cursors and joins , How can I troubleshoot the same, please help.
thanx in advance.
May 20, 2013 at 6:40 am
Identify what is using lots of TempDB via the task_space_usage and session_space_usage DMVs, tune the offending queries to use less TempDB space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2013 at 7:12 am
Common causes include insufficient memory, large temporary tables,large queries silling into TempDB. use queries on Monitor TempDB usage or if you want to list current TempDB statements - List Current TempDB statements
Work on optimizing the queries
Jack Vamvas
sqlserver-dba.com
May 20, 2013 at 9:27 am
SQL Addict (5/20/2013)
My tempdb is getting full within 2 hours , there are some executing SP that are using temporary tables, cursors and joins , How can I troubleshoot the same, please help.thanx in advance.
How big is your tempdb in relation to the databases (and their largest tables) on your instance?
May 21, 2013 at 2:51 am
I have identified the stored procedures which are causing increase in tempdb size , procedures are using cursors and creating huge temp tables, what should I do to avoid this?
thanks in advance...
May 21, 2013 at 2:52 am
I have identified the stored procedures which are causing increase in tempdb size , procedures are using cursors and creating huge temp tables, what should I do to avoid this?
thanks in advance...
May 21, 2013 at 2:54 am
Work through the procedures, see if you can rewrite them not to use cursors and temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2013 at 11:48 pm
Are you receiving any Sort warnings? these can be a clue of ORDER BY spilling into TempDB and insufficient memory. Check Statistics are up to date
Jack Vamvas
sqlserver-dba.com
May 24, 2013 at 12:19 am
Jack Vamvas (5/23/2013)
Are you receiving any Sort warnings? these can be a clue of ORDER BY spilling into TempDB and insufficient memory. Check Statistics are up to date
Sorts are very prone to spilling even if everything's correct in terms of stats and there's tonnes of memory. It's because of the size of the memory required to do the sort, while SQL may well have enough memory, it's not likely to toss lots of stuff out of the buffer pool just so that one process can have a few GB of workspace memory for a sort
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2013 at 11:38 pm
When I see Sort warnings - I check a) Statistics are up to date b) Consider Query Tuning
Jack Vamvas
sqlserver-dba.com
May 26, 2013 at 8:17 pm
See if the queries don't need to sort all those rows. Re-writing the query to filter out certain rows could help. But I don't know what your needs are.
If you could use table variables instead of temporary tables, that could help. But it requires a large amount of memory. Upgrading memory could help.
Instead of temporary tables you could use permanent tables and have a routine that truncates the permanent table when it is no longer needed.
Some databases have the option SORT_IN_TEMPDB set to on. Maybe you need to set it to off.
If you could increase the size of tempdb by putting it on a different hard drive, that could improve performance too. For more information on optimizing tempdb see this link:
http://msdn.microsoft.com/en-us/library/ms175527%28v=sql.105%29.aspx
For capacity issues, see this link: http://msdn.microsoft.com/en-us/library/ms345368%28v=sql.105%29.aspx
P.S. For the sorting issue, look at the estimated execution plan. See if there is a hash join or merge join. If the sorts are using hash joins to sort, those can use a great deal of space in tempdb. If introducing an ORDER BY clause to the tables could happen before the main sorting, then the optimizer would use a merge join to get the final sorted result. It would be worth re-writing the joining and sorting portions of the stored procedure not just to save space in tempdb, but to save time. Merge sorts have both of these advantages. Merge sorts happen when the different tables being sorted are semi-sorted already. The optimizer chooses this underlying algorithm under certain conditions. If it does, it will save you space in your tempdb. If performance of the stored procedures is currently faster than acceptable and tempdb space is your absolute concern, you could rewrite the queries to use nested loops instead of hash joins. But this could be a considerable development effort and the stored procedure doing the sorting (and presumably joining) would take much longer than it was previously taking.
May 26, 2013 at 8:42 pm
Golfer22 (5/26/2013)
...
If you could use table variables instead of temporary tables, that could help. But it requires a large amount of memory. Upgrading memory could help.
...
Using table variables is not necessarily the best option. Since there are no statistics on table variables SQL Server assumes 1 row even if there are 100,000 rows. This can easily result in a poor execution plan.
Also, table variables can use tempdb just as temporary tables.
May 27, 2013 at 2:50 am
Golfer22 (5/26/2013)
If you could use table variables instead of temporary tables, that could help. But it requires a large amount of memory. Upgrading memory could help.
Unlikely, since table variables are, like temp tables, stored in TempDB and do not require a large amount of memory (no more than temp tables do)
Instead of temporary tables you could use permanent tables and have a routine that truncates the permanent table when it is no longer needed.
Personally I would not recommend doing that, it introduces concurrency issues and permanent tables log more than temp tables, so now it's the user database and user database log growing, more than TempDB would have and without all the optimisations that TempDB has for frequent table creation.
If introducing an ORDER BY clause to the tables could happen before the main sorting, then the optimizer would use a merge join to get the final sorted result. It would be worth re-writing the joining and sorting portions of the stored procedure not just to save space in tempdb, but to save time.
Sorts spill to TempDB just as hash tables do, and in fact are more likely to spill because of the memory grants required. Sorts are expensive operations and it's very likely that forcing a merge join instead of the hash that the optimiser chooses will result in a slower query that uses more resources.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply