Solved

SQlServer Load Balancing

Posted on 2004-04-30
33
650 Views
Last Modified: 2012-06-27
I have a large SqlServer table with over 400 tables and over 600 SP's and alot of triggers as well.
Recently, it has started giving perfomrance issues, huge memory leaks, heavy CPU usage etc.

I am interested in the following :-

1) Options/costs involved in Load Balancing, and how is this done.
2) Fine Tuning the DB for improving the performance

I have searched articles on the internet but I would also like to hear some expert advise from the gurus here.

Any help would be appreciated.
0
Comment
Question by:jimmy282
  • 13
  • 9
  • 7
  • +1
33 Comments
 
LVL 11

Expert Comment

by:rdrunner
ID: 10956963
Hi!

I never did any load balancing so far since we dont have the $$$ for another server so i am forced to tuning the DB as good as possible.

I usually do my tuning based on the usage. I run the Profiler against the DB and let it return any Querries that run longer then 1000 ms. (Start Profiler, select New "whatever" ("Ablaufverfolgung" in german) STRG + N , Select the template "SQLProfilerTSQL_Duration"

Goto the Filter tab and check the checkbox to exclude system IDs. Now locate the Duration in the Filters and select "At least 1000" there

After i got a few lines i try to analyse those querries. If you click on a line it will show you the TSQL that was used to run this querry. Copy and Paste it into the Querry Analyser, show the Exicution Plan and look for any "Fat" lines. Those you need to eliminate... (This is the tricky part... ;) )

You can also do it "Programatically" by saving the "Ablaufverfolgung" and run the Index Tuning Wizzard against it. But i found that the Index Tuning Wizzard gets stuck when you use more then 4 joins or so (and most of our slow ones are complex.... )

Hope this helps....
0
 
LVL 13

Expert Comment

by:danblake
ID: 10957194
1) Options/costs involved in Load Balancing, and how is this done.
--> Its done via partitioning the database into multiple sites/units via Site Codes or similar mechanisms

2) Fine Tuning the DB for improving the performance
--> Its more a case of identifiying the problematic/slow areas of your system.
Depending what front-end application you are using, you may be able to add a email back to say X took too long, have a look at this... next time.

It's also the case of looking at your change/control and seeing what has changed/differed since the last time to result in this heavy cpu usage.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10957205
The load balancing can also be achieved via Active/Active clustering... but it is very expensive, and can result in other bottlenecks.

I've often found there is a small amount of code causing the problems, and once this is sorted all is back to normal.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10959393
agree with danblake.  There really isn't "load balancing" for sql server.  The best we can do (right now) is with partitioning or Active/Active clustering.

From my experience, the current partitioning in SQL Server doesn't generate the best query plans.  So, unless you have a lot of data, it's not really worth it....

SQL Profiler and Windows Perfmon are your friend--trace and tune.....
0
 
LVL 13

Expert Comment

by:danblake
ID: 10959429
You're going to have to Wait for Yukon with the SQL Resolver (Redirector) and create a customised solution when this is available some time in 2005...


Its proabable that there is some unoptimised t-sql code, that we can optimise and change to make this run more efficiently once we have identified the problematic areas.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10960765
Still don't know exactly what your system is, but another option might be to split any operational data and reporting data as well.....
0
 
LVL 13

Expert Comment

by:danblake
ID: 10960868
Recently, it has started giving peformance issues, huge memory leaks, heavy CPU usage etc
--> SQL Server doesnt normally give a memory leak -- it increases in size to take the amount it uses...

Have you done any perf monitoring of the system ?

(How big is the system / size / CPU / number of them..)
--> Heavy CPU usage -- SQL Server will take all available resources to a query where possible to get the job done as quickly as possible with efficient algorithms, its possible to split partition the number of cpus used some querys cause problems running on multiple cpus, some dont' ...

Hardware configuration is a big issue
Number of users ?
Number of T/sec required ?
Number of Reads or Writes/Sec required ?

Give us some further info, and we'll all be very happy to help ...
0
 
LVL 11

Author Comment

by:jimmy282
ID: 10960878
its not practical for me to start optimizing the t-sql code, since there are thousands of lines of code. each SP is about 2000 lines of code.  Its a huge application.

I need to make a decision on this and I am still not sure what to do.

I read an article about transactional replication and clustering. I cant wait for Yukon and Profiler is not of much help to me.

Please suggest.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10960936
Clustering is all about failover and redundancy... (You could have an A/P Cluster..)
Replication is about load balancing/splitting the load up to different sites, rather than areas.....


How big is the system ?
(It may be just buy a bigger system which is a cheaper alternative than an A/A Cluster or Multiple Replication nodes in your scenario... )

What does the system do ?


0
 
LVL 13

Expert Comment

by:danblake
ID: 10960973
Have there been any driver/system updates, that were made or is it increase DB load causing the problem ?
0
 
LVL 11

Author Comment

by:jimmy282
ID: 10961075
its a web based application using SQlServer as backend. SQlServer takes a lot of CPU usage and memory and query response time is slower now. There is about 7 GB of data inside the DB.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10961098
There is about 7 GB of data inside the DB.

On what size hardware/type of system.. ?

You don't really want a cluster or load balancing/replication on this size system.
A single box can easily handle 100 Gb.
(If you throw the right resources at it, and thats using the internal hdds !)

0
 
LVL 34

Expert Comment

by:arbert
ID: 10961141
"its not practical for me to start optimizing the t-sql code, since there are thousands of lines of code. each SP is about 2000 lines of code.  Its a huge application. "

You can't just throw hardware at something and expect it to be better....Bad code on a big box will still cause problems.....



"I need to make a decision on this and I am still not sure what to do.

I read an article about transactional replication and clustering. I cant wait for Yukon and Profiler is not of much help to me."

\Profile should be a big help to you.  You can capture a "workload" and at least run it through the index tuning wizard and see if there are any "obvious" indexes that have been missed......

"A single box can easily handle 100 Gb."--We're easily running 500gb on a single box now.....
0
 
LVL 11

Author Comment

by:jimmy282
ID: 10961186
"A single box can easily handle 100 Gb."-- any online articles confirming this? I need to show the management that load balancing is not really required.

0
 
LVL 11

Author Comment

by:jimmy282
ID: 10961347
the hardware of the system is quite nice. I am not sure but its few Ghz of RAM.

I wont say that the DB is well designed. But we are not at a stage where we can think about re-design of the DB. so we have to come up with something that improves the performance and since you guys say load balancing is not required, what are the options I have now.
A step by step guide will  be helpful.

Thanks,
Jimmy
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 13

Expert Comment

by:danblake
ID: 10961354
Yes, have a look at the following
www.tpc.org

It gives good examples tested, by price/perf clustering/nonclustered..
(Its a good guide.)
-- make sure that you choose a HCL Certified System !

SQL Server will scale > 15 TB... (in terms of data-sizes as required..)

It just depends on how you attach storage to a system... just have a look at the major hardware vendors via SANs (MSA-1000) with Compaq to extend beyond 100Gb, why not give a vendor a ring they should be able to provide you with a previous example from their web-site that they have "sold" to another customer.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10961372
hardware of the system is quite nice. -->

It cant be great if it cant cope with 7 GB...
What is nice ?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10963970
"I wont say that the DB is well designed. But we are not at a stage where we can think about re-design of the DB. so we have to come up with something that improves the performance and since you guys say load balancing is not required, what are the options I have now. "


I really don't understand.  People never like the idea at "throwing" money at a redesign, but they hardly ever think twice about throwing more money at hardware.  If you actually profiled the application/design and you see that you have lots of bottlenecks or blocking, you really wouldn't be "interested" in fixing it?
0
 
LVL 11

Accepted Solution

by:
rdrunner earned 168 total points
ID: 10980898
Code gets faster worse then hardware gets faster....


Its incredibble how much performance a bad querry can eat up... Just by fixing a few corssjoins (evil things) on our DB i was abe to bring down the average CPU load by 30 %... You need to track down the EVIL querries... And fix those... If a querry needs to do Table scans or a cross join then you need to fix them... Monitor your DB and find out which querries take to long... You have maybe a lot of querries but a few bad ones will be able to slow your server down....

Do the tracing i suggested above and see what it turns up....
0
 
LVL 11

Author Comment

by:jimmy282
ID: 11013970
Ok, so I need to check from Profiler the bad queries and try optimizing them. Is that the only thing I can do now?

This is going to take a lot of time and I really need something else to suggest to the management which shows something concrete. What else can be done to improve the performance.

Please suggest.
0
 
LVL 13

Expert Comment

by:danblake
ID: 11014619
Once you've got a standard profile trace, place it into the index optimiser -- see what the results are...
That'll give you a start..
But as a rule of thumb, check all your tables have at least one index on them (preferably a clustered index , PK and your FKs are all indexed).

It may not be your code that is the problem, what are the performance monitoring results (from Win2000/2003) ?
(Especially cpu usage, disk queue lengths, memory buffer utilisation %, ...)

Often it can take longer to track and solve code issues, (They do need to be fixed) sometimes it is faster to throw money at the problem on hardware -- but it has to be based upon trend/tracking utilisation.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11016077
"This is going to take a lot of time and I really need something else to suggest to the management which shows something concrete. What else can be done to improve the performance."


It might take you an afternoon......
0
 
LVL 11

Author Comment

by:jimmy282
ID: 11016227
I was talking about the optimization itself, not the time to find out the bad queries.

FYI, the app involves about a million lines of code and over 300 SP's (100-2000 lines each)

Its not going to be easy to optimize all of this. (At least not in an afternoon)
0
 
LVL 13

Expert Comment

by:danblake
ID: 11016288
Often it can take longer to track and solve code issues, (They do need to be fixed) sometimes it is faster to throw money at the problem on hardware -- but it has to be based upon trend/tracking utilisation....

Imagine a piece of code that needs to be changed...
1) Write a RFI (Request for Change -- Information)
2) Actual Refactor (Rewrite)
3) Test (Regression, Extreme, Normal, Side-by-Side)
4) Confidence factor of new version if bugs are introduced
5) Beta-Release Testing

How long it will take depends on what the problem, is where it is and the extent of the problem.
Generally it takes a developer minimum of a day to perform the necessary admin/source control/testing...
often longer..
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 166 total points
ID: 11016850
"How long it will take depends on what the problem, is where it is and the extent of the problem.
Generally it takes a developer minimum of a day to perform the necessary admin/source control/testing...
often longer.."


But, from a database perspective, you might find out that a LOT of the maintenance can simply be performed by adding appropriate indexes.  Queries that use to fun fast may not function the same any more because of data growth...

When I say it may take you an afternoon, I'm talking about getting a workload and analyzing it--not recoding an application.  I bet you will be amazed at the number of database changes you could make without even touching the application.  So far, you have not provided us with any performance stats to even know where the problem may lie....
0
 
LVL 11

Author Comment

by:jimmy282
ID: 11021346
ok, so does anyone have a nice little document that says about all these things, somone might have prepared such a document earlier. I am trying to come up with a report that says something in this direction

Option 1 ) we try to optimize the DB/queries/Sps etc. and state the approx. time/cost involved
Option 2 ) we try to implement load balancing/multiple CPUs/more hardware/ and state the approx. time/xcost involved
Option 3)  we do both of the above

And then I will let the management chose what they want.

Thanks.
0
 
LVL 13

Assisted Solution

by:danblake
danblake earned 166 total points
ID: 11022487
We need more information on your enviroment...

(Depends on numerous items suppliers/the size of your system/Added Indexes = Extra IO + DB Growth/
/number of transactions/performance of the system required... ....)...

Generally :
1) -- Querys | SPs -- can only quickly (generally) be optimised by use of indexes or code-rewrites
(Cost -- Extra IO rqd/Extra Main rqd -- depends on how many indexes you currently have/use -- this still requires testing in pre-release/dev/test/beta/final release -- which is totally dependant on your enviroment)
(Code/Setup of the system -- 500,000 rows of code, can probably identify the poorly running stuff quickly as we have already said but fix time takes a while -- depends on what the issue is.)

2) Perf Monitor system to get a base-line (You should have one --- but you havn't given us any figures/hardware guidelines -- don't you do any perf monitoring ?  How do you know that your hardware is not just faulty ?), and guestimate the required base-line output of the system.
The four to track are: Network Utilisation / CPU Utilisation / Disk Queue Lengths / Memory Utilisation (+ Buffer hit cache ratio)
if we had results on these we could say if you needed a new system, with what your existing system is we would be able to generate some form of guestimate on what you need to change/upgrade.
Contact vendors -- depends on who you use/what size of system you require/etc..

3) The system should always be under constant optimisation -- there is no point in preventing good code maintance from occuring -- this helps you prepare for when you need to modify the application at a later date.

Recently, it has started giving perfomrance issues, huge memory leaks, heavy CPU usage etc.
--> The heavy CPU usage, could be because of what Disk IO mechanisms you are using... (inbuilt MB IDE Controller ?)

If you havn't got a seperate dev enviroment to a prod enviroment for scalability releases, maybe now is a good time to invest in one ?
There are often other reasons why you may request extra hardware.

I do not supply commercial costings of systems on a forum, especially with so little information on your enviroment -- this depends on so many factors : agreements with vendors/delivery costs/stock.  I provide advice, your enviroment will reflect how long it takes for things to get done.  In some places changing the application can be quick (depends on how big a team you have what processes you use or dont use / how big the modifications need to be to solve the problem), some hardware changes can be more suitable its all a balance of time/cost as you want figures it will be difficult to give without knowing what kind of team who have/how "big" the problem is or even "what" the problem is -- as you have provided very few clues.   For all we know you may have a single component giving you high CPU usage that can be easily fixed by replacing one or two components on your system, which is generally a faster fix than a complete new system.

Aside:
If you had a car that didn't go fast enough, you would buy a new car and trade in the old one -- You don't ask the engineers to rewire the car, rebuild the body with a new lighter frame -- thats the job of the producer understanding that it will take a while to get things right before a new release of car (and your size of market will depend on how long even a simple change such as new indexes takes to implement with all your customers) -- you also get the producers guarantee by buying the new car.  Problem is if you are the producer, how are you managing to ensure you are going to meet your customers needs if they want a faster car -- you ask the engineers to go get to work giving them tools and facilites (e.g. time/resources to find out why it doesnt go fast enough) to sort it out so they can analyse the problem.  If you're the customer with the fastest car and nothing goes faster, then you buy engineers to 'fix' your car to make it go faster with some creative thinking.....Things change if its a safety issue -- all hands to the deck to get it right before people have 'accidents' and getting everybody sorted everywhere, can you guarantee you will not loose changes that you want because you are tied up with other concerns that a change may bring up?

New systems, new cars, all require extensive testing no matter what change is introduced -- how you test / what you test -- depends on your enviroment(needs), some people buy a car on sight others after closer examination.  Most find patience and closer examination pays dividens and helps reassure others that they have made the right choice.

Its not normally a straight issue of time/cost involved.  
Its a matter of business risk/benefit to customers/degree of success to project/where customers use this car(or code)....
0
 
LVL 34

Expert Comment

by:arbert
ID: 11022694
I agree with danblake on his last post.  You've really taken this question way over it's original scope.  We've supplied you with some very good answers.  If you need some kind of document/cost proposal, you better be looking for a consultant on the side...
0
 
LVL 13

Expert Comment

by:danblake
ID: 11025095
I also believe there are lots of good consultants in India -- some of us woudn't mind a holiday either... ;-)
0
 
LVL 11

Author Comment

by:jimmy282
ID: 11025403
:)

Actually, the truth is that I run a company of my own here in India www.mavensolutions.net
And in one of our large projects, the client wanted us to propose to them a resolution for the slow performance. So when I say management, I actually mean the clients' management. I was not talking about my bosses. I am my own boss.

And the client is not in India :) So a holiday to India is not possible :)

You guys have been a great help. I will come up with exact server details/hardware/software etc.


0
 
LVL 11

Author Comment

by:jimmy282
ID: 11025404
:)

Actually, the truth is that I run a company of my own here in India www.mavensolutions.net
And in one of our large projects, the client wanted us to propose to them a resolution for the slow performance. So when I say management, I actually mean the clients' management. I was not talking about my bosses. I am my own boss.

And the client is not in India :) So a holiday to India is not possible :)

You guys have been a great help. I will come up with exact server details/hardware/software etc.


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now