SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

SQL Server 2005 Ch12

aracely avatar
aracely
11 diapositives · 293 Views · Jan 13, 2014

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

SQL Server 2005 Ch12 - slide 1
01 / 11SQL Server 2005 Ch12
1 / 17

Télécharger la présentation

SQL Server 2005 Ch12
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

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