April 13, 2012 at 9:28 am
Hi guys, I know you'll hate not having numbers, but I am working on getting them, but I dont mind some guessing for now.
I am dealing with a application that was designed for a line of business on a set on infrastructure, that has been picked up and dropped on another and is performing terribly. I know not a surprise but not my choice.
In Infra 1 we had 2 webservers and a cluster with 4 databases. It wasnt the greatest at times, but it managed.
The new Infra 2, has 6 webservers and a single high end database that everyone believes has the proper cpu and memory. We are also have a significantly larger set of data and are testing about 1300 concurrent users hitting the website at once and are seeing some crazy processing times, like 70, 90, 500 second wait times when they should only take a few seconds.
I know I am leaving a lot out, but I am not really knowledgable on hardware. I am good at optimizing procedures and indexes and so forth, but given the fact that this app already has a lot wrong with it design-wise, I was wondering the impact of using a single database server especially when request could be coming in from 6 different places.
I am imagining that even though none of the databases in Infra 1 are anywhere near as good as Infra 2's single one, the cluster distributed work, while everything has to be funnelled to a single point with the awesome server.
I know this is very high level, and I'll provide details as I can get them, but I was wondering if it was worth spending more thought on this.
April 13, 2012 at 10:29 am
Have you done any work to determine what types of waits are happening on the new server? Depending on what the requests are waiting for will help determine where the bottlenecks are and what to do about it. Here are some good references to get started:
http://qa.sqlservercentral.com/blogs/johnsansom/2011/05/16/what-are-you-waiting-for_3F00_/
http://www.sqlmag.com/article/performance/dissecting-sql-server-s-top-waits
You could also start with some basic research with perfmon:
http://qa.sqlservercentral.com/articles/Performance+Tuning/2677/
April 13, 2012 at 10:53 am
Hi, I ran that query in the link and got this:
wait_type wait_time_s pct running_pct
PAGELATCH_EX 2284365.71 56.52 56.52
ASYNC_NETWORK_IO 869947.99 21.53 78.05
CXPACKET 475977.99 11.78 89.83
OLEDB 166699.77 4.12 93.95
LATCH_EX 70083.50 1.73 95.69
PAGELATCH_SH 49057.88 1.21 96.90
SOS_SCHEDULER_YIELD 48003.85 1.19 98.09
CMEMTHREAD 38781.88 0.96 99.05
We are using a product called Load Runner which is simulating users going into the app and running things, and we see how long it takes to run each test, but evaluating each one is complicated and uses many stored procedures.
Also the app is about as a relational database as possible (which I know isnt always good) and we do have a lot of joins using varchars, which I know isnt good either.
April 13, 2012 at 11:00 am
Referring to the data above, I should have mentioned that I dont even know how high the numbers are supposed to go before they are a problem.
April 13, 2012 at 12:26 pm
KTG (4/13/2012)
I know I am leaving a lot out
Way too much for us to make a sensible recommendation.
KTG (4/13/2012)
the cluster distributed work, while everything has to be funnelled to a single point with the awesome server.
No, that's not how it works. You have a fail over cluster which is exactly as its title suggests. One node runs until it dies then a partner node takes over.
What spec is the stand alone SQL server?
What spec are the cluster nodes?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply