SQL Server日常维护SQL语句
标签（空格分隔）： 未分类
检查数据库完整性
dbcc checkdb(cndba)
通过加tablock提高速度
dbcc checkdb(test) with tablock
数据库重命名、修改恢复模式、修改用户模式
数据库重命名
ALTER DATABASE WC MODIFY NAME = test
设置数据库为完整恢复模式
alter database test set recovery full
只允许一个用户访问数据库
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 multi_user
with no_wait --不等待立即改变，如不能立即完成，那么会导致执行错误
移动文件
–由于在SQL Server中文件组、文件不能离线
–所以必须把整个数据库设置为离线
checkpoint
go
ALTER DATABASE WC
SET OFFLINE
go
–修改文件名称
ALTER DATABASE WC
MODIFY FILE
(
NAME = WC_fg8,
FILENAME = 'D:\WC\WC_FG8.NDF'
)
go
–把原来的文件复制到新的位置：‘D:\WC\WC_FG8.NDF’
–设置数据库在线
ALTER DATABASE WC
SET ONLINE
扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称
添加文件组
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 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_LOG3,
NEWNAME = WC_FG33
)
设置默认文件组、只读文件组
–设置默认文件组
ALTER DATABASE WC
MODIFY FILEGROUP WC_FG8 DEFAULT
–设为只读文件组
–如果文件已经是某个属性，不能再次设置相同属性
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 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 )
查看实例名
SELECT @@SERVICENAME AS InstantName;
SELECT ISNULL(SERVERPROPERTY('InstanceName'),'MSSQLSERVER') AS InstanceName;
查看数据库属性
sp_helpdb master
查看数据库恢复模式
select databasepropertyex('cndba','recovery')
查看日志空间
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 ;
查询缓存中具体的执行计划，及对应的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 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
查询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
查询SQLSERVER内存使用情况
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 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])='cndba'
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
查看当前数据库用户连接
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
查看数据库大小
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;
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;
查看索引碎片
也可由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 语句从一个区移动到另一个区的次数。 |
| 每个区的平均页数 | 页链中每个区的页数。 |
| 扫描密度 [最佳计数:实际计数] | 百分比。 这是“最佳计数”与“实际计数”的比率。 如果所有内容都是连续的，则该值为 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 cndba;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO
- B. 使用 OBJECT_ID 获得表 ID，使用 sys.indexes 获得索引 ID
以下示例使用 OBJECT_ID 和 sys.indexes 目录视图，以获取 AdventureWorks2012 数据库中 Production.Product 表的 AK_Product_Name 索引的表 ID 和索引 ID。
USE cndba;
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
- C. 显示表的简略结果集
以下示例会返回 AdventureWorks2012 数据库中 Product 表的简略结果集。
SQL
USE cndba;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO
- D. 显示数据库中每个表的每个索引的完整结果集
下面的示例将返回 cndba 数据库中每个表的每个索引的完整表结果集。
SQL
USE cndba;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO
- E. 使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 对数据库中的索引进行碎片整理
下面的一个例子是如果数据库中碎片数量指定阈值之上的时，会对所有索引进行碎片整理。
SQL
/*可以使用USE <数据库名>来指定数据库检查*/
USE cndba;
-- 声明变量
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
查看指定表的索引信息
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
- 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 }
)
重新生成或重新组织索引
自动重新组织或重新生成数据库中平均碎片超过 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
查看索引使用情况
包括扫描次数，最近使用时间等信息
select * from sys.dm_db_index_usage_stats
where database_id=DB_ID('cndba');
统计信息
包括创建，删除，修改，更新统计信息.
注：默认情况下，数据库已经生成了必要的统计信息。如果不是特殊需要，不需要单独创建统计信息，因为一旦创建了统计信息，那么数据库会定期的去更新统计信息，会造成一定的资源浪费。
创建
USE cndba;
GO
CREATE STATISTICS pk_id
ON dbo.lei (ID);
GO
修改
无法通过SQL语句来修改，只能通过删除，再添加的方式。所以直通通过SSMS工具来
USE [cndba]
GO
DROP STATISTICS [dbo].[lei].[pk_id]
GO
查看
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;
GO
EXEC sp_rename N'PK_Sales_Orders', N'PK_Sales_Orderss', N'STATISTICS';
GO
更新
可通过UPDATE STATISTICS语句或sp_updatestats存储过程来更新统计信息。
更新该表上所有索引的统计信息
USE WideWorldImporters;
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO
更新数据库中所有表的统计信息
USE WideWorldImporters;
GO
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!
上面表示已经安装成功了。
使用
存储过程支持参数还是很多的，我这里只介绍其中几个常用的。其他参数，请查看官方的文档：
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'
- 指定数据库，表
EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials'
- 只显示命令，不会运行命令去整理碎片
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);
查看缺少的索引
通过脚本来查看由于缺少索引而导致查询缓慢的表，并生成相应的索引创建语句。
脚本内容：https://github.com/Microsoft/tigertoolbox/blob/master/Index-Creation/view_IndexCreation.sql
直接运行脚本即可
语法
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
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
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
D. 从数据库中删除文件
以下示例删除示例 B 中添加的一个文件。
USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO
E. 修改文件
以下示例增加示例 B 中添加的一个文件的大小。
- 带有 MODIFY FILE 命令的 ALTER DATABASE 只能增大文件大小，因此，如果需要缩小文件大小，则需使用 DBCC SHRINKFILE。
USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO
- 此示例将数据文件的大小缩小为 100 MB，然后指定该数量的大小。
USE AdventureWorks2012;
GO
DBCC SHRINKFILE (AdventureWorks2012_data, 100);
GO
USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO
F. 将文件移至新位置
以下示例将在示例 A 中创建的 Test1dat2 文件移至新目录中。
备注
必须先将该文件实际移至新目录中，然后才能运行此示例。 然后，停止和启动 SQL Server 的实例，或使 AdventureWorks2012 数据库 OFFLINE 再 ONLINE，以实施更改。
USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(
NAME = Test1dat2,
FILENAME = N'c:/t1dat2.ndf'
);
GO
G. 将 tempdb 移至新位置
以下示例将 tempdb 从其在磁盘上的当前位置移至另一个磁盘位置。 由于每次启动 MSSQLSERVER 服务时都会重新创建 tempdb，因此您不必实际移动数据和日志文件。 在步骤 3 中重新启动服务时，将创建这些文件。 在重新启动该服务之前，tempdb 将继续在现有位置发挥作用。
- 确定 tempdb 数据库的逻辑文件名称以及这些文件在磁盘上的当前位置。
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
- 使用 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
停止再重新启动 SQL Server的实例。
- 验证文件更改。
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
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
以下示例将一个包含 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
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
扫一扫
306
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
