March 8, 2013 at 8:37 am
My company is rolling out a new product that was supposed to go to beta on March 1. That has been deferred, so the pressure is to get everything done yesterday.
It is a complex multi-tiered application running web services, Citrix published apps, multiple databases and instances on a virtualized cluster. Sort of a kitchen sink of input sources. I had ZERO input on the database design and system architecture. So I'm having to learn the system in the middle of the problem.
Which probably sounds familiar to most people here, no?
The load test was focused on the web services so I was not allowed to capture any SQL statistics. I was only able to watch the defaults available through the Activity Monitor.
The strangest thing during the test from the database end is that memory utilization peaked at 1.5 GB on an instance that had 28 GB assigned to it.
Today we tested the instance with a few memory hogging scripts just to show that they were configured properly and, as expected, the memory was easily consumed.
The load test had some interesting things happen. As the web requests loaded up the front end, the CPU climbed linearly - a nice direct correlation to the number of request hitting the web servers. But as soon as the CPU hit 25% it leveled off even as we doubled, tripled and quadrupled the number of web hits.
More interesting is that there were two SQL instances in the test and when the CPU leveled off the waits displayed in the Activity Monitor started climbing up into the thousands. Even more curious is that the waits on each instance were inversely correlated. When one would peak, the other would be at a minimum in a very regular saw toothed pattern.
So I have to recommend a "solution" without having any data since I wasn't allowed to pull any during the test.
FWIW, disk I/O was fine.
Today's memory test showed that the memory allocation of the instance is fine.
My first recommendation is going to be to put all the databases on the same instance (there were two databases, one one each instance, that talked to each other a great deal) just to see how it effects the waits and the cross talk between those two databases.
Then look at tempDB issues and insist that I be allowed to pull some performance counters DURING the test.
I found the oscillation of peak waits very interesting. Has anyone ever seen this type of behavior?
I'm not expecting any magic answers here. More just some possibilities so I can drill down into the lower levels.
Bob
SuccessWare Software
March 8, 2013 at 8:51 am
Using Task Manager to monitor SQL's memory usage?
32 bit SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2013 at 9:01 am
Sorry. Forgot some basics.
64 Bit SQL 2008 R2, 64 bit Windows 2008 R2 Standard.
Virtualized on VMWare.
Memory observations during the load test was a perfmon capture.
Memory test this morning was with perfmon captures.
Any monitoring I did during the load test was by using the Activity Monitor in the SQL Server Management Studio, not task manager. I was not allowed to observe any specific SQL counters.
Bob
SuccessWare Software
March 11, 2013 at 7:40 am
Bump.
Is this in the wrong category?
Bob
SuccessWare Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply