Indexes and Statistics Table Fragmentation Causes 1 Massive Updates or deletes 2 Frequent Page Splitting 3 Disk space contention 4 Insert Statements if Clustered Key is not a sequential key ID: 315971
Download Presentation The PPT/PDF document "Managing and Maintaining" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Managing and Maintaining
Indexes and StatisticsSlide2
Table Fragmentation Causes
1 Massive
Updates
or deletes
2 Frequent
Page Splitting
3 Disk
space contention
4 Insert
Statements if Clustered
Key is not a sequential keySlide3
How to Check Index Fragmentation
use
AdventureWorks2012
GO
Select
DB_NAME(
ips.database_id
)
DBname
,
OBJECT_NAME(
ips.object_id
)
ObjName
,
i.name
InxName
,
ips.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats
(
db_id
(‘AdventureWorks2012'),
default, default, default, default)
ips
INNER JOIN
sys.indexes
i
ON
ips.index_id
=
i.index_id
AND
ips.object_id
=
i.object_id
WHERE
ips.object_id
> 99 AND
ips.avg_fragmentation_in_percent
>= 10 AND
ips.index_id
> 0Slide4
REBUILD OR REORGANIZE?
It is suggested that if fragmentation is between 10% and 30% a Reorganization might be called for and beneficial. If fragmentation is > 30% most likely a rebuild is necessary. If fragmentation is < 10%, it probably is not too consequentialSlide5
Methods to Reorganize an Index
SQL Server Manager allows 3 Methods to Reorganize an Index
SQL Server Management Studio (SSMS) – on the fly
T-SQL
A Maintenance Plan
During Index Reorganization the following
Activities take place
Index pages of the index are compacted
The leaf level is reordered to match the
logical order of the leaf nodes in the
index B-treeSlide6
Reorganize an Index
T-SQL
USE [AdventureWorks2012]
GO
ALTER INDEX [
IX_SalesOrderHeader_OrderDate
] on
[Sales].[
SalesOrderHeader
] REORGANIZE
Always an online operationSlide7
Methods to Rebuild an Index
SQL Server Manager allows 2 Methods to Rebuild an Index
SQL Server Management Studio (SSMS) – on the fly
T-SQL
During Rebuilding an Index the following activities take place
Disk space is reclaimed because the pages are compacted
The index rows are reordered
Fragmentation is removedSlide8
Rebuild
an Index
T-SQL
USE [AdventureWorks2012]
GO
ALTER INDEX [
IX_SalesOrderHeader_OrderDate
]
On [Sales
].[
SalesOrderHeader
]
REBUILD WITH (ONLINE = ON) - only with SQL Enterprise 2012
Always an
offline
operationSlide9
How to Check Index Usage
USE
AdventureWorks2012
SELECT
DB_NAME(
ius.database_id
)
DBName
,
OBJECT_NAME(ius.object_id
)
ObjName
,
i.name,
ius.user_seeks
,
ius.user_scans, ius.user_lookups, ius.user_updatesFROM sys.dm_db_index_usage_stats ius
INNER JOIN
sys.indexes
i
ON
ius.object_id
=
i.object_id
and
ius.index_id
=
i.index_id
WHERE
DB_NAME(
ius.database_id
) =
‘ ‘ ‘AdventureWorks2012'Slide10
DATABASE STATISTICS
DATABASE STATISTICS are carried both at the Database Level and also at the Table Level. Typically maintained automatically, they can also be maintained manually
Database Statistics are updated either thru SSMS or thru T-SQLSlide11
UPDATING DATABASE STATISTICS T-SQL
2 Different Programs are available to update Database Statistics
UPDATE STATISTICS
Sp_updatestats
Here is an example of both programs in use
USE AdventureWorks2012
GO
--Update all statistics within the AdventureWorks2012 database
EXEC
sp_updatestats
GO
--update all statistics for a given index on the specified table
UPDATE STATISTICS [Sales].[
SalesOrderHeader
] [
IX_SalesOrderHeader_OrderDate
]