March 19, 2013 at 3:11 am
Hello All,
I am facing a load performance issue in one of the tables in our database.Whenever we face this,we abort the session and update the stats for that particular table.The load completes immediately after the restart.
We already have an update stats job for the entire database and stats are updated on a daily basis for all the tables but this issue seems to be occurring very frequently
What could be the reason.
Your Help would be appreciated.
March 19, 2013 at 3:33 am
As you're loading data into the table, the statistics are only getting updated if you cross a particular threshold. The rules are:
* First row on an empty table
* If less than 500 rows in a table, when it passes 500 rows
* >500, 20% of the number of rows + 500 rows
So, if you're doing a data load or modification, or both, then you may have added rows, but not crossed the threshold for statistics updates. This can lead to slow-downs because your queries are looking for values that may fall outside the statistics, leading to poor performance because the estimations lead the optimizer to make bad choices. That's why refreshing the stats works.
Thing to do, see if you can break down or break apart your data load so that you can run a manual update of statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply