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
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.
answered Mar 25, 2011 at 01:39 AM
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:
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.
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.
answered Mar 25, 2011 at 02:10 AM
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:
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:
answered Mar 25, 2011 at 06:00 AM
You can start by checking this blog: SQL Server Performance Tuning for Stored Procedures
answered Mar 25, 2011 at 06:01 AM