| Author |
Topic |
|
inbs
Aged Yak Warrior
860 Posts |
 Posted - 2010-02-24 : 05:01:59
|
| can i install tempdb database in the disk with OS(operation system) and Data Log (in c -physical disk)?(did i need to seperat log and data of tempdb)?? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-24 : 05:14:22
|
| You can, but it's not usually a good idea. If TempDB sees heavy usage (and it can), it may need to be on a dedicated disk.--Gail ShawSQL Server MVP |
 |
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2010-02-24 : 07:06:50
|
| 1.tempdb can place on OS drive?2.did i need to separate data and log of tempdb of diff disks? |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-26 : 18:37:04
|
| It's a good idea to have your data file and log file on different drives (spindles). If you keep them on the same drive and the data changes, the disk will need to write once for the first file, move the disk head and then write again for the second file. You will be getting dusk thrashing which will impact performance negatively. This is true for all databases but especially true for TempDB since it gets used by seemingly everything.You want your data files separate from your log files and, if possible, place your TempDB files on a different set of drives. There may be some databases, such as MASTER, which change so infrequently, that you can live with the data and log files on the same drive.Here is a brief overview of the separation that I'm referring to. I realize that you may not have the hardware to support it but it's a first cut at what I try to aim at.C: - OS and system databases (MASTER, MSDB) data and logs (Logs could be moved to the L: drive)D: - Data files for user databasesI: - Index files (Different file group) for user databasesL: - Log files for user databasesT: - TempDB data files (1 per CPU; all of the same size)S: - TempDB log fileU: - SQL BackupsIf anyone has any better suggestions, I'd welcome them.One last "best practice" to follow - When Gail Shaw gives you a suggestion, just go ahead and do it. There's a high likelihood that you'll be glad that you did.=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-26 : 23:08:25
|
| You do realize that is at least eleven drives (all drives minimally fault tolerant except T, S, and U), right? Most server class hardware can't even hold that many drives, and I don't know of any non-server class hardware that can. That's one heck of a "first cut".Let me try another recommendation that only works if I assume that the reader has infinite cash: Everyone should have five cars. One for when they go to work, this one should be stylish but conservative. One for clubbing, it should be fun and cool. One to be used when driving with food, you wouldn't want to stink up the good cars. One big one in case you need to buy a few two-by-fours at Home Depot. Finally, a spare that you leave at work in case your work car breaks down while not at home. It would be hard to argue that having these five cars wouldn't make a person more productive. It would also be hard to argue that the "five car solution" isn't a terribly stupid idea for a normal person, regardless of how productive it makes you.For most people, the only physical drive recommendation needed is to isolate the log file of the app database to a separate set of spindles (but make sure it is fault tolerant). If you have extra drives, do the tempdb thing next. If you have more drives and you are unsure what to do, go one of two ways:1. Figure out your actual needs and learn the benefits and costs of all of the "standard" recommendations. Do not follow a template. Do not follow anyone's advice who doesn't know your specific needs.2. Hire a consultant. You are now spending at least ten thousand dollars, don't mess around.Finally, to directly answer inbs's question -- You don't need to put anything on a separate drive. However, if you want to maximize the performance of a given piece of hardware, you should carefully plan the drive layout of a database server. I should mention that the $5,000 you would spend on Bustaz Kool's suggestions would probably net you less of a performance gain than spending that $5,000 on index tuning. An eleven drive server set up properly is probably only about ten to fifty times faster than an eleven drive server with one humungous C drive. Proper index and query tuning can easily make a three order of magnitude difference in performance. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-27 : 04:40:13
|
quote:
Originally posted by Jaime
You do realize that is at least eleven drives (all drives minimally fault tolerant except T, S, and U), right?
TempDB can be non-fault tolerant if you're happy that a single drive failure brings SQL down and will keep it down until the drive can be replaced. If the SQL server isn't mission critical, maybe, if it's one of those As for U, placing backups on a non-fault tolerant drive may not be the best of ideas. Sure, they'll be going off somewhere else shortly after backup, but still it's a risk.quote:
Most server class hardware can't even hold that many drives, and I don't know of any non-server class hardware that can. That's one heck of a "first cut".
I don't know many servers that use direct attached storage. SAN or iSCSI is the norm the bigger SQL servers (the ones that need optimal disk layout)quote:
For most people, the only physical drive recommendation needed is to isolate the log file of the app database to a separate set of spindles (but make sure it is fault tolerant). If you have extra drives, do the tempdb thing next.
Maybe. Depends which of the log and tempDB sees higher usage. In an OLTP system with minimal tempDB usage, then tempDB can be anywhere but I'd want the log on a separate array. With an OLAP-type system that's mostly read but uses a lot of TempDB, I'd rather go with TempDB on a different drive and put the log with the data file.That's if there's limited hardware.quote:
I should mention that the $5,000 you would spend on Bustaz Kool's suggestions would probably net you less of a performance gain than spending that $5,000 on index tuning.
Probably, but it's a hell of a lot easier to tune indexes than it is to re-do a server's drive layout once the system is up and running. IO bottleneck is the one that most well-tuned servers will have. It's hard to max out modern processors, but it's easy to max out modern disks (unless using SSD)It costs, that's a certainty, but for important, high-usage servers it's far better to get it right immediately rather than try to fix later when the system's in use and the users are screaming.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-27 : 06:12:16
|
| Gail (et al): For "limited hardware" I have been taking the approach that MDF and LDF are on different drives, and Full backups are on LDF drive and Tlog backups on MDF drive - limiting exposure to backup recoverability in the event of a drive/RAID failure.With three drives I go for MDF, LDF, backups - and generally put the TEMPDB on the Backups drive(O/S is on a separate drive)This is OLTP, comments appreciated. |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-27 : 09:44:15
|
quote:
Originally posted by GilaMonster
I don't know many servers that use direct attached storage. SAN or iSCSI is the norm the bigger SQL servers (the ones that need optimal disk layout)
That makes it worse. Eleven drives and the associated controllers on a decent SAN can easily run $20,000 to $50,000. My point is that anybody asking these questions in a public forum isn't maintaining one of these expensive SQL servers. Recommending tens of thousands of dollars of hardware as a "best practice" before even looking at their system is irresponsible. |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-27 : 09:48:41
|
quote:
Originally posted by GilaMonster
It costs, that's a certainty, but for important, high-usage servers it's far better to get it right immediately rather than try to fix later when the system's in use and the users are screaming.
The odds of fixing a "users are screaming" scenario by rearranging drives are pretty slim. 99% of the time if the users are screaming, the system is slow enough that moving the log file to another drive are moving tempdb will simply make them scream less loudly. If you need to cut response from from minutes to seconds, then you will most likely need to address the problem at the indexing level, not the disk IO level (or perhaps both). IO bottlenecks can be solved by simply reducing IO demand, you don't always have to expand IO capacity. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-27 : 10:27:50
|
quote:
Originally posted by Jaime
My point is that anybody asking these questions in a public forum isn't maintaining one of these expensive SQL servers.
Sorry, don't agree there. People on public forums are working with everything from tiny servers right up to those expensive SAN storage servers. It's impossible to make an assumption about a person's responsibilities from where they post questions--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-27 : 10:47:57
|
quote:
Originally posted by Jaime
99% of the time if the users are screaming, the system is slow enough that moving the log file to another drive are moving tempdb will simply make them scream less loudly.
Maybe. It depends.If the blatant, obvious bottleneck is the drive(s) (15 second IO warnings, high log waits, high read/write stalls, slow backups, high disk write times, etc), then fix the IO subsystem. If the problem is poor indexing (excessive reads by queries, multiple index/table scans, inadequate indexing) then fix the indexing. If the problem is badly written queries (non-SARGable predicates, excessive temp table/table variable usage, cursors, etc) then fix the queries.quote:
IO bottlenecks can be solved by simply reducing IO demand, you don't always have to expand IO capacity.
To a point. However there's unavoidable overhead in having everything on the same drive. It is not a good config for busier servers.I agree that one cannot simply state what the I/O config should be without analysis of the system. That said, there's a reason that multiple drives and separated log/data/tempDB is recommended configuration.--Gail ShawSQL Server MVP |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-27 : 11:29:24
|
quote:
Originally posted by GilaMonster
If the blatant, obvious bottleneck is the drive(s) (15 second IO warnings, high log waits, high read/write stalls, slow backups, high disk write times, etc), then fix the IO subsystem. If the problem is poor indexing (excessive reads by queries, multiple index/table scans, inadequate indexing) then fix the indexing. If the problem is badly written queries (non-SARGable predicates, excessive temp table/table variable usage, cursors, etc) then fix the queries.
Yet the "best practices" trotted out at a moment's notice will use all of most people's budget on just the IO subsystem. If people follow these recommendations blindly, they won't have any money left to even evaluate indexing and queries. This is doubly true for those in India and China where labor costs are lower than hardware costs.Don't get me wrong, I think the IO subsystem is critical for a heavily loaded SQL server. However, trends like virtualization and SANs exist because most servers are not fully loaded. The entire industry in well into a cycle of making servers cheaper, more reliable, and more manageable but slower, through aggregation.How can you simultaneously call a recommendation a best practice, while also claiming that recommendation is specifically targeted at the minority of servers at one end of the scale? Pick a side; is this a recommendation for building a no-holds-barred high end system, or is it a general purpose everybody-should-do-it recommendation? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-27 : 13:21:38
|
quote:
Originally posted by Jaime
However, trends like virtualization and SANs exist because most servers are not fully loaded.
Virtualisation is a trend because many servers show a lower than desired average CPU usage. Consolidating multiple virtuals on one physical allows higher average CPU and better usage of the processing resources. As I mentioned earlier it's really hard to max out a modern CPU. I/O bottlenecks come far earlier than CPU bottlenecks.SANs, in my experience, are a way to manage large amounts of storage. Nothing to do with speed or usage.quote:
How can you simultaneously call a recommendation a best practice, while also claiming that recommendation is specifically targeted at the minority of servers at one end of the scale?
I have not once used the term 'best practice'. It's one I abhor. I said that splitting files across different drives is a recommendation. I spoke about the 'all on one drive' not being a good config for 'busier servers', not a minority. Or are you claiming that only a minority of servers are busy? (in an I/O sense)quote:
Pick a side; is this a recommendation for building a no-holds-barred high end system, or is it a general purpose everybody-should-do-it recommendation?
Spreading files across multiple drives is a general recommendation. How many drives, in what config and what size depends on exactly how busy the server is, what its usage pattern is and how much storage will be needed.--Gail ShawSQL Server MVP |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2010-03-01 : 01:02:55
|
| first of all thank to replies.sorry but i confused.our server is for BI-DWH (the cube setting in another server).i have 6 disks and each one is 146 giga.and 1 disk -in raid 1+0 is 300 giga . (i place the OS on it).help me to mapped the log and the data of the system and user db (we dont use indexes) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-01 : 02:12:57
|
| Hard to give you a config good for your system without knowing what you're running on it.How many databases?What's their usage pattern likely to be (read-heavy, write-heavy, both)What's tempDB's usage likely to be?How big do you expect the DB?With only 6 disks and needing to RAID them, there aren't that many options here...You don't use indexes? Got performance problems much? (I hope that meant that you don't use index filegroups)--Gail ShawSQL Server MVP |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2010-03-01 : 03:38:46
|
| 1.i have 35 database (5 of them are very important)2.5 of the 35 are read and wirte heavy3.the usage of tempd is high (the size of data is 6 giga)4.i except the the db will increase 50 giga in next year(the problem that we have is the i/o in the disks ) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-01 : 06:27:37
|
| I think you need more disks and maybe someone to come in to do a proper analysis and recommendation. If TempDB is heavily used, that should be on an independent array, RAID 1 or 10 (10's not really an option with the number of disks you have), though it doesn't have to be the 146 GB drives if you can get smaller.What's the current raid setting on those 6 disks? RAID 5?--Gail ShawSQL Server MVP |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2010-03-01 : 06:44:37
|
| we already bought 6 disks of 146 giga.so i take one for tempdb (log and data).and the other i take for datai have one more question, tempdb can set with backup databse in raid 10(i dont need that bachup will be in raid 5 )? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-01 : 10:24:44
|
| You can, but you're taking a risk. If the drive TempDB is on fails, SQL will shut down. If the DBs are mission critical that risk may not be acceptable. Check with the business owner. Also, a single disk won't perform as well as a array of disk. But then, with the small number of disks and high usage, you don't have many good choices.--Gail ShawSQL Server MVP |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2010-03-01 : 13:45:35
|
| sorry,so what is your recomended,which raid adequate to tempdb(i cant allow that sql will shut down)?in this link (section 7),recommended for raid 1+0[url]http://msdn.microsoft.com/en-us/library/cc966534.aspx[/url] |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-01 : 14:46:28
|
| RAID 10 requires minimum 4 disks. With only 6, you can't use that and still have enough disks (and space) for the data drives. I gave my recommendation 4 posts up.--Gail ShawSQL Server MVP |
|
|
|
Next Page
|
tempdb