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

cbrichards via SQLMonster.com (u3288@uwe) writes:
> 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?

 



-- 
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

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

"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.

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

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

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

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