Post reply

Report Performance

  • April 16, 2009 at 9:56 am

    #130704

    So, I run an OLAP database on SQLS2K. I import several million new records every week into a table that contains a CalendarWeek column. CalendarWeek is the first column of the table's clustered index and primary key.

    Every week after the large import, I run a series of a few thousand reports that go out to my customers. However, this week, some reports are taking a LOT longer to run against this week's data than last week's data.

    By looking at execution plans, I've identified that some queries are getting drastically different execution plans when run against this week's data, versus when run against previous weeks' data. However, the overall characteristics of each week of data is pretty much the same, so I don't know why SQLS is choosing different plans for this week's data.

    I'm completely stuck here. I don't have time to reengineer all the reports to use specific execution plans. Is there anything I can do?

  • April 16, 2009 at 10:22 am

    #979017

    I just did an UPDATE STATISTICS on the table, and that fixed this. So I don't need help.

    What I could still use, though, is an explanation why this fixed it, since I have auto-create and auto-update statistics on for this database.

  • April 16, 2009 at 10:40 am

    #979044

    i think the rule is a count of 20% of the total rows in a given table have to insert/change before auto-update of the statistics occurs. On a billion row table, for example, you might not meet that huge threshold for the number of rows.(20 million rows?)

    so inserting 19 million rows is enough to make execution plans suffer due to poor stats, but not enough for the system to auto update.

    you'd be best off adding update statistics to occur after your imports for best performance/

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply