Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting All Tempdb Data Files to the Same Size


Setting All Tempdb Data Files to the Same Size

Author
Message
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22836 Visits: 18262
Thanks for sharing the article.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Tim-153783
Tim-153783
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 685
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.
SQLBalls
SQLBalls
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 630
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
Aldo Gonzalez
Aldo Gonzalez
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 209
I agree. The article only addresses the sizing of each datafile with respect to the other datafiles on the tempdb filegroup.
Thanks.
Aldo Gonzalez
Aldo Gonzalez
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 209
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.
paul.kohlmiller
paul.kohlmiller
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 28
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.
SQLBalls
SQLBalls
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 630
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
Keith Edwards
Keith Edwards
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 312
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



mhoglund
mhoglund
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
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



Aldo Gonzalez
Aldo Gonzalez
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 209
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search