September 4, 2008 at 3:46 pm
Just a simple question - I know declared tables take memory and hold it - but do derived tables used in stored procedures hold that memory the same way?
September 4, 2008 at 3:56 pm
All DML statements must first load the appropriate data pages into memory so using a derived table should not have an abnormal effect to your query. It needs to load the pages into memory regardless of how you structure your SQL code. I suppose it depends on what you mean by 'hold' it. Is there a specific concern you have with memory?
September 4, 2008 at 3:59 pm
Yes - our tempdb grew out of control over the last month. I've written a ton of code for reporting purposes for a company that was using spreadsheets to track everything. I simplfy it by using derived tables so get the results I want. Guessing we just need to restart the server periodically?
September 4, 2008 at 4:11 pm
So you're talking TempDB, not memory? Yes, restarting the service will shrink your TempDB back to it's original size, but I would only do that if you are out of disk space. If you shrink your TempDB, SQL Server will just grow it again back to the size it needs.
From BOL:
tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.
By default, tempdb autogrows as needed while SQL Server is running. Unlike other databases, however, it is reset to its initial size each time the database engine is started. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.
So is your TempDB filling up or are you having another problem with it?
September 5, 2008 at 8:12 am
It was just filling up. Then it got to big obviously and caused other issues. It had been 3 weeks since that server was restarted. Is it 'best practices' to restart it on a scheduled interval?
Why can't you just run a shrink job say once a week? I'd read where that wasn't 'best practice'.
Thank you for your info. Some of these things you just have to learn the hard way.
September 8, 2008 at 4:16 am
Have you had a look at the query plan to see ohw your queries are being executed by the optimiser? Generally, my personal preference is to use DT's rather than temp tables or tablevars, but I have found on occasion that for queries getting to the 'slightly complex' stage the optimiser can pull together odd plans which access much more data than is actually required by effectively just lumping everything together in the query rather than break things down as you might expect.
Also - as these reports used to be on spreadsheet - they're probably excellent candidates for being taken 'offline' into a reporting database using a dimensional modelling approach which may well help in the medium term
September 8, 2008 at 7:34 am
lklein (9/5/2008)
It was just filling up. Then it got to big obviously and caused other issues. It had been 3 weeks since that server was restarted. Is it 'best practices' to restart it on a scheduled interval?Why can't you just run a shrink job say once a week? I'd read where that wasn't 'best practice'.
Thank you for your info. Some of these things you just have to learn the hard way.
Our servers only restart once a year as part of the annual power down (although, some of them do, occasionally get rebooted for other reasons :sick: ). I would definitely not recommend restarting servers once a month to fix a problem like this. It's so clearly an issue with your code, not your server. You can shrink the tempdb once a week or so, but it's just going to grow again. You should spend your time fixing the cause, not addressing the symptoms.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2008 at 7:55 am
They were report starving. I just don't see how I can give them the data they want without using temp or declared tables. Any other ideas or suggestions?
September 8, 2008 at 7:57 am
You said use DT - is that a space saver for tempdb? I mostly use declared unless I need an index.
September 8, 2008 at 8:00 am
lklein (9/8/2008)
They were report starving. I just don't see how I can give them the data they want without using temp or declared tables. Any other ideas or suggestions?
I'd suggest starting another post describing the types of queries that you're trying to run. There are probably ways to do it without creating temporary tables of any type.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2008 at 6:50 am
If your queries need to sort and group data they will use space in tempdb. Basically you have 3 choices:
1) Rewrite your queries to avoid the sorting and grouping. This may not be possible.
2) Schedule your queries to run sequentially to minimise the space needed in tempdb.
3) Plan a permanent increase in the space used by tempdb.
It is not unusual to need a large tempdb in a reporting environment. At my last place we had a data warehouse of about 250 GB that needed a tempdb of 60 GB to satisfy the reporting requirements.
Best practice is to plan to have the space needed by your business. Anything less is by definition harming the business. You can optimise your queries to minimise their resource requirements, but whatever is still needed should be provided.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 9, 2008 at 11:33 am
tempdb can fill up for multiple reasons. The root cause of your problem may or not be related to your reports. Are you using SQL Server 2005, and do you have read committed snapshot isolation turned on? How long are your reports running? Are there any long running transactions?
Microsoft does a nice job covering how to determine root cause of TempDB fillups : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
If you look through this article there are queries to help determine root cause of tempdb fillup.
Please let us know how it goes.
September 11, 2008 at 12:33 pm
Grant Fritchey (9/8/2008)
Our servers only restart once a year as part of the annual power down (although, some of them do, occasionally get rebooted for other reasons :sick: ). I would definitely not recommend restarting servers once a month to fix a problem like this. It's so clearly an issue with your code, not your server. You can shrink the tempdb once a week or so, but it's just going to grow again. You should spend your time fixing the cause, not addressing the symptoms.
So how do you apply Windows updates that require a reboot? Surely you don't attempt to go an entire year without patching security issues?
September 11, 2008 at 1:39 pm
There was also a DB engine bug that wasn't recycling temp tables that were going out of scope. Assuming your SSRS is using temp tables, that might be why.
I am sorry I am not pointing you directly at something specific, but I do recall reading that in one of those Connect entries out there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 11, 2008 at 2:39 pm
matt stockham (9/11/2008)
Grant Fritchey (9/8/2008)
Our servers only restart once a year as part of the annual power down (although, some of them do, occasionally get rebooted for other reasons :sick: ). I would definitely not recommend restarting servers once a month to fix a problem like this. It's so clearly an issue with your code, not your server. You can shrink the tempdb once a week or so, but it's just going to grow again. You should spend your time fixing the cause, not addressing the symptoms.So how do you apply Windows updates that require a reboot? Surely you don't attempt to go an entire year without patching security issues?
Other reasons...
If there are security patches, of course we schedule those. But we're not rebooting once every two weeks. I don't think it's even once every two months. It's probably, on most servers, 2-3 times a year and one of those is the scheduled power-down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply