1 / 11

Managing and Maintaining Indexes and Statistics

Managing and Maintaining 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. How to Check Index Fragmentation.

karim
Télécharger la présentation

Managing and Maintaining Indexes and Statistics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing and Maintaining Indexes and Statistics

  2. 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

  3. 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.indexesi 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 > 0

  4. 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 consequential

  5. 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-tree

  6. Reorganize an Index T-SQL USE [AdventureWorks2012] GO ALTER INDEX [IX_SalesOrderHeader_OrderDate] on [Sales].[SalesOrderHeader] REORGANIZE Always an online operation

  7. 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 removed

  8. 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 operation

  9. 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_updates FROM sys.dm_db_index_usage_statsius INNER JOIN sys.indexesi ON ius.object_id= i.object_id and ius.index_id= i.index_id WHERE DB_NAME(ius.database_id) = ‘ ‘ ‘AdventureWorks2012'

  10. 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-SQL

  11. 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]

More Related