March 25, 2005 at 11:36 am
Hi All,
I have a stored proc that uses a cursor to iterate over the join resultset of 5 tables. After fetching roughly 12,000 records and running for about 30 minutes, the next FETCH statement inside the WHILE loop hangs. This happens consisently. I tried to do a commit / checkpoint after every 10,000 records, but the problem persists.
Can any one please provide some thoughts about why this could be happening?
Thanks,
Rajesh
March 25, 2005 at 12:02 pm
I would look at possible contention inside of your TEMPDB table. What is the size of your TEMPDB database and hard disk size?
I would alos probably turn on low-level profiling to capture what is going on. We recently had similar problem and it turned out that the query plan that worked fine for 1st 25mm records started table scanning ...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 25, 2005 at 12:20 pm
Thanks for the response.
The tempdb is 955 MB in size and the hard disk is 33 GB (12 GB free space). Its our development database machine.
Please pardon my lack of knowledge about SQL Server - what do you mean by low level profiling? How do i turn it on?
Thanks so much.
Rajesh
March 25, 2005 at 3:54 pm
SQL Profiler (Found inside Enterprise Manager)
Only selections you need should hopefully be:
TSQL > StmtComplete
Stored-Procedure > StmtComplete
Forget where these are under
Show Plan All
Show Plan Text
Execution Plan
For the fields to capture add BinaryData.
Save this to a text file (This way the plans won't be truncated and/or missing)
Let it run for 5 - 10 minutes during the problem and then you can review it. You want to look for HIGH READS/WRITES/CPU
The plans will show you table scans and help point to code that may need assistance with optimization
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply