Metadata Collection - SQL Server Integration Services Design Patterns - page 29

------------- -----------------------
9

2014-06-28 19:15:28.837

21 2014-06-28 19:15:28.837

28 2014-06-28 19:15:28.837

As you can see, this query is a bit more complex than the last one. Let's discuss
what you're doing. Developers are usually very good at identifying performance issues.
Why? When a query is slow, someone is usually complaining about it! It's not uncom-
mon for the fix to involve creating an index, which can reduce IO and improve query
duration. Over time, however, the query may change—resulting in the optimizer using
different indexing—or perhaps the query is no longer needed. Unlike the user-impact-
ing issues that garner so much attention, these types of changes tend to creep up si-
lently over time. Eventually that same index that was so beneficial when it was first be-
ing used starts consuming unnecessary resources—specifically, it slows down insert
speed, consumes precious disk space, and inflates backup sizes.
One way to stay on top of unused indexes is to search the
sys.dm_db_index_usage_stats DMV. This DMV keeps track of index utiliza-
tion statistics, including how many times an index has been seeked or scanned and how
many updates have been performed. This information is refreshed after every reboot,
so please note that a server that has been restarted recently may show an inaccurately
high number of “unused” indexes. Also, this information is merely a starting point
from which you can conduct further research into whether an index should be dropped
or redesigned; many organizations may have indexes that are not called frequently but
are necessary for important monthly or annual reports.
One other important thing to note is that this script makes use of the undocumented
sp_MSforeachdb stored procedure. This stored procedure performs a very useful
task: it iterates through every database, executing whatever command is passed to it.
For numerous reasons—not the least of which is the fact that it is an undocumented,
and therefore unsupported, stored procedure that may occasionally skip databases—we
recommend using Aaron Bertrand's sp_foreachdb stored procedure instead for
production workloads. However, to keep things simple, you will use the
sp_MSforeachdb procedure in your example.

Tip Aaron Bertrand's sp_foreachdb stored procedure can be found at
www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-
and-flexible-spmsforeachdb .