Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

tempdb getting full Expand / Collapse
Author
Message
Posted Sunday, May 26, 2013 8:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, December 28, 2013 5:45 PM
Points: 94, Visits: 96
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.
Post #1456945
Posted Sunday, May 26, 2013 8:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 20,744, Visits: 32,560
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1456953
Posted Monday, May 27, 2013 2:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1457009
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse