1 / 11

SQL Server 2005 Ch12

SQL Server 2005 Ch12. Using Transact-SQL to Manage Databases. Managing Index Fragmentation. Index Fragmentation – index structure not optimal Two types of fragmentation

aracely
Download Presentation

SQL Server 2005 Ch12

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. SQL Server 2005 Ch12 Using Transact-SQL to Manage Databases

  2. Managing Index Fragmentation • Index Fragmentation – index structure not optimal • Two types of fragmentation • Internal fragmentation – happens with delete operations – page files holding only a fraction of index rows they would normally hold if full • External fragmentation – happens with insert and update operations – causes page splits which in turn causes physical disordering of information

  3. Managing Index Fragmentation Cont. • Identifying Index Fragmentation • Use the sys.dm_db_index_physical_stats dynamic management function • USE AdventureWorks; • GO • SELECT a.index_id, name, avg_fragmentation_in_percent, avg_page_space_used_in_percent • FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'), • NULL, NULL, NULL) AS a • JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; • GO • External Fragmentation is indicated when avg_fragmentation_in_percent > 10 • Internal Fragmentation is indicated when avg_page_space_used_in_percent < 75

  4. Managing Index Fragmentation Cont. • Fixing Index Fragmentation • Have two ways – ALTER INDEX..REORGANIZE and ALTER INDEX..REBUILD • Reorganize option is less intrusive – defrags leaf level of index • ALTER INDEX ALL ON HumanResources.Employee REORGANIZE; • Rebuild option is more intrusive – physically rebuilds all index pages • ALTER INDEX ALL ON HumanResources.Employee REBUILD;

  5. Managing Index Fragmentation Cont. • Can use the ONLINE option only when • Creating multiple nonclustered indexes • Reorganizing different indexes on the same table • Reorginizing different indexes while rebuilding nonoverlaping indexes on the same table • Use reorganize if avg_page_space_used_in_percent between 75 and 60 or avg_fragmentation_in_percent between 10 and 15 • Use rebuild if avg_page_space_used_in_percent < 60 or avg_fragmentation_in_percent > 15

  6. Managing Statistics • Statistics – used by query optimizer to determine if utilizing an index will be beneficial • Creates a histogram that is based on up to 200 values contained in the column separated by intervals • Automatic statistics generation • Use the AUTO_CREATE_STATISTICS database option set to ON (ON is default) • Manual Statistics Generation • Sp_createstats will create statistics on all columns and tables • CREATE STATISTICS will create statistics on a target table and columns • CREATE STATISTICS statistics_name • ON { table | view } ( column [ ,...n ] ) • [ WITH • [ [ FULLSCAN • | SAMPLE number { PERCENT | ROWS } • | STATS_STREAM = stats_stream ] [ , ] ] • [ NORECOMPUTE ] • ] ;

  7. Managing Statistics Cont. • Manual Statistics Updating • Sp_updatestats will update stats on all generated stats in database • UPDATE STATISTICS will update all stats on table or view • Viewing column statistics information • Sp_autostats displays or changes the automatic UPDATE STATISTICS • Sys.stats catalog view displays a row for each statistic of a tabular object of the type U, V, or TF • Sys.stats_columns catalog view displays a row for each column that is part of sys.stats • STATS_DATE function returns the date that the statistics for a specified index were last updated • DBCC SHOW_STATISTICS statement displays the current distribution statistics for the specifed target on the specified table

  8. Shrinking Files • Large delete operations or one-time data loads might leave database files larger then they need be • Can use database option DATABASEAUTO_SHRINK set to ON to have server automatically shrink files • Show use with discretion as this operation can happen at inopportune times, effecting performance • To manually shrink database use the command DBCC SHRINKDATABASE or DBCC SHRINKFILE commands

  9. Shrinking Files Cont. • DBCC SHRINKDATABASE • ( 'database_name' | database_id | 0 • [ ,target_percent ] • [ , { NOTRUNCATE | TRUNCATEONLY } ] • ) • DBCC SHRINKFILE • ( • { 'file_name' | file_id } • { [ , EMPTYFILE ] • | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ] • } • )

  10. Using DBCC CHECKDB • CHECKDB performs the following integrity checks • DBCC CHECKALLOC • DBCC CHECKTABLE • DBCC CHECKCATALOG • Validates Service Broker data in database • Validates the contents of every indexed view in the database

  11. Using DBCC CHECKDB • DBCC CHECKDB • [ • [ ( 'database_name' | database_id | 0 • [ , NOINDEX • | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] • ) ] • [ WITH • { • [ ALL_ERRORMSGS ] • [ , NO_INFOMSGS ] • [ , TABLOCK ] • [ , ESTIMATEONLY ] • [ , { PHYSICAL_ONLY | DATA_PURITY } ] • } • ] • ]

More Related