Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm not experienced with DBA work, but I'm trying to make a case for requesting additional resources for our sql server and was hoping I could get some smart folks to provide a rough rough estimate of what we should be running. I'm suspecting that the allocation of resources IT has given to our production sql server is low.

Hardware & Software:

Database: sql server 2008 r2 enterprise database

Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on VMware.

Processor: Intel(R) Xeon(R) CPU E7-4860 @ 2.27GHz 2.26 GHz (2 processors)

Installed memory: 4GB

Hard drive for Database files: 300GB

Hard drive for backups: 150GB

Hard drive for logs: 100GB

Application:

We have 3 main databases that add up to about 170GB in data, a Reporting Services database (SSRS) on the same server that houses maybe 10 different reports (comprising average of 700k records each) that get generated daily. Our user base is about 20 simultaneous users, maybe 5 of those could be considered "resource intensive" with generating data-crunching large reports. Majority of users interact with database through asp.net website and Report server website. Additionally, our developers use SSIS in BIDS extensively by remoting directly onto the server (2 remote connections max). Finally, we have a fairly involved data warehousing operation that probably brings in 3 million records per day by way of SSIS packages that also run on the server.

Current Problems:

We have chronic sever server timeouts and the response time to the website is pretty bad. I suspect the amount of memory we have (4GB) is probably a large bottleneck. Our previous requests for additional memory has been denied with the common response that we need to perform more query optimizations. While we aren't sql pros or (as I'm sure you can tell by our setup) db admin pros, I want to make sure I'm not expending all my time trying to squeeze out little potential performance if the hardware is the bottleneck.

Thanks all for the tl;dr avoidance!

share|improve this question
4  
They expect to service 170 GB of data with 4 GB of memory? No query tuning is going to fix that, and they're out of their tree. –  Aaron Bertrand Aug 1 '13 at 23:13
    
Show them the numbers (performance stats). You could also show them Microsoft documentation that shows the minimum memory for Window Server 2008 R2, is 4GB; this does not leave much for SQL Server. –  Shawn Melton Aug 2 '13 at 0:09
1  
What do your wait stats show? I suspect you are seeing a lot of PAGEIOLATCH_XX waits for your queries. If you are, you may be able to use that as proof that some extra memory would be beneficial. –  SQLRockstar Aug 2 '13 at 0:10
2  
And one you have memory you can start working on a better IO subsystem. Hard DRIVE for a well used database is a IOPS joke. THat should be DRIVES. You do not buy drive as capacity, for databases you buy drive as IOPS source. WHich means a 512gb SSD would be nice for the database files. Standard "lets get it large and cheap" will kill the database. –  TomTom Aug 2 '13 at 4:41
    
@TomTom I'm sure (I hope) we have a raid array. I'm not sure how to detect it though. I was just basing the hard drive description on what the Computer window explorer was showing on the windows server. –  RMuesi Aug 2 '13 at 16:40
add comment

2 Answers

up vote 10 down vote accepted

... was hoping I could get ... a rough rough estimate of what we should be running.

Without more information about your queries and data sizes, it's really difficult to give you any kind of estimate, let alone an accurate estimate.

Database: sql server 2008 r2 enterprise database

Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on VMware.

Processor: Intel(R) Xeon(R) CPU E7-4860 @ 2.27GHz 2.26 GHz (2 processors)

Installed memory: 4GB

Two processors (I'm assuming this is exposed in the VM as 2 cores) may or may not be under-provisioned. The cores assigned to a VM aren't necessarily mapped directly to physical cores (or even allowed to use 100% of a single core when it's needed!), so you may find this is a more flexible resource than memory. Without any more information about your workload or hardware/virtualization configuration, I would say increasing this to 4 would be nice-to-have.

Memory allocation. Oh boy. This is grossly under-provisioned for the workload. Windows itself needs a bare minimum of 2-3 GB to stay happy, and each of the 2 users running BIDS on the box will require at least 500 MB each. And with that, the box is maxed out already, and I didn't even start figuring out how much the database is going to need.

Majority of users interact with database through asp.net website and Report server website.

You didn't say, but if these are running on the same box, memory requirements for them need to be taken into account as well.

Finally, we have a fairly involved data warehousing operation that probably brings in 3 million records per day by way of SSIS packages that also run on the server.

Assuming this runs at night when there are no live users on the system, I don't see this as a problem unless it's taking too long to run. This part of things is the least of your worries; live users are more important.

Our previous requests for additional memory has been denied with the common response that we need to perform more query optimizations.

As I demonstrated above, the current amount of memory that's been provisioned is completely inadequate. At the same time, though, at the other end of the spectrum, it's exceedingly unlikely you'll be able to get enough memory provisioned to be able to keep the entire database in memory at once.

Even though you got a blanket response like that (which, by the way, probably had more to do with how persuasive your justification for additional resources was, and not the actual resource usage itself), it's highly likely the efficiency of the database could be improved. Yet there's no amount of tuning alone that can fix the issues you're experiencing now; the suggestion of that is a complete non-starter to me.

I would take the overall approach that the amount of memory currently provisioned is below the minimum required (which should be corrected ASAP), and additional resources may be required to improve the user experience to a usable level while improvements are made to increase the efficiency of the systems.

Here are a few thoughts (in order of attack):

  • You will win if you can prove how much performance improves every time you get more resources provisioned. Keep track of performance metrics using Performance Monitor logging (note: the logging part is very important), including website response times if you can. Start doing this now, before doing anything else. When you do finally get to the minimum amount of memory (you aren't going to get 32 GB right away), suddenly you now have evidence that the added memory improved things... which means adding even more would probably help, too! If you don't collect a baseline on the current configuration, you're going to miss the boat when things are bumped up to the minimum recommended level.

  • Analyze your server's wait statistics. This will tell you what the biggest bottleneck in the system is. You'll probably have PAGEIOLATCH_XX as the most common/highest wait time, which indicates too much I/O is being done to fetch pages from disk. This can be alleviated by adding memory, so the physical I/O's become less frequent as the needed data is already in memory. While this analysis is pretty much a foregone conclusion, the fact you've gathered these stats at all gives you more ammo when justifying the need for resources.

  • As I mentioned above, the bare minimum requirement for memory is not being met. Collect the set of recommended hardware requirements for all the software you're running, and maybe also grab screenshots of Task Manager. This alone should be enough to justify at least 4-8 GB more, on the spot. If they still refuse, try to convince them to allow you to try it out for a week, and give it back after that (you're collecting performance stats, so you won't need to give it back because mid-week you'll be able to prove how much it's improved the situation). If they still refuse, you're being set up to fail; URLT.

  • If you can offload some of the workload (in particular, avoid remoting in if at all possible), this will increase the amount of memory available for the database, which is more critical.

  • You won't be able to fit the entire database in memory at once, which means you need to set SQL Server's max memory setting very carefully to prevent memory over-commit, which kills performance like nothing else. Over-commit is actually even worse than simply not being able to fit all the data in memory. It's highly likely you're in this scenario right now simply because there's just no memory available at all, and it's probable that the max memory setting is set to the default (unlimited).

  • Since you're running SQL Server Enterprise Edition, and memory is at a premium, I would strongly consider implementing data compression. This will trade off an increase in CPU usage for space-savings of memory (and hence reduced disk accesses, which are comparatively very slow).

  • Tune the database. It's likely the structures and queries could use improvements as far as indexing and access patterns go. Also, if a lot of data is being frequently scanned and aggregated, creating indexed views, summary tables, or precomputed reports may be very helpful.

  • This might be a longshot because it probably means more hardware provisioning, but implement a caching solution. The fastest query is the one you never make.

Those are just a few ideas. The bottom line is that tuning alone will not solve the problems here, nor will hardware alone, even though the latter probably will alleviate the majority of the immediate issues. That's really how it goes: throw hardware at the problem in the short-term to put out the fire, and throw tuning at the problem in the long-term to fix the root cause as best you can.

share|improve this answer
1  
John I love your answer and +1 but in this scenario, it seems Aaron's comment hit it on the head and they simply need more ram, regardless how hard they try to tune it. –  Ali Razeghi Aug 2 '13 at 4:29
1  
@Ali: Yep, I agree, and I mentioned that in my answer. Mostly I wanted to focus on strategies to get more RAM, because that seems like the problem here. (If it's simply not available that's a separate issue.) –  Jon Seigel Aug 2 '13 at 4:33
    
Thanks so much for the detailed answer! I'm new to db performance tuning so I'm just trying to understand where to begin. I've got some reading material and Performance Monitor up, now I just got to understand the metrics. But your suggestions provide a good road map for approaching this problem. THanks again. –  RMuesi Aug 2 '13 at 16:20
    
@RMuesi: You're very welcome. If you have any specific questions on how to accomplish what you need to do, feel free to post them (please search first, of course) and the community will be happy to help. –  Jon Seigel Aug 2 '13 at 16:45
add comment

This is account 'bean counter' insanity. The $1100-$2500 you would spend on RAM could possible pay for itself back within a week!

They are getting time outs for 20 employees and 5 of them are doing 'resource intensive' work. I'd imagine their time isn't cheap, and some of those reports are the ones the boss not signing off on the paycheck would love. That's tons of spent wasted re-transmitting and dealing with frustration.

Explain to them that they're probably looking at $15-$20 a GB for RAM. 128GB of RAM would be roughly $2200-$2500 right now, and RAM prices currently have gone up a little. Even if your servers motherboard can't support it (that would be odd), then 64GB would be $1100-$1200 (I just checked, and that's server ram quality from Dell with no discounts applied). Just even 64GB of ram could make a HUGE difference (esp if we make sure to avoid table scans of large tables).

Figure out how much time is wasted, and ask them if it is worth $1100-$2500. If you only spend $1100 and get 64GB ram, make sure to avoid table scans on large tables. Use more disk with indexes (if you have the latency to support it) to avoid large scans dumping memory.

share|improve this answer
5  
Well said for the insanity. 4gb memory is less than I would suggest these days for a desktop. And that is now trying to run in a 170gb database environment. Damn joke. But hey, virtualization for beancounters means you can get rid of the large expensive servers ;) –  TomTom Aug 2 '13 at 4:42
2  
Completely agree. At least the bean counters in OPs scenario had their claws taken off the physical server (hopefully!) –  Ali Razeghi Aug 2 '13 at 5:03
1  
believe it. I don't. TYPICAL would be a server with plenty of RAM and - LAAAAARGE cheap discs. So you can run a LOT of virtual machines on them. After all, RAM is normally the limiting factor, isn't it ;)? THe result will be horeendous IOPS performance - even without a database. Been there, seen that. 64gb memory, 2tb mirrors SATA disc ;) –  TomTom Aug 2 '13 at 9:10
    
Thanks for the answer. I suspected as much but just don't have the data to prove the problem is partially hardware imitations. I'm working on that now. –  RMuesi Aug 2 '13 at 16:22
1  
You might want to just let them know that SQL Server is a in memory database engine. Reading from disk, even SSDs (which I'm pretty sure this shop isn't utilizing) is painfully slow. A read from RAM takes about 5ns. Show them an execution plan with SET STATISTICSIO ON which shows table scans and reads. Show them the physical reads vs logical reads. Physical is conducted from the disk. –  Ali Razeghi Aug 5 '13 at 17:52
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.