Stored procedure fine tuning steps/guidelines

Question

Post reply

Stored procedure fine tuning steps/guidelines

SQL*

SSCertifiable

Points: 6638
More actions
April 14, 2010 at 3:08 am

#139668

hi to all,
Can anyone give the sp fine tuning steps/guidelines, how can i optimize the sp?
if a sp is taking log time to execute how can check why it is giving log time.
Thanks
🙂

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

dba-vb (4/14/2010)
hi to all,
if a sp is taking log time to execute how can check why it is giving log time.

1. Study execution plan

2. Read IO statistics

3. Refer Sql Profiler

dba-vb (4/14/2010)
hi to all,
Can anyone give the sp fine tuning steps/guidelines, how can i optimize the sp?

Refer this http://www.sqlservercentral.com/articles/Performance+Tuning/67427/

and

http://www.sqlservercentral.com/articles/Performance+Tuning/ospr/666/

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SQL* SSCertifiable Points: 6638 More actions · Answer 2

hi,

Thanks for the help, But how can i use execution plan/io/profiler one by one. when to check these is there any specific rule.

Thanks,

🙂

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

dba-vb (4/15/2010)
how can i use execution plan/io/profiler one by one. when to check these is there any specific rule.

Profiler's Videos

http://www.sqlservercentral.com/articles/Video/66676/

http://www.sqlservercentral.com/articles/Video/63814/

for Execution plan

http://www.sqlservercentral.com/articles/Performance+Tuning/displayingexecutionplans/1103/

http://www.sqlservercentral.com/articles/Performance+Tuning/understandingexecutionplans/739/

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

dba-vb (4/14/2010)
hi to all,
Can anyone give the sp fine tuning steps/guidelines, how can i optimize the sp?
if a sp is taking log time to execute how can check why it is giving log time.
Thanks

Read the Gail's shaw Blog

http://sqlinthewild.co.za/index.php/category/sql-server/execution-plans/

http://sqlinthewild.co.za/index.php/category/sql-server/performance/

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

WayneS SSC Guru Points: 95460 More actions · Answer 5

You might also want to look at these e-books available here:

SQL Server Maintenance Plans by Brad McGehee[/url]

Mastering SQL Server Profiler by Brad McGehee

SQL Server Execution Plans by Grant Fritchey

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

Basic setup:

SET STATISTICS PROFILE ON

(or your favorite execution plan viewing tool)

AND

Watch it in Profiler (Reads, CPU, Duration, Writes)

Have a sample of various execution steps just before the CREATE PROCEDURE, commented with /* to make it easy to test over and over, i.e.

/* A list of standard executions and comments on performance from Profiler

spProc 'bob', 90210

-- BASE: 450 CPU, 45k Reads, 120 Duration, 50 Writes

-- Tweak X: 640 CPU, 35k Reads, 150 Duration, 50 Writes

-- Tweak Y: 300 CPU, 50k Reads, 300 Duration, 30 Writes

spProc '%', 90210

spProc 'Peter%', 0

spProc '%eter', 55555

*/

Run each line item a few times in a row after a stored procedure update, watch the different between initial execution and subsequent executions.

Try both with the cache emptying statements before a run, and without. Know which situation is more likely in reality (cached vs uncached SP and/or data)

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Advanced usage: run the lines in different orders, so you can see what parameter sniffing is doing to you.

Only bother to performance test on a dataset at least as large as your current production set. Testing on cut down "test data" is a waste of your time

Tweak/recode the stored procedure in different ways, and watch the results in your particular environment:

Update statistics

Defrag clustered and nonclustered indexes (INDEXDEFRAG/Reorganize)

Defrag clustered and nonclustered indexes (DBREINDEX/Rebuild)

Derived tables

Temp #tables

@Table variables

2005 features I don't yet have much access to

Temp tables with additional indexes

Create different indexes, different column order within the indexes

OPTION MAXDOP

If you have branching in the stored procedure, split it into multiple nonbranched stored procedures (each with a potentially simpler, potentially more optimal execution plan)

If you have to, sometimes it is necessary to get into more advanced options that may provide suboptimal performance after SQL Server upgrades:

Index hints

Join hints

Know your environment; given a CPU vs Reads tradeoff, which is best for you? Maybe you have a very slow server with a very modern underused SAN, maybe you have the reverse.

Above all: Benchmarking real performance is all that matters. Theory is an entertaining guideline, but what actually matters is how it actually performs in your production environment, now and in the future.