0

OK… I’ve been tasked to figure out why an intranet site is running slow for a small to medium sized company (less than 200 people). After three days of looking on the web. I’ve decided to post what I’m looking at. Here is what I know:

Server: HP DL380 Gen9 (new) OS: MS Server 2012 – running hyper-v RAM: 32GB

Server 2012 was built to run at most 2 to 3 VMs at most (only running one VM at the moment) 16GB of RAM dedicated for the VHD (not dynamic memory) Volume was created to house the VHD The volume has a fixed 400GB VHD inside it. Inside that VHD is server 2008r2 running SQL 2008r2 and hosting an iis7 intranet.

Here is what’s happening: A page in the intranet is set to run a couple of stored procedures that do some checking against data in other tables as well as insert data (some sort of attendance db) after employee data is entered. The code looks like it creates and drops approximately 5 tables in the process of crunching the data. The page takes about 1min50secs to run on the newer server. I was able to get hold of the old server & run a speed test: 14 seconds.

I’m at a loss… a lot of sites say alter the code. However it was running quick before. I've looked at perfmon... everything looks to be in good shape there.

Old server is a 32bit 2003 server running SQL2000… new is obviously 64bit.

Any ideas?

Mesaboogie1970
  • 11
  • 2
  • Separate the two roles (DB and Web) into different VMs. Among other things, that will help determine which part is the slow part and needs attention, especially over time as parts grow. Give the lion's share of the resource to the SQL VM, and tune from there. – Joel Coel Jul 23 '15 at 02:11
  • Part 2: `The code looks like it creates and drops approximately 5 tables in the process of crunching the data.`. Those will likely be temp tables, and while that's not unusual it hints at massive opportunity for potential improvement. If you were to post that procedure to Stack Overflow we there's a chance we could re-write to use more joins, better indexes, and fewer or no temp tables to get a 10x or better performance inprovement. – Joel Coel Jul 23 '15 at 02:13

1 Answers1

1

Solved my own issue... just took a while for me to get back to this. Hopefully this will help others.

Turned on SQL Activity Monitor under tools\options => at startup => Open Object Explorer and Activity Monitor.

Opened Recent Expensive Queries. Right clicked on the top queries and selected Show Execution Plan. This showed a missing index for the db. Added index by clicking the plan info at the top. Added the index.

Hope this helps!

Mesaboogie1970
  • 11
  • 2