Delivering a SQL Server Health Check - SQL Server 2012 Internals and Troubleshooting - page 442

This query returns every table and index in the current database, ordered by average fragmentation
level. It i lters out indexes that have fewer than 500 pages, as fragmentation in very small tables is
not something you typically have to worry about. Depending on the size of your tables and indexes,
and your hardware, this query could take some time to run. This query uses the LIMITED mode
option (which is the default if no mode option is specii ed) when it runs, so it will return less infor-
mation, but take less time to run than the DETAILED mode.
This query is useful because it can show you the overall condition of your indexes as far as frag-
mentation goes relatively quickly. Heavily fragmented indexes can reduce your I/O performance and
your query performance for some types of queries. It can also increase the space required by your
data i les.
If you see indexes that have more than 10% fragmentation, you need to decide whether to reorga-
nize them or simply rebuild them. Reorganizing an index is always an online operation, and it can
be stopped at any time. It can take longer than simply rebuilding an index and it may not reduce
the fragmentation as much as rebuilding the index will. Rebuilding an index can be either an online
operation or an ofl ine operation, depending on several factors. The i rst factor is whether you have
SQL Server Standard Edition or SQL Server Enterprise Edition.
If you have Standard Edition, rebuilding an index is always an ofl ine operation. If you have
Enterprise Edition, your index rebuild operations can be online or ofl ine depending on a few more
factors. With SQL Server 2012, you can rebuild clustered indexes in online mode, regardless of what
data types your table contains. With earlier versions of SQL Server, you cannot rebuild a clustered
index in online mode if your table has any lob data types, such as nvarchar(max) .
After you reorganize or rebuild indexes that are heavily fragmented, you may free up a considerable
amount of space within your data i le(s). The data i le will still be the same size, but more free space
will be available. This is a good thing! Strongly resist any urge you may have to shrink your data
i les to reclaim that disk space. Shrinking data i les is a very resource-intensive operation that has
the unfortunate side-effect of heavily fragmenting your indexes. Do not let your system administra-
tor or SAN administrator talk you into shrinking data i les or entire databases on a regular basis.
Finally, don't make the common mistake of simply rebuilding all your indexes on a regular basis,
whether they need it or not. This is a huge waste of resources on your database server. You can i nd
many good index maintenance scripts on the Internet. One very well regarded one was developed
and is maintained by Ola Hallengren which you can get from here: http://ola.hallengren.com .
SUMMARY
In this chapter, you walked through a set of 51 queries that enable you to gather a wealth of useful
information about your database server — starting at the hardware level, then at the instance level,
and i nally at the individual database level. For each query, you have read the underlying rationale
for why you would want to run the query and what you can expect to discover. You should now be
able to interpret the results of each query, and in many cases also be able to respond to them. For
some queries, I provided a more extended discussion of my thoughts and experiences regarding best
practices for the area covered by the query.