how do you troubleshoot a procedure?

Hi Every one,

I am a production maintanence DBA, I have minimal knowledge on development, Can you suggest answer for below questions:

How do u troubleshoot a Stored procedure?

How do you debug a procedur..?

Thanks In advance

more ▼

asked Mar 25, 2011 at 01:34 AM in Default

avatar image

414 25 26 31

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

It depends.

Seriously - what's the procedure doing? Is it a simple thing, or a big mahoosive thing?

What's the problem with it? Is it performance, or slightly buggy behaviour?

If the proc is small(ish) (in terms of number of distinct statements within it), and there's a performance issue, then it's just like trouble shooting any other nonperformant query.

If the proc is large(ish) with lots of distinct statements, the query plan might not be so helpful... unless it identifies one query taking the vast majority of the run-time, in which case have a crack at that one first.

If it's buggy behaviour, and you've got dev time, then make liberal use of the ability to put in things to spit out messages to show you what's going on - SELECT, PRINT, RAISERROR, for example.

more ▼

answered Mar 25, 2011 at 01:39 AM

avatar image

ThomasRushton ♦♦
42.3k 20 57 53

  • for "mahoosive".

Mar 25, 2011 at 02:09 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

I am tempted to avoid this question because of the reason for the question (interview question), but for the knowledge sharing cause I will.

I would try to find out:

  • How many IO operations is caused by the procedure (SET STATISTICS IO ON)

  • How long will the procedure take (SET STATISTICS TIME ON)

  • How the execution plan looks like, to see if there are missing indexes, implicit conversions etc.

  • Review to code to make sure there is a proper transaction and exception handling

  • Is the procedure causing any exceptions?

  • Is the procedure causing any long running locks / deadlocks

  • In SQL server 2008, i will run the procedure in DEBUG mode.

  • In some cases I have to run indvidual statements outside the procedure to make sure I get the expected results.

And in the best of all worlds I have a test environment with "actual" data on an production like server.

As for the interview part, what will happen if you get the job and can't investigate/solve the issue? I would make sure I have enough experience in the specific area before I apply for the job.

more ▼

answered Mar 25, 2011 at 01:59 AM

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left

Personally I'd see if it was the worst procedure on the server and then start on whatever came at the top of the list.

is it always the same performance
how many times is it executed
where is it executed from

more ▼

answered Mar 25, 2011 at 02:10 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

Are you wanting to do proactive troubleshooting/performance tuning, or fix a known issue? Either area is very involved, so doing so with little/no dev experience could prove difficult. You may want to get a T-SQL dev to look over your shoulder whilst digging in the code. Best option is to talk to the person that wrote the code, so they can also explain what they were thinking/wanting to achieve in the sproc.

For a known issue, you would take the sproc call that is bad/slow (with the parameters that prove to be bad) and step through the sproc to evaluate where things are going wrong.

You should do this on a test-box with a backup of your production system restored to it. That way you won't be causing more problems on your production system.

You can use the debug tools in SSMS and step through a sproc quite easily, assessing the codepath taken for the specific sproc call (some sprocs have logical re-direction depending upon variables you pass to them).

The way to approach pro-active performance tuning would be to find the worst performing procs in terms of:

  • Runtime

  • CPU Usage

  • Logical I/O

  • Physical I/O

You may well find that a few sprocs are in the top 20 for more than one of thos categories. Take these as your first optimisation possibilities. Improve them, then take another look at the top 20 (leave some time to allow a new top 20 to materialise). The low-hanging fruit principle of fixing the things with the greatest overall benefit to the system works wonders. It can quite often turn out to be that a system is totally bunged up by one or two sprocs and once you fix them, the system hums along. Do that, and you become the overnight hero in the company!

You can use something like the following to interrogate SQL Server to find the bad queries:

 --'Logical Reads'
 --'Physical Reads' 
 --'Logical Writes' 
 --'CLR Time' 
 DECLARE @OrderBy_Criteria varchar(20)
 SET @OrderBy_Criteria = 'Logical Reads' -- Set to one of the values from the comments above
 SELECT  query_rank,
         CASE WHEN LEN(qt.text) < 2048 THEN qt.text
              ELSE LEFT(qt.text, 2048) + N'...'
         END AS query_text,
 FROM    (SELECT s.*,
                 row_number() OVER (ORDER BY charted_value DESC, last_execution_time DESC) AS query_rank
          FROM   (SELECT *,
                         CASE @OrderBy_Criteria
                           WHEN 'Logical Reads' THEN total_logical_reads
                           WHEN 'Physical Reads' THEN total_physical_reads
                           WHEN 'Logical Writes' THEN total_logical_writes
                           WHEN 'CPU' THEN total_worker_time / 1000
                           WHEN 'Duration' THEN total_elapsed_time / 1000
                           WHEN 'CLR Time' THEN total_clr_time / 1000
                         END AS charted_value
                  FROM   sys.dm_exec_query_stats) AS s
          WHERE  s.charted_value > 0) AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
 WHERE   qs.query_rank <= 20     -- return only top 20 entries

more ▼

answered Mar 25, 2011 at 06:00 AM

avatar image

26.2k 18 37 48

wow, that turned out longer than I expected!

Mar 25, 2011 at 06:00 AM WilliamD

TWSS! Nice answer though, worth the effort :)

Mar 25, 2011 at 06:06 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

You can start by checking this blog: SQL Server Performance Tuning for Stored Procedures

more ▼

answered Mar 25, 2011 at 06:01 AM

avatar image

4.9k 33 39 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 25, 2011 at 01:34 AM

Seen: 4147 times

Last Updated: Jul 25, 2013 at 12:55 PM

Copyright 2018 Redgate Software. Privacy Policy