 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Clustering / March 2008cluster and tempdb on c:\
I'm trying to boost SQL server 2000's speed at running some of my long queries.Is it a good idea to put the tempDB on a physically separate HD subsystem in the interest of speed? I want to do this but...... Server: Msg 5184, Level 16, State 1, Line 1 Cannot use file 'c:\MSSQL\data\tempdb.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. since hte temp DB is re-created every time SQL starts, this should not be a requirement. Any thoughts??? Leon It is a good idea to have tempdb on a separate disk from your data. However, in a clustered system, any data or log files *must* be on a clustered drive - not a local one.  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau I'm trying to boost SQL server 2000's speed at running some of my long queries.Is it a good idea to put the tempDB on a physically separate HD subsystem in the interest of speed? I want to do this but...... Server: Msg 5184, Level 16, State 1, Line 1 Cannot use file 'c:\MSSQL\data\tempdb.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. since hte temp DB is re-created every time SQL starts, this should not be a requirement. Any thoughts??? Leon > It is a good idea to have tempdb on a separate disk from your data. > However, in a clustered system, any data or log files *must* be on a > clustered drive - not a local one. I have a SQL server with 3 RAID drives: C: (Boot) 36GB RAID 1 D: (Log files) 36GB RAID 1 E: (Databases) 146 GB RAID 10 All drives are 15K RPM SCSI. Which would be best to store TempDb? SignatureRegards, Hank Arnold Microsoft MVP Windows Server - Directory Services Hank, I am not sure why you posted this to the clustering group. Is this a cluster? If so then it must be on one of the cluster resources which I would assume to be D: & E:. But since I rarely see D: as a drive in a cluster I am not sure which way you have it. In any case how many disks are there in the Raid 10 and how much activity do you have in the user dbs and tempdb? SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >> It is a good idea to have tempdb on a separate disk from your data. >> However, in a clustered system, any data or log files *must* be on a [quoted text clipped - 7 lines] > > All drives are 15K RPM SCSI. Which would be best to store TempDb? > I'm trying to boost SQL server 2000's speed at running some of my long > queries.Is it a good idea to put the tempDB on a physically separate HD > subsystem in the interest of speed? Yes. > I want to do this but...... > > Server: Msg 5184, Level 16, State 1, Line 1 > Cannot use file 'c:\MSSQL\data\tempdb.mdf' for clustered server. Only > formatted files on which the cluster resource of the server has a > dependency can be used. Makes sense, doesn't it? What do you think will happen to tempdb when that node goes down and the cluster fails over? Failing over should be as seamless as possible. If you had to rebuild tempdb on failover, it wouldn't really be instantaneous (especially if tempdb is large, and/or if instant file initialization is not in effect). Plus, failover is supposed to be transparent, and if you rebuild tempdb every time, then users will lose data in temp tables, possibly table variables, and even any sorting or hashing that is occurring at the time of failover. Add a disk to the subsystem that can be shared by both nodes, and put tempdb on that. You are not going to be able to put tempdb on c:\, whether or not you think that is a nonsense requirement. A > Plus, failover is supposed to be transparent, and if you rebuild tempdb > every time, then users will lose data in temp tables, possibly table > variables, and even any sorting or hashing that is occurring at the time > of failover. Wait a minute! Why would any data in tempdb be lost during a failover even if tempdb is allowed to locate on a local drive? When SQL Server restarts, nothing in tempdb is retained anyway. You won't be able to keep any table variables or sorting/hashing intermediate data. Linchi > > I'm trying to boost SQL server 2000's speed at running some of my long > > queries.Is it a good idea to put the tempDB on a physically separate HD [quoted text clipped - 22 lines] > > A >> Plus, failover is supposed to be transparent, and if you rebuild tempdb >> every time, then users will lose data in temp tables, possibly table [quoted text clipped - 5 lines] > nothing in tempdb is retained anyway. You won't be able to keep any table > variables or sorting/hashing intermediate data. If tempdb is on a shared drive, why would anything from tempdb be wiped out? Technically, you're not "restarting" SQL Server. Anyway, you may be right, I haven't explicitly checked this, it is just an assumption based on casual observation. We have some high volume systems that have failed over during busy times and did not hear a single complaint from our users, and our test harnesses (which certainly call stored procedures continuously, that make heavy use of tempdb) did not register a blip either. Maybe just coincidence that nothing was ever noticed. To a client app, a failover is exactly the same as a SQL instance restart on the same server. Nothing is different. Your test harness or users don't complain for exactly the same reason they would not complain with the app running against a standalone SQL instance: the stored procedure would create whatever temp tables it may use when it is executed again. Linchi > >> Plus, failover is supposed to be transparent, and if you rebuild tempdb > >> every time, then users will lose data in temp tables, possibly table [quoted text clipped - 15 lines] > procedures continuously, that make heavy use of tempdb) did not register a > blip either. Maybe just coincidence that nothing was ever noticed. > To a client app, a failover is exactly the same as a SQL instance restart > on [quoted text clipped - 3 lines] > create > whatever temp tables it may use when it is executed again. But what about when the test harness is running a stored procedure that makes heavy use of tempdb and takes 40 minutes to run? Surely if the object disappeared in the middle of the run, then it would have recorded some kind of problem during failover? Whoever coded that test harness had proper error handling to recover from a lost connection. Think about it. When a server is hosed, all the state info in memory is gone and can't be replicated on the new node. All those table variables and hash structures that are being used but not persisted on the old node cannot possibly appear on the new node. So strictly speaking, there is no transparent failover as far as application programming is concnered, though the error handling may make it transparent to the end user (if transaction recovery doesn't take too long on restart). Linchi > > To a client app, a failover is exactly the same as a SQL instance restart > > on [quoted text clipped - 8 lines] > disappeared in the middle of the run, then it would have recorded some kind > of problem during failover? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.