Prev: Backup Behavior
Next: Finding a row with the heigest/lowest value under specific conditions.
From: cbrichards via SQLMonster.com on 14 Jul 2010 18:23
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

From: Erland Sommarskog on 15 Jul 2010 04:30
cbrichards via SQLMonster.com (u3288(a)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(a)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

From: cbrichards via SQLMonster.com on 15 Jul 2010 11:29
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

From: Nobody on 15 Jul 2010 12:17

"cbrichards via SQLMonster.com" <u3288(a)uwe> wrote in message
news:ab0acc12736c5(a)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.


From: cbrichards via SQLMonster.com on 15 Jul 2010 12:33
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

 |  Next  |  Last
Pages: 1 2
Prev: Backup Behavior
Next: Finding a row with the heigest/lowest value under specific conditions.