数据库索引维护语句

 

查看数据库索引碎片，Fragmentation 代表碎片的大小

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
INTO #TempFragmentation 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempFragmentation 
SELECT TOP 20 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE s.database_id = DB_ID() 
AND i.name IS NOT NULL 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
ORDER BY [Fragmentation %] DESC' 
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC 
DROP TABLE #TempFragmentation

 

查看那些经常被大量更新，但是却基本不适用的索引项

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
INTO #TempUnusedIndexes 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempUnusedIndexes 
SELECT TOP 20 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE s.database_id = DB_ID() 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
AND s.user_seeks = 0 
AND s.user_scans = 0 
AND s.user_lookups = 0 
AND i.name IS NOT NULL 
ORDER BY s.user_updates DESC' 
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC 
DROP TABLE #TempUnusedIndexes

 

查看未使用的索引DMV脚本如下

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.name AS IndexName 
INTO #TempNeverUsedIndexes 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempNeverUsedIndexes 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.NAME AS IndexName 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id 
AND I.index_id = S.index_id 
AND DATABASE_ID = DB_ID() 
WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 
AND I.name IS NOT NULL 
AND S.object_id IS NULL' 
SELECT * FROM #TempNeverUsedIndexes 
ORDER BY DatbaseName, SchemaName, TableName, IndexName 
DROP TABLE #TempNeverUsedIndexes

 

查看索引使用情况

DBCC SHOWCONTIG('MaterialStockSellInfo')

重建索引

DBCC DBREINDEX('MaterialStockSellInfo')

 

收缩数据库

--DBCC SHRINKDATABASE
--收缩数据库
--USAGE:
dbcc SHRINKDATABASE
(
    { 'database_name' | database_id | 0 }
    [ , target_percent ]
    [ , { NOTRUNCATE | TRUNCATEONLY } ]
)
    [ WITH NO_INFOMSGS ]

示例

DBCC SHRINKDATABASE(db_test,10) --收缩数据库db_test,剩余可用空间为10%

 

收缩文件

--DBCC SHRINKFILE
--收缩指定数据库中特定文件
dbcc SHRINKFILE
(
    { 'file_name' | file_id }
    {
        [ , EMPTYFILE]
        | [ [, target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
    [ WITH NO_INFOMSGS ]

示例

DBCC SHRINKFILE(db_test1,20)--将数据库db_test中的db_test1文件收缩的20MB

DBCC SHRINKFILE(db_test1,EMPTYFILE)--将数据库db_test中的db_test1文件清空

DBCC SHRINKFILE (db_test_Log, 1)--收缩日志文件

 

--1. SHRINKFILE 并不独占数据文件，且可以随时终止，停止之前收缩的工作将被保留。

--2. SHRINKFILE 针对区做收缩，将使用的区前移，将不使用的区删除，但不会合并区，也不合并和删除区中的页，因此当区中空页过多或者页面填充度较低时，SHRINKFILE并不能释放过多的空间。

--3. 如果碎片过多，可以先重建索引后再做SHRINKFILE

 

清除数据库日志文件

USE [master]
GO
ALTER DATABASE DBNAME SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE DBNAME SET RECOVERY SIMPLE
GO
USE DBNAME
GO
DBCC SHRINKFILE (N'logfilename' , 5, TRUNCATEONLY) 
GO
USE [master]
GO
ALTER DATABASE DBNAME SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE DBNAME SET RECOVERY FULL
GO