August 12, 2010 at 2:27 pm
We have a user deletion process on my website which times out almost 100% of the time, so we have to run it manually on the database. I'd like to see what is going on with the stored procedure when it is executing. Is there a way I can run the SP and collect a log of everything that it does? Or maybe extract all that information from the transaction log?
August 12, 2010 at 6:19 pm
This sounds like a perfect job for Profiler. There's a couple of ways you could trace this.
For stored procedures, you'll want to capture RPC:Completed and SP:StmtCompleted events with Profiler. The first captures the completion of the stored proc, and the second captures the completion of each statement inside the proc.
1. You can trace the timing out web page. This will involve capturing the two events above, and setting a filter for DURATION >= 10000 (10 seconds, assuming you have a timeout of about 30 seconds). This will show you every statement that takes longer than 10 seconds - a timeout will generate an event when it times out. Note that you have no control over which connection the website will use, so you're capturing the long duration statements for every connection, not just the one donig the user deletion process.
2. You can trace your Management Studio query. For this, you can either use the Duration filter above, or, for a more detailed view, you can focus on what your SPID is doing. First, find your SPID. SELECT @@spid, or look at the bottom of Management Studio. You'll see the servername you're connected to, and, to the right of that is your login name. The SPID is the number in the brackets next to your login. Create a filter on your Profiler trace for this SPID.
3. It's also possible to trace a single stored procedure, but I've never had reliable success with this.
The important thing is to make sure your filter is sufficient to pare down the number of events you'll get back. On a busy Production system, failure to supply a filter can quite quickly cause Profiler to use a ton of memory and cause the machine to become unresponsive.
August 13, 2010 at 4:53 am
Post the Sp you are using for deletion along with related index/table definfiton.
Additionally, you can use "batch approach" to delete the heavy data volume.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply