合 MSSQL常用SQL语句

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

0 179 1

👉 本文共约8296个字，系统预计阅读时间或需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，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： MSSQL SQL Server 常用SQL

小麦苗

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

发表回复取消回复

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

合 MSSQL常用SQL语句

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

检查数据库完整性

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

数据库重命名

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

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

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

多用户模式

移动文件

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

添加文件组

添加数据文件

添加日志文件

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

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

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

收缩数据库、收缩文件

重新生成索引

查看实例名

查看数据库属性

查看数据库恢复模式

查看日志空间

系统函数

查看数据文件

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

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

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

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

查询SQLSERVER内存使用情况

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

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

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

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

查看当前数据库用户连接

查看数据库大小

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

SQL优化相关、执行时间

查看索引碎片

相关文章

您可能还喜欢...

MSSQL的创建索引占用的内存

SQL Server备份恢复系列

MSSQL收缩数据文件和日志文件

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复