Databases Reference
In-Depth Information
Of course, more complicated maintenance plans can exist, for example, when indexes
are rebuilt or reorganized depending on their fragmentation level. A good starting point
to do this is to use the
avg_fragmentation_in_percent
column and the index
fragmentation thresholds as deined on the Topics Online entry for the
sys.dm_db_
index_physical_stats
dynamic management function. You should keep in mind
the items mentioned above, so that you can avoid problems like updating the index
statistics twice, as could occur when both index rebuild and update statistics operations
are performed. You could also avoid discarding work previously performed, for example,
when you rebuild the indexes of a table (which also updates statistics by scanning
the entire table), and later running a job updating the statistics with a default or
smaller sample.
Let me show you how these commands work, with some examples. Create a new table
dbo.SalesOrderDetail
:
SELECT
*
INTO
dbo
.
SalesOrderDetail
FROM
sales
.
SalesOrderDetail
Listing 3-20.
The next query uses the
sys.stats
catalog view to show that there are no statistics
objects for the new table:
SELECT
name
,
auto_created
,
stats_date
(
object_id
,
stats_id
)
AS
update_date
FROM
sys
.
stats
WHERE
object_id
=
object_id
(
'dbo.SalesOrderDetail'
)
Listing 3-21.
