Managing Indexes
SQL Server
maintains indexes automatically. However, indexes can become fragmented over
time. The fragmentation can be of two types: internal and external.
Internal
fragmentation occurs when large amounts of data are deleted and pages are less
full. A certain amount of free space on index pages is beneficial as it allows
room for future inserts into these pages without having to split a page.
When the
next logical page of an index is not the next physical page, it is called
external fragmentation. This may impact performance when SQL Server is doing an
ordered scan of all or part of a table, or an index. The access by a range of
values is no longer sequential, limiting the ability of the storage engine to
issue large I/O requests.
Parallel
Index Operations
For
PeopleSoft applications, creating or maintaining indexes on large tables can
benefit tremendously when you execute the index creation or maintenance
operation on parallel CPUs.
SQL Server
2005 provides the ability to use the MAXDOP query hint to manually configure
the number of processors that are used to run the index statement. In prior
versions of SQL Server, the server setting of MAXDOP (and the current workload)
determined the number of processors to be used by the index operation.
The MAXDOP
index option overrides the max degree of parallelism configuration
option for the query specifying this option.
Parallel
index execution and the MAXDOP index option apply to the following Transact-SQL
statements:
• CREATE
INDEX
• ALTER
INDEX REBUILD
• DROP
INDEX (for clustered indexes only)
• ALTER
TABLE ADD (index) CONSTRAINT
• ALTER
TABLE DROP (clustered index) CONSTRAINT19
Note. Parallel index operations are
available only in SQL Server 2005 Enterprise Edition.
Following
is an example of using the MAXDOP index option with an ALTER INDEX statement:
ALTER INDEX
PSALEDGER ON dbo.PS_LEDGER
REBUILD
WITH (MAXDOP = 4);
For
PeopleSoft applications, it is recommended that you set the MAXDOP server
setting to 1. However, during index operations, this setting could be
overridden by using the MAXDOP query hint as shown above, for better
performance and CPU resource utilization.
Index-Related
Dynamic Management Views
SQL Server 2005
provides the ability to query and return server and database state information
to monitor the health of a server instance, diagnose problems, and tune
performance through dynamic management views. The following sections describe
some index-specific dynamic management views that can be used for monitoring
and tuning indexes.
Identify
Frequently Used Indexes
The sys.dm_db_index_usage_stats
dynamic management view returns information on the usage frequency of an
index. The following example query can be used to return information on the
frequency of seeks, scans, and lookups by a user query on all indexes for all
user tables in a specific database:
USE PSFTDB
GO
select
db_name(database_id)as 'DB Name', object_name(isu.object_id) as 'Table
Name'
, si.name
as 'Index Name', user_seeks as 'Seeks', user_scans as 'Scans' , user_
lookups as
'Lookups'
from
sys.dm_db_index_usage_stats isu
inner join
sys.indexes si
on
si.index_id = isu.index_id
and
si.object_id = isu.object_id
inner join
sys.objects so
on
so.object_id = si.object_id
and so.type
= 'U'
The sys.dm_db_index_usage_stats
dynamic management view or the query using it can be used in PeopleSoft
applications to retrieve information on the index usage statistics of the
database. This information can help to evaluate index usage and plan for index
maintenance operations as well. It is important to note that the information in
this dynamic management view is reset or deleted when the SQL Server service is
started.
Identify
Missing Indexes
The SQL
Server 2005 query optimizer has the ability to identify missing indexes for
queries. When the query optimizer generates a query plan, it analyzes the best
indexes for a particular filter condition. If the best indexes do not exist,
the query optimizer generates a query plan based on available indexes, but
stores information about the desired missing indexes.
This
information can be retrieved from the dynamic management views and analyzed to
improve the indexing and query performance.
The dynamic
management views that identify and store the missing index information consist
of the following:
• sys.dm_db_missing_index_group_stats:
Returns summary information about missing index groups, for example, the performance
improvements that could be gained by implementing a specific group of missing
indexes.
• sys.dm_db_missing_index_groups:
Returns information about a specific group of missing indexes, such as the
group identifier and the identifiers of all missing indexes that are contained
in that group.
• sys.dm_db_missing_index_details:
Returns detailed information about a missing index; for example, it returns the
name and identifier of the table where the index is missing, and the columns
and column types that should make up the missing index.
• sys.dm_db_missing_index_columns:
Returns information about the database table columns that are missing an index.
The following example query can be used to identify missing index information
for PeopleSoft applications.
USE PSFTDB
GO
select d.*
,
s.avg_total_user_cost
,
s.avg_user_impact
,
s.last_user_seek
,s.unique_compiles
from
sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups
g
,sys.dm_db_missing_index_details
d
where
s.group_handle = g.index_group_handle
and
d.index_handle = g.index_handle
order by
s.avg_user_impact desc
go
---
suggested index columns & usage
declare
@handle int
select
@handle = d.index_handle
from
sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups
g
,sys.dm_db_missing_index_details
d
where
s.group_handle = g.index_group_handle
and
d.index_handle = g.index_handle
select *
from
sys.dm_db_missing_index_columns(@handle)
order by
column_id
It is
highly recommended for PeopleSoft applications that you do a thorough analysis
of the missing index data before adding any indexes. Adding indexes may improve
query performance; however, adding indexes may add overhead for update and
insert operations.
Note. Use only PeopleTools to add indexes.
Note. The information in this dynamic
management view is reset or deleted when SQL Server service is started.
Identify
Indexes Not Used to a Point in Time
Indexes
that are created in the database, but have not been used by any query until a
point in time can be identified with the sys.dm_db_index_usage_stats dynamic
management view.
The
following example query identifies unused indexes:
use PSFTDB
go
select
object_name(i.object_id), i.name
from
sys.indexes i, sys.objects o
where
i.index_id NOT IN (select s.index_id
from
sys.dm_db_index_usage_stats s
where
s.object_id=i.object_id and
i.index_id=s.index_id
and
database_id
= db_id('PSFTDB') )
and o.type
= 'U'
and
o.object_id = i.object_id
order by
object_name(i.object_id) asc
go
For
PeopleSoft applications, it is important to understand that some indexes could
be used quite infrequently; however, they still could be quite critical for
performance of some specific functionality. For example, a batch process could
run monthly, quarterly, or even annually. The index identified by the previous
example query may never be used, but a batch process that is scheduled to be
run could be using it. Deleting such an index could have adverse effects on
performance for scheduled (but unrun) batch processes. Though it may lower
overhead to delete indexes that are never used, thorough analysis should be
made before deleting any index.
Note. The dynamic management view used to
identify the unused index information gets reset or information in it is
deleted when SQL Server is restarted. So at best, the information revealed by
the previous query is from the last known SQL Server restart to the point in
time the dynamic management view query was executed.
Detecting Fragmentation
In SQL
Server 2005, fragmentation can be detected by using the index-related dynamic
management view sys.dm_db_index_ physical_stats. The sys.dm_db_index_physical_stats
dynamic management function replaces the DBCC SHOWCONTIG statement in SQL
Server 2000.
The
algorithm for calculating fragmentation is more precise in SQL Server 2005 than
in SQL Server 2000. As a result, the fragmentation values will appear higher.
The
fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent
column. For heaps, the value represents the extent fragmentation of the
heap. For indexes, the value represents the logical fragmentation of the index.
Unlike DBCC
SHOWCONTIG, the fragmentation calculation algorithms in both cases consider
storage that spans multiple files and, therefore, are more accurate.
The
following example query demonstrates how the dynamic management view returns
fragmentation information:
USE PSFTDB
GO
SELECT
database_id,object_id, index_id, index_type_desc, index_depth, index_
level,
avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats
(DB_ID(N'PSFTDB'),
OBJECT_ID(N'PS_LEDGER'), NULL, NULL , NULL);
GO
For
PeopleSoft applications, the value for avg_fragmentation_in_percent should
ideally be as close to zero as possible for maximum performance. However,
values from 0 percent through 10 percent may be acceptable.
Reducing Fragmentation
In SQL
Server 2005, there are three ways to reduce fragmentation:
• Drop and
re-create the index
•
Reorganize the index
• Rebuild
the index
The CREATE
INDEX .. DROP EXISTING = ON statement can be used to drop and re-create the
index.
The
following example demonstrates using the CREATE INDEX statement to drop and
re-create an index:
CREATE
CLUSTERED INDEX PS_LEDGER
ON
PS_LEDGER(BUSINESS_UNIT, OPERATING_UNIT, FISCAL_YEAR, ACCOUNTING_
PERIOD,
LEDGER, ACCOUNT, ALTACCT, DEPTID, PRODUCT, PROJECT_ID, AFFILIATE,
CURRENCY_CD,
STATISTICS_CODE, FUND_CODE, CLASS_FLD, MSSCONCATCOL)
WITH
(DROP_EXISTING = ON);
GO
Use the
DROP_EXISTING option to change the characteristics of an index or to rebuild
indexes without having to drop the index and re-create it. The benefit of using
the DROP_EXISTING option is that you can modify indexes created with PRIMARY
KEY or UNIQUE constraints. This option performs the following:
• Removes
all fragmentation.
•
Reestablishes FILLFACTOR/PAD_INDEX.
•
Recalculates index statistics.
The second
way to reduce fragmentation is to reorganize the index. To reorganize the
index, use the ALTER INDEX REORGANIZE statement. It is the replacement for DBCC
INDEXDEFRAG, and it will reorder the leaf-level pages of the index in a logical
order. Use this option to perform online logical index defragmentation.
It is
possible that an interruption may happen to the defragmentation process. The
operation can be interrupted without losing work already completed.
The
drawback in this method is that it does not do as good a job of reorganizing
the data as an index rebuild operation and it does not update statistics.
The
following example demonstrates the ALTER INDEX REORGANIZE statement:
ALTER INDEX
PS_LEDGER ON PS_LEDGER
REORGANIZE;
The third
way to reduce fragmentation is to rebuild the index. To do so, use the ALTER
INDEX REBUILD statement. It is the replacement for DBCC DBREINDEX and it will
rebuild the index online or offline. Use this option to:
• Remove
heavy defragmentation.
• Rebuild
the physical index online or offline.
The ALTER
INDEX REBUILD statement requires a statistics update.
The
following example demonstrates the ALTER INDEX REBUILD statement:
ALTER INDEX
PS_LEDGER ON PS_LEDGER
REBUILD;
In general,
when the avg_fragmentation_in_percent value is between 5 and 30 percent,
the ALTER INDEX REORGANIZE statement can be used to remove fragmentation. For
heavy fragmentation (more than 30 percent) the ALTER INDEX REBUILD or CREATE
INDEX DROP EXISTING statements can be used.
Use the
following guidelines to decide between the two options.
* A
nonclustered index can be converted to a clustered index type by specifying
CLUSTERED in the index definition. This operation must be performed with the
ONLINE option set to OFF. Conversion from clustered to nonclustered is not
supported regardless of the ONLINE setting.
** If the
index is re-created by using the same name, columns and sort order, the sort
operation may be omitted. The rebuild operation checks that the rows are sorted
while building the index.
Fragmentation
alone is not a sufficient reason to reorganize or rebuild an index. The main
effect of fragmentation is that it slows down page read-ahead throughput during
index scans. This causes slower response times. If the query workload on a fragmented
table or index does not involve scans, because the workload is primarily
singleton lookups, removing fragmentation may have no effect on performance. It
is also not recommended to remove fragmentation for fragmentation of 5 percent
or less. Depending on the index size, the cost may outweigh the benefit.
Online
Index Reorganization
For most
PeopleSoft applications, high application uptime and availability are expected.
Database maintenance operations can sometimes affect the uptime requirement, because
the database may have to be taken offline for database maintenance.
SQL Server
2005 provides capabilities for database maintenance, specifically index
reorganizations to be done online without affecting application uptime and
availability.
Online Operations
In SQL
Server 2005 you can create, rebuild, or drop indexes online. The ONLINE option
allows concurrent user access to the underlying table or clustered index data
and any associated non-clustered indexes during these index operations. When the
indexes are being built with the ONLINE option, concurrent user access to query
and modify the underlying table data is permitted.
The ONLINE
option is available in the following Transact-SQL statements.
• CREATE
INDEX
• ALTER
INDEX
• DROP
INDEX
• ALTER TABLE
(To add or drop UNIQUE or PRIMARY KEY constraints with CLUSTERED index option)
The
following example demonstrates an online index rebuild operation:
ALTER INDEX
PS_LEDGER ON PS_LEDGER
REBUILD
WITH (ONLINE = ON);
In the
following example, all indexes on the PS_LEDGER table are rebuilt online.
ALTER INDEX
ALL ON PS_LEDGER
REBUILD
WITH (ONLINE = ON);
When you
perform online index operations, the following guidelines apply:
• Clustered
indexes must be created, rebuilt, or dropped offline when the underlying table
contains large object (LOB) datatypes: image, ntext, text, varchar(max),
nvarchar(max), varbinary(max), and xml.
•
Non-unique non-clustered indexes can be created online when the table contains
LOB data types, but none of these columns are used in the index definition as
either key or non-key (included) columns. Non-clustered indexes defined with
LOB data type columns must be created or rebuilt offline.
• Indexes
on local temp tables cannot be created, rebuilt, or dropped online. This
restriction does not apply to indexes on global temp tables.
• The
underlying table cannot be modified, truncated, or dropped while an online index
operation is in process.
You can
perform concurrent online index operations on the same table only when doing
the following:
• Creating
multiple non-clustered indexes.
•
Reorganizing different indexes on the same table.
•
Reorganizing different indexes while rebuilding non-overlapping indexes on the
same table.
All other
online index operations performed at the same time fail. For example, you
cannot rebuild two or more indexes on the same table concurrently, or create a
new index while rebuilding an existing index on the same table.
http://www.mybasicknowledge.com/2012/09/peoplesoft-database-maintenance-part2.html
http://www.mybasicknowledge.com/2012/09/peoplesoft-database-maintenance-part2.html
