Performance tuning

Question

Performance tuning

y.koteswarrao-652921

SSCrazy

Points: 2010
More actions
March 13, 2008 at 12:39 am

#122694

Hi
How to do performance tuning? and when to do?
Koteswarrao

Viewing 15 posts - 1 through 15 (of 22 total)

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

Susantha Bathige Ten Centuries Points: 1040 More actions · Answer 1

This is a more generic question.

You need to make sure every db portion that pushes to production will be performed as intended. This is where the performance tuning comes into play.

Performance tuning is an art that you have to learn through the experience.

Susantha

karthikeyan-146504 SSC Eights! Points: 945 More actions · Answer 2

You can tune your db thru

1. SQL Server profiler

2. DTA (Database Engine Tuning Advisor)

3. DMVs(Dyanamic management views)

4. DBCC commands

Kindest Regards,

karthik

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 3

y.koteswarrao-652921

SSCrazy

Points: 2010

March 13, 2008 at 6:54 am

#788994

Hi

Thank you for giving reply

koteswarrao

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 4

hi

thank you for giving reply......my query is what shuld considered while doing performance tuning? plz give me reply

reards

koteswarrao

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

While it's true that there is some trickery you can perform to "Tune the hardware" and "Tune the database" and you can throw indexes at tables to make some queries run better, 90% of all major performance gains come from writing good code... period!

Does the code have cursors, While loops, triangular joins, multiple correlated sub-queries, aggregated views (not indexed views), views of views, multiple complex triggers, UDF's of UDF's, or calculated join/filter conditions? All of those are either a form of RBAR or just plain bad practices.

Trying to tune a hard disk so it will do a triangular join or a cursor faster might buy you 5% performance improvement.

Fix the code... that's where the real tuning is done.

--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)
Intro to Tally Tables and Functions

anishrtuladhar SSC Rookie Points: 37 More actions · Answer 6

Do performance tuning using SSAS....... when u need.:-D

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

anishrtuladhar (3/8/2010)
Do performance tuning using SSAS....... when u need.:-D

Not sure what you mean. How do you performance tune a query using SSAS?

--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)
Intro to Tally Tables and Functions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

There are a kajillion things that can affect performance. Read books, take classes, do the work for several years. If you are smart then by that point you might start to get good at it. If you aren't smart then you will still be struggling. 😀

The BEST way to learn how to tune is to hire a performance tuning mentor and learn from them as they work on your system.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

subha.msc2007 Mr or Mrs. 500 Points: 519 More actions · Answer 9

Hi,

i will give you the brief introduction about the performance issues.

1. connectivity problems &

2. Database tuning issues

Database tuning issues in

1. identifying the blockings & Deadlocks.

2. identifying missing indexes.

3. identifying top 10 cpu bound queries.

4. identifying top 10 I/O bound queries

Subha

DBA

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

subha.msc2007 (8/21/2010)
Hi,
i will give you the brief introduction about the performance issues.
1. connectivity problems &
2. Database tuning issues
Database tuning issues in
1. identifying the blockings & Deadlocks.
2. identifying missing indexes.
3. identifying top 10 cpu bound queries.
4. identifying top 10 I/O bound queries
Subha
DBA

You forgot the "crap code" problems. 😛 Ah... maybe not. 3 and 4 cover those, I suppose. :hehe:

--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)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

Jeff Moden (8/21/2010)
subha.msc2007 (8/21/2010)
Hi,
i will give you the brief introduction about the performance issues.
1. connectivity problems &
2. Database tuning issues
Database tuning issues in
1. identifying the blockings & Deadlocks.
2. identifying missing indexes.
3. identifying top 10 cpu bound queries.
4. identifying top 10 I/O bound queries
Subha
DBA
You forgot the "crap code" problems. 😛 Ah... maybe not. 3 and 4 cover those, I suppose. :hehe:

I'd flip the order around too. Once you've identified the top 10 worst performing queries, you're quite likely to be able to identify missing indexes from those. Will make more impact that just adding indexes without testing if they are useful first.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Yup, the good old addage that you can achieve 80% of the results by doing 20% of the work. That holds extremely true in sql server tuning.

I've even found 95%-5% to be quite possible.

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 13

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

August 23, 2010 at 7:18 am

#1210430

I would agree that these 4 are a good starting point.

Nadrek SSC-Insane Points: 20039 More actions · Answer 14

Turn on SQL Profiler with TextData, HostName, ApplicationName, CPU, Reads, Writes, and Duration (and whatever else you like) for the SQL:BatchCompleted and RPC:Completed events only.

Set it to run for a few hours, and save to a trace file or a database table. I've heard repeatedly that saving to a trace file is lighter weight than to a database table; on one particular oddball system the opposite appears to be the case.

Pull that output into a database table from the trace file http://support.microsoft.com/kb/270599.

Sanitize the TextData so any TextData that has high reads or shows up a lot becomes identical, i.e.

UPDATE Table

SET TextData = 'SELECT * FROM table WHERE EmployeeID = %'

WHERE TextData LIKE 'SELECT * FROM table WHERE EmployeeID = %'

Then start with:

SELECT TOP 500 TextData, SUM(Reads), SUM(Writes), SUM(CPU)

FROM Table

GROUP BY TextData

ORDER BY SUM(Reads) DESC

and

SELECT TOP 500 HostName, SUM(Reads), SUM(Writes), SUM(CPU)

FROM Table

GROUP BY HostName

ORDER BY SUM(Reads) DESC

and so on.

If you can, use a good text editor with RegEx search and replace to build the UPDATE statements in bulk; it can get exquisitely tedious, but I'm not aware of a better way to automatically do it.

If you can, have the developers parameterize their SQL before you start(!)