(This is part of the Study Guide series,
70-464)
Microsoft’s
Measured Skill description: This objective may include but is not
limited to: inspect physical characteristics of indexes and perform index
maintenance; identify fragmented indexes; identify unused indexes; implement
indexes; defrag/rebuild indexes; set up a maintenance strategy for indexes and
statistics; optimize indexes (full, filter index); statistics (full, filter)
force or fix queue; when to rebuild vs. reorganize and index; create a tuning
and maintenance strategy for proactive operations
What
I see:
·
physical index characteristics
·
index maintenance
·
index usage
·
create indexes
·
optimize indexes
Physical index characteristics
Like any other storage, indexes
get fragmented over time due to DML and page splits. The way to gather this information is through
the sys.dm_db_index_physical_stats
DMV. A basic query for these stats would
be:
Example
use AdventureWorks2012;
go
declare
@page_count_min bigint
= 1000;
select
db_name(database_id) as database_name,
object_name(object_id) as object_name,
index_id,
index_type_desc,
avg_fragmentation_in_percent,
page_count
from sys.dm_db_index_physical_stats
(
db_id('AdventureWorks2012'),
default,
default,
default,
'detailed'
)
where page_count >= @page_count_min
order by avg_fragmentation_in_percent desc;
Notice the data that this query
returns. The fragmentation, as well as
the page count. It is typically advised
to ignore fragmentation on indexes that have a negligible page count (i.e. not
such a huge impact from the fragmentation).
I have chosen a minimum of 1000 pages to consider.
Index maintenance
Index maintenance includes
cleaning up fragmented indexes that can be retrieved from the section
above. It’s a rule of thumb that any
fragmentation between 5% and 30%, and index should be reorganized. Greater than 30% should be rebuilt. Again, this is just a rule of thumb and not
to be taken as fact in all scenarios. If
your particular maintenance window is large enough, there’s no reason not to
rebuild all fragmented indexes.
Likewise, if you are running Enterprise Edition you have the ability for
an ONLINE index rebuild. There are a lot
of caveats to that particular method, but it does solve some index availability
issues.
Index Rebuild Example
alter index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
on Sales.SalesOrderDetail
rebuild;
go
Index ONLINE Rebuild Example
alter index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
on Sales.SalesOrderDetail
rebuild
with
(
online = on
);
go
Index Reorganize Example
alter index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
on Sales.SalesOrderDetail
reorganize;
go
Index usage
Index usage can be found through
querying the sys.dm_db_index_usage_stats DMV. This DMV contains aggregated statistics of
how indexes are utilized. If an index is
not contained in this DMV, it hasn’t been utilized since the last time SQL
Server started or DMV stats have been cleared.
Using this DMV with a LEFT/RIGHT OUTER JOIN with the sys.indexes
catalog view will yield unused indexes.
The below query shows how to get index usage statistics:
Example
select
db_name(us.database_id) as database_name,
object_name(us.object_id) as object_name,
i.name,
us.user_seeks,
us.user_scans,
us.user_lookups,
us.user_updates
from sys.dm_db_index_usage_stats
us
inner join sys.indexes i
on us.object_id = i.object_id
and us.index_id = i.index_id
where us.database_id = db_id();
Create indexes
Creating indexes can be a very
simple operation, but like many things in SQL Server there is a large list of
possible options that can be specified.
A basic CREATE INDEX command is shown below, but reference BOL for the
full list of options.
Example
use MeasureMySkills;
go
create clustered index IX_LockedTable_Id
on LockedTable(id)
go
Optimize indexes
Indexes can be used to greatly
optimize code and query performance.
Likewise, too many and useless indexes can cause a serious performance degradation
with INSERT/UPDATE/DELETE statements.
But given the correct indexes, they can change a table scan to an index
seek minimizing IO, CPU time, and overall duration. The sys.dm_db_missing_index_* prefixed DMVs
can be used as suggestions for indexes that can help. Like DTA, these should be taken as
suggestions not necessarily hard facts.
References
Version-Controlled Code (GitHub)
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at sqlsalt@gmail.com.
No comments:
Post a Comment