| Author |
Topic |
|
ZZartin
Starting Member
30 Posts |
 Posted - 2011-01-25 : 15:10:05
|
| We're having a rather odd problem in our datawarehouse. We have a massive job we run on a nightly basis that updates a large amount of data we use for reporting. Recently this query has been running extremely poorly, when i look at the estimated execution plan for one of the main queries in the job I can see that a couple of steps are taking massively more resources than they should(37% cost for a step running an index seek on a table with 200 rows) which just doesn't seem right. Now after I update the statistics on all the tables used in this query the problem steps go down to 1% cost which is fine. The problem is after we run our nightly update from our CRM database into our datawarehouse these steps go back to 37% cost and the job gets hosed.I was wonderring if anyone here had any idea what the problem might be or if there are any setting we should check? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-01-25 : 15:21:42
|
| Oddly enough updating the statistics on the small table doesn't resolve the problem. I actually had to update the statistics on another table in the query which has significantly more data in it and takes 1+ hour to update the stats on. Another issue is that the updates from our CRM run while this job is running.We've been running this job for around 2 years without having issues until recently. We originally had this problem a couple months ago and figured out that updating the statistics resolved it, however it looks like that was only a temporary solution and now it seems to be reoccuring every night even after updating statistics. To clarify I check the execution plan for the query see the problem, update the statistics check the execution plan again and the problem seems to be resolved. When i check the execution plan the next day after updates from our CRM are loaded the problem is there again. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-01-25 : 16:07:39
|
| Hm... it was still showing the problem in the estimated execution plan after i cleared the procedure cache, I'll try updating the statistics now that I've cleared the cache and see if the problem occurs again tomorrow. |
|
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-01-26 : 13:03:48
|
| Herm... that didn't work, yesterday after freeing the procedure cache and refreshing the statistics everything looked good. This morning the two problem steps are back up to 37%.Would anyone have any other suggestions to try since i'm kind of at a loss here? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-01-26 : 15:03:23
|
| It's mostly just an observation, when this job is running with the expected performance the cost for these two steps is 1%. Right now it's at 37% and the job is simply not completing in any reasonable time.It seems to be the problem because it doesn't seem to make sense that an index seek on a table with 200 records would have a cost of 37% while an index seek on another table with millions of records has a cost of 2%. I'm also trying to understand why the cost of those steps would change overnight so drastically(the only difference being we load some new data from CRM over the night). Looking at it in more detail it looks like the tool used to pull data from our CRM is doing a cheap quick update statistics command instead of a WITH FULLSCAN on the tables after it loads them, it's possible this is causing the problem. |
|
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-02-01 : 13:42:29
|
| Hm... so i thought the problem might have been that some of the indexs were badly fragmented so I tried defragmenting them. However this didn't resolve the issue, and to make this more interesting I'm seeing multiple steps in the execution plan now that are showing as 100% cost.Does anyone have any suggestions as to what to try next since i'm running out of ideas? |
|
|
|
|
Query Optimization Problem