Sql Server 索引使用情況及優化的相關 Sql 語句，非常好的SQL語句，記錄于此：
--
Begin Index(索引) 分析優化的相關 Sql
--
返回當前數據庫所有碎片率大于25%的索引
--
運行本語句會掃描很多數據頁面
--
避免在系統負載比較高時運行
--
避免在系統負載比較高時運行
declare
@dbid
int
select
@dbid
=
db_id
()
SELECT
o.name
as
tablename,s.
*
FROM
sys.dm_db_index_physical_stats (
@dbid
,
NULL
,
NULL
,
NULL
,
NULL
) s,sys.objects o
where
avg_fragmentation_in_percent
>
25
and
o.
object_id
=
s.
object_id
order
by
avg_fragmentation_in_percent
desc
GO
--
當前數據庫可能缺少的索引
--
非常好用的 Sql 語句
select
d.
*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
,s.unique_compiles
from
sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where
s.group_handle
=
g.index_group_handle
and
d.index_handle
=
g.index_handle
order
by
s.avg_user_impact
desc
go
--
自動重建或重新組織索引
--
比較好用，慎用，特別是對于在線 DB
--
Ensure a USE <databasename> statement has been executed first.
SET
NOCOUNT
ON
;
DECLARE
@objectid
int
;
DECLARE
@indexid
int
;
DECLARE
@partitioncount
bigint
;
DECLARE
@schemaname
nvarchar
(
130
);
DECLARE
@objectname
nvarchar
(
130
);
DECLARE
@indexname
nvarchar
(
130
);
DECLARE
@partitionnum
bigint
;
DECLARE
@partitions
bigint
;
DECLARE
@frag
float
;
DECLARE
@command
nvarchar
(
4000
);
--
Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
--
and convert object and index IDs to names.
SELECT
object_id
AS
objectid,
index_id
AS
indexid,
partition_number
AS
partitionnum,
avg_fragmentation_in_percent
AS
frag
INTO
#work_to_do
FROM
sys.dm_db_index_physical_stats (
DB_ID
(),
NULL
,
NULL
,
NULL
,
'
LIMITED
'
)
WHERE
avg_fragmentation_in_percent
>
10.0
AND
index_id
>
0
;
--
Declare the cursor for the list of partitions to be processed.
DECLARE
partitions
CURSOR
FOR
SELECT
*
FROM
#work_to_do;
--
Open the cursor.
OPEN
partitions;
--
Loop through the partitions.
WHILE
(
1
=
1
)
BEGIN
;
FETCH
NEXT
FROM
partitions
INTO
@objectid
,
@indexid
,
@partitionnum
,
@frag
;
IF
@@FETCH_STATUS
<
0
BREAK
;
SELECT
@objectname
=
QUOTENAME
(o.name),
@schemaname
=
QUOTENAME
(s.name)
FROM
sys.objects
AS
o
JOIN
sys.schemas
as
s
ON
s.schema_id
=
o.schema_id
WHERE
o.
object_id
=
@objectid
;
SELECT
@indexname
=
QUOTENAME
(name)
FROM
sys.indexes
WHERE
object_id
=
@objectid
AND
index_id
=
@indexid
;
SELECT
@partitioncount
=
count
(
*
)
FROM
sys.partitions
WHERE
object_id
=
@objectid
AND
index_id
=
@indexid
;
--
30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF
@frag
<
30.0
SET
@command
=
N
'
ALTER INDEX
'
+
@indexname
+
N
'
ON
'
+
@schemaname
+
N
'
.
'
+
@objectname
+
N
'
REORGANIZE
'
;
IF
@frag
>=
30.0
SET
@command
=
N
'
ALTER INDEX
'
+
@indexname
+
N
'
ON
'
+
@schemaname
+
N
'
.
'
+
@objectname
+
N
'
REBUILD
'
;
IF
@partitioncount
>
1
SET
@command
=
@command
+
N
'
PARTITION=
'
+
CAST
(
@partitionnum
AS
nvarchar
(
10
));
EXEC
(
@command
);
PRINT
N
'
Executed:
'
+
@command
;
END
;
--
Close and deallocate the cursor.
CLOSE
partitions;
DEALLOCATE
partitions;
--
Drop the temporary table.
DROP
TABLE
#work_to_do;
GO
--
查看當前數據庫索引的使用率
--
非常的有用
SELECT
object_name
(
object_id
)
as
table_name,
(
select
name
from
sys.indexes
where
object_id
=
stats.
object_id
and
index_id
=
stats.index_id
)
as
index_name,
*
FROM
sys.dm_db_index_usage_stats
as
stats
WHERE
database_id
=
DB_ID
()
order
by
table_name
--
指定表的索引使用情況
declare
@table
as
nvarchar
(
100
)
set
@table
=
'
t_name
'
;
SELECT
(
select
name
from
sys.indexes
where
object_id
=
stats.
object_id
and
index_id
=
stats.index_id
)
as
index_name,
*
FROM
sys.dm_db_index_usage_stats
as
stats
where
object_id
=
object_id
(
@table
)
order
by
user_seeks, user_scans, user_lookups
asc
--
End Index 分析優化的相關 Sql
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
