January 5, 2009 at 2:08 am
Hi
This is lee from bangalore
Plz give me the full navigation
in sql 2005
How to monitor and tune the stored procedures
With regards
Lee
January 5, 2009 at 6:39 am
Monitoring stored procedures is best done by running what is called a server side trace. You can use the tool "Profiler" to define the trace and then script it out and run it against the server. Be sure to capture the data to a file, not to a table and not back to your screen through the gui.
As far as tuning a procedure... Start with the execution plan to see what the query is doing.
These are both really big topics, so getting a complete and detailed answer in a post is not really possible.
For a primer on performance monitoring, go here[/url].
For a primer on execution plans, try here [/url]& here[/url].
Also, you do a search on this site for some great articles that drill down on all this stuff.
"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 5, 2009 at 1:29 pm
Running a trace that logs the time it took for commands to complete is a good place to start. Then, when you pull the data from that trace, you have to look at the queries that took the longest to run, not including any that are expected to take a long time.
For example, you may want to look at all queries that take more than 1 full second to run. But, if you include stuff like ETL queries that load data warehouses, you'll end up with a whole bunch on your list that may not actually need to be worked on. Use good sense on this and you'll be fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2009 at 4:04 pm
Another thing you can do when analyzing a trace is to aggregate as you may be better off tuning a statement that takes 1/2 a second to run, but runs 5000 times in a day, than tuning a 1 second query that runs 10 times a day.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply