วิธี การ Maintenance SQL Server 2005 | Maintenance SQL Server 2008 | SQL 2005 Express | Sql management Studio
How to SQL Server Maintenance
SQL Server statistics that are out of date and tables and indexes that are significantly
fragmented adversely affect system performance. You can monitor their condition and take
steps to enhance their performance.Statistical InformationSQL Server uses statistical information about the distribution of values in a column to
determine the optimal strategy for evaluating a query. Distribution statistics help the
system estimate how efficient an index would be in retrieving data associated with a key
value or range specified in the query.
As the data in a column changes, index and column statistics can become out-of-date,
affecting query performance. The statistics should be refreshed anytime significant
numbers of changes to keys occur in the index.
You can use the dbcc show_statistics statement to generate a report on the distribution
statistics for an index. The statements in this section use the following syntax:
dbcc show_statistics (table_name, index_name)
In SQL Query Analyzer, with the application database selected as the current database,
the following statements show the current statistics and the last time statistics were
updated for primary keys in major tables:
dbcc show_statistics (item, pk_item)
dbcc show_statistics (customer, pk_customer)
dbcc show_statistics (ledger, pk_ledger)
dbcc show_statistics (matltran, pk_matltran)
dbcc show_statistics (matltran_amt, pk_matltran_amt)
dbcc show_statistics (journal, pk_journal)
dbcc show_statistics (ledger_all, pk_ledger_all)
The results indicate the selectivity of an index (the lower the density returned, the higher
the selectivity) and provide the basis for determining whether an index is useful in
optimizing queries.
See SQL Server Help for dbcc show_statistics and other DBCC (Database Console
Commands) statements.
Update Statistics Use the Transact-SQL statement UPDATE STATISTICS if
• A process suddenly takes much longer than usual to run
• There is a significant change in the key values in an index
• A large amount of data in an indexed column has been added, changed, or removed,
or the table has been truncated using the TRUNCATE TABLE statement and then
repopulated.
We recommend that you update statistics nightly or weekly.
This example updates the statistics for all indexes on the customer table.
UPDATE STATISTICS customer
To update statistics for all tables in the in the current database, you can run the SQL Server
stored procedure sp_updatestats, which uses UPDATE STATISTICS:
EXEC sp_updatestats
For more information, see SQL Server Help for UPDATE STATISTICS and
sp_updatestats
Fragmentation Information
Fragmentation occurs through data modifications (INSERT, UPDATE, and DELETE). For
queries that scan part or all of a table, this fragmentation can cause additional pages to be
read, adversely affecting performance.
You can use the Transact-SQL DBCC SHOWCONTIG statement to display fragmentation
information for the data and indexes of a specified table.
To determine whether a table is heavily fragmented, use the following syntax in SQL Query
Analyzer, with the application database selected as the current database:
DBCC SHOWCONTIG (table_name)
In the result set, the value of Logical Scan Fragmentation gives an indication of the table's
fragmentation level. The value should be close to zero, although a value from 0% through
10% may be acceptable.
To show in a grid an abbreviated result set for every index on every table, use:
DBCC SHOWCONTIG WITH TABLERESULTS, FAST
To show the full result set for every index on every table, use:
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
For more information, see SQL Server Help for DBCC SHOWCONTIG.
Defragment IndexesWe recommend that you rebuild your table indexes on a weekly basis if possible.
The Transact-SQL DBCC INDEXDEFRAG statement defragments indexes of a specified
table, improving index-scanning performance.
DBCC INDEXDEFRAG (database_name, table_name, index_name)
The script below uses DBCC INDEXDEFRAG and DBCC SHOWCONTIG to defragment
all indexes in a database fragmented above a declared threshold of 30 percent. The script
is from Microsoft’s Transact-SQL Reference, copyright © 2004 Microsoft Corporation, One
Microsoft Way, Redmond, Washington 98052-6399 U.S.A.; all rights reserved.
Note that you must specify a database before you run the script.
/*Perform a 'USE ' to select the database in which to run
the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tablesINTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GOวิธี Maintenance SQL Server 2005
