Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a monthly ETL process that's too complicated to explain here, but basically we get a large amount of customer data, bulk load it into various tables, then run a series of build processes.

The volume of total data is large, we add 25-30m records monthly to the largest table, which has around 700m records total. There are support tables from 2m-300m records each as well. Total data for this set is around 3.5TB.

This month, some processes are taking exponentially longer than usual. One example is a proc that ordinarily completes in 30 minutes took around 40 hours before I killed it (it still hadn't completed).

Not all processes are affected, and some run even faster than usual.

Obviously the root cause is beyond the scope of something I can ask here, but there is something that bothers me:

We have actual query plans showing in the millions of % for individual operators.

Like a clustered index scan for 791,358,704%, a merge join for 75,566,494%, etc. This occurs in multiple query plans, and these are plans taken from sys.dm_exec_query_plan.

Are these query plans indicative of some other larger issue?

I know with a certainty the problem is not out of date stats - we ran fullscans on all the major tables, and the biggest table and DB I manually rebuilt with pre-sizing to eliminate fragmentation.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

JNK,

You meant something like this?

http://sankarreddy.com/wp-content/uploads/2011/03/CropperCapture2.jpg

This is an estimated execution plan that's generated based on the statistics by the client tools and note that its NOT an engine issue. If you want MSFT to fix this issue then please vote for this connect item item.

https://connect.microsoft.com/SQLServer/feedback/details/436184/huge-operator-cost-in-estimated-execution-plan

Since you mentioned SQL Server 2008, I would encourage you to look at the WAIT STATS information when the job is being run. Look at why the execution is NOT moving along and figure out the bottleneck.

http://blogs.technet.com/b/sqlos/archive/2008/07/18/debugging-slow-response-times-in-sql-server-2008.aspx

Also, look at the sys.virtualfilestats information and see if there are significant IO lags and as well as memory usage.

HTH

share|improve this answer
    
Perfect, thanks for the info and links! –  JNK Jun 17 '11 at 15:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.