Prev: Backup Behavior
Next: Finding a row with the heigest/lowest value under specific conditions.
From: Erland Sommarskog on 15 Jul 2010 17:28
cbrichards via SQLMonster.com (u3288(a)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(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 23:56
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

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

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