Page 87 - DLIS405_INFORMATION_STORAGE_AND_RETRIEVAL

Information Storage and Retrieval

Notes One last thing to talk about is index statistics. When you create an index, SQL Server generates
index statistics. SQL Server will also keep these statistics up to date as your index gets updates,
provided your database setting for “AUTO_CREATE_STATISTICS” is on (which is the default).
The database engine keeps these statistics up to date based the percentage of pages that have been
updated. When around 20% or more of the data rows have been updated, SQL Server will
automatically update your index statistics. Keep in mind when SQL Server automatically creates
statistics it does s by only sampling that data rows. If your tables are quite large, in the millions or
billions of rows, it might take quite a while to update 20% of the rows for the statistics to get
automatically updated.
Therefore, because of the sampling method and the 20% rule you might want to consider routinely
updating your index statistics manually. Doing this will give the database engine updated statistics
and this can help drastically in improving the performance of your application. Up to date statistics
allow the database engine to make the appropriate choices when selecting an execution plan. Here
is a script that will tell you when the statistics were last updated:
Display Index Statistic Update Date
SELECT s.name, o.name,i.name, STATS_DATE(i.object_id, i.index_id)
StatisticsLastUpdated , i.type_desc
FROM sys.indexes I
JOIN sys.objects o
ON i.object_id = o.object_id
JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.name NOT like ‘sys%’
AND STATS_DATE(i.object_id,i.index_id) IS NOT NULL
ORDER BY STATS_DATE(i.object_id, i.index_id)

Good Indexes are Not a Mistake

Creating good indexes for your application does not happen by mistake. You need to have a plan for
how you will develop your indexes. Think of developing indexes using an indexing development
lifecycle approach. Using this method gives you the best shot at hitting the mark when it comes to
developing good indexes for your application.

Self Assessment

Fill in the blanks:
4. The notion of building indexes and forgetting about them should not be used as philosophy
when thinking about ...... .
5. The ...... for indexes is just like the design phase for developing code.
6. The missing index information can be exposed using ...... .
7. You should only consider adding those missing indexes that are identified to have a large
number user ...... and ...... .
8. The last phase of the index development lifecycle is ...... .

82 LOVELY PROFESSIONAL UNIVERSITY