生成整理碎片脚本
1 生成整理碎片脚本 2013 -12 - 19
2 --Rebuild the following indexes in the AdventureWorks database
3 --检查当前数据库的所有索引,然后根据查询出来的碎片比例,然后决定是否要对各个索引生成重建索引脚本
4 --使用ctrl+t 拷贝输出结果的重建索引脚本,然后粘贴到新建查询窗口执行重建索引
5 --对于碎片不对的索引,在输出结果里不会有该索引的重建索引脚本
6 USE [GPOSDB]
7 GO
8
9 --Table to hold results
10 DECLARE @tablevar TABLE
11 (
12 lngid INT IDENTITY( 1 , 1 ) ,
13 objectid INT ,
14 index_id INT
15 )
16
17 INSERT INTO @tablevar
18 ( objectid ,
19 index_id
20 )
21 SELECT [object_id] ,
22 index_id
23 FROM sys . dm_db_index_physical_stats( DB_ID ('GPOSDB' ), NULL -- NULL to view all tables
24 , NULL -- NULL to view all indexes; otherwise, input index number
25 , NULL -- NULL to view all partitions of an index
26 , 'DETAILED' ) --We want all information
27 WHERE ( ( avg_fragmentation_in_percent > 15 ) -- Logical fragmentation
28 OR ( avg_page_space_used_in_percent < 60 )
29 ) --Page density
30 AND page_count > 8 -- We do not want indexes less than 1 extent in size
31 AND index_id NOT IN ( 0 )
32 --Only clustered and nonclustered indexes
33
34 SELECT 'ALTER INDEX ' + ind .[name] + ' ON ' + sc. [name] + '.'
35 + OBJECT_NAME ( objectid) + ' REBUILD'
36 FROM @tablevar tv
37 INNER JOIN sys. indexes ind ON tv .objectid = ind .[object_id]
38 AND tv . index_id = ind . index_id
39 INNER JOIN sys. objects ob ON tv .objectid = ob .[object_id]
40 INNER JOIN sys. schemas sc ON sc .schema_id = ob .schema_id
41
42
43 ( 2 行受影响 )
44
45 ALTER INDEX PK_ST_OperationLOG ON dbo . ST_OperationLOG REBUILD
46 ALTER INDEX PK_CT_Append ON dbo . CT_Append REBUILD
47
48 ( 2 行受影响 )
49
50 ------------------------------------------------------------------
51 --Reorganize the following indexes in the AdventureWorks database
52 USE AdventureWorks
53 GO
54
55 SELECT OBJECT_NAME ( [object_id]) AS 'Table Name' ,
56 index_id AS 'Index ID'
57 FROM sys . dm_db_index_physical_stats( DB_ID ('AdventureWorks' ), NULL -- NULL to view all tables
58 , NULL -- NULL to view all indexes; otherwise, input index number
59 , NULL -- NULL to view all partitions of an index
60 , 'DETAILED' ) --We want all information
61 WHERE ( ( avg_fragmentation_in_percent > 10
62 AND avg_fragmentation_in_percent < 15
63 ) -- Logical fragmentation
64 OR ( avg_page_space_used_in_percent < 75
65 AND avg_page_space_used_in_percent > 60
66 )
67 ) --Page density
68 AND page_count > 8 -- We do not want indexes less than 1 extent in size
69 AND index_id NOT IN ( 0 )
70 --Only clustered and nonclustered indexes
71
72 --Rebuild the following indexes in the AdventureWorks database
73 USE AdventureWorks
74 GO
75
76 SELECT OBJECT_NAME ( [object_id]) AS 'Table Name' ,
77 index_id AS 'Index ID'
78 FROM sys . dm_db_index_physical_stats( DB_ID ('AdventureWorks' ), NULL -- NULL to view all tables
79 , NULL -- NULL to view all indexes; otherwise, input index number
80 , NULL -- NULL to view all partitions of an index
81 , 'DETAILED' ) --We want all information
82 WHERE ( ( avg_fragmentation_in_percent > 15 ) -- Logical fragmentation
83 OR ( avg_page_space_used_in_percent < 60 )
84 ) --Page density
85 AND page_count > 8 -- We do not want indexes less than 1 extent in size
86 AND index_id NOT IN ( 0 ) --Only clustered and nonclustered indexes
87 -------------------------------------------------------------------------
88 SELECT page_latch_wait_count --page latch counts
89 ,
90 page_latch_wait_in_ms --page latch wait times
91 ,
92 row_lock_wait_in_ms --row lock wait times
93 ,
94 page_lock_wait_in_ms --page lock wait times
95 ,
96 row_lock_count --row lock counts
97 ,
98 page_lock_count --page lock counts
99 ,
100 page_io_latch_wait_count --I/O wait counts
101 ,
102 page_io_latch_wait_in_ms --I/O wait times
103 FROM sys . dm_db_index_operational_stats( DB_ID ('AdventureWorks' ),
104 OBJECT_ID ('HumanResources.Employee' ),
105 NULL -- NULL to view all indexes; otherwise, input index number
106 , NULL -- NULL to view all partitions of an index
107 )
