Setting All Tempdb Data Files to the Same Size

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Aldo Gonzalez » Setting All Tempdb Data Files to the Same...

22 posts, Page 2 of 3

««1 2 3 »»

Setting All Tempdb Data Files to the Same Size

Rate Topic

Display Mode

Topic Options

Author

Message

SQLRNNR

Posted Monday, July 19, 2010 12:44 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Thanks for sharing the article.

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

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

Tim-153783

Posted Monday, July 19, 2010 6:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 18, 2013 4:42 PM
Points: 108, Visits: 573

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

SQLBalls

Posted Tuesday, July 20, 2010 6:25 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:34 PM
Points: 385, Visits: 611

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

Aldo Gonzalez

Posted Tuesday, July 20, 2010 6:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:59 PM
Points: 8, Visits: 155

I agree. The article only addresses the sizing of each datafile with respect to the other datafiles on the tempdb filegroup.
Thanks.

Post #955485

Aldo Gonzalez

Posted Tuesday, July 20, 2010 6:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:59 PM
Points: 8, Visits: 155

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

paul.kohlmiller

Posted Tuesday, July 20, 2010 12:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, April 06, 2013 2:09 PM
Points: 3, Visits: 16

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

SQLBalls

Posted Tuesday, July 20, 2010 12:42 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:34 PM
Points: 385, Visits: 611

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

Keith Edwards

Posted Tuesday, July 20, 2010 12:43 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:54 AM
Points: 183, Visits: 237

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

mhoglund

Posted Tuesday, November 02, 2010 12:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:08 AM
Points: 44, Visits: 118

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

Aldo Gonzalez

Posted Tuesday, November 02, 2010 1:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:59 PM
Points: 8, Visits: 155

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 »

22 posts, Page 2 of 3

««1 2 3 »»

Permissions