January 20, 2011 at 1:04 pm
Before you give me the best practices, hear me out: I realize most of the time the goal is to give separate spindles to the tempdb and another separate group to the transaction logs. However due to our SLA and the fact that we do a full backup everyday, we are running in simple mode. This means our transactions logs are not very helpful in the event of a crash I think. Also because of the way our product works which uses SQL our tempdb doesn't grow too big comparitively.
Most importantly, our unique situation has shown that the drive performance of the RAID sets that carry these files are very good. Meaning they are not too intensive.
We need more drives to create a larger RAID set for other parts of the database that are high IO. We would like to consider moving the tempdb and transaction logs to the drive set that hosts the OS and pagefile. Here's my question:
If after doing this we see that the drive performance stats are still good (Average disk sec/transfer and Average disk queue length), is there any other strategic reason not to put these on the same disks as the OS and pagefile. Of all the drive sets we have the OS/pagefile drive set is performing the best, with very low ADs/T and ADQL. But I wondered if there's some other reason to avoid putting these together even if the drive performance is fine.
Thanks!
January 25, 2011 at 8:36 pm
I would not put them on the same spindle as the OS and paging file. I would still give them their own spindle to reside on, separate from the database files (.mdf/.ndf) files.
January 25, 2011 at 9:54 pm
Thanks Lynn. However can you explain why? If the performance of the spindles are fine, why use up disks that can help the database performance by adding to a RAID10 set? If performance is fine, I'm trying to determine if there's some other strategic reason not to let them be combined.
Thanks.
Lynn Pettis (1/25/2011)
I would not put them on the same spindle as the OS and paging file. I would still give them their own spindle to reside on, separate from the database files (.mdf/.ndf) files.
January 26, 2011 at 8:27 am
You generally don't want any database files on the same drive as the OS because database files can grow. If something goes wacky, they could grow to consume all available disk space. If your OS and page file are on the same drive, you may not be able to boot your machine if it is out of disk space.
January 26, 2011 at 10:08 am
shaun.stuart (1/26/2011)
You generally don't want any database files on the same drive as the OS because database files can grow. If something goes wacky, they could grow to consume all available disk space. If your OS and page file are on the same drive, you may not be able to boot your machine if it is out of disk space.
That problem is easily prevented by setting a max file growth.
OP, what you propose is outside best practices, but it certainly isn't the end of the world, especially given your analysis (good on you for even doing that before hand!) of IO and usage patterns. I have seen MANY PRODUCTION systems at clients with everything on a single drive, system databases on boot drives, etc. Put things where you need to and continue to monitor usage (and set max file sizes for things on boot partition). Address things if/when you need to.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 26, 2011 at 8:02 pm
TheSQLGuru (1/26/2011)
shaun.stuart (1/26/2011)
You generally don't want any database files on the same drive as the OS because database files can grow. If something goes wacky, they could grow to consume all available disk space. If your OS and page file are on the same drive, you may not be able to boot your machine if it is out of disk space.That problem is easily prevented by setting a max file growth.
OP, what you propose is outside best practices, but it certainly isn't the end of the world, especially given your analysis (good on you for even doing that before hand!) of IO and usage patterns. I have seen MANY PRODUCTION systems at clients with everything on a single drive, system databases on boot drives, etc. Put things where you need to and continue to monitor usage (and set max file sizes for things on boot partition). Address things if/when you need to.
Interesting that you should mention these and here's why. We have 15 SQL servers that do the exact same thing but for different groups of customers. They were built before I was here and I'm still learning the differences among them. Today I noticed that all of a sudden with 10 minutes our C drive on one server had 115MB free! I called the other IT guy who built all these and we found the tempdb was actually on the boot volume for this server. All servers ran the same operation that required a very large tempdb (75GB) and this server only had about 40GB free. It ate it up. Here's what I found interesting:
1. Why didn't the OS hose? I'm glad it didn't but I thought for sure it would.
2. If I set the max size to 70GB and it need 75 for the operation, what happens?
3. What happens if a non-boot volume runs out of space?
4. The system databases are on the C drive for all our servers it turns out. Why the harm? They are all tiny and the IO on the C drive is minuscule.
January 26, 2011 at 8:35 pm
Mostly from practical experience. Seems I've worked with server admins that have partitioned the "c" drive into two drives, C: and D:. The C: Drive is usually only about 30 GB with the rest of the drive partitioned to the D: drive. As such, I wouldn't put anything on the C: drive other than the OS and paging file. This drive is usually mirrored as well.
When we went to the blade servers at my last employer, the local drive on the blade was also small (32GB iirc). Again, not putting anything else there except OS and paging file.
In addition, all my production databases ran using full recovery model, so I needed the room for unexpected log growth. When we moved to SQL Server 2005, the PeopleSoft systems also started using Read Committed Snapshot Isolation, which uses tempdb, and again needed to be sure we had adequate space for that should tempdb start growing.
Then we added Talent Acquisition to HR, and started loading files into the database. Our HR DB grew from a mere 14 GB to nearly 70 GB in less than a year.
If we were using MS SQL Server for the project I am working on now at my current employer, I wouldn't have a problem with consolidating data/log and temp DB on a single dedicated server. I think we are lucky if the database is using 100 MB, and the level of transactions, though it will become a mission critical application, is very low.
As The SQL Guru stated, monitor your systems and as things change, be ready to change with them.
January 27, 2011 at 1:40 am
As all others said, if you monitor it and all is good - no worries.
I would keep the data and log files separate as the log file is accessed sequentially and the data files randomly. See http://support.microsoft.com/kb/2033523
January 28, 2011 at 6:55 am
shifty1981 (1/26/2011)
TheSQLGuru (1/26/2011)
shaun.stuart (1/26/2011)
You generally don't want any database files on the same drive as the OS because database files can grow. If something goes wacky, they could grow to consume all available disk space. If your OS and page file are on the same drive, you may not be able to boot your machine if it is out of disk space.That problem is easily prevented by setting a max file growth.
OP, what you propose is outside best practices, but it certainly isn't the end of the world, especially given your analysis (good on you for even doing that before hand!) of IO and usage patterns. I have seen MANY PRODUCTION systems at clients with everything on a single drive, system databases on boot drives, etc. Put things where you need to and continue to monitor usage (and set max file sizes for things on boot partition). Address things if/when you need to.
Interesting that you should mention these and here's why. We have 15 SQL servers that do the exact same thing but for different groups of customers. They were built before I was here and I'm still learning the differences among them. Today I noticed that all of a sudden with 10 minutes our C drive on one server had 115MB free! I called the other IT guy who built all these and we found the tempdb was actually on the boot volume for this server. All servers ran the same operation that required a very large tempdb (75GB) and this server only had about 40GB free. It ate it up. Here's what I found interesting:
1. Why didn't the OS hose? I'm glad it didn't but I thought for sure it would.
2. If I set the max size to 70GB and it need 75 for the operation, what happens?
3. What happens if a non-boot volume runs out of space?
4. The system databases are on the C drive for all our servers it turns out. Why the harm? They are all tiny and the IO on the C drive is minuscule.
In general if a SQL Server database file (data or tlog) "fills up" (meaning new space cannot be allocated for any reason) then everything SQL Server attempts that might result in new page allocation on that file will fail. That can mean everything from you never notice to your entire SQL Server becomes virtually non-functional.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2011 at 7:16 am
So there is some inherent benefit to a log or database filling up the space on a non-boot drive instead of the boot drive? Or perhaps I should say 'less harm'?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply