You can add them without downtime but for an equal growth of the tempdb you have to restart sql server because of decreasing the current size of the ONE tempdb-file to the same initial size of all files! If it is more than 8 start with 8 files and add more if needed. If it is less than 8 add one more file per each core to the tempdb. I do not know how much cores you have in your server. The first thing I see is the bad configuration of tempdb! 52GB memory but only 36GB is being utilized due to standard edition restrictions.Įdit: C:\ Drive is built on RAID 5 (Local drive)Īnd Rest of the drives are built on SAN (Raid 50) For high availability we are using double take mirroring. ![]() However we still want to keep the G:\ drive on SAN as well as I:\. This is how the LUNs are configured: Drive But the backup drive still remains on the SAN. If you are moving from a SAN solution to SSD, are you saying you are moving from iSCSI or Fiber SAN to internal SSD? What is the server hardware and RAID controller? I do not recommend ever partitioning a RAIDĭrive into multiple partitions. ![]() I would simply make an 2 x 4 disk RAID 5, one for logs and one for data. With only 8 drives to work with, nothing you do will make a significant difference in speed of the database. You really need to know your IO before you decide how to allocate hardware. I am not suggesting the above configuration, just wanted to highlight the capacity/drive/raid layout. Or two 2x480GB RAID 1 with capacity 240GB 4-240GB 2-240GB (C:\Drive (OS and system databases excluding tempdb)- RAID 1) => Capacity of C:\ 240GB/2 = 120GB approx 2-240GB (T:\ drive (Tempdb) -Raid 1) => Capacity of T:\ 240GB/2 = 120GB approx Option change to 4x480GB RAID 10 with capacity of 960GB approx I think looking at your raid layout you probably have an 8 slot server? Which would mean you have a number of RAID options (depending on your controller(s))Ĥ(+) Disk RAID 10 Your Example: 4-480GB 2-480GB (Sql server logs- RAID 5) => RAID 5 min 3 disks 2-480GB (Sql server data- Raid 5) => RAID 5 min 3 disks What version/edition of SQL Server are you using?Īre you looking at totally new hardware? or adding to an existing server? Is tempdb heavily used on your system? why? (not necessarily a cause for concern) Why is the IO high? concurrency/volume of data? poorly written queries? ![]() I am not saying yes/no to ssd's as I have used them before in enterprise environments I just want you to get them for the right reasons. Sorry if you have the answers to the questions below, but I don't what you to throw hardware at an issue that perhaps could be solvedīy doing some root cause investigation. What is the best raid configuration? => This question is about solving the problem, but first you must understand it. MCITP Microsoft SQL Server 2008, Database Developmentĭb Berater GmbH SQL Server Blog (german only) ![]() If you have a system where only random write activity and more read activity is avaiable I would locate it on a RAID 5 because reading will be - in most cases - from the RAM and not from the disk subsystem.Īs more writing performance you need as better is a RAID 10.Īs more security you need RAID 5 would be a better choice! Backup is a mandatory job you have to implement a web site or a financial system) where you need to have a fast IO for writing you should have it on a RAID 10, too. If you have a system with high concurrent IO (e.g. Writing to the log is always sequential(!). It is important that CHECKPOINTS will be handled as fast as possible. Tempdb is the most used database in a sql system.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |