Test the performance of my database

Question

Test the performance of my database

a2zwd

SSCrazy

Points: 2356
More actions
October 20, 2009 at 1:42 pm

#212232

Hi pals,
We have installed new server(W2K8 and sql 2008).
The database has many tables and millions of rows.
How can I check the performance whether I can increase bit more or not?
How can I check any indexes to be created and if I create any index, how can I check whether it degrades insert/update statements on the table?
can anybody explain me or suggest me any tutorial on this?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 1

a2zwd (10/20/2009)
Hi pals,
We have installed new server(W2K8 and sql 2008).
The database has many tables and millions of rows.
How can I check the performance whether I can increase bit more or not?
How can I check any indexes to be created and if I create any index, how can I check whether it degrades insert/update statements on the table?
can anybody explain me or suggest me any tutorial on this?
thanks

If you don't know how to check the peformance, then that means that you probably didn't check the performance before the new server... If you took the old server down, that means you have nothing to compare it to (if that's what you meant).

So far as checking performance, there are many articles available through a Google search that will tell you how to find slow performing code using SQL Server Profiler. The same goes for how to do "index tuning".

I'm not being coy or evasive here on my suggestion to Google the methods... I don't want to limit the info available because both are huge subjects.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Undoubtedly performance can be improved upon.

Your first port of call should be the users, are they complaining about speed ?

Note that most users will always complain that the system is to slow, but the key is finding out which slow downs are caused by database issue rather than front end app, network, spyware etc...

Once you have found your top 10 slow down processes , in your development environment, trace the calls using SQLProfiler.

Capture those calls , and in SSMS run them capturing the execution plan.

Now you will have a fair idea as to the major offenders.

The 'big' gains will come from (in no particular order)

Removing Table Scans, ie adding indexes

Using correct set based logic (Removing cursors)

Removing unnecessary code, if the app is old then some functionality may not be used.

Using less "round trips" between the client and server.

Blocking will obviously have a massive impact too and will not show in a development environment.

So are there long running transactions ?

As Jeff said though this is huge subject which is why there are any number of consultants our there who specialise in this. Maybe your best bet would be to hire one for a day or so to give you a health check and to learn from.

Clear Sky SQL
My Blog[/url]

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Without a baseline on the old system, you can't possibly see if performance improved.

All you can do now is baseline the new system (baseline means collect a set of performance metrics using whatever performing monitoring tools you have) and storing it on the side. Then, start tuning. Like the post above said, see if the users have complaints in any particular area. If not, identify slowest performing queries or identify where the system is seeing waits and queues and work on tuning what's causing things to wait.

Then, go back and compare the results of your tuning to the baseline you captured. Capture a new baseline. Rinse and repeat.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

a2zwd SSCrazy Points: 2356 More actions · Answer 4

Hi,

Thanks for the replies. No complaints from the users. We are importing data from external systems using SSIS packages. The data import is slow.

How can I analyze Execution plan result. My query using joins and some aggregations.

Exec. plan showing different percentages(1 table: 40%, 2 table: 50%) and other task showing 5%

Whats that percentage means. How much percentage is meant that performance is good?

I am new to this topic.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

This is a huge topic. I'd suggest picking up a copy of my book (linked below). You could also start with reading Gail Shaw's articles over here at Simple-Talk[/url].

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning