Server Drive Configuration

Question

Server Drive Configuration

Terry B-214928

Say Hey Kid

Points: 696
More actions
September 21, 2016 at 10:13 am

#317725

Hello - We will be setting up a SQL server to host our accounting system databases. There will be around 90 users accessing the system.
The server has 8 drive bays. I was thinking about the following configuration.
OS - Mirrored SAS drives
Data - Mirrored Solid State drives
Logs - Mirrored Solid State drives
Because the data and log arrays are solid state drives we figure the tempdb and log can go on the data and log arrays and will not require their own seperate arrays.
Thoughts?
Thanks,
Terry
A great day starts with a great attitude

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 1

What works best depends upon the IO demands of the applications. If the applications have been observed to demand sudden, rapid, and seemingly uncontrollable increases in tempdb data space or log space, then all of your eggs would be in one basket and there might not be enough room for others to grow....

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Terry B-214928 (9/21/2016)
Hello - We will be setting up a SQL server to host our accounting system databases. There will be around 90 users accessing the system.
The server has 8 drive bays. I was thinking about the following configuration.
OS - Mirrored SAS drives
Data - Mirrored Solid State drives
Logs - Mirrored Solid State drives
Because the data and log arrays are solid state drives we figure the tempdb and log can go on the data and log arrays and will not require their own seperate arrays.
Thoughts?
Thanks,
Terry

Where are the drives where you'll store the backups to disk until they can be copied to tape or copied to offsite? Short answer should be, "On a different machine".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Terry B-214928 Say Hey Kid Points: 696 More actions · Answer 3

Backups are on a separate server.

Thanks

A great day starts with a great attitude

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Terry B-214928 (9/22/2016)
Backups are on a separate server.
Thanks

With that out of the way, then I agree... you probably don't need to put TempDB on a separate drive.

I will also advise that having TempDB on "memory drives" does help a bit but is usually only really effective if you have some bad code that should be fixed. Although TempDB is used a lot by the system, you have to remember that everything that goes to TempDB goes to main memory (which is faster than many SSDs) to begin with and only "spills to disk" when whatever it is gets too big. Usually, only really bad code has that problem.

We did the same thing. We put TempDB on SSDs. It was a part of what doubled the performance of our queries after the machine upgrade and move to SQL Server 2012. While doubling performance sounds impressive, it's not really. That means that single row queries that took 250ms ran in 125ms. Considering that the queries are hit millions (and sometimes billions) of times in an 8 hour period, that's still a hell of a load on the system.

For front end code, the best thing to do is to find the top 10 worst queries according to their cumulative CPU, Reads, Writes, and Duration and fix them so that they run like they should, which is much closer to being sub-millisecond as you can. When you're done with those 10, do the next 10. Fixing the top 20 will usually result in a system that flies. When I started this type of "Continuous Improvement Program" at work, all 16 CPUs were cranking between 30 and 40% during the day with several blocking "outages" each day. After fixing the top 20 queries (many came from the ORM), we're down to 3% with virtually no blocking.

Same goes for batch runs. Moving to the new system doubled their performance. You just have to love it when a 4 hour job now runs in only two hours??? :blink::sick: I don't think so. That's still terrible especially when you have a dozen or two such jobs to run each night and sometimes during the day.

Ranting a bit...

In a previous job, we had a "dupe check" routine that compared 61 tables (1 per database) that each had about 4 million rows (Call Detail Records, each file was one day and it was 2 months of files). They really wanted to compare 3 months but, because the code took 10 to 24 hours (let's call it an average of 16) to usually fail for the monthly run, they could only go back the 2 months. The daily job, which only compared 3 tables of 4 million rows each, usually took 45 minutes and would frequently fail, as well. All of that was after a massive hardware upgrade with nasty fast disks, nasty fast CPUs, lots of added memory, etc (no SSDs at the time, though).

I rewrote the code for the job and the first time they ran the daily job, it ran in about 17 seconds. Without even looking at the results, they insisted that it could NOT have run correctly because it didn't take long enough. Similarly the 10-24 hour job that would normally fail now only took 17 minutes. If you take an average of 16 hours and compare that to 17 minutes (and hasn't failed since I rewrote it) you'll find that the code ran more than 55 times faster except for one thing. I made it so that it would handle the full 93 tables... a 50% increase in what it did so it was actually running more than 83 times faster.

Although it sometimes helps a bit, no trickery with TempDB or other database being on SSDs or other hardware (except maybe for an MPP {Massively Parallel Processing system}, which also requires rewriting much of the code and is horribly expensive hardware) will ever be able to achieve such an improvement and even MPP manufacturers usually only brag about a (max) of 30X improvement.

If you want speed, buy as much memory as your system and version of SQL Server can take and then fix your code to take advantage of it because true performance (not to mention scalability over time) can only be achieved by writing good code.

On that note, a lot of people will say "too expensive". Oh... right. If you'd done it right the first time, you wouldn't need to go through the expense and to try to alleviate that expense, you're going to spend a shedload of man hours and a ton of money on new improved server hardware and maybe have to go though the expense of increasing the size of your server room or buying more space in the {ugh!} cloud and still maybe only get a 2X improvement.

To end the rant, it all boils down to something the famous oil-well fire fighter, "Red" Adair, once said... "If you think it's expensive to hire a professional, wait until you hire an amateur." 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Terry B-214928 Say Hey Kid Points: 696 More actions · Answer 5

Thanks Jeff!

I agree ... do it right the first time.

We always provide ample memory to SQL. If SQL ever uses 80% of what we provide then we add more.

Thanks,

Terry

A great day starts with a great attitude