January 7, 2011 at 12:47 am
I want to know what do you guys(sql server experts) do when you are asked to tune a stored procedure?what are all the basic things u check up? i need some tips..I don want any theory..i want u guys to share ur experience. please suggest some tips for beginners like me.
January 7, 2011 at 12:55 am
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
January 7, 2011 at 1:07 am
Before following Gails great articles, you need to understand what the procedure you're trying to improve actually does.
Therefore, a brief code review is highly recommended (=mandatory).
Look for the obvious things like cursors, while loops and the like and verify if those are the only way to get the desired result. In most cases there are set based solutions that will perform much better. If you don't try to find and eliminate loops you might end up with a huge trace file (consider a loop that's fetching data from a several million row table row by row....).
January 7, 2011 at 1:44 am
@lutz: thank u for ur valuable tip...:-)
@gila:I have already read ur articles..really nice..I ll read it again for better understanding.:-)
January 7, 2011 at 1:59 am
Gila, really a wonderful article..
Lot of effort put into that.
January 7, 2011 at 8:16 am
I know of this book. It's pretty good. It's on the MCM reading list... look down near the bottom of my post.
"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
January 7, 2011 at 10:53 pm
you can write this query to get all the missing indexs
select * from sys.dm_db_missing_index_details
January 8, 2011 at 12:29 am
the
select * from sys.dm_db_missing_index_details
the way it works the sql server optimaizer write
to dm_db_missing_index_details
indexs that will help the queries run
faster it know it base on the queryies that runs on the server and the optimaizer
see that if you will add certain indexs the queryies he is oprtimnaizig will run faster
January 8, 2011 at 12:35 am
you can also read this artical
January 8, 2011 at 5:20 am
I would be extremely cautious about taking the missing index reports at face value. They are frequently more than a little bit inaccurate and can lead to too many indexes and indexes that duplicate each other. They are useful for suggestions to begin your investigation, but they should never be applied blindly.
"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
January 8, 2011 at 2:16 pm
To expand on that, less logic and processing goes into the missing index DMV entries than into the suggestions from DTA (database tuning adivsor) and even that's often questionable.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply