March 16, 2012 at 1:15 pm
Hi Everyone,
We have 2 databases with millions of records and there is a process that moves data from one database to another database. It's esentially the same table in both databases and both tables are partitioned by month. After we partitioned the tables, the process is taking much longer to complete. We used SQL Profiler to find out what is going on. We found a stored procedure that runs for a long time (2 min), and we're trying to find out what is going on.
In SQL Profiler, the duration doens't make sense. It shows up as 1,500,000, which is over 30 hrs is i'm doing the math correctly. In the start time and end time, there is only a 2 min. difference. I'm not sure why the duration is wrong, so if anybody has any ideas, please let me know. The reads are also high, 1.5 million. The writes are at 0.
The SPROC uses a cursor, so that might be an issue in itself but before we make changes, we would like to find out what is going on.
My main question is this. We found the SPROC that's causing the problem, but when we run it manually, it takes 0 seconds. We tried clearing the buffer and it still takes 0 seconds. Any ideas on how we can troubleshoot this? If it also took 2 min. to run the SPROC manually, then we can look at redesiging it and maybe getting a performance improvement. But without being able to confirm the 2 min. time, it would be hard to spend the time on this. We are not sure what else to look at. Any help would be appreciated. Thank you.
March 16, 2012 at 1:39 pm
Duration is in Microseconds. Does that help with your math?
March 16, 2012 at 1:51 pm
Unfortunately, I have a feeling, while looking duration in a trace file, that it is in millisecods.
BOL may be wrong. Should do some checking on this.
March 16, 2012 at 2:01 pm
In Profiler, it looks like duration is displayed in milliseconds, but in the trace file it is stored as microseconds.
March 16, 2012 at 2:24 pm
I also thought that the duration was displayed in milliseconds not microseconds in Profiler.
Either way, i'm more concerned with finding out why the SPROC runs for 0 seconds manually, but for 2 min. when in ran for the first time and it was captured in Profiler.
March 16, 2012 at 2:29 pm
mikeb5555 (3/16/2012)
I also thought that the duration was displayed in milliseconds not microseconds in Profiler.Either way, i'm more concerned with finding out why the SPROC runs for 0 seconds manually, but for 2 min. when in ran for the first time and it was captured in Profiler.
When run manually, was it after it was captured in Profiler?
Could be cached data in subsequent runs.
March 16, 2012 at 2:32 pm
That's what we thought too, so we ran this command to clear the buffers.
DBCC DROPCLEANBUFFERS
I've research a bit more and it seems that maybe we also need to run: DBCC FREEPROCCACHE.
Any thoughts on this?
March 16, 2012 at 2:34 pm
Possible that the first run of the prc required a execution plan to be created and subsequent runs used the cached plan. If this is a test environment, go for it.
March 16, 2012 at 2:38 pm
If we do that, do you think that once we manually run the SPROC, it will have to create an execution plan, which will take the 2 min. that it took the first time? That's what I'm hoping for.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply