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

Setting All Tempdb Data Files to the Same Size Expand / Collapse
Author
Message
Posted Monday, July 19, 2010 12:44 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 21,657, Visits: 15,324
Thanks for sharing the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #955039
Posted Monday, July 19, 2010 6:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:43 PM
Points: 111, Visits: 613
I would encourage everyone following this thread to heed what Paul Randal posted in his blog give above. Tempdb should have no more than 8 datafiles. You should also make sure that the account running the sql server service has the local security policy right, Perform Volume Maintenance Tasks. This will allow for instant file initialization rather than waiting for the format to finish.
Post #955232
Posted Tuesday, July 20, 2010 6:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
Tim-153783 (7/19/2010)
You should also make sure that the account running the sql server service has the local security policy right, Perform Volume Maintenance Tasks. This will allow for instant file initialization rather than waiting for the format to finish.


Remember this is for NON-TDE databases, SQL instances that use TDE cannot use instant initialization for the Tempdb as it is encrypted. As well any database on the instance that is using TDE cannot use Instant initialization as well.


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #955478
Posted Tuesday, July 20, 2010 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 PM
Points: 8, Visits: 179
I agree. The article only addresses the sizing of each datafile with respect to the other datafiles on the tempdb filegroup.
Thanks.
Post #955485
Posted Tuesday, July 20, 2010 6:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 PM
Points: 8, Visits: 179
Thanks you for the comment. It is clear that there are two schools of thought when it comes to the right number of datafiles in tempdb. Microsoft recommends the datafile per core setting, and I have read articles that report major improvements when one datafile per core is used over the single datafile configuration. The purpose of the article is not to argue in favor of such practice, but to provide the steps to implement it.
I will read the article you point to. A great experiment would be to set up both configurations and compare.
Thanks again.
Post #955502
Posted Tuesday, July 20, 2010 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 4:18 PM
Points: 3, Visits: 18
I've been told that all of this goes out the window if you storage is on a SAN. With fiber-attached SAN you can put all of the tempdb storage on one disk and you don't need one file per processor and balancing the sizes doesn't matter. True?
BTW, the phrase "I've been told" is the old problem of software types like me being told how to do things from hardware types. The information is sometimes valid and sometimes not relevant to what we are doing. So it goes.
Post #955824
Posted Tuesday, July 20, 2010 12:42 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
Once Again, I think it depends. If you get the page io latch error then it can only be one thing. depending on capacity and load everything eventually has a limit.

I worked in a place where we did not have a SAN guy for quite some time, when we got one on board he made a lot of changes and performance increased. But i wasn't a network or SAN guy, and was a newer DBA so the changes he made with regards to partitions, striping, and even out of date driver updates, network gear in between the DB and web servers, were all eye openers to me at the time. But are things that have stayed with me ever since.

I would say if the tempdb is appropriately sized, with 2-3 datafiles more you are probably okay. but you would want to make sure using the queries refrenced that the temp db is indeed your problem and it is not an IO problem. Do the investigation and the truth will out.


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #955832
Posted Tuesday, July 20, 2010 12:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:15 AM
Points: 198, Visits: 294
Based on what I have seen I don't believe the "it all goes out the window if SAN is involved" statement would be correct in most cases. Most of my TempDB data files reside on SAN luns and we dedicate different sets of spindles to those Luns to maximize performance. Typically our TempDB's are broken into four or eight files and that has performed well for us. Thanks


Post #955835
Posted Tuesday, November 2, 2010 12:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 31, 2014 7:46 AM
Points: 44, Visits: 133
Thanks for a good step by step instruction!

I have a failover cluster (SQL 2005 Standard) and followed your instructions but could not connect to the instance once it was started in single user mode. Probably the cluster service IsAlive-polling started to quick? I've fixed it anyway since it wasn't any other logged in at the moment, but for the sake of understanding - how can I connect to a clustered server which is started in single user mode?

/Martin



Post #1014766
Posted Tuesday, November 2, 2010 1:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 PM
Points: 8, Visits: 179
Martin,
Thank you for your observation, which shows a problem with using Cluster Administrator to start a clustered instance in single user mode. The problem is that even if SQL Server Agent is not started, the single connection is exhausted. I assume the connection is being used by the isAlive session the cluster starts frequently.

The proper way here is to log into the Active Node and start SQL Server service in single user mode using any of the methods described as if the instance were not a clustered instance. Once the changes are done, stop the service before using Cluster Administrator to bring SQL Server online.

Thanks again for pointing this out.

Aldo
Post #1014813
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse