October 26, 2009 at 9:07 pm
Hi everyone,
Any advice is appreciated. Let me do my best to describe what is going on here.
I have a client who moved from P2V because the Physical Hardware crashed and they needed to do something. We suggested virtualization and they moved to that. The fact that they are running all of the servers necessary for our system in a virtual environment is not a problem, but their SQL Server is having performance issues.
Users are complaining the applicaiton is sluggish. Upon examination of the database server it is not hard to see why. SQL Server is using a minimum of 25% CPU all the time. If there is any level of processing going on, SQL takes 50% to 75%. During a previous support session I found that their Indexes were badly fragmented and the pages were only 50% used. I rebuilt all of the Indexes and that improved performance, for a brief period.
Over the weekend, the client changed the configuration of the server by adding two additional CPUs for a total of 4. Of the four, two are dedicated to SQL Server. When I asked why they changed the configuration the response was to deal with the performance issues. (read as "more processors must be better"). I noted that of the two that are dedicate to SQL Server, that one always seems to be at 100% utilization.
The server is configured with 3.6 GB RAM with SQL Server set to Dynamically allocate memory with a maximum memory allocation of 2 GB. The actual allocation is 1.7 and change, which is about what I would expect. From what I can tell, there is adequate free memory on the server to prevent the OS or other applications from paging to disk.
I found that the option to "Boost SQL Server Priority" was enabled. However, this server is not dedicated to SQL Server only. We changed that setting and restarted the entire system. I'm also not sure about how the Max DOP is setup. The configuration shows that it is set to use 1 processor with a minimum query plan threshold for considering queries for parallel execution set to 5. I think that I should change that to use all available processors, but not 100% on that one.
The disk configuration is less that ideal to my way of thinking. There is one 200 GB "physical" (VMDK) disk split logically. There is a partition each for the OS, Program Files, Transaction Logs, and Data. The allocation of each logical partition is adequate for its application. I do not think that the Disk is the bottle neck here as I have used Windows Performance Monitor to watch Disk Queues. The average on the disk queue never goes above 1.
Using SQL Profiler I watched for long running statements. While there were some statements that ran longer than 5 seconds most of the time these were for reports and were expected. In other cases there were some long running statements, but again these were optimized per our specification.
To make sure that the Host is not over utilized or under speced, I had one of our VCP's review the configuration. He was not able to find anything that stood out as unusual or a red flag to him.
As for our options:
1) They cannot upgrade to SQL 2005 as the version of software they are running from us is not supported and they are not willing to upgrade to a later version (likewise for any later version of Windows OS)
2) Adding more memory does not seem likely given the amount of memory free on the server and the lack of evidence that memory pressure is at fault
3) There are two more CPU's that could be added for SQL Server processing, but I fear that adding those two in (for a total of four) would be consumed by SQL Server as well.
Hopefully I have provided enough information so that someone can provide some advice or guidance.
Regards, Irish
November 2, 2009 at 4:14 pm
How many VMs are on the physical machine? I remember one thing I read a while back was that Microsoft does not recomment setting CPU affinity on a VM because that is something that the hypervisor is supposed to take care of.
Another issue is that if multiple VMs are sharing the same physical disk, this is obviously going to be a point of contention. Since the VM will only be able to see what resources it is using, you will have to get with your sysadmins of the VM to find that out (if you don't know already).
You could also be experiencing memory issues. Do you see any memory errors in your error log? Since you stated that your VM is only configured for 3.6GB, you may experience some performance gain by adding the /3GB switch to the boot.ini file.
Was there a lot of capacity planning done before doing the p2v conversion? It sounds as if your sysadmins tried to give you the most minimal machine possible to run your SQL instance on, which is one of the reasons that I am wary of moving to VMs in my own organization.
Please let us know what you find out.
Joie Andrew
"Since 1982"
November 3, 2009 at 6:28 am
Hi,
It could be that you need to add another tempdb data file to take advantage of the new dedicated processor.
See this link for info: http://www.sql-server-performance.com/articles/dba/post_installation_steps_p2.aspx
November 3, 2009 at 7:01 am
Thanks for the ideas.
Since this is my client, I have little, if any, influence over what they decide to do with regard to their hardware. I can only make suggestions.
I suspect that the decision to move to virtual was made because their physical server crashed. I'm pretty sure the motivation was recovery more than anything, but I can't blame them for that. I also believe that they have more servers than they should on this VMware Cluster, but I am not a VCP so I can't say with any real authority.
I was hoping for some kind of "silver bullet" to point me in the right direction. For the most part we need to get them to upgrade our application so that they can upgrade to newer versions of Microsoft OS and SQL.
Regards, Irish
July 29, 2010 at 2:11 am
Did you resolve this issue? I'm curious.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply