Perfomance Tuning

Question

Post reply

Perfomance Tuning

Mani Dev

Right there with Babe

Points: 737
More actions
July 19, 2012 at 9:19 pm

#259280

Can anyone please help me with Performance tuning in general steps.
Like what values we need to considers when we do performance tuning on stored procedures and queries.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 1

I guess there are no default recipes and most of the people will say "it depends". 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Jared SSC Guru Points: 61793 More actions · Answer 2

Take a look at the books that are free pdf download from RedGate http://www.red-gate.com/community/books/#sqldev

Jared
CE - Microsoft

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

The below articles might also help you to get started

Finding the Causes of Poor Performance in SQL Server, Part 1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

Finding the causes of poor performance in SQL Server, Part 2

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Get Grant's book

http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=sr_1_3?s=books&ie=UTF8&qid=1342768686&sr=1-3&keywords=Grant+Fritchey

or

http://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035/ref=sr_1_1?s=books&ie=UTF8&qid=1342768686&sr=1-1&keywords=Grant+Fritchey

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

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

Not much to add. I'd start with Gail's articles. Once you're going, my books can help. Also, performance issues can be from causes other than queries (although that's near the top of most issues) so I'd suggest checking out the Troubleshooting SQL Server[/url] book (which Gail also contributed to).

"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

Mani Dev Right there with Babe Points: 737 More actions · Answer 6

Thank you one and all.I'll get back to you if i have any dbts..

Mani Dev Right there with Babe Points: 737 More actions · Answer 7

SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,

( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))

/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )

AS PercentageSignalWaitsOfTotalTime

FROM sys.dm_os_wait_stats

What values do we need to consider for this??

Do we have any default values for this.?

If it is a wrong question let me know.

Jared SSC Guru Points: 61793 More actions · Answer 8

If you are interested in wait types. Pinal Dave has a great series here.[/url]

Jared
CE - Microsoft

Mani Dev Right there with Babe Points: 737 More actions · Answer 9

@SSCrazy

Hello

I just wanted to know like what all types of counters are to be checked during perfmon.

(OR)

and what values do we need to go though if we use DMV's

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

devullapallymanidhar (7/20/2012)
I just wanted to know like what all types of counters are to be checked during perfmon.
(OR)
and what values do we need to go though if we use DMV's

Grant Fritchey (7/20/2012)
I'd suggest checking out the Troubleshooting SQL Server[/url] book (which Gail also contributed to).

It's covered in Chapter 1 and 6, and probably most of the others too.

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

Jared SSC Guru Points: 61793 More actions · Answer 11

GilaMonster (7/20/2012)
Grant Fritchey (7/20/2012)
I'd suggest checking out the Troubleshooting SQL Server[/url] book (which Gail also contributed to).
It's covered in Chapter 1 and 6, and probably most of the others too.

I think the OP is now fishing for fast fixes that don't exist... To the OP... There is no easy answer to the questions you have. You should read the books we all suggested.

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

There's one very fast way to resolve performance problems - hire a specialist to do it for you. 🙂

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

Jared SSC Guru Points: 61793 More actions · Answer 13

GilaMonster (7/20/2012)
There's one very fast way to resolve performance problems - hire a specialist to do it for you. 🙂

I hope to be that "specialist" in a couple of years 🙂

Jared
CE - Microsoft