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

Loading in 2 Seconds...

play fullscreen
1 / 11

SQL Server 2005 Ch12 - PowerPoint PPT Presentation


  • 114 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Server 2005 Ch12' - aracely


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql server 2005 ch12

SQL Server 2005 Ch12

Using Transact-SQL to Manage Databases

managing index fragmentation
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
managing index fragmentation cont
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
managing index fragmentation cont1
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;
managing index fragmentation cont2
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
managing statistics
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 ]
      • ] ;
managing statistics cont
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
shrinking files
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
shrinking files cont
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 } ] ]
  • }
  • )
using dbcc checkdb
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
using dbcc checkdb1
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 } ]
  • }
  • ]
  • ]