Sql Server日常运维！
内容来源于网络，如有侵权，请联系我删除。
一、基础命令
查看当前数据库的版本
SELECT @@VERSION;
查看服务器部分特殊信息
select SERVERPROPERTY(N'edition') as Edition --数据版本，如企业版、开发版等
,SERVERPROPERTY(N'collation') as Collation --数据库字符集
,SERVERPROPERTY(N'servername') as ServerName --服务名
,@@VERSION as Version --数据库版本号
,@@LANGUAGE AS Language --数据库使用的语言，如us_english等
获取数据库当前时间
SELECT GETDATE() AS CurrentDateTime;
查看数据库启动的参数
sp_configure
查看所有数据库用户登录信息
sp_helplogins
查看数据库启动时间（最近一次）
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看有多少个端口
SELECT * FROM sys.dm_tcp_listener_states;
查看当前的连接数
SELECT COUNT(*) AS [Connection Count] FROM sys.dm_exec_connections;
查看各个磁盘分区的剩余空间
Exec master.dbo.xp_fixeddrives
查看数据库的磁盘使用情况
Exec sp_spaceused
查看数据库服务器各数据库日志文件的大小及利用率
DBCC SQLPERF(LOGSPACE)
查看当前占用 cpu 资源最高的会话和其中执行的语句
select spid,cmd,cpu,physical_io,memusage, (select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text from master..sysprocesses order by cpu desc,physical_io 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
看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据
select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 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
查看用户的权限
EXEC sp_helprotect;
查看当前数据库内存使用情况
select * from sys.dm_os_process_memory
查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量 -- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？ 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
查询缓存中具体的执行计划，及对应的SQL
-- 查询缓存中具体的执行计划，及对应的SQL -- 将此结果按照数据表或SQL进行统计，可以作为基线，调整索引时考虑 -- 查询结果会很大，注意将结果集输出到表或文件中 SELECT usecounts , refcounts , size_in_bytes , cacheobjtype , objtype , TEXT FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) ORDER BY objtype DESC ; GO
查看具体某个用户的权限
SELECT p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc, u.name AS user_name FROM sys.database_permissions p JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id WHERE u.name = ‘test’
查看注册时的实例名
SELECT * FROM sys.servers;
查询用户角色
select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid from sys.server_principals u, sys.server_principals g, sys.server_role_members m where g.principal_id = m.role_principal_id and u.principal_id = m.member_principal_id order by 1, 2 go
看服务器角色
select 用户名 = u.name,管理员权限 = g.name,是否在用 = u.is_disabled,MemberSID = u.sid from sys.server_principals u, sys.server_principals g, sys.server_role_members m where g.principal_id = m.role_principal_id and u.principal_id = m.member_principal_id and g.name = 'sysadmin' order by 1, 2 go
查询当前用户所有用户表
select name from sysobjects where xtype='u' order by name
查看所有的数据库
Select Name FROM Master..SysDatabases orDER BY Name
查看服务器角色相关信息
SP_HELPSRVROLE SP_HELPSRVROLEMEMBER 服务器角色 SP_HELPSRVROLE 服务器角色
查看数据库角色相关信息
SP_HELPROLE SP_HELPROLEMEMBER 数据库角色 SP_HELPROLE 数据库角色
查看用户相关信息
SP_HELPUSER SP_HELPUSER 数据库用户名
查看上次启动以来尝试的连接数
select @@connections //返回 SQL Server 自上次启动以来尝试的连接数，无论连接是成功还是失败
当前实例允许同时进行的最大用户连接数
select @@max_connections //返回 SQL Server 实例允许同时进行的最大用户连接数。返回的数值不一定是当前配置的数值
查询当前最大的连接数
SELECT value_in_use FROM sys.configurations c WHERE c.name = 'user connections'; #0表示无限制
设置修改连接数
exec sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE exec sp_configure 'user connections', 300 RECONFIGURE WITH OVERRIDE
查询当前会话超时时间
select @@lock_timeout //返回当前会话的当前锁定超时设置（毫秒）。
查询每个用户的连接数
select loginame,count(1) as Nums from sys.sysprocesses group by loginame order by 2 desc select spid,ecid,status,loginame,hostname,cmd,request_id from sys.sysprocesses where loginame='' and hostname=''
查看当前活动的实例
SELECT CURRENT_USER AS [Current User], SESSION_USER AS [Session User];
查看当前活动进程
SELECT * FROM sys.dm_exec_requests;
查看所有数据库的大小
SELECT DB_NAME(database_id) AS DatabaseName, SUM(size/128.0) AS SizeInMB, SUM(size/128.0)/1024 AS SizeInGB FROM sys.master_files GROUP BY database_id ORDER BY SizeInMB DESC;
查看某个数据库的大小
SELECT sys.databases.name AS [Database Name], CAST(SUM(size * 8 / 1024.0) AS DECIMAL(10,2)) AS [Size (MB)] FROM sys.master_files INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id WHERE sys.databases.name = 'master' GROUP BY sys.databases.name; #也可以用EXEC sp_spaceused @updateusage = N'TRUE';
查看当前数据库的日志大小
SELECT sys.databases.name AS [Database Name], CAST(size * 8 / 1024.0 AS DECIMAL(10,2)) AS [Log File Size (MB)] FROM sys.master_files INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id WHERE sys.databases.name = 'master' AND sys.master_files.type = 1;
查询当前数据库的表和视图
SELECT TABLE_NAME AS [Table/View Name], TABLE_TYPE AS [Type]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW');
查询表结构信息
sp_help 'test';
二、运维小技巧
一次性清除数据库所有表的数据（高危操作，谨慎）
CREATE PROCEDURE sp_DeleteAllData AS EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL' EXEC sp_MSForEachTable 'DELETE FROM ?' EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL' EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?' GO
数据备份与恢复
备份
BACKUP DATABASE test TO DISK = 'C:\backup\MyDatabase.bak';
恢复
RESTORE DATABASE MyDatabase FROM DISK = 'C:\backup\MyDatabase.bak';
完整数据库备份
BACKUP DATABASE test TO DISK = 'C:\Backup\MyDatabase.bak' WITH FORMAT, MEDIANAME = 'MyDatabase_Full', NAME = 'Full Backup';
差异备份
BACKUP DATABASE test TO DISK = 'C:\Backup\MyDatabase_diff.bak' WITH DIFFERENTIAL, FORMAT, MEDIANAME = 'MyDatabase_Diff', NAME = 'Differential Backup';
事务日志备份
BACKUP LOG test TO DISK = 'C:\Backup\MyDatabase_log.trn' WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
还原数据库
RESTORE DATABASE test FROM DISK = 'C:\Backup\MyDatabase.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;
创建账户及数据库用户
#创建账户 CREATE LOGIN test WITH PASSWORD = '123123'; #创建数据库用户并映射到登录名 CREATE USER test FOR LOGIN test; ALTER ROLE db_datareader ADD MEMBER test; -- 给予读权限 ALTER ROLE db_datawriter ADD MEMBER test; -- 给予写权限
SQL优化相关、执行时间
SELECT creation_time N'语句编译时间' ,last_execution_time N'上次执行时间' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' ,execution_count N'执行次数' ,total_worker_time/1000 N'所用的CPU总时间ms' ,total_elapsed_time/1000 N'总花费时间ms' ,(total_elapsed_time / execution_count)/1000 N'平均时间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) N'执行语句' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE 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) NOT LIKE '%fetch%' ORDER BY total_elapsed_time / execution_count DESC;
查看job运行持续时间
SELECT [T1].[job_id] ,[T1].[name] AS [job_name] ,[T2].[run_status] ,[T2].[run_date] ,[T2].[run_time] ,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime] ,[T2].[run_duration] ,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s] FROM [dbo].[sysjobs] AS T1 INNER JOIN [dbo].[sysjobhistory] AS T2 ON [T2].[job_id] = [T1].[job_id] WHERE [T1].[enabled] = 1 AND [T2].[step_id] = 0 AND [T2].[run_duration] >= 1 and [T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData' ORDER BY [T2].[job_id] ASC ,[T2].[run_date] ASC GO
从所有缓存中释放所有未使用的缓存条目
DBCC FREESYSTEMCACHE('ALL');
查询、解除死锁
--查询表死锁信息 select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks where resource_type = 'OBJECT' dbcc opentran --查看死锁的详细信息、执行的sql语句 exec sp_who2 53 --exec sp_who 53 DBCC inputbuffer (53) --解除死锁 kill 53
查询SQL Server根据CPU消耗列出前5个最差性能的查询
-- Worst performing CPU bound queries SELECT TOP 5 st.text, qp.query_plan, qs.* FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_worker_time DESC GO
查询数据库中各数据表大小
-- ============================================= -- 描 述：更新查询数据库中各表的大小，结果存储到数据表中 -- ============================================= --查询是否存在结果存储表 IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN --不存在则创建 CREATE TABLE temp_tableSpaceInfo (name NVARCHAR(128), rows char(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18)) END --清空数据表 DELETE FROM temp_tableSpaceInfo --定义临时变量在遍历时存储表名称 DECLARE @tablename VARCHAR(255) --使用游标读取数据库内所有表表名 DECLARE table_list_cursor CURSOR FOR SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name --打开游标 OPEN table_list_cursor --读取第一条数据 FETCH NEXT FROM table_list_cursor INTO @tablename --遍历查询到的表名 WHILE @@FETCH_STATUS = 0 BEGIN --检查当前表是否为用户表 IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN --当前表则读取其信息插入到表格中 EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename END --读取下一条数据 FETCH NEXT FROM table_list_cursor INTO @tablename END --释放游标 CLOSE table_list_cursor DEALLOCATE table_list_cursor SELECT *,replace(reserved,'KB','')/1024 数据表大小M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc drop table temp_tableSpaceInfo
显示如何依据I/O消耗来找出你性能最差的查询
-- Worst performing I/O bound queries SELECT TOP 5 st.text, qp.query_plan, qs.* FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_logical_reads DESC GO
压缩数据库、文件、日志
DBCC ShrinkFile(‘数据库名’, targetsize); /* 收缩数据库文件 */ DBCC ShrinkFile(‘数据库名_log’, targetsize); /* 收缩日志文件 */ Targetsize：单位为兆，必须为整数，DBCC SHRINKFILE 尝试将文件收缩到指定大小。 DBCC SHRINKFILE 不会将文件收缩到小于“实际使用的空间”大小，例如“分配空间”为10M，“实际使用空间”为6M，当制定targetsize为1时，则将该文件收缩到6M，不会将文件收缩到1M。 --收缩数据库 DBCC SHRINKDATABASE(数据库名，百分比) 百分比：即“收缩后文件中的最大可用空间”，取值范围“大于等于0， 小于100%”，实际使用中设为0即可。
查询数据库表字段各项属性信息，便于直接复制导出excel表
SELECT 表名 = Case When A.colorder=1 Then D.name Else '' End, 表说明 = Case When A.colorder=1 Then isnull(F.value,'') Else '' End, 字段序号 = A.colorder, 字段名 = A.name, 字段说明 = isnull(G.[value],''), 标识 = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End, 主键 = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end, 类型 = B.name, 占用字节数 = A.Length, 长度 = COLUMNPROPERTY(A.id,A.name,'PRECISION'), 小数位数 = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0), 允许空 = Case When A.isnullable=1 Then '√'Else '' End, 默认值 = isnull(E.Text,'') FROM syscolumns A Left Join systypes B On A.xusertype=B.xusertype Inner Join sysobjects D On A.id=D.id and D.xtype='U' and D.name<>'dtproperties' Left Join syscomments E on A.cdefault=E.id Left Join sys.extended_properties G on A.id=G.major_id and A.colid=G.minor_id Left Join sys.extended_properties F On D.id=F.major_id and F.minor_id=0 --where d.name='OrderInfo' --如果只查询指定表,加上此条件 Order By A.id,A.colorder
数据库缓存清理
CREATE PROCEDURE [dbo].ClearMemory AS BEGIN --清除所有缓存 DBCC DROPCLEANBUFFERS --打开高级配置 exec sp_configure 'show advanced options', 1 --设置最大内存值，清除现有缓存空间 exec sp_configure 'max server memory', 25600 EXEC ('RECONFIGURE') --设置等待时间 WAITFOR DELAY '00:00:01' --重新设置最大内存值 EXEC sp_configure 'max server memory',40960 EXEC ('RECONFIGURE') --关闭高级配置 exec sp_configure 'show advanced options',0 END GO
三、日常运维操作
数据库用户、权限操作
USE [master] GO --待确认账号密码 CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE PIMS go CREATE USER [NDIT] FOR LOGIN [NDIT] GO --大权限， 如果是指定的部分表，不执行这个，如果是所有内容都可以读，用此脚本 --EXEC sp_addrolemember N'db_datareader', N'NDIT' --GO --指定特定表名赋予新增/更新/查询 DECLARE @Sql NVARCHAR(max) SET @Sql='' --table --SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2'); --view --SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2'); --procedure --SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2'); PRINT @Sql EXEC(@Sql) go --禁用登陆帐户 alter login NDIT disable --启用登陆帐户 alter login NDIT enable --登陆帐户改名 alter login NDIT with name=dba_tom --登陆帐户改密码： alter login NDIT with password='aabb@ccdd' --数据库用户改名： alter user NDIT with name=dba_tom --更改数据库用户 defult_schema： alter user NDIT with default_schema=sales --删除数据库用户： drop user NDIT --删除 SQL Server登陆帐户： drop login NDIT
原文链接：Sql Server日常运维看我这篇就够了！ (qq.com)
冷门数据库的一键巡检脚本分享，使用前请在测试环境先测试。
参考地址：https://mp.weixin.qq.com/s/QmGqkeJbqboDMyYEroa0Wg
1、数据库 --所有数据库的大小 exec sp_helpdb --所有数据库的状态 select name, user_access_desc, --用户访问模式 state_desc, --数据库状态 recovery_model_desc, --恢复模式 page_verify_option_desc, --页检测选项 log_reuse_wait_desc --日志重用等待 from sys.databases --某个数据库的大小:按页面计算空间，有性能影响，基本准确，有时不准确 use test go exec sp_spaceused go --可以@updateusage = 'true',会运行dbcc updateusage exec sp_spaceused @updateusage = 'true' --对某个数据库,显示目录视图中的页数和行数错误并更正 DBCC UPDATEUSAGE('test')
数据文件
--查看某个数据库中的所有文件及大小 sp_helpfile --查看所有文件所在数据库、路径、状态、大小 select db_name(database_id) dbname, type_desc, --数据还是日志 name, --文件的逻辑名称 physical_name, --文件的物理路径 state_desc, --文件状态 size * 8.0/1024 as '文件大小（MB）' from sys.master_files --按区extent计算空间，没有性能影响，基本准确,把TotalExtents*64/1024,单位为MB --同时也适用于计算tempdb的文件大小，但不包括日志文件 dbcc showfilestats
日志文件
--查看日志文件所在数据库、路径、状态、大小 select db_name(database_id) dbname, type_desc, --数据还是日志 name, --文件的逻辑名称 physical_name, --文件的物理路径 state_desc, --文件状态 size * 8.0/1024 as '文件大小（MB）' from sys.master_files where type_desc = 'LOG' --所有数据库的日志的大小,空间使用率 dbcc sqlperf(logspace)
数据文件、日志文件的I/O统计信息
--数据和日志文件的I/O统计信息,包含文件大小 select database_id, file_id, file_handle, --windows文件句柄 sample_ms, --自从计算机启动以来的毫秒数 num_of_reads, num_of_bytes_read, io_stall_read_ms, --等待读取的时间 num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, --用户等待文件完成I/O操作所用的总时间 size_on_disk_bytes --文件在磁盘上所占用的实际字节数 from sys.dm_io_virtual_file_stats(db_id('test'), --数据库id 1 ) --数据文件id union all select database_id, file_id, file_handle, --windows文件句柄 sample_ms, --自从计算机启动以来的毫秒数 num_of_reads, num_of_bytes_read, io_stall_read_ms, --等待读取的时间 num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, --用户等待文件完成I/O操作所用的总时间 size_on_disk_bytes --文件在磁盘上所占用的实际字节数 from sys.dm_io_virtual_file_stats( db_id('test'), --数据库id 2 ) --日志文件id
对象，包括：表、索引、索引视图等
--不一定准确:某个表的行数,保留大小，数据大小，索引大小，未使用大小 exec sp_spaceused @objname ='temp_lock' --准确:但有性能影响 exec sp_spaceused @objname ='temp_lock', @updateusage ='true' --按页统计，没有性能影响，有时不准确 /*====================================================== 一次计算多个对象的空间使用情况 sys.dm_db_partition_stats返回当前数据库中每个分区(表和索引)的页和行计数信息 ========================================================*/ select o.name, sum(p.reserved_page_count) as reserved_page_count, --保留页，包含表和索引 sum(p.used_page_count) as used_page_count, --已使用页，包含表和索引 sum(case when p.index_id <2 then p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count else p.lob_used_page_count + p.row_overflow_used_page_count end) as data_pages, --数据页,包含表中数据、索引中的lob数据、索引中的行溢出数据 sum(case when p.index_id < 2 then p.row_count else 0 end) as row_counts --数据行数，包含表中的数据行数，不包含索引中的数据条目数 from sys.dm_db_partition_stats p inner join sys.objects o on p.object_id = o.object_id where p.object_id= object_id('表名') group by o.name --按页或区统计，有性能影响，准确 --显示当前数据库中所有的表或视图的数据和索引的空间信息 --包含：逻辑碎片、区碎片(碎片率)、平均页密度 dbcc showcontig(temp_lock) --SQL Server推荐使用的动态性能函数，准确 select * from sys.dm_db_index_physical_stats( db_id('test'), --数据库id object_id('test.dbo.temp_lock'), --对象id null, --索引id null, --分区号 'limited' --default,null,'limited','sampled','detailed',默认为'limited' --'limited'模式运行最快，扫描的页数最少,对于堆会扫描所有页,对于索引只扫描叶级以上的父级页 --'sampled'模式会返回堆、索引中所有页的1%样本的统计信息，如果少于1000页，那么用'detailed'代替'sampled' --'detailed'模式会扫描所有页,返回所有统计信息 ) --查找哪些对象是需要重建的 use test go if OBJECT_ID('extentinfo') is not null drop table extentinfo go create table extentinfo ( [file_id] smallint, page_id int, pg_alloc int, ext_size int, obj_id int, index_id int, partition_number int, partition_id bigint, iam_chain_type varchar(50), pfs_bytes varbinary(10) ) go /*==================================================================== 查询到的盘区信息是数据库的数据文件的盘区信息，日志文件不以盘区为单位 命令格式: DBCC EXTENTINFO（dbname,tablename,indexid） DBCC EXTENTINFO('[test]','extentinfo',0) ======================================================================*/ insert extentinfo exec('dbcc extentinfo(''test'') ') go --每一个区有一条数据 select file_id, obj_id, --对象ID index_id, --索引id page_id, --这个区是从哪个页开始的,也就是这个区中的第一个页面的页面号 pg_alloc, --这个盘区分配的页面数量 ext_size, --这个盘区包含了多少页 partition_number, partition_id, iam_chain_type, --IAM链类型:行内数据,行溢出数据,大对象数据 pfs_bytes from extentinfo order by file_id, OBJ_ID, index_id, partition_id, ext_size /*===================================================================================================== 数据库的数据文件的盘区信息,通过计算每个对象理论上区的数量和实际数量,如果两者相差很大, 那就应该重建对象. 1.每一条记录就是一个区 2.如果pg_alloc比ext_size小，也就是实际每个区分配的页数小于理论上这个区的页数， 那么就会多一条记录，把本应该属于这个区的页放到多出来的这条记录对应的区中， 那么原来只有一条记录(也就是一个区)，现在就有2条记录(也就是2个区)， 导致实际的区数量2大于理论上的区数量1. ========================================================================================================*/ select file_id, obj_id, index_id, partition_id, ext_size, count(*) as '实际区的个数', sum(pg_alloc) as '实际包含的页数', ceiling(sum(pg_alloc) * 1.0 / ext_size) as '理论上的区的个数', ceiling(sum(pg_alloc) * 1.0 / ext_size) / count(*) * 100.00 as '理论上的区个数 / 实际区的个数' from extentinfo group by file_id, obj_id, index_id, partition_id, ext_size having ceiling(sum(pg_alloc)*1.0/ext_size) < count(*) --过滤: 理论上区的个数 < 实际区的个数,也就是百分比小于100%的 order by partition_id, obj_id, index_id, [file_id]
tempdb数据库
--tempdb数据库的空间使用 /*====================================================== tempdb中包含的对象： 用户对象：是用户显式创建的，这些对象位于用户会话的作用域， 可以位于创建对象的例程(存储过程、触发器、函数)的作用域中。 1.用户定义的表、索引 2.系统表、索引 3.全局临时表、索引 4.局部临时表、索引 5.表变量 6.表值函数中返回的表 内部对象：是根据需要由SQL Server数据库引擎创建的，用于处理SQL Server语句， 内部对象可以在语句作用域中创建、删除。 每个内部对象至少需要9个页面，一个IAM页，一个区包含了8个页。 1.游标、假脱机操作、临时的大型对象(LOB)，存储的工作表 2.哈希联接、哈希聚合操作的工作文件 3.如果设置了sort_in_tempdb选项，那么创建、重新生成索引的重建排序结果存放在tempdb； group by、order by、union操作的中间结果。 版本存储区：是数据页的集合，包含了支持行版本控制功能的所需的数据，主要支持快照事务隔离级别， 以及一些其他的提高数据库并发性能的新功能。 1.公用版本存储区：在使用快照隔离级别、已提交读隔离级别的数据库中，由数据修改事务生成的行版本。 2.联机索引生成版本存储区：为了实现联机索引操作而为数据修改事务生成的行版本， 多个活动结果集，after触发器生成的行版本。 上面也提到了，由于sys.allocation_units和sys.partitions视图没有记录tempdb中的内部对象、版本存储区 所以这2个视图和sp_spaceused，不能准确反应出tempdb的空间使用。 分析tempdb现有的工作负载: 1.设置tempdb的自动增长 2.通过模拟单独的查询、工作任务，监控tempdb空间使用 3.通过模拟执行一些系统维护操作(重新生成索引),监控tempdb空间使用 4.根据2和3中tempdb的空间使用量,预测总工作负荷会使用的空间,并针对任务的并发度调整这个值. 5.根据4得到的值,设置生成环境中tempdb的初始大小,并开启自动增长. 另外,tempdb的文件个数和大小,不仅需要满足实际使用需要,还要考虑性能优化. 监控tempdb的空间使用方法: 1.可以通过SQL Trace来跟踪,但是由于不能预期造成大量使用tempdb语句在什么时候运行, 而且SQL Trance操作比较昂贵,如果一直开着会产生大量的跟踪文件,对硬盘的负担也比较重,一般不用. 2.轻量级的监控是通过一定时间间隔运行能够监控系统运行的dbcc命令、动态性能视图-函数， 把结果记录在文件中，这对于很繁忙的系统是不错的选择。 =======================================================*/ Select DB_NAME(database_id) as DB, max(FILE_ID) as '文件id', SUM (user_object_reserved_page_count) as '用户对象保留的页数', ----包含已分配区中的未使用页数 SUM (internal_object_reserved_page_count) as '内部对象保留的页数', --包含已分配区中的未使用页数 SUM (version_store_reserved_page_count) as '版本存储保留的页数', SUM (unallocated_extent_page_count) as '未分配的区中包含的页数', --不包含已分配区中的未使用页数 SUM(mixed_extent_page_count) as '文件的已分配混合区中:已分配页和未分配页' --包含IAM页 From sys.dm_db_file_space_usage Where database_id = 2 group by DB_NAME(database_id) --能够反映当时tempdb空间的总体分配,申请空间的会话正在运行的语句 SELECT t1.session_id, t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count, t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count, t.text from sys.dm_db_session_space_usage t1 --反映每个session的累计空间申请 inner join sys.dm_exec_sessions as t2 on t1.session_id = t2.session_id inner join sys.dm_exec_requests t3 on t2.session_id = t3.session_id cross apply sys.dm_exec_sql_text(t3.sql_handle) t where t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0 --返回tempdb中页分配和释放活动， --只有当任务正在运行时，sys.dm_db_task_space_usage才会返回值 --在请求完成时，这些值将按session聚合体现在SYS.dm_db_session_space_usage select t.session_id, t.request_id, t.database_id, t.user_objects_alloc_page_count, t.internal_objects_dealloc_page_count, t.internal_objects_alloc_page_count, t.internal_objects_dealloc_page_count from sys.dm_db_task_space_usage t inner join sys.dm_exec_sessions e on t.session_id = e.session_id inner join sys.dm_exec_requests r on t.session_id = r.session_id and t.request_id = r.request_id # 导入必要的模块 Import-Module SQLPS -DisableNameChecking # 设置SQL Server实例名称和数据库名称 $serverName = "YourServerName" $databaseName = "YourDatabaseName" # 检查数据库大小 Write-Output "=== Checking Database Size ===" $sqlCommand = "USE $databaseName; EXEC sp_spaceused;" Invoke-Sqlcmd -Query $sqlCommand -ServerInstance $serverName # 检查最后备份时间 Write-Output "=== Checking Last Backup Time ===" $sqlCommand = @" SELECT database_name AS DatabaseName, MAX(backup_finish_date) AS LastBackupTime FROM msdb.dbo.backupset WHERE type = 'D' GROUP BY database_name; "@ Invoke-Sqlcmd -Query $sqlCommand -ServerInstance $serverName # 检查数据库文件的增长情况 Write-Output "=== Checking Database File Growth ===" $sqlCommand = @" SELECT name AS [File Name], physical_name AS [Physical Name], size*8/1024 AS [Size (MB)], CASE max_size WHEN -1 THEN 'Unlimited' ELSE CAST(max_size*8/1024 AS NVARCHAR(10)) END AS [Max Size (MB)], growth AS [Growth], is_percent_growth AS [Is Percent Growth] FROM sys.master_files WHERE database_id = DB_ID('$databaseName'); "@ Invoke-Sqlcmd -Query $sqlCommand -ServerInstance $serverName # 检查磁盘使用情况 Write-Output "=== Checking Disk Usage ===" Get-WmiObject -Class Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} | Select-Object DeviceID, FreeSpace, Size | Format-Table -AutoSize # 检查数据库死锁情况 Write-Output "=== Checking Deadlocks ===" $sqlCommand = "SELECT * FROM sys.dm_tran_locks WHERE resource_type='DATABASE';" Invoke-Sqlcmd -Query $sqlCommand -ServerInstance $serverName # 输出巡检完成信息 Write-Output "=== Inspection Complete ==="
