February 28, 2013 at 11:06 am
ScottPletcher (2/28/2013)
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.Indeed it is, but so's shrinking TempDB on startup. I'll typically have monitoring or automatic reports on autogrow events (for any DB) and TempDB utilisation.
I capture autogrow events as well. But that doesn't prevent them from reoccuring every start up for tempdb. So you force a manual process to increase tempdb rather than doing it automatically?
Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.
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
February 28, 2013 at 11:08 am
If you were successfully sizing tempdb to be the size that SQL needs, you wouldn't be using startup procs to check file sizes and be concerned about checking the files to see if they grew any and resizing the files if it happened.
That's okay. You keep doing it the hard way and making tempdb take stalls for auto-growth events. I'm going to do it the smart way and ensure that tempdb doesn't have to auto-grow. Less work for me and better performance for my tempdb.
February 28, 2013 at 11:12 am
ScottPletcher (2/28/2013)
So if tempdb now does need to grow for whatever reason, and it causes a SQL issue because it can't, you've guaranteed that there's no easy way to recover, since the current physical files absolutely can't be expanded?
A) How does this differ from tempdb trying to autogrow past the size of the drive and failing because you've filled up the drive?
B) Yes, there's an easy way to recover. The transaction returns an error and rolls back. Which is exactly the same thing that would happen if you auto-grew the file to use the whole drive and it ran out of space, except in my case, it would happen quicker because it wouldn't have to attempt the auto-growth before failing.
What are you expecting to happen? Really, your arguments are not logical.
February 28, 2013 at 11:21 am
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.Indeed it is, but so's shrinking TempDB on startup. I'll typically have monitoring or automatic reports on autogrow events (for any DB) and TempDB utilisation.
I capture autogrow events as well. But that doesn't prevent them from reoccuring every start up for tempdb. So you force a manual process to increase tempdb rather than doing it automatically?
Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.
I don't miss the signs of it.
I, too, log when autogrow occurs anywhere. And notify immediately when it occurs on tempdb.
You just have to have someone manually intervene to correct it. I auto-adjust it whenever possible, so that the error can be corrected w/o requiring manual intervention.
Yes, a person reviews and re-adjusts later if necessary, but performance doesn't stay degraded even if someone can't manually intervene.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 11:28 am
ScottPletcher (2/28/2013)
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.Indeed it is, but so's shrinking TempDB on startup. I'll typically have monitoring or automatic reports on autogrow events (for any DB) and TempDB utilisation.
I capture autogrow events as well. But that doesn't prevent them from reoccuring every start up for tempdb. So you force a manual process to increase tempdb rather than doing it automatically?
Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.
I don't miss the signs of it.
I, too, log when autogrow occurs anywhere. And notify immediately when it occurs on tempdb.
You just have to have someone manually intervene to correct it. I auto-adjust it whenever possible, so that the error can be corrected w/o requiring manual intervention.
Yes, a person reviews and re-adjusts later if necessary, but performance doesn't stay degraded even if someone can't manually intervene.
One big difference. If I'm paying attention the autogrow never happens. I'll have seen the increased usage of TempDB and manually grown the files (at a quiet time) before the autogrow would be required.
TempDB autogrowing is a sign that I've stuffed up, not done my job properly.
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
February 28, 2013 at 11:30 am
Robert Davis (2/28/2013)
ScottPletcher (2/28/2013)
So if tempdb now does need to grow for whatever reason, and it causes a SQL issue because it can't, you've guaranteed that there's no easy way to recover, since the current physical files absolutely can't be expanded?
A) How does this differ from tempdb trying to autogrow past the size of the drive and failing because you've filled up the drive?
B) Yes, there's an easy way to recover. The transaction returns an error and rolls back. Which is exactly the same thing that would happen if you auto-grew the file to use the whole drive and it ran out of space, except in my case, it would happen quicker because it wouldn't have to attempt the auto-growth before failing.
What are you expecting to happen? Really, your arguments are not logical.
I have additional space to grow, you prevent it.
So if a task unexpectedly needs, say, 50MB more of tempdb space to complete, you abend it -- perhaps after it ran for hours, so you can look forward to twice that long in rollback time -- while I let it complete.
It's 100% illogical to assume that ANY expansion of tempdb will result in a runaway condition that will fill all tempdb drives.
[Yes, I had to manage dozens of servers spread across the country, and I didn't get to do daily watch over all of them all of the time, so I made the "second/third" tier servers as self-correcting as possible. But the above situation could apply to any server any time.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 11:37 am
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.Indeed it is, but so's shrinking TempDB on startup. I'll typically have monitoring or automatic reports on autogrow events (for any DB) and TempDB utilisation.
I capture autogrow events as well. But that doesn't prevent them from reoccuring every start up for tempdb. So you force a manual process to increase tempdb rather than doing it automatically?
Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.
I don't miss the signs of it.
I, too, log when autogrow occurs anywhere. And notify immediately when it occurs on tempdb.
You just have to have someone manually intervene to correct it. I auto-adjust it whenever possible, so that the error can be corrected w/o requiring manual intervention.
Yes, a person reviews and re-adjusts later if necessary, but performance doesn't stay degraded even if someone can't manually intervene.
One big difference. If I'm paying attention the autogrow never happens. I'll have seen the increased usage of TempDB and manually grown the files (at a quiet time) before the autogrow would be required.
TempDB autogrowing is a sign that I've stuffed up, not done my job properly.
Yes, you're clearly in the preferred position, where you have massive time to devote to every server.
And you never have developer(s) accidentally overfill a temp table(s).
Have to admit, International Paper wasn't that organized, and with dozens of servers spread across the country, and hundreds of developers -- who were not fully monitored all the time -- we had things "come up" with tempdb.
Hmm, so do you too, then, disable autogrow on your temp tables? That's an interesting approach, although I admit I'm not ready to try it on my company's system.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 11:37 am
ScottPletcher (2/28/2013)
I have additional space to grow, you prevent it.
So if a task unexpectedly needs, say, 50MB more of tempdb space to complete, you abend it -- perhaps after it ran for hours, so you can look forward to twice that long in rollback time -- while I let it complete.
Umm, no. My file size is already much larger than yours, so if a process needs say 50MB more space, yours has to stall and perform an auto-grow while my continues onward happily because my file is already large enough.
If on the other hand, the file has already autogrown to fill the entire drive (and mine is already pre-sized to that size) then where are you getting this additional space to grow like you say above? Please educate me on these magical tempdb drive fairies that give you additional space even when the drive is full.
February 28, 2013 at 11:41 am
Robert Davis (2/28/2013)
ScottPletcher (2/28/2013)
I have additional space to grow, you prevent it.
So if a task unexpectedly needs, say, 50MB more of tempdb space to complete, you abend it -- perhaps after it ran for hours, so you can look forward to twice that long in rollback time -- while I let it complete.
Umm, no. My file size is already much larger than yours, so if a process needs say 50MB more space, yours has to stall and perform an auto-grow while my continues onward happily because my file is already large enough.
If on the other hand, the file has already autogrown to fill the entire drive (and mine is already pre-sized to that size) then where are you getting this additional space to grow like you say above? Please educate me on these magical tempdb drive fairies that give you additional space even when the drive is full.
So you massively overallocate tempdb. That's one approach I guess, if you're not required to justify drive costs.
Why do you insist on having all tempdb files on one drive? Given how proportional fill works, aren't you banging the bejeebers out of that one drive?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 11:54 am
Exactly. If the drive space is there, why not allocate it? I'm not going to use it for anything else.
The reason for allocating multiple files is to avoid tempdb contention, and overloading the drive with IO is rarely a concern. On those rare occasions, where IO becomes a problem on the drive, I will separate the files to multiple drives. simply writing the same amount of IO to more files does not greatly increase the amount of IO going to the drive. There is some increase because more files affect the size of I/O block you can write. If writing to one file, it will write a larger block (when it's writing enough to need to write the larger block) than when to multiple files. That IO block size decreases a little each time you add another file, but it flattens out at 8 files. So the IO block size will be the same for 8 data files as it is for 16 files or 24 files or more.
The SQLCat team measured this in benchmarking tests (specifically driving a workload that would take advantage of large writes):
1 file = 256 KB max IO block size
2 files = 196 KB max IO block size
4 files = 132 KB max IO block size
8 files = 64 KB max IO block size
16 files = 64 KB max IO block size
32 files = 64 KB max IO block size
February 28, 2013 at 12:42 pm
Robert Davis (2/28/2013)
Exactly. If the drive space is there, why not allocate it? I'm not going to use it for anything else.The reason for allocating multiple files is to avoid tempdb contention, and overloading the drive with IO is rarely a concern. On those rare occasions, where IO becomes a problem on the drive, I will separate the files to multiple drives. simply writing the same amount of IO to more files does not greatly increase the amount of IO going to the drive. There is some increase because more files affect the size of I/O block you can write. If writing to one file, it will write a larger block (when it's writing enough to need to write the larger block) than when to multiple files. That IO block size decreases a little each time you add another file, but it flattens out at 8 files. So the IO block size will be the same for 8 data files as it is for 16 files or 24 files or more.
The SQLCat team measured this in benchmarking tests (specifically driving a workload that would take advantage of large writes):
1 file = 256 KB max IO block size
2 files = 196 KB max IO block size
4 files = 132 KB max IO block size
8 files = 64 KB max IO block size
16 files = 64 KB max IO block size
32 files = 64 KB max IO block size
That IO block size decreases a little each time you add another file
Yes, that's the problem. You're scattering (small) I/Os all over the drive, causing lots of drive head movement, which is brutal on performance.
Allocating "one drive" is very nebulous. Does this mean 2TB? 3TB? 5TB? No matter what SQL actually needs. How do you know ahead of time for a new instance what a massively-oversize, will absolutely never fill up drive size is? [Btw, how do you get the storage people to let you waste TBs of unused disk space on half-filled "tempdb drives"?]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 12:48 pm
Int'l Paper also was buying lots of companies for awhile, some of them other paper companies.
During a merger, there are legal restrictions on what can and can't be viewed, in stages of nn-days before the official merger date (and other legal milestone dates as well).
Sometimes we kept old support personnel from the original company, other times not.
For one acquisition, one day I simply had 12 more prod SQL instances to support, which I had not been logged into before. Those kind of situations force you to develop more flexibility in how you handle certain things.
I guess I should wish I had the luxury of the ultra-controlled environments some people have, but it's a mixed blessing/curse. I know several people at FedEx and they are in NO-ERROR PERIOD production envs. But it takes them 3 days to change a title on a screen because of all the code and app checks everything must go thru.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 4, 2013 at 9:28 am
GilaMonster (2/28/2013)
stehoban (2/27/2013)
Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?Not shrink. Alter database and set the initial size of the original TempDB file. That size takes effect on a restart of SQL, resulting in 2 files of the defined size.
Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?
No reboot necessary. Not SQL agent though, restart SQL Server itself
For SQL 2008, that works great.
For SQL 2005, as this forum is, you can't do that.
I need the changes to take automatically take effect on the next start of SQL 2005. I can't be available to do multiple stops and starts of SQL, and many (paper) mills wouldn't allow that to occur anyway. Some equipment is multi-multi-million dollar equipment and never stops except for schedule machine maintenance. It's nice when they can afford the time to reboot/restart SQL even once while up.
I don't know of any other automatic SQL 2005 method than the one I've outlined here. I've never had an issue shrinking log files at start up. And I wouldn't expect a 1.5G tempdb, as in the case we're discussing, to be so used it couldn't be shrunk to 1GB at start up.
If you're in the ideal situation of being able to be present for every server as it comes up, with the time available and allowed to restart as needed, etc., then, yeah, you can do everything strictly by the book.
But for someone without SQL expertise, I've found the automatic method works better. My experience has been that even Windows admins sometimes aren't ideal for starting SQL in special mode, issuing unfamiliar SQL-based commands, etc..
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 4, 2013 at 9:49 am
ScottPletcher (2/28/2013)
Yes, that's the problem. You're scattering (small) I/Os all over the drive, causing lots of drive head movement, which is brutal on performance.Allocating "one drive" is very nebulous. Does this mean 2TB? 3TB? 5TB? No matter what SQL actually needs. How do you know ahead of time for a new instance what a massively-oversize, will absolutely never fill up drive size is? [Btw, how do you get the storage people to let you waste TBs of unused disk space on half-filled "tempdb drives"?]
We weren't talking size. We were talking whether or not additional files should be separated to a different drive. How is saying "on a single drive" nebulous?
There is no way to know what size could never possibly be filled up. That's true whether you pre-size the files out or not. Whether you pre-size or not, it doesn't change how you estimate how big the drive should be. You're really grasping at straws in an effort to continue arguing.
I don't waste TBs of space. You pulled that out of your backside, I never advocated having multi-TB drives for tempdb. You're so intent on arguing that you're making stuff up now. I aslo don't leave tempdb drives half-filled. I have stated many times in this thread that I pre-size the files out to consume at least 90% of the drive space. In what world is at least 90% allocated the same as half-filled?
March 4, 2013 at 10:10 am
ScottPletcher (3/4/2013)
GilaMonster (2/28/2013)
stehoban (2/27/2013)
Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?Not shrink. Alter database and set the initial size of the original TempDB file. That size takes effect on a restart of SQL, resulting in 2 files of the defined size.
Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?
No reboot necessary. Not SQL agent though, restart SQL Server itself
For SQL 2008, that works great.
For SQL 2005, as this forum is, you can't do that.
SQ 2000, 2005, 2008, 2008 R2, 2012. Probably 7, but I never worked with it.
At restart TempDB is reset back to the size defined in the system catalog and cleared. I've depended on that behaviour in SQL 2000 (several years ago) and SQL 2005 (just last month)
I've never had an issue shrinking log files at start up.
No one said anything about the log. It's the data files that are the ones that could get corrupted if shrinking TempDB. There's a KB article that describes the potential problems and discusses the ways to shrink TempDB, mostly being restart SQL and let it go back to default. Want to shrink below default, start SQL in single user mode and then shrink.
And I wouldn't expect a 1.5G tempdb, as in the case we're discussing, to be so used it couldn't be shrunk to 1GB at start up.
How heavily used TempDB is before the restart is completely irrelevant, since it's cleared upon restart. So unless you have TempDB defined at 1 GB and model with 1.5 GB of stuff in, there's no problems (and if you do, there are lots of other potential problems)
If you're in the ideal situation of being able to be present for every server as it comes up, with the time available and allowed to restart as needed, etc., then, yeah, you can do everything strictly by the book.
I used to work at an investment bank, the servers restarted at most once a month (patches), usually somewhere around 1AM on a sunday morning. I certainly wouldn't plan on being present when the restart happens, if I even knew about it before hand. Restarting as needed, not a chance in hell, took 5 documents and a week's notice to schedule a reboot.
I don't know why you're so fixated on shrinking TempDB anyway, if it's regularly growing, it needs a larger default size (and better monitoring so that it can be manually grown before an autogrow would be necessary). Only time when it's grown and you don't want a larger default size is if something unusual happened (runaway query, data import, etc). In that case, just check all files are still the same size, grow any that aren't and let the next restart (whenever it is) handle getting the files back to default size.
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 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply