合 MSSQL常用SQL语句

发布日期 2022年11月16日 · 已更新 2024年4月26日

0 170 1

👉 本文共约8484个字，系统预计阅读时间或需32分钟。

SQL Server对象查询（只查询当前数据库）
检查数据库完整性
数据库重命名、修改恢复模式、修改用户模式
数据库重命名
设置数据库为完整恢复模式
只允许一个用户访问数据库
只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库
多用户模式
移动文件
扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称
添加文件组
添加数据文件
添加日志文件
修改数据文件的大小,增长大小,最大大小
修改数据文件或日志文件的逻辑名称
设置默认文件组、只读文件组
收缩数据库、收缩文件
重新生成索引
查看实例名
查看数据库属性
查看数据库恢复模式
查看日志空间
系统函数
查看数据文件
查询缓存中具体的执行计划，及对应的SQL
查询缓存的各类执行计划，及分别占了多少内存
查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
查询SqlServer总体的内存使用情况
查询SQLSERVER内存使用情况
看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据
查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）–全局
查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
查询会话中有多少个worker在等待
查看当前数据库用户连接
查看数据库大小
查看数据库可用大小，已使用大小
SQL优化相关、执行时间
查看索引碎片
查看指定表的索引信息
重新生成或重新组织索引
查看索引使用情况
统计信息
自动索引和统计信息管理
查看缺少的索引
ALTER DATABASE 语法
A. 向数据库中添加文件
B. 向数据库中添加由两个文件组成的文件组
C. 向数据库中添加两个日志文件
D. 从数据库中删除文件
E. 修改文件
F. 将文件移至新位置
G. 将 tempdb 移至新位置
H. 使文件组成为默认文件组
I. 使用 ALTER DATABASE 添加文件组
J. 更改文件组，以便当文件组中的某个文件达到自动增长阈值时，文件组中的所有文件都会增长
参考

SQL Server对象查询（只查询当前数据库）

select 
case when xtype='U' then '表' 
when xtype='P' then '存储过程' 
when xtype='FN' then '标量值函数' 
when xtype='IF' then '表值函数'
when xtype='V' then '视图'
when xtype='TR' then '触发器'
when xtype='SN' then '同义词' end as xtype,
count(*) cnt 
from sysobjects 
where xtype in('P','FN','IF','TR','TR','U','V') and uid=1 
and category=0
group by xtype
order by xtype;

select

case when xtype='U' then '表'

when xtype='P' then '存储过程'

when xtype='FN' then '标量值函数'

when xtype='IF' then '表值函数'

when xtype='V' then '视图'

when xtype='TR' then '触发器'

when xtype='SN' then '同义词' end as xtype,

count(*) cnt

from sysobjects

where xtype in('P','FN','IF','TR','TR','U','V') and uid=1

and category=0

group by xtype

order by xtype;

检查数据库完整性

dbcc checkdb(lhrdb)

1	dbcc checkdb(lhrdb)

通过加tablock提高速度

dbcc checkdb(test) with tablock

1	dbcc checkdb(test) with tablock

数据库重命名、修改恢复模式、修改用户模式

数据库重命名

ALTER DATABASE WC  MODIFY NAME = test

1	ALTER DATABASE WC MODIFY NAME = test

设置数据库为完整恢复模式

alter database test set recovery full

1	alter database test set recovery full

只允许一个用户访问数据库

alter database test  
set single_user   
with rollback after 10 seconds --指定多少秒后回滚事务

alter database test

set single_user

with rollback after 10 seconds --指定多少秒后回滚事务

只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库

alter database wc  
set restricted_user   
with rollback immediate        --立即回滚事务

alter database wc

set restricted_user

with rollback immediate --立即回滚事务

多用户模式

alter database wc  
set multi_user  
with no_wait       --不等待立即改变，如不能立即完成，那么会导致执行错误

alter database wc

set multi_user

with no_wait --不等待立即改变，如不能立即完成，那么会导致执行错误

移动文件

–由于在SQL Server中文件组、文件不能离线
–所以必须把整个数据库设置为离线

checkpoint
go

ALTER DATABASE WC
SET OFFLINE
go

checkpoint

ALTER DATABASE WC

SET OFFLINE

–修改文件名称

ALTER DATABASE WC
MODIFY FILE
(
    NAME = WC_fg8,
    FILENAME = 'D:\WC\WC_FG8.NDF'
)
go

ALTER DATABASE WC

MODIFY FILE

(

NAME = WC_fg8,

FILENAME = 'D:\WC\WC_FG8.NDF'

)

–把原来的文件复制到新的位置：‘D:\WC\WC_FG8.NDF’

–设置数据库在线

ALTER DATABASE WC
SET ONLINE

1 2	ALTER DATABASE WC SET ONLINE

扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称

添加文件组

ALTER DATABASE test
ADD FILEGROUP WC_FG8

1 2	ALTER DATABASE test ADD FILEGROUP WC_FG8

添加数据文件

ALTER DATABASE test
ADD FILE
(
    NAME = WC_FG8,
    FILENAME = 'D:\WC_FG8.ndf',
    SIZE = 1mb,
    MAXSIZE = 10mb,
    FILEGROWTH = 1mb
)
TO FILEGROUP WC_FG8

ALTER DATABASE test

ADD FILE

(

NAME = WC_FG8,

FILENAME = 'D:\WC_FG8.ndf',

SIZE = 1mb,

MAXSIZE = 10mb,

FILEGROWTH = 1mb

)

TO FILEGROUP WC_FG8

添加日志文件

ALTER DATABASE test
ADD LOG FILE
(
    NAME = WC_LOG3,
    FILENAME = 'D:\WC_FG3.LDF',
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 100KB
)

ALTER DATABASE test

ADD LOG FILE

(

NAME = WC_LOG3,

FILENAME = 'D:\WC_FG3.LDF',

SIZE = 1MB,

MAXSIZE = 10MB,

FILEGROWTH = 100KB

)

修改数据文件的大小,增长大小,最大大小

ALTER DATABASE test
MODIFY FILE
(
    NAME = 'WC_FG8',
    SIZE = 2MB,      --必须大于之前的大小,否则报错
    MAXSIZE= 8MB,
    FILEGROWTH = 10%
)

ALTER DATABASE test

MODIFY FILE

(

NAME = 'WC_FG8',

SIZE = 2MB, --必须大于之前的大小,否则报错

MAXSIZE= 8MB,

FILEGROWTH = 10%

)

修改数据文件或日志文件的逻辑名称

ALTER DATABASE test
MODIFY FILE
(
    NAME = WC_LOG3,
    NEWNAME = WC_FG33
)

ALTER DATABASE test

MODIFY FILE

(

NAME = WC_LOG3,

NEWNAME = WC_FG33

)

设置默认文件组、只读文件组

–设置默认文件组

ALTER DATABASE WC
MODIFY FILEGROUP WC_FG8 DEFAULT

1 2	ALTER DATABASE WC MODIFY FILEGROUP WC_FG8 DEFAULT

–设为只读文件组
–如果文件已经是某个属性，不能再次设置相同属性

ALTER DATABASE WC
MODIFY FILEGROUP WC_FG8 READ_WRITE

1 2	ALTER DATABASE WC MODIFY FILEGROUP WC_FG8 READ_WRITE

收缩数据库、收缩文件

–收缩数据库

DBCC SHRINKDATABASE('test',    --要收缩的数据库名称或数据库ID  
                    10         --收缩后，数据库文件中空间空间占用的百分比  
                    )  

DBCC SHRINKDATABASE('test',    --要收缩的数据库名称或数据库ID  
                    10,        --收缩后，数据库文件中空闲空间占用的百分比  
                    NOTRUNCATE --在收缩时，通过数据移动来腾出自由空间  
                    )  

DBCC SHRINKDATABASE('test',      --要收缩的数据库名称或数据库ID  
                    10,          --收缩后，数据库文件中空间空间占用的百分比  
                    TRUNCATEONLY --在收缩时，只是把文件尾部的空闲空间释放  
                    )

DBCC SHRINKDATABASE('test', --要收缩的数据库名称或数据库ID

10 --收缩后，数据库文件中空间空间占用的百分比

)

DBCC SHRINKDATABASE('test', --要收缩的数据库名称或数据库ID

10, --收缩后，数据库文件中空闲空间占用的百分比

NOTRUNCATE --在收缩时，通过数据移动来腾出自由空间

)

DBCC SHRINKDATABASE('test', --要收缩的数据库名称或数据库ID

10, --收缩后，数据库文件中空间空间占用的百分比

TRUNCATEONLY --在收缩时，只是把文件尾部的空闲空间释放

)

–收缩文件

DBCC SHRINKFILE(wc_fg8,   --要收缩的数据文件逻辑名称  
                7         --要收缩的目标大小，以MB为单位  
                ) 

DBCC SHRINKFILE(wc_fg8,   --要收缩的数据文件逻辑名称  
                EMPTYFILE --清空文件，清空文件后，才可以删除文件  
                )

DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称

7 --要收缩的目标大小，以MB为单位

)

DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称

EMPTYFILE --清空文件，清空文件后，才可以删除文件

)

重新生成索引

ALTER INDEX [idx_temp_lock_id] ON [dbo].[temp_lock] 
REBUILD PARTITION = ALL 
WITH ( PAD_INDEX  = OFF, 
       STATISTICS_NORECOMPUTE  = OFF, 
       ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON, 
       ONLINE = OFF, 
       SORT_IN_TEMPDB = OFF )

ALTER INDEX [idx_temp_lock_id] ON [dbo].[temp_lock]

REBUILD PARTITION = ALL

WITH ( PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON,

ONLINE = OFF,

SORT_IN_TEMPDB = OFF )

查看实例名

SELECT @@SERVICENAME AS InstantName;

SELECT ISNULL(SERVERPROPERTY('InstanceName'),'MSSQLSERVER') AS InstanceName;

SELECT @@SERVICENAME AS InstantName;

SELECT ISNULL(SERVERPROPERTY('InstanceName'),'MSSQLSERVER') AS InstanceName;

查看数据库属性

sp_helpdb master

1	sp_helpdb master

查看数据库恢复模式

select databasepropertyex('lhrdb','recovery')

1	select databasepropertyex('lhrdb','recovery')

查看日志空间

dbcc sqlperf(logspace)

1	dbcc sqlperf(logspace)

系统函数

app_name()函数返回当前从SQL Server请求数据的应用程序名称。
SYSDATETIME()函数返回SQL Server上的当前时间。
host_name()函数确定是哪台工作站正在连接到SQL Server。
system_user函数提供正在连接的用户的登陆名
db_name()告诉你连接是哪个数据库

如：select app_name()

查看数据文件

    SELECT    Name                                     AS DataBaseName ,
              Physical_Name                            AS PhysicalName ,
              type_desc                                AS FileTypeDesc ,
              State_Desc                               AS StateDesc    ,
              (( size * 8.0 ) / 1024 / 1024 )          AS [Size(GB)]   ,
              CASE WHEN max_size = 0  THEN N'不允许增长'
                   WHEN max_size = -1 THEN N'自动增长'
                   ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
              END AS MaxSize ,
              CASE WHEN is_percent_growth = 1
                   THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
                   ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
              END AS Growth ,
              Is_Read_Only                             AS IsReadOnly      ,
              Is_Percent_Growth                        AS IsPercentGrowth ,
              CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)]
    FROM      sys.database_files ;

SELECT Name AS DataBaseName ,

Physical_Name AS PhysicalName ,

type_desc AS FileTypeDesc ,

State_Desc AS StateDesc ,

(( size * 8.0 ) / 1024 / 1024 ) AS [Size(GB)] ,

CASE WHEN max_size = 0 THEN N'不允许增长'

WHEN max_size = -1 THEN N'自动增长'

ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'

END AS MaxSize ,

CASE WHEN is_percent_growth = 1

THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'

ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'

END AS Growth ,

Is_Read_Only AS IsReadOnly ,

Is_Percent_Growth AS IsPercentGrowth ,

CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)]

FROM sys.database_files ;

查询缓存中具体的执行计划，及对应的SQL

–查看缓存中具体的执行计划，及对应的SQL语句

select 
usecounts,
refcounts,
size_in_bytes,
cacheobjtype,
objtype,
TEXT 
from sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC;

select

usecounts,

refcounts,

size_in_bytes,

cacheobjtype,

objtype,

TEXT

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

ORDER BY objtype DESC;

查询缓存的各类执行计划，及分别占了多少内存

–查看缓存的各类执行计划，及分别占了多少内存

select cacheobjtype,
objtype,
sum(cast(size_in_bytes as bigint))/1024 as size_in_kb,
count(bucketid) as cache_count
from sys.dm_exec_cached_plans
group by cacheobjtype,objtype
order by cacheobjtype,objtype

select cacheobjtype,

objtype,

sum(cast(size_in_bytes as bigint))/1024 as size_in_kb,

count(bucketid) as cache_count

from sys.dm_exec_cached_plans

group by cacheobjtype,objtype

order by cacheobjtype,objtype

查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量

select p.object_id,OBJECT_NAME=OBJECT_NAME(p.object_id),p.index_id,buffer_pages=count(*)
from sys.allocation_units a,
sys.dm_os_buffer_descriptors b,
sys.partitions p
where a.allocation_unit_id=b.allocation_unit_id
and a.container_id=p.hobt_id
and b.database_id=DB_ID()
group by p.object_id,p.index_id
order by buffer_pages desc

select p.object_id,OBJECT_NAME=OBJECT_NAME(p.object_id),p.index_id,buffer_pages=count(*)

from sys.allocation_units a,

sys.dm_os_buffer_descriptors b,

sys.partitions p

where a.allocation_unit_id=b.allocation_unit_id

and a.container_id=p.hobt_id

and b.database_id=DB_ID()

group by p.object_id,p.index_id

order by buffer_pages desc

查询SqlServer总体的内存使用情况

select type,
sum(virtual_memory_reserved_kb)/1024/1024 as vm_Reserved_gb, --保留的内存
sum(virtual_memory_committed_kb)/1024/1024 as vm_Committed_gb, --提交的内存
sum(awe_allocated_kb)/1024/1024 as  awe_Allocated_gb,  --开启AWR后使用的内存
sum(shared_memory_committed_kb)/1024/1024 as sm_Committed_gb, --共享的保留内存
sum(shared_memory_reserved_kb)/1024/1024 as sm_Reserved_gb  --共享的提交内存
from sys.dm_os_memory_clerks
group by type
order by type

select type,

sum(virtual_memory_reserved_kb)/1024/1024 as vm_Reserved_gb, --保留的内存

sum(virtual_memory_committed_kb)/1024/1024 as vm_Committed_gb, --提交的内存

sum(awe_allocated_kb)/1024/1024 as awe_Allocated_gb, --开启AWR后使用的内存

sum(shared_memory_committed_kb)/1024/1024 as sm_Committed_gb, --共享的保留内存

sum(shared_memory_reserved_kb)/1024/1024 as sm_Reserved_gb --共享的提交内存

from sys.dm_os_memory_clerks

group by type

order by type

查询SQLSERVER内存使用情况

select * from sys.dm_os_process_memory

1	select * from sys.dm_os_process_memory

看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据

select OBJECT_NAME(object_id) 表名,count(*) 页数,COUNT(*)*8/1024 Mb 
from sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c
where a.allocation_unit_id=b.allocation_unit_id
and b.container_id=c.hobt_id
and database_id=DB_ID()
group by OBJECT_NAME(object_id)
order by 2 desc

select OBJECT_NAME(object_id) 表名,count(*) 页数,COUNT(*)*8/1024 Mb

from sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c

where a.allocation_unit_id=b.allocation_unit_id

and b.container_id=c.hobt_id

and database_id=DB_ID()

group by OBJECT_NAME(object_id)

order by 2 desc

查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）–全局

select TOP 100 
usecounts,
objtype,
p.size_in_bytes,
[sql].[text]
from sys.dm_exec_cached_plans p 
OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql
order by usecounts,p.size_in_bytes desc

select TOP 100

usecounts,

objtype,

p.size_in_bytes,

[sql].[text]

from sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql

order by usecounts,p.size_in_bytes desc

查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master.sys.sysprocesses
order by cpu desc,physical_io desc;

SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句', 
DB_NAME([database_id]) AS '数据库名',
[cpu_time] AS 'CPU时间',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='lhrdb'  
ORDER BY [cpu_time] DESC

select spid,cmd,cpu,physical_io,memusage,

(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text

from master.sys.sysprocesses

order by cpu desc,physical_io desc;

SELECT TOP 10

[session_id],

[request_id],

[start_time] AS '开始时间',

[status] AS '状态',

[command] AS '命令',

dest.[text] AS 'sql语句',

DB_NAME([database_id]) AS '数据库名',

[cpu_time] AS 'CPU时间',

[blocking_session_id] AS '正在阻塞其他会话的会话ID',

[wait_type] AS '等待资源类型',

[wait_time] AS '等待时间',

[wait_resource] AS '等待的资源',

[reads] AS '物理读次数',

[writes] AS '写次数',

[logical_reads] AS '逻辑读次数',

[row_count] AS '返回结果行数'

FROM sys.[dm_exec_requests] AS der

CROSS APPLY

sys.[dm_exec_sql_text](der.[sql_handle]) AS dest

WHERE [session_id]>50 AND DB_NAME(der.[database_id])='lhrdb'

ORDER BY [cpu_time] DESC

查询会话中有多少个worker在等待

SELECT TOP 10
 [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句', 
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 INNER JOIN [sys].[dm_os_wait_stats] AS dows 
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

SELECT TOP 10

[session_id],

[request_id],

[start_time] AS '开始时间',

[status] AS '状态',

[command] AS '命令',

dest.[text] AS 'sql语句',

DB_NAME([database_id]) AS '数据库名',

[blocking_session_id] AS '正在阻塞其他会话的会话ID',

der.[wait_type] AS '等待资源类型',

[wait_time] AS '等待时间',

[wait_resource] AS '等待的资源',

[dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',

[reads] AS '物理读次数',

[writes] AS '写次数',

[logical_reads] AS '逻辑读次数',

[row_count] AS '返回结果行数'

FROM sys.[dm_exec_requests] AS der

INNER JOIN [sys].[dm_os_wait_stats] AS dows

ON der.[wait_type]=[dows].[wait_type]

CROSS APPLY

sys.[dm_exec_sql_text](der.[sql_handle]) AS dest

WHERE [session_id]>50

ORDER BY [cpu_time] DESC

查看当前数据库用户连接

USE master
GO
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

USE master

SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'

SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

查看数据库大小

exec sp_spaceused
select name,CONVERT(float,size)*(8192/1024)/1024
from dbo.sysfiles

exec sp_spaceused

select name,CONVERT(float,size)*(8192/1024)/1024

from dbo.sysfiles

查看数据库可用大小，已使用大小

USE tempdb;  
GO  
SELECT SUM(unallocated_extent_page_count) AS [free pages],   
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]  
FROM sys.dm_db_file_space_usage;  

USE tempdb;  
GO  
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],  
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]  
FROM sys.dm_db_file_space_usage;

USE tempdb;

SELECT SUM(unallocated_extent_page_count) AS [free pages],

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

USE tempdb;

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

SQL优化相关、执行时间

SELECT creation_time AS 语句编译时间, last_execution_time AS 上次执行时间, total_physical_reads AS 物理读次数, total_logical_reads / execution_count AS 每次逻辑读次数
    , total_logical_reads AS 逻辑读次数, total_logical_writes AS 逻辑写入次数, execution_count AS 执行次数, total_worker_time / 1000 AS 所用的CPU总时间ms
    , total_elapsed_time / 1000 AS 总花费时间ms
    , total_elapsed_time / execution_count / 1000 AS 平均时间ms
    , SUBSTRING(st.text, qs.statement_start_offset / 2 + 1, (CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset) / 2 + 1) AS 执行语句
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, qs.statement_start_offset / 2 + 1, (CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 + 1) NOT LIKE '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;

SELECT creation_time AS 语句编译时间, last_execution_time AS 上次执行时间, total_physical_reads AS 物理读次数, total_logical_reads / execution_count AS 每次逻辑读次数

, total_logical_reads AS 逻辑读次数, total_logical_writes AS 逻辑写入次数, execution_count AS 执行次数, total_worker_time / 1000 AS 所用的CPU总时间ms

, total_elapsed_time / 1000 AS 总花费时间ms

, total_elapsed_time / execution_count / 1000 AS 平均时间ms

, SUBSTRING(st.text, qs.statement_start_offset / 2 + 1, (CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset) / 2 + 1) AS 执行语句

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

WHERE SUBSTRING(st.text, qs.statement_start_offset / 2 + 1, (CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset) / 2 + 1) NOT LIKE '%fetch%'

ORDER BY total_elapsed_time / execution_count DESC;

查看索引碎片

也可由sys.dm_db_index_physical_stats视图来代替。

DBCC SHOWCONTIG('Sales.Orders')；

结果：
DBCC SHOWCONTIG 正在扫描 'Orders' 表...
表: 'Orders' (1154103152)；索引 ID: 1，数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数................................: 688
- 扫描区数..............................: 87
- 区切换次数..............................: 86
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 98.85% [86:87]
- 逻辑扫描碎片 ..................: 0.29%
- 区扫描碎片 ..................: 98.85%
- 每页的平均可用字节数.....................: 73.3
- 平均页密度(满).....................: 99.09%  --密度很高，不需要重建
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

DBCC SHOWCONTIG('Sales.Orders')；

结果：

DBCC SHOWCONTIG 正在扫描 'Orders' 表...

表: 'Orders' (1154103152)；索引 ID: 1，数据库 ID: 7

已执行 TABLE 级别的扫描。

- 扫描页数................................: 688

- 扫描区数..............................: 87

- 区切换次数..............................: 86

- 每个区的平均页数........................: 7.9

- 扫描密度 [最佳计数:实际计数].......: 98.85% [86:87]

- 逻辑扫描碎片 ..................: 0.29%

- 区扫描碎片 ..................: 98.85%

- 每页的平均可用字节数.....................: 73.3

- 平均页密度(满).....................: 99.09% --密度很高，不需要重建

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

对上面的结果说明：

统计信息	描述
扫描页数	表或索引中的页数。
扫描区数	表或索引中的区数。
区切换次数	遍历表或索引的页时，DBCC 语句从一个区移动到另一个区的次数。
每个区的平均页数	页链中每个区的页数。
扫描密度 [最佳计数:实际计数]	百分比。这是“最佳计数”与“实际计数”的比率。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片。 “最佳计数”是指所有内容连续链接时理想的盘区更改次数。 “实际计数”是指实际的盘区更改次数。
逻辑扫描碎片	扫描索引的叶级页时返回的出错页的百分比。此数与堆无关。对于出错页，分配给索引的下一个物理页不是当前叶级页中的下一页指针所指向的页。
区扫描碎片	扫描索引的叶级页时出错区所占的百分比。此数与堆无关。对于出错区，包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。注意：如果索引涉及多个文件，则此数字毫无意义。
每页的平均可用字节数	扫描的页上平均可用字节数。此数字越大，则页的填充程度越低。如果索引不会有很多随机插入，则数字越小越好。此数字还受行大小影响：行越大，此数字就越大。
平均页密度(满)	页的平均密度，以百分比表示。该值会考虑行大小。因此，该值可以更准确地指示页的填充程度。百分比越大越好。

–如果指定了FAST，那么则显示以下几列信息：

扫描页数
区切换次数
扫描密度 [最佳计数:实际计数]
区扫描碎片
逻辑扫描碎片

–如果指定了 TABLERESULTS，会多显示以下几列信息

统计信息	描述
Object Name	处理的表或视图的名称。
ObjectId	对象名的 ID。
IndexName	处理的索引的名称。堆的 IndexName 为 NULL。
IndexId	索引的 ID。堆的 IndexId 为 0。
Level	索引的级别。级别 0 是索引的叶（或数据）级。堆的级别为 0。
Pages	组成某个索引级别或整个堆的页数。
Rows	某个索引级别上的数据或索引记录数。对于堆，此值是整个堆中的数据记录数。对于堆，此函数返回的记录数可能与通过对堆运行 SELECT COUNT(*) 返回的行数不匹配。这是因为一行可能包含多个记录。例如，在某些更新情况下，单个堆行可能由于更新操作而包含一条前推记录和一条被前推记录。此外，多数大型 LOB 行在 LOB_DATA 存储中拆分为多个记录。
MinimumRecordSize	某个索引级别或整个堆中的最小记录大小。
MaximumRecordSize	某个索引级别或整个堆中的最大记录大小。
AverageRecordSize	某个索引级别或整个堆中的平均记录大小。
ForwardedRecords	该索引级别或整个堆中的被前推记录数。
Extents	某个索引级别或整个堆中的区数。
ExtentSwitches	遍历表或索引的页时，DBCC 语句从一个区移动到另一个区的次数。
AverageFreeBytes	扫描的页上平均可用字节数。此数字越大，则页的填充程度越低。如果索引不会有很多随机插入，则数字越小越好。此数字还受行大小影响：行越大，此数字就越大。
AveragePageDensity	页的平均密度，以百分比表示。该值会考虑行大小。因此，该值可以更准确地指示页的填充程度。百分比越大越好。
ScanDensity	百分比。这是“BestCount”与“ActualCount”的比率。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片。
BestCount	所有内容连续链接时的区更改理想数量。
ActualCount	区更改实际数量。
LogicalFragmentation	扫描索引的叶级页时返回的出错页的百分比。此数与堆无关。对于出错页，分配给索引的下一个物理页不是当前叶级页中的下一页指针所指向的页。
ExtentFragmentation	扫描索引的叶级页时出错区所占的百分比。此数与堆无关。对于出错区，包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。注意：如果索引涉及多个文件，则此数字毫无意义。

– 如果索引的碎片非常多，可选择以下方法来减少碎片：

删除然后重新创建聚集索引。
重新创建聚集索引将重新组织数据，从而使数据页填满。填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。这种方法的缺点是索引在删除/重新创建周期内为脱机状态，并且该操作是一个整体，不可中断。如果中断索引创建，则不能重新创建索引。
对索引的叶级页按逻辑顺序重新排序。
使用 INDEX…REORGANIZE 对索引的叶级页按逻辑顺序重新排序。由于此操作是联机操作，因此语句运行时索引可用。此外，中断该操作不会丢失已完成的工作。这种方法的缺点是在重新组织数据方面没有聚集索引的删除/重新创建操作有效。
重新生成索引。
使用 REBUILD 和 ALTER INDEX 重新生成索引。

–索引的碎片级别可通过以下方式确定：

比较“区切换次数”和“扫描区数”的值。
“区切换次数”的值应尽可能接近“扫描区数”的值。此比率将作为“扫描密度”值计算。此值应尽可能的大，可通过减少索引碎片得到改善。
了解“逻辑扫描碎片”和“区扫描碎片”的值。
“逻辑扫描碎片”和“区扫描碎片”（对于较小的盘区）的值是表的碎片级别的最好指标。这两个值应尽可能接近零，但 0% 到 10% 之间的值都是可接受的。

具体例子

A. 显示表的碎片信息
下面的示例将显示 Employee 表的碎片信息。

USE lhrdb;  
GO  
DBCC SHOWCONTIG ('HumanResources.Employee');  
GO

USE lhrdb;

DBCC SHOWCONTIG ('HumanResources.Employee');

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

B. 使用 OBJECT_ID 获得表 ID，使用 sys.indexes 获得索引 ID
以下示例使用 OBJECT_ID 和 sys.indexes 目录视图，以获取 AdventureWorks2012 数据库中 Production.Product 表的 AK_Product_Name 索引的表 ID 和索引 ID。

USE lhrdb;  
GO  
DECLARE @id int, @indid int  
SET @id = OBJECT_ID('Production.Product')  
SELECT @indid = index_id   
FROM sys.indexes  
WHERE object_id = @id   
   AND name = 'AK_Product_Name'  
DBCC SHOWCONTIG (@id, @indid);  
GO

USE lhrdb;

DECLARE @id int, @indid int

SET @id = OBJECT_ID('Production.Product')

SELECT @indid = index_id

FROM sys.indexes

WHERE object_id = @id

AND name = 'AK_Product_Name'

DBCC SHOWCONTIG (@id, @indid);

C. 显示表的简略结果集
以下示例会返回 AdventureWorks2012 数据库中 Product 表的简略结果集。
SQL

USE lhrdb;  
GO  
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;  
GO

USE lhrdb;

DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;

D. 显示数据库中每个表的每个索引的完整结果集
下面的示例将返回 lhrdb 数据库中每个表的每个索引的完整表结果集。
SQL

USE lhrdb;  
GO  
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;  
GO

USE lhrdb;

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;

E. 使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 对数据库中的索引进行碎片整理
下面的一个例子是如果数据库中碎片数量指定阈值之上的时，会对所有索引进行碎片整理。
SQL

/*可以使用USE <数据库名>来指定数据库检查*/  
USE lhrdb;
-- 声明变量  
SET NOCOUNT ON;  
DECLARE @tablename varchar(255);  
DECLARE @execstr   varchar(400);  
DECLARE @objectid  int;  
DECLARE @indexid   int;  
DECLARE @frag      decimal;  
DECLARE @maxfrag   decimal;  

-- 最大碎片数量
SELECT @maxfrag = 30.0;  

-- 声明游标 
DECLARE tables CURSOR FOR  
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME  
   FROM INFORMATION_SCHEMA.TABLES  
   WHERE TABLE_TYPE = 'BASE 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 tables;  

-- 循环取数据库中所有表.  
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 tables  
      INTO @tablename;  
END;  

-- 关闭，释放游标 
CLOSE tables;  
DEALLOCATE tables;  

-- 为要碎片整理的索引列表声明游标.  
DECLARE indexes CURSOR FOR  
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag  
   FROM #fraglist  
   WHERE LogicalFrag >= @maxfrag  
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;  

-- 打开游标.  
OPEN 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 indexes;  
DEALLOCATE indexes;  

-- 删除临时用的表  
DROP TABLE #fraglist;  
GO

100

101

102

103

/*可以使用USE <数据库名>来指定数据库检查*/

USE lhrdb;

-- 声明变量

SET NOCOUNT ON;

DECLARE @tablename varchar(255);

DECLARE @execstr varchar(400);

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @frag decimal;

DECLARE @maxfrag decimal;

-- 最大碎片数量

SELECT @maxfrag = 30.0;

-- 声明游标

DECLARE tables CURSOR FOR

SELECT TABLE_SCHEMA + '.' + TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE 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 tables;

-- 循环取数据库中所有表.

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 tables

INTO @tablename;

END;

-- 关闭，释放游标

CLOSE tables;

DEALLOCATE tables;

-- 为要碎片整理的索引列表声明游标.

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectId, IndexId, LogicalFrag

FROM #fraglist

WHERE LogicalFrag >= @maxfrag

AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- 打开游标.

OPEN 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 indexes;

DEALLOCATE indexes;

-- 删除临时用的表

DROP TABLE #fraglist;

查看指定表的索引信息

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  

SET @db_id = DB_ID(N'WideWorldImporters');  
SET @object_id = OBJECT_ID(N'WideWorldImporters.Sales.Orders');  

IF @db_id IS NULL  
BEGIN;  
    PRINT N'Invalid database';  
END;  
ELSE IF @object_id IS NULL  
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN;  
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;  
GO

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N'WideWorldImporters');

SET @object_id = OBJECT_ID(N'WideWorldImporters.Sales.Orders');

IF @db_id IS NULL

BEGIN;

PRINT N'Invalid database';

END;

ELSE IF @object_id IS NULL

BEGIN;

PRINT N'Invalid object';

END;

ELSE

BEGIN;

SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

END;

avg_fragmentation_in_percent 的值应尽可能接近零。但是，从 0 到 10％范围内的值都可以接受。

语法

sys.dm_db_index_physical_stats (   
    { database_id | NULL | 0 | DEFAULT }  
  , { object_id | NULL | 0 | DEFAULT }  
  , { index_id | NULL | 0 | -1 | DEFAULT }  
  , { partition_number | NULL | 0 | DEFAULT }  
  , { mode | NULL | DEFAULT }  
)

sys.dm_db_index_physical_stats (

{ database_id | NULL | 0 | DEFAULT }

, { object_id | NULL | 0 | DEFAULT }

, { index_id | NULL | 0 | -1 | DEFAULT }

, { partition_number | NULL | 0 | DEFAULT }

, { mode | NULL | DEFAULT }

)

重新生成或重新组织索引

自动重新组织或重新生成数据库中平均碎片超过 10％的所有分区。

-- 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

-- 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;

查看索引使用情况

包括扫描次数，最近使用时间等信息

select * from sys.dm_db_index_usage_stats 
where database_id=DB_ID('lhrdb');

1 2	select * from sys.dm_db_index_usage_stats where database_id=DB_ID('lhrdb');

统计信息

包括创建，删除，修改，更新统计信息.

注：默认情况下，数据库已经生成了必要的统计信息。如果不是特殊需要，不需要单独创建统计信息，因为一旦创建了统计信息，那么数据库会定期的去更新统计信息，会造成一定的资源浪费。

创建

USE lhrdb;   
GO  

CREATE STATISTICS pk_id  
    ON dbo.lei (ID);   
GO

USE lhrdb;

CREATE STATISTICS pk_id

ON dbo.lei (ID);

修改
无法通过SQL语句来修改，只能通过删除，再添加的方式。所以直通通过SSMS工具来

USE [lhrdb]
GO
DROP STATISTICS [dbo].[lei].[pk_id]
GO
1234

USE [lhrdb]

DROP STATISTICS [dbo].[lei].[pk_id]

1234

查看

USE WideWorldImporters;  
GO  
-- The following example displays all statistics information for the AK_Address_rowguid index of the Person.Address table.   
DBCC SHOW_STATISTICS ("Sales.Orders", PK_Sales_Orders);   --用户名，表名，统计信息名。
GO

USE WideWorldImporters;

-- The following example displays all statistics information for the AK_Address_rowguid index of the Person.Address table.

DBCC SHOW_STATISTICS ("Sales.Orders", PK_Sales_Orders); --用户名，表名，统计信息名。

重命名
一般情况不建议修改名称，要么就直接删除再重新创建一个。

USE WideWorldImporters;  
GO

EXEC sp_rename N'PK_Sales_Orders', N'PK_Sales_Orderss', N'STATISTICS';  
GO

USE WideWorldImporters;

EXEC sp_rename N'PK_Sales_Orders', N'PK_Sales_Orderss', N'STATISTICS';

更新
可通过UPDATE STATISTICS语句或sp_updatestats存储过程来更新统计信息。
更新该表上所有索引的统计信息

USE WideWorldImporters;   
GO  

UPDATE STATISTICS Sales.SalesOrderDetail;   
GO

USE WideWorldImporters;

UPDATE STATISTICS Sales.SalesOrderDetail;

更新数据库中所有表的统计信息

USE WideWorldImporters;   
GO

EXEC sp_updatestats;

USE WideWorldImporters;

EXEC sp_updatestats;

自动索引和统计信息管理

微软在Github上提供了一个脚本，用于自动管理索引和统计信息。
地址
其实就是创建一个存储过程对一个或多个数据库的一个或多个索引和统计信息执行智能碎片整理。简而言之，这个过程自动选择是根据索引的碎片级别，还是根据其他参数(如是否允许页锁或lob的存在)重新构建或重新组织索引，同时使用线性阈值更新统计信息。所有这些都在您选择的指定时间范围内，默认为8小时。还可以根据大小、碎片级别或索引使用(基于范围扫描计数)设置碎片整理优先级，这是默认设置。它还处理分区索引、columnstore索引、内存表中的索引、统计信息更新(表范围内的或仅与索引相关的)、使用原始填充因子或索引填充进行重新构建以及在线操作，等等。
环境要求
支持SQL Server 2005 SP2及之后的版本。

注：不保证可以在SQL Server 2005版本中正常运行。

安装
直接在SSMS中运行脚本即可。如：
由于脚本太长，需要看脚本内容的朋友自己去网上查看：https://github.com/Microsoft/tigertoolbox/blob/master/AdaptiveIndexDefrag/usp_AdaptiveIndexDefrag.sql

输出信息:

Droping existing objects
Preserving historic data
tbl_AdaptiveIndexDefrag_log table created
tbl_AdaptiveIndexDefrag_Analysis_log table created
tbl_AdaptiveIndexDefrag_Exceptions table created
tbl_AdaptiveIndexDefrag_Working table created
tbl_AdaptiveIndexDefrag_Stats_Working table created
tbl_AdaptiveIndexDefrag_Stats_log table created
tbl_AdaptiveIndexDefrag_IxDisableStatus table created
Copying old data...
Done copying old data...
Removed old tables...
Procedure usp_AdaptiveIndexDefrag created
Reporting views created
Procedure usp_AdaptiveIndexDefrag_PurgeLogs created (Default purge is 90 days old)
Procedure usp_AdaptiveIndexDefrag_CurrentExecStats created (Use this to monitor defrag loop progress)
Procedure usp_AdaptiveIndexDefrag_Exceptions created (If the defrag should not be daily, use this to set on which days to disallow it. It can be on entire DBs, tables and/or indexes)
All done!

Droping existing objects

Preserving historic data

tbl_AdaptiveIndexDefrag_log table created

tbl_AdaptiveIndexDefrag_Analysis_log table created

tbl_AdaptiveIndexDefrag_Exceptions table created

tbl_AdaptiveIndexDefrag_Working table created

tbl_AdaptiveIndexDefrag_Stats_Working table created

tbl_AdaptiveIndexDefrag_Stats_log table created

tbl_AdaptiveIndexDefrag_IxDisableStatus table created

Copying old data...

Done copying old data...

Removed old tables...

Procedure usp_AdaptiveIndexDefrag created

Reporting views created

Procedure usp_AdaptiveIndexDefrag_PurgeLogs created (Default purge is 90 days old)

Procedure usp_AdaptiveIndexDefrag_CurrentExecStats created (Use this to monitor defrag loop progress)

Procedure usp_AdaptiveIndexDefrag_Exceptions created (If the defrag should not be daily, use this to set on which days to disallow it. It can be on entire DBs, tables and/or indexes)

All done!

上面表示已经安装成功了。

使用
存储过程支持参数还是很多的，我这里只介绍其中几个常用的。其他参数，请查看官方的文档：
https://github.com/Microsoft/tigertoolbox/blob/master/AdaptiveIndexDefrag/OPTIONS.md

@Exec_Print：默认是1(执行存储过程生成的SQL命令)，0表示不执行(仅显示)。
@printCmds ：默认是0(不打印SQL命令)，1(打印SQL命令)。如果只想查看哪些命令要执行，这个选项很有用。
@outputResults：默认0(不输出碎片信息)，1表示命令执行完后会显示碎片信息。
@timeLimit ：用于限制用于整理索引碎片的时间。默认：8小时。当然如果一个很大的索引，还是可能会超过8小时的。
@dbScope：指定要整理碎片的数据库。如果不指定，则整理所有非系统数据库，包括msdb，model。
@tblName：指定要整理碎片表的索引。如果不指定，则整理数据库中所有表。格式：schema.table_name。
@minFragmentation：默认5%，低于5%将不会被整理碎片。
@rebuildThreshold：默认30%，高于30%将会被重建。而不是重新组织。
@scanMode指定使用哪种扫描模式来确定碎片级别。LIMITED模式是默认模式。扫描最小数量的页面。对于索引，只扫描b树的父级页面（即叶级以上的页面）。对于堆，只检查相关的PFS和IAM页面。没有扫描堆的数据页。其他选项包括SAMPLED（根据索引或堆中所有页面的1％的样本返回统计信息）或DETAILED（扫描所有页面并返回所有统计信息）。可能导致性能问题）。如果索引或堆的页数少于10,000，则自动使用DETAILED模式而不是SAMPLED模式。
@onlineRebuild：默认0(离线重建)，1表示在线重建。

–下面结合几个例子来说明一下：

全部采用默认值，整理碎片：
EXEC dbo.usp_AdaptiveIndexDefrag
默认设置是对碎片大于5％的索引进行碎片整理; 重建碎片大于30％的索引; 整理所有索引; 命令将自动执行; 以DESC顺序对RANGE_SCAN_COUNT值进行碎片整理; 规定时限，为480分钟（8小时）; 所有数据库都将进行碎片整理; 所有表都将进行碎片整理; 将重新扫描索引; 扫描将以限定模式执行; LOB将被压缩; 将defrags限制为超过8页的索引; 索引将进行碎片整理OFFLINE; 索引将在DATABASE中排序; 索引将具有其原始填充因子; 如果大于8页，则仅考虑最右侧填充的分区; 统计数据将在重组指数上更新; 碎片整理将使用处理器的系统默认值; 不打印t-sql命令; 不输出碎片级别; 在索引操作之间等待5秒;

注意：慎用。执行需要时间过长，而且默认情况下是离线重建索引。

指定数据库

EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014'

1	EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014'

指定数据库，表

EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials'

1	EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials'

只显示命令，不会运行命令去整理碎片

EXEC dbo.usp_AdaptiveIndexDefrag @Exec_Print = 0, @printCmds = 1

1	EXEC dbo.usp_AdaptiveIndexDefrag @Exec_Print = 0, @printCmds = 1

结果：

 Printing SQL statements...
     ALTER INDEX [PK_Warehouse_StockItems] ON [WideWorldImporters].[Warehouse].[StockItems] REORGANIZE WITH (LOB_COMPACTION = ON);
     -- No need to update statistic [PK_Warehouse_StockItems] on table or view [StockItems] of DB [WideWorldImporters]...
     ALTER INDEX [PK_Purchasing_PurchaseOrders] ON [WideWorldImporters].[Purchasing].[PurchaseOrders] REORGANIZE WITH (LOB_COMPACTION = ON);
     -- No need to update statistic [PK_Purchasing_PurchaseOrders] on table or view [PurchaseOrders] of DB [WideWorldImporters]...
     ALTER INDEX [IX_Application_People_Perf_20160301_05] ON [WideWorldImporters].[Application].[People] REORGANIZE WITH (LOB_COMPACTION = ON);
     -- No need to update statistic [IX_Application_People_Perf_20160301_05] on table or view [People] of DB [WideWorldImporters]...
     ALTER INDEX [FK_Purchasing_PurchaseOrderLines_PurchaseOrderID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON);
     -- No need to update statistic [FK_Purchasing_PurchaseOrderLines_PurchaseOrderID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]...
     ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON);
     -- No need to update statistic [FK_Purchasing_PurchaseOrderLines_StockItemID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]...
     ALTER INDEX [FK_Purchasing_PurchaseOrderLines_PackageTypeID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON);
     -- No need to update statistic [FK_Purchasing_PurchaseOrderLines_PackageTypeID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]...
     UPDATE STATISTICS [msdb].[dbo].[tbl_AdaptiveIndexDefrag_Stats_log] ([PK_AdaptiveIndexDefrag_Stats_log]); 
     ALTER INDEX [PK_AdaptiveIndexDefrag_Stats_log] ON [msdb].[dbo].[tbl_AdaptiveIndexDefrag_Stats_log] REBUILD WITH (DATA_COMPRESSION = NONE, FILLFACTOR = 100, SORT_IN_TEMPDB = OFF);

Printing SQL statements...

ALTER INDEX [PK_Warehouse_StockItems] ON [WideWorldImporters].[Warehouse].[StockItems] REORGANIZE WITH (LOB_COMPACTION = ON);

-- No need to update statistic [PK_Warehouse_StockItems] on table or view [StockItems] of DB [WideWorldImporters]...

ALTER INDEX [PK_Purchasing_PurchaseOrders] ON [WideWorldImporters].[Purchasing].[PurchaseOrders] REORGANIZE WITH (LOB_COMPACTION = ON);

-- No need to update statistic [PK_Purchasing_PurchaseOrders] on table or view [PurchaseOrders] of DB [WideWorldImporters]...

ALTER INDEX [IX_Application_People_Perf_20160301_05] ON [WideWorldImporters].[Application].[People] REORGANIZE WITH (LOB_COMPACTION = ON);

-- No need to update statistic [IX_Application_People_Perf_20160301_05] on table or view [People] of DB [WideWorldImporters]...

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_PurchaseOrderID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON);

-- No need to update statistic [FK_Purchasing_PurchaseOrderLines_PurchaseOrderID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]...

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON);

-- No need to update statistic [FK_Purchasing_PurchaseOrderLines_StockItemID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]...

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_PackageTypeID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON);

-- No need to update statistic [FK_Purchasing_PurchaseOrderLines_PackageTypeID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]...

UPDATE STATISTICS [msdb].[dbo].[tbl_AdaptiveIndexDefrag_Stats_log] ([PK_AdaptiveIndexDefrag_Stats_log]);

ALTER INDEX [PK_AdaptiveIndexDefrag_Stats_log] ON [msdb].[dbo].[tbl_AdaptiveIndexDefrag_Stats_log] REBUILD WITH (DATA_COMPRESSION = NONE, FILLFACTOR = 100, SORT_IN_TEMPDB = OFF);

查看缺少的索引

通过脚本来查看由于缺少索引而导致查询缓慢的表，并生成相应的索引创建语句。
脚本内容：https://github.com/Microsoft/tigertoolbox/blob/master/Index-Creation/view_IndexCreation.sql
直接运行脚本即可

ALTER DATABASE 语法

ALTER DATABASE database_name   
{  
    <add_or_modify_files>  
  | <add_or_modify_filegroups>  
}  
[;]  

<add_or_modify_files>::=  
{  
    ADD FILE <filespec> [ ,...n ]   
        [ TO FILEGROUP { filegroup_name } ]  
  | ADD LOG FILE <filespec> [ ,...n ]   
  | REMOVE FILE logical_file_name   
  | MODIFY FILE <filespec>  
}  

<filespec>::=   
(  
    NAME = logical_file_name    
    [ , NEWNAME = new_logical_name ]   
    [ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]   
    [ , SIZE = size [ KB | MB | GB | TB ] ]   
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]   
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]   
    [ , OFFLINE ]  
)   

<add_or_modify_filegroups>::=  
{  
    | ADD FILEGROUP filegroup_name   
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]  
    | REMOVE FILEGROUP filegroup_name   
    | MODIFY FILEGROUP filegroup_name  
        { <filegroup_updatability_option>  
        | DEFAULT  
        | NAME = new_filegroup_name   
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }  
        }  
}  
<filegroup_updatability_option>::=  
{  
    { READONLY | READWRITE }   
    | { READ_ONLY | READ_WRITE }  
}

ALTER DATABASE database_name

{

<add_or_modify_files>

| <add_or_modify_filegroups>

}

[;]

<add_or_modify_files>::=

{

ADD FILE <filespec> [ ,...n ]

[ TO FILEGROUP { filegroup_name } ]

| ADD LOG FILE <filespec> [ ,...n ]

| REMOVE FILE logical_file_name

| MODIFY FILE <filespec>

}

<filespec>::=

(

NAME = logical_file_name

[ , NEWNAME = new_logical_name ]

[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]

[ , SIZE = size [ KB | MB | GB | TB ] ]

[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]

[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]

[ , OFFLINE ]

)

<add_or_modify_filegroups>::=

{

| ADD FILEGROUP filegroup_name

[ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]

| REMOVE FILEGROUP filegroup_name

| MODIFY FILEGROUP filegroup_name

{ <filegroup_updatability_option>

| DEFAULT

| NAME = new_filegroup_name

| { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }

}

<filegroup_updatability_option>::=

{

{ READONLY | READWRITE }

| { READ_ONLY | READ_WRITE }

}

A. 向数据库中添加文件

以下示例将一个 5 MB 的数据文件添加到 AdventureWorks2012 数据库。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = Test1dat2,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat2.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
);  
GO

USE master;

ALTER DATABASE AdventureWorks2012

ADD FILE

(

NAME = Test1dat2,

FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat2.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

);

B. 向数据库中添加由两个文件组成的文件组

以下示例在 AdventureWorks2012 数据库中创建文件组 Test1FG1，然后将两个 5 MB 的文件添加到该文件组。

USE master  
GO  
ALTER DATABASE AdventureWorks2012  
ADD FILEGROUP Test1FG1;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = test1dat3,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat3.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1dat4,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat4.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP Test1FG1;  
GO

USE master

ALTER DATABASE AdventureWorks2012

ADD FILEGROUP Test1FG1;

ALTER DATABASE AdventureWorks2012

ADD FILE

(

NAME = test1dat3,

FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat3.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

(

NAME = test1dat4,

FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat4.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP Test1FG1;

C. 向数据库中添加两个日志文件

下面的示例向 AdventureWorks2012 数据库中添加两个 5 MB 的日志文件。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD LOG FILE   
(  
    NAME = test1log2,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/test2log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1log3,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/test3log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
);  
GO

USE master;

ALTER DATABASE AdventureWorks2012

ADD LOG FILE

(

NAME = test1log2,

FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/test2log.ldf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

(

NAME = test1log3,

FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/test3log.ldf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

);

D. 从数据库中删除文件

以下示例删除示例 B 中添加的一个文件。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE test1dat4;  
GO

USE master;

ALTER DATABASE AdventureWorks2012

REMOVE FILE test1dat4;

E. 修改文件

以下示例增加示例 B 中添加的一个文件的大小。

带有 MODIFY FILE 命令的 ALTER DATABASE 只能增大文件大小，因此，如果需要缩小文件大小，则需使用 DBCC SHRINKFILE。

USE master;  
GO

ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO

USE master;

ALTER DATABASE AdventureWorks2012

MODIFY FILE

(NAME = test1dat3,

SIZE = 200MB);

此示例将数据文件的大小缩小为 100 MB，然后指定该数量的大小。

USE AdventureWorks2012;
GO

DBCC SHRINKFILE (AdventureWorks2012_data, 100);
GO

USE master;  
GO

ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO

USE AdventureWorks2012;

DBCC SHRINKFILE (AdventureWorks2012_data, 100);

USE master;

ALTER DATABASE AdventureWorks2012

MODIFY FILE

(NAME = test1dat3,

SIZE = 200MB);

F. 将文件移至新位置

以下示例将在示例 A 中创建的 Test1dat2 文件移至新目录中。

备注

必须先将该文件实际移至新目录中，然后才能运行此示例。然后，停止和启动 SQL Server 的实例，或使 AdventureWorks2012 数据库 OFFLINE 再 ONLINE，以实施更改。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012  
MODIFY FILE  
(  
    NAME = Test1dat2,  
    FILENAME = N'c:/t1dat2.ndf'  
);  
GO

USE master;

ALTER DATABASE AdventureWorks2012

MODIFY FILE

(

NAME = Test1dat2,

FILENAME = N'c:/t1dat2.ndf'

);

G. 将 tempdb 移至新位置

以下示例将 tempdb 从其在磁盘上的当前位置移至另一个磁盘位置。由于每次启动 MSSQLSERVER 服务时都会重新创建 tempdb，因此您不必实际移动数据和日志文件。在步骤 3 中重新启动服务时，将创建这些文件。在重新启动该服务之前，tempdb 将继续在现有位置发挥作用。

确定 tempdb 数据库的逻辑文件名称以及这些文件在磁盘上的当前位置。

SELECT name, physical_name  
FROM sys.master_files  
WHERE database_id = DB_ID('tempdb');  
GO

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

使用 ALTER DATABASE更改每个文件的位置。

USE master;  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = tempdev, FILENAME = 'E:/SQLData/tempdb.mdf');  
GO  
ALTER DATABASE  tempdb   
MODIFY FILE (NAME = templog, FILENAME = 'E:/SQLData/templog.ldf');  
GO

USE master;

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:/SQLData/tempdb.mdf');

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'E:/SQLData/templog.ldf');

停止再重新启动 SQL Server的实例。

验证文件更改。

SELECT name, physical_name  
FROM sys.master_files  
WHERE database_id = DB_ID('tempdb');

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

将 tempdb.mdf 和 templog.ldf 文件从其原始位置中删除。

H. 使文件组成为默认文件组

以下示例使示例 B 中创建的 Test1FG1 文件组成为默认文件组。然后，默认文件组被重置为 PRIMARY 文件组。请注意，必须使用括号或引号分隔 PRIMARY。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP Test1FG1 DEFAULT;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP [PRIMARY] DEFAULT;  
GO

USE master;

ALTER DATABASE AdventureWorks2012

MODIFY FILEGROUP Test1FG1 DEFAULT;

ALTER DATABASE AdventureWorks2012

MODIFY FILEGROUP [PRIMARY] DEFAULT;

I. 使用 ALTER DATABASE 添加文件组

以下示例将一个包含 FILEGROUP 子句的 FILESTREAM 添加到 FileStreamPhotoDB 数据库。

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause.  
ALTER DATABASE FileStreamPhotoDB  
ADD FILEGROUP TodaysPhotoShoot  
CONTAINS FILESTREAM;  
GO  

--Add a file for storing database photos to FILEGROUP   
ALTER DATABASE FileStreamPhotoDB  
ADD FILE  
(  
    NAME= 'PhotoShoot1',  
    FILENAME = 'C:/Users/Administrator/Pictures/TodaysPhotoShoot.ndf'  
)  
TO FILEGROUP TodaysPhotoShoot;  
GO

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause.

ALTER DATABASE FileStreamPhotoDB

ADD FILEGROUP TodaysPhotoShoot

CONTAINS FILESTREAM;

--Add a file for storing database photos to FILEGROUP

ALTER DATABASE FileStreamPhotoDB

ADD FILE

(

NAME= 'PhotoShoot1',

FILENAME = 'C:/Users/Administrator/Pictures/TodaysPhotoShoot.ndf'

)

TO FILEGROUP TodaysPhotoShoot;

以下示例将一个包含 FILEGROUP 子句的 MEMORY_OPTIMIZED_DATA 添加到 xtp_db 数据库。该文件组存储内存优化数据。

--Create and add a FILEGROUP that CONTAINS the MEMORY_OPTIMIZED_DATA clause.
ALTER DATABASE xtp_db 
ADD FILEGROUP xtp_fg 
CONTAINS MEMORY_OPTIMIZED_DATA;  
GO  

--Add a file for storing memory optimized data to FILEGROUP
ALTER DATABASE xtp_db 
ADD FILE 
(
    NAME='xtp_mod', 
    FILENAME='d:/data/xtp_mod'
) 
TO FILEGROUP xtp_fg;  
GO

--Create and add a FILEGROUP that CONTAINS the MEMORY_OPTIMIZED_DATA clause.

ALTER DATABASE xtp_db

ADD FILEGROUP xtp_fg

CONTAINS MEMORY_OPTIMIZED_DATA;

--Add a file for storing memory optimized data to FILEGROUP

ALTER DATABASE xtp_db

ADD FILE

(

NAME='xtp_mod',

FILENAME='d:/data/xtp_mod'

)

TO FILEGROUP xtp_fg;

J. 更改文件组，以便当文件组中的某个文件达到自动增长阈值时，文件组中的所有文件都会增长

以下示例会生成所需的 ALTER DATABASE 语句，以使用 AUTOGROW_ALL_FILES 设置修改读写文件组。

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements  
--so that all read-write filegroups grow at the same time.  
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, isdone bit);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, fgname sysname, isdone bit);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid int, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

    SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
    INSERT INTO #tmpfgs
    EXEC (@query)

    UPDATE #tmpdbs
    SET isdone = 1
    WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
    WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
    BEGIN
        SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

        SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

        PRINT @query

        UPDATE #tmpfgs
        SET isdone = 1
        WHERE [dbid] = @dbid AND fgname = @fgname
    END
END; 
GO

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements

--so that all read-write filegroups grow at the same time.

SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs

CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, isdone bit);

DROP TABLE IF EXISTS #tmpfgs

CREATE TABLE #tmpfgs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, fgname sysname, isdone bit);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])

SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid int, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0

BEGIN

SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'

INSERT INTO #tmpfgs

EXEC (@query)

UPDATE #tmpdbs

SET isdone = 1

WHERE [dbid] = @dbid

END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0

BEGIN

WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0

BEGIN

SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

PRINT @query

UPDATE #tmpfgs

SET isdone = 1

WHERE [dbid] = @dbid AND fgname = @fgname

END

END;

参考

https://blog.csdn.net/qianglei6077/article/details/86576301

打赏赞(1)

标签： MSSQL SQL Server 常用SQL

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

合 MSSQL常用SQL语句

SQL Server对象查询（只查询当前数据库）

检查数据库完整性

数据库重命名、修改恢复模式、修改用户模式

数据库重命名

设置数据库为完整恢复模式

只允许一个用户访问数据库

只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库

多用户模式

移动文件

扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称

添加文件组

添加数据文件

添加日志文件

修改数据文件的大小,增长大小,最大大小

修改数据文件或日志文件的逻辑名称

设置默认文件组、只读文件组

收缩数据库、收缩文件

重新生成索引

查看实例名

查看数据库属性

查看数据库恢复模式

查看日志空间

系统函数

查看数据文件

查询缓存中具体的执行计划，及对应的SQL

查询缓存的各类执行计划，及分别占了多少内存

查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量

查询SqlServer总体的内存使用情况

查询SQLSERVER内存使用情况

看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据

查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）–全局

查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

查询会话中有多少个worker在等待

查看当前数据库用户连接

查看数据库大小

查看数据库可用大小，已使用大小

SQL优化相关、执行时间

查看索引碎片

查看指定表的索引信息

重新生成或重新组织索引

查看索引使用情况

统计信息

自动索引和统计信息管理

查看缺少的索引

ALTER DATABASE 语法

A. 向数据库中添加文件

B. 向数据库中添加由两个文件组成的文件组

C. 向数据库中添加两个日志文件

D. 从数据库中删除文件

E. 修改文件

F. 将文件移至新位置

G. 将 tempdb 移至新位置

H. 使文件组成为默认文件组

I. 使用 ALTER DATABASE 添加文件组

J. 更改文件组，以便当文件组中的某个文件达到自动增长阈值时，文件组中的所有文件都会增长

参考

相关文章

您可能还喜欢...

MSSQL之重命名数据库

MSSQL查询前几张大表（查询表大小）

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复