Optimising SQL Server 2008 Stored Procedures

Update 25/03/2011 I've marked this question as answered, while I don't yet have the specific answers to help improve the system I don't think it's fair to hold off until I've gone that far. The question was broad, and the answers have given me lots of pointers to help me on my way. When I have more specific questions to ask i'll be back with those. Unfortunately I can only mark one answer with the tick, but I want to thank everyone for their input, it's all been valuable, thanks for your time.

Original Question

Apologies in advance as this question is going to probably be quite broad!

I'm literally just about to step in to a project where one of our other developers has put together a massive system based around SQL Server 2008 Enterprise Edition, Reporting Services and Stored Procedures. What we've found in performance testing on a VM system (i.e. where SQL Server is running on a Windows Server 2008 R2 system hosted by VMWare1 ESXi) is that it does not appear to take advantage of extra memory or cores.

As I get in to this project I was looking for some guidance about some initial outline questions:

1. What are the opportunities for optimising to make use of more memory and more processor cores within SQL Server 2008?
2. Are there rules regarding how the stored procedures are called, structured, developed etc that will influence how SQL Server can parallelise the stored procedures?

Further, I guess the next obvious point is - what are the things I need to look out for in the code that might provide mor开发者_开发问答e insight to others as to how it's structured and allow me to provide more specific examples? There are a lot of lines of code in the system, but obviously the ideal is to try and find a condensed example or pattern that demonstrates usage.

The Stored Procedures are, I believe, called by Reporting Services.

In our tests it almost seems that it's linear execution - i.e. if we configure the VM with 2 cores, 2ghz CPU allocation, and 4gb of memory, if 1 report concurrently runs in 2 minutes, 2 will take nearly 4 minutes (i.e. twice the time for both reports), it's not quite as linear as that but close enough - everything gets slower almost as though they aren't really running in parallel at all.

Could it be SQL Server Reporting Services is simply only running reports one at a time or something related to that?

Stored procedures are all developed in Transact-SQL.

Again, apologies for the broad question. If there's someone out there that can help educate me in how to get deeper and more knowledgeable in this area then that would be most appreciated!

Matt.

A couple of things to keep in mind

1. Memory is your friend, the more you have the less SQL Server has to go to disk which is 1000 times slower
2. Do you have the correct indexes and are they used?
3. Are your where clauses/order by covered by indexes?
4. is the where clause sargable or is it written in a way that SQL Server won't use the index?
5. Are the procs using a SET based operation or is it using cursors and looping through data sets (which is way slower)
6. Are the procs using SELECT * when only really 2 columns are needed?
7. Separate Log, tempdb and data files on their own disk spindle, if you have non clustered indexes also put those on their own disk
8. While the procs are running use performance monitor or even task manager and look at what happens...is CPU maxed..is RAM maxed
9. Is one report blocking the other? run EXEC sp_who2 and look at the BlkBy column
10. Grab one of the procs, run it in SSMS and look at the execution plan..if you see a lot of scans and high % of total execution items..first focus on those, also look for things like conversions in the plan

Can you show some code, we can quickly look at it and tell you if we see red flag

if from the same connection you make two procedure calls, they will run consecutive. That is, one will run and when it is complete the next one will run.

from one connection, if you a procedure and at the same time from a different connection run a procedure, they will run simultaneiosly, depending on locking conditions.

when it comes to virtualization, brent ozar is your best friend. Virtualization Best Bractices

Also, is the dat athe reports pulling optimized for the reports? If not then it'll take a lot of processing on the sql side and report side to render the reports. you might need to develop some sort of olap process if the oltp system is under a lot of use. You also might consider cacheing the reports depending on the requirements for the reports.

Another thing is to check to the max memory setting for sql server, if it's set to 2 gb max, adding antoher 4gb isn't going to do you a whole lot of good.