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 «««12345»»

Adding an extra file to TEMPDB Expand / Collapse
Author
Message
Posted Thursday, February 28, 2013 11:06 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: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
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 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 #1425210
Posted Thursday, February 28, 2013 11:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:37 PM
Points: 1,612, Visits: 1,536
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.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425211
Posted Thursday, February 28, 2013 11:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:37 PM
Points: 1,612, Visits: 1,536
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.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425212
Posted Thursday, February 28, 2013 11:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 1,949, Visits: 2,884
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425217
Posted Thursday, February 28, 2013 11:28 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: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
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 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 #1425223
Posted Thursday, February 28, 2013 11:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 1,949, Visits: 2,884
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425225
Posted Thursday, February 28, 2013 11:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 1,949, Visits: 2,884
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425227
Posted Thursday, February 28, 2013 11:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:37 PM
Points: 1,612, Visits: 1,536
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.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425229
Posted Thursday, February 28, 2013 11:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 1,949, Visits: 2,884
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425231
Posted Thursday, February 28, 2013 11:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:37 PM
Points: 1,612, Visits: 1,536
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




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425234
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse