title: Optimising SQL Server 2008 Stored Procedures
body: <p><strong>Update 25/03/2011</strong>
  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 <em>all</em> been valuable, thanks for your time.</p>
  
  <p><strong>Original Question</strong></p>
  
  <p>Apologies in advance as this question is going to probably be quite broad!</p>
  
  <p>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.</p>
  
  <p>As I get in to this project I was looking for some guidance about some initial outline questions:</p>
  
  <ol>
  <li>What are the opportunities for optimising to make use of more memory and more processor cores within SQL Server 2008?</li>
  <li>Are there rules regarding how the stored procedures are called, structured, developed etc that will influence how SQL Server can parallelise the stored procedures?</li>
  </ol>
  
  <p>Further, I guess the next obvious point is - what are the things I need to look out for in the code that might provide more insight to others as to how it's structured and allow me to provide more specific examples? There are a <em>lot</em> of lines of code in the system, but obviously the ideal is to try and find a condensed example or pattern that demonstrates usage.</p>
  
  <p>The Stored Procedures are, I believe, called by Reporting Services.</p>
  
  <p>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 <em>both</em> 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.</p>
  
  <p>Could it be SQL Server Reporting Services is simply only running reports one at a time or something related to that?</p>
  
  <p>Stored procedures are all developed in Transact-SQL.</p>
  
  <p>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 <em>most</em> appreciated!</p>
  
  <p>Matt.</p>