November 24, 2008 at 12:01 am
my Sr dba told me move temp db to another drive after inastallation .why to move temp db to another location only for performance .it effects the sqlservices.pls tell me
November 24, 2008 at 12:05 am
No it doesn't affect the sql services ,but due to shortage of memory these types of steps alwys took by a good dba
Thanks
November 24, 2008 at 1:07 am
It is a best practice to have tempdb on its own drive(s).
Yes it is for performance. (cfr like you would separate page files from C-drive)
Keep in mind: every instance user will use tempdb. tempdb is a system db being used by the system in many situations.
(sort / group / cursors / #-, ##-, @-objects,..)
Howto move: BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/72bb62ee-9602-4f71-be51-c466c1670878.htm
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 24, 2008 at 5:43 pm
how to i move temp db.the link is not opened
November 24, 2008 at 6:16 pm
mohinidba (11/24/2008)
how to i move temp db.the link is not opened
hi,
yes this is for better performance..
this link will help you out in moving tempDB database:
November 24, 2008 at 11:51 pm
mohinidba (11/24/2008)
how to i move temp db.the link is not opened
That only means you haven't got Books Online installed on your pc.
No problem. You can find it online at http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 25, 2008 at 12:00 am
mohinidba (11/24/2008)
my Sr dba told me move temp db to another drive after inastallation .why to move temp db to another location only for performance .it effects the sqlservices.pls tell me
below links may help to your query:
http://support.microsoft.com/kb/187824
http://support.microsoft.com/kb/224071
http://www.tech-recipes.com/rx/2342/sql_server_2005_move_tempdb/
for more info, search microsoft site.
November 25, 2008 at 4:19 am
If your Senior DBA has asked you to do this, it is worth discusing the issue with that person. Although you can use the BOL references to tell you how to move tempdb, it cannot give you advice about anything that is special for your site. Your Senior DBA should be able to give advice on what disks to use and why, and also on setting the best file size and number of tempdb files for the server in question.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 26, 2008 at 8:30 am
While placing the TemDB on a separate drive is a "best practice", it's not an automatic to do. It all depends on your application. Is your App doing sorts or a lot of order-by's. I would monitor the system for a while first and determine the TemDB usage. You may find that the tempdb is very little used (or maybe not). then, you can make an informed decision.
Tim White
November 26, 2008 at 7:28 pm
2 Tim 3:16 (11/26/2008)
While placing the TemDB on a separate drive is a "best practice", it's not an automatic to do. It all depends on your application. Is your App doing sorts or a lot of order-by's. I would monitor the system for a while first and determine the TemDB usage. You may find that the tempdb is very little used (or maybe not). then, you can make an informed decision.
My 2cents: Make sure it is actually a physically separate drive, and not just a drive partition on the same physical drive. My experience has been that you will not gain any performance improvements unless your disk i/o's are on separate spindles....
November 27, 2008 at 12:17 am
Coyote Blue (11/26/2008)
...My 2cents: Make sure it is actually a physically separate drive, and not just a drive partition on the same physical drive. My experience has been that you will not gain any performance improvements unless your disk i/o's are on separate spindles....
That's indeed what it is all about.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 27, 2008 at 10:23 am
ALZDBA (11/27/2008)
Coyote Blue (11/26/2008)
...My 2cents: Make sure it is actually a physically separate drive, and not just a drive partition on the same physical drive. My experience has been that you will not gain any performance improvements unless your disk i/o's are on separate spindles....
That's indeed what it is all about.
I have seen many people; with lots of letters after thier names, including VP and/or DIR, that do not understand the difference between a physically separate drive and a partition on the same drive...
Them: "It's a different drive, it has a different letter."
Me: "Wel,,, it has a different letter, but it's a partition on the same drive."
Them: "It has a different letter... It has to be a different drive..."
Me: ... walking away, shaking my head, wondering again about the maxim of rising to levels inverse to your incompetence.....
November 27, 2008 at 1:28 pm
Coyote Blue (11/27/2008)
...I have seen many people; with lots of letters after thier names, including VP and/or DIR, that do not understand the difference between a physically separate drive and a partition on the same drive...
Them: "It's a different drive, it has a different letter."
Me: "Wel,,, it has a different letter, but it's a partition on the same drive."
Them: "It has a different letter... It has to be a different drive..."
Me: ... walking away, shaking my head, wondering again about the maxim of rising to levels inverse to your incompetence.....
Most of us are "just" technicians .... the one thing managers don't want to bother about... nuts and bolts.
We do something with computers .... they (ab)use it.
Our motto: "To serve and protect"
Their motto: "to gain and grow"
My daughter can make a powerpoint silde show, but she doesn't care how and where it is stored, as long as it is available, it is ok.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 27, 2008 at 2:16 pm
Hi,
The other common performance issue with tempdb is contention in the allocation pages. This has nothing to do with your IO because the contention will be in the cache.
If you are going to move tempdb around, I would advice you to split it up into several equally sized files as well. The reason is that some of allocation pages are per file and by having more than 1 file, you avoid possible contention.
There is another issues as well, and both of them are described in:
- "Concurrency enhancements for the tempdb database"
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en (shorter story)
- Working with tempdb in SQL Server 2005", paragraph "tempdb size, including the right number of files and file size", http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx (").
Someone might argue that this is not necessary unless you have contention but splitting up tempdb in a number of files = number of processors on the machine is gratis so I would say this is a best practice as well. System changes and what is true today might no be true in the future.
The resolution for the second problem, contention in the SGAM allocation pages, has some overhead so you might want to wait with that until you have determined whether that is an issue or not.
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 27, 2008 at 5:16 pm
Elisabeth Rédei (11/27/2008)
Hi,The other common performance issue with tempdb is contention in the allocation pages. This has nothing to do with your IO because the contention will be in the cache.
If you are going to move tempdb around, I would advice you to split it up into several equally sized files as well. The reason is that some of allocation pages are per file and by having more than 1 file, you avoid possible contention.
There is another issues as well, and both of them are described in:
- "Concurrency enhancements for the tempdb database"
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en (shorter story)
- Working with tempdb in SQL Server 2005", paragraph "tempdb size, including the right number of files and file size", http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx (").
Someone might argue that this is not necessary unless you have contention but splitting up tempdb in a number of files = number of processors on the machine is gratis so I would say this is a best practice as well. System changes and what is true today might no be true in the future.
The resolution for the second problem, contention in the SGAM allocation pages, has some overhead so you might want to wait with that until you have determined whether that is an issue or not.
/Elisabeth
You can split it up in separate file groups as well...each filegroup containing similar objects.....
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply