June 6, 2013 at 9:25 am
Have a production physical server running SQL 2005 and reporting services. The server is about 6 years OLD (yikes) with dual/dual procs and 4GB RAM. It's been doing the job perfectly fine, but we need to move on from EOL hardware. The database is 6GB in size.
I've created a virtual machine in Hyper-V with Windows 2008 R2, 4 procs, 8GB RAM - I've also moved up to SQL 2008 R2 Standard (had some CALs from a previous project) and migrated the data over. As far as functionality, I'm not really seeing any issues (nor is the testing user group complaining of anything) HOWEVER, the reports in reporting services are extremely slow.
Just as an example, one of the reports in the old environment is instantaneous. In the virtual environment, it takes 20 seconds to render. That time is very consistent.
I have the data on one LUN and System Databases on another LUN and SQL/OS on another LUN in the virtual environment. The current production is actually all on a single LUN attached to the same SAN.
I know it's something related to the VM, as I created the same environment my local machine and everything is instant. I guess I'm confused as I've read over the years the advancement of SQL running in a virtual environment. I'm pulling my hair out on this. I even isolated the virtual machine on a host (32 procs, 256GB RAM) with nothing else on it. Same results.
Other information I gathered on the VM while running the report:
Disk Queue Length (on data LUN) - .2
Disk Reads/Sec (on data LUN) - 48avg (136max)
Disk Writes/Sec (on data LUN) - 48avg (148max)
Proc Queue Length - 2 (the CPU max during report is 20% - which I'm questioning why it's that high)
When I run a profiler - I see on the production box, Reads of 691 and the VM Reads of 1,572,147. The same query.
What else can I check to pinpoint the bottleneck?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply