Download
sql server 2005 ch12 n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2005 Ch12 PowerPoint Presentation
Download Presentation
SQL Server 2005 Ch12

SQL Server 2005 Ch12

129 Vues Download Presentation
Télécharger la présentation

SQL Server 2005 Ch12

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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 } ] • } • ] • ]