View Message Thread (7 replies)
| Index Rebuild and Statistics |
 |
From: cbrichards via SQLMonster.com
Date Posted: 7/14/2010 4:25:00 PM
When an index is rebuilt are:
(1) statistics updated on the index during the rebuild process, or
(2) is a flag set so that when the index is used next the statistics are
updated on the index, or
(3) if auto update stats is enabled the statistics are next updated when the
modified rows meet the internal thresholds, or
(4) does a manual UPDATE STATISTICS need to be executed if #2 or #3 has not
already occurred?
--
Message posted via http://www.sqlmonster.com
| Re: Index Rebuild and Statistics |
|
From: Erland Sommarskog
Date Posted: 7/15/2010 2:34:00 AM
| Re: Index Rebuild and Statistics |
|
From: cbrichards via SQLMonster.com
Date Posted: 7/15/2010 9:32:00 AM
Thanks Erland. That is what I understood but was not sure. Yet, we seem to
have a situation (and I will have to do more tests) where a certain UPDATE
statement in a stored procedure is producing high reads and duration. In
production I rebuilt all indexes and the statement is still problematic.
According to our software engineers, they both rebuilt indexes and ran update
statistics with fullscan and the statement executes successfully. So, I am
scratching my head. Like I said, I will have to do some testing myself rather
than relying on the word of the engineers, just to be sure.
Lastly, I have my reindexing job set up so that indexes are rebuilt online if
fragmentation exceeds 30%. If the fragmentation is between 10% and 30% I
perform a reorganize followed by an update statistics. I am finding that the
reorganize and update statistics takes longer to execute than a rebuild, and,
as you said, the rebuild updates statistics. We are running SQL 2008
Enterprise. I am thinking of doing away with the reorganize and update
statistics altogether and just do rebuilds as it seems more efficient. Any
concerns you may have that I am not aware?
Erland Sommarskog wrote:
>> When an index is rebuilt are:
>> (1) statistics updated on the index during the rebuild process, or
>
>Yes. SQL Server has to read all data anyway, so why not update the
>statistics, while it's at it?
>
>
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201007/1
| Re: Index Rebuild and Statistics |
|
From: Nobody
Date Posted: 7/15/2010 10:22:00 AM
"cbrichards via SQLMonster.com" <u3288@uwe> wrote in message
news:ab0acc12736c5@uwe...
>
> Thanks Erland. That is what I understood but was not sure. Yet, we
> seem to
> have a situation (and I will have to do more tests) where a certain
> UPDATE
> statement in a stored procedure is producing high reads and
> duration. In
> production I rebuilt all indexes and the statement is still
> problematic.
> According to our software engineers, they both rebuilt indexes and
> ran update
> statistics with fullscan and the statement executes successfully.
> So, I am
> scratching my head. Like I said, I will have to do some testing
> myself rather
> than relying on the word of the engineers, just to be sure.
>
So are you saying the statement runs fine from query analyzer but is
slow in the
stored procedure? if so, sounds like a parameter sniffing issue.
| Re: Index Rebuild and Statistics |
|
From: cbrichards via SQLMonster.com
Date Posted: 7/15/2010 10:36:00 AM
Sorry I wasn't clear. The stored procedure is executed as a whole, either via
query analyzer or the application, and a specific update statement in the
executed stored procedure is acting up.
I have ruled out parameter sniffing because there are no parameters used in
the update statement.
Nobody wrote:
>> Thanks Erland. That is what I understood but was not sure. Yet, we
>> seem to
>[quoted text clipped - 11 lines]
>> myself rather
>> than relying on the word of the engineers, just to be sure.
>
>So are you saying the statement runs fine from query analyzer but is
>slow in the
>stored procedure? if so, sounds like a parameter sniffing issue.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201007/1
| Re: Index Rebuild and Statistics |
|
From: Erland Sommarskog
Date Posted: 7/15/2010 3:34:00 PM
cbrichards via SQLMonster.com (u3288@uwe) writes:
> Thanks Erland. That is what I understood but was not sure. Yet, we seem
> to have a situation (and I will have to do more tests) where a certain
> UPDATE statement in a stored procedure is producing high reads and
> duration. In production I rebuilt all indexes and the statement is still
> problematic. According to our software engineers, they both rebuilt
> indexes and ran update statistics with fullscan and the statement
> executes successfully. So, I am scratching my head. Like I said, I will
> have to do some testing myself rather than relying on the word of the
> engineers, just to be sure.
Just because statistics are up to date, all fragmentation done away
with, does not mean that the optimizer will get it right anyway. Statistics
are after all just that, statistics.
You will need to analyse the query plan for the statement and see if there
are any issues with it. And of course, you need to investigate if there
are any triggers on the table.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
| Re: Index Rebuild and Statistics |
|
From: cbrichards via SQLMonster.com
Date Posted: 7/16/2010 9:31:00 AM
Thanks for the insight and areas to focus on.
Do you have any thoughts relative to my earlier question regarding the
efficiency of rebuilding an index versus reorganizing and updating statistics?
Erland Sommarskog wrote:
>> Thanks Erland. That is what I understood but was not sure. Yet, we seem
>> to have a situation (and I will have to do more tests) where a certain
>[quoted text clipped - 5 lines]
>> have to do some testing myself rather than relying on the word of the
>> engineers, just to be sure.
>
>Just because statistics are up to date, all fragmentation done away
>with, does not mean that the optimizer will get it right anyway. Statistics
>are after all just that, statistics.
>
>You will need to analyse the query plan for the statement and see if there
>are any issues with it. And of course, you need to investigate if there
>are any triggers on the table.
>
--
Message posted via http://www.sqlmonster.com
| Re: Index Rebuild and Statistics |
|
From: Erland Sommarskog
Date Posted: 7/16/2010 9:31:00 AM
cbrichards via SQLMonster.com (u3288@uwe) writes:
> Do you have any thoughts relative to my earlier question regarding the
> efficiency of rebuilding an index versus reorganizing and updating
> statistics?
In the system I work with there is a maintenance module that the customers
may use if they want to. The default principle there is some value is
below 70% we rebiuld the index, else we run UPDATE STATIISTCS WITH FULLSCAN,
INDEX on the table. The default is that these jobs run once a week.
I will have to admit that I don't know which value we look at. I would
guess it's logical fragmentation, but as long as you don't have scans
on the table, that is not really a very intersting number. There may be
more reason to look at free bytes per pages, because if your pages are
only half-full, you are not utilising the cache effectively.
As for UPDATE STATISTICS, we restricted it to index-only, since that
was a big time saver. When SQL Server updates statistics for non-indexed
columns, it scans the table once for each column.
I also like to point out that the absolutely most important type of
columns to keep updated statistics for, are columns that grow monotonically
in big tables. That is, numeric id:s, date and datetime columns. And this
is particular important if there are queries that goes against the most
recently inserted data. In this case, the statistics quickly become scale,
and auto-stats will not set in until it's far too late.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Would you like to track this thread?
By adding this News Thread to your Favorites Area you can refer to it later with just a click of the mouse.
Also you can optionally be notified instantly whenever there are any replies
posted to this Thread. To add this Thread to your Favorites Area just click on the red arrow next to the subject of the thread above
 .
|
