June 3, 2011 at 3:17 pm
I have a few store procs on a SQL Server 2005 machine that get executed thousands of time a week. Just wondering if someone can share their experience about how to go about tuning these guys and where to start.
has anyone used plan guides?
June 3, 2011 at 4:38 pm
I'd start with figuring out what in the proc(s) needed to be tuned. Tuned for speed, tuned for concurrency, or tuned for reducing I/O.
Usually, I tune for speed.
From there, I crack the proc open and wrap each statement in a timer. Find out the longest running statement/components. Figure out where the problems lie. Use either SET STATISTICS IO, TIME ON/OFF, or build your own custom timer (my usual way).
Once I find out the problem areas, I'll start digging into the execution plans. Figure out if anything there is wonky.
Sometimes you find out it's just the cost of doing business, sometimes you end up opening major threads here and bugging MCMs to come help you out. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 7, 2011 at 6:48 am
If you've already defined which queries are causing you pain, you're past the starting point. Once you identify the queries, then yes, absolutely, the execution plans are the way to go. If you don't have experience with them in SQL Server, then you might want to get a copy of my book on Exec Plans. You can buy a dead tree version from Amazon, or download the e-book from free here on SSC. Link to Amazon is in my signature.
"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
June 8, 2011 at 4:20 am
You can check your procedures with profiler to find out paining areas.
Include recompilation event in profiler to find out any recompilation..
-Cheers
Saurabh
June 13, 2011 at 4:35 am
First check the Execution Plan of the particular SP ,where is the cost
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 14, 2011 at 8:20 am
There are SOOOOO many things that can make a sproc run slowly: parameter sniffing, widely disparate input parameters, data value distribution skew, blocking, IO stalls, any number of other 'waits', poor data structures, UDFs, too complex a query, recompiles, table variables, unnecesary indexes on temp tables, poor coding practices such as IS NULL OR, missing indexes, too many indexes, out of date statistics, etc, etc, etc. By far the quickest way to get a sproc tuned is to have a professional do it and mentor you in their methods. Outside of that you can hunt and peck on this forum for days to try to get a reasonably complex sproc tuned.
Lots of people say post a query plan and tune from that. But that could be REALLY bad if that plan you show is an outlier plan or is based on outlier inputs. Not saying you shouldn't do that, but you must be aware of the limitations of tuning from a single plan or execution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 15, 2011 at 6:05 am
If you have already identified the procedures then that was your starting point. Now determine how many times the SPs are running and for how long. From that statistics, determine on which one to attack first.. then analyze the execution plans and tune.
If SPs are not identified and wondering how to find them then you can get it from:
Cross apply sys.dm_exec_query_stats qs with sys.dm_exec_sql_text(qs.sql_handle)
You can get the following from the above query:
Total CPU time
Avg CPU time
Total Elapsed time
Avg time elapsed
No of Physical Reads
No of Logical Reads
No of time executed
SQL Statement etc
Then decide based on the server load at the time these queries run on whether you want to go by CPU, I/O or time.
PM me if you need the complete query.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply