收缩迁移
1. 收缩迁移tempdb
--迁移tempdb的位置
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQL Server\DATA\\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\SQL Server\DATA\templog.ldf');
GO
--删除tempdb多余的文件
use tempdb
go
dbcc shrinkfile (temp2,emptyfile); --(把temp3里的数据迁移至组内其他文件内,并使其暂时不可写)
go
ALTER DATABASE tempdb
REMOVE FILE temp2;
GO
一、EXEC的使用
1. 执行存储过程
// 执行存储过程无参数
EXEC 存储过程名
// 执行存储过程有参数
EXEC 存储过程名 参数1,参数2,...
2. 执行动态Sql语句(不输出变量)
// 执行动态Sql 语句
DECLARE @sql NVARCHAR(max)
set @sql='......'
--记得加括号要不然会认为是执行存储过程,会报错
EXEC (@sql)
3. 执行动态Sql语句(输出变量)
// EXEC sp_executesql 执行动态Sql 语句
DECLARE @count INT
DECLARE @id INT=1
SET @sql=' select @count=count(*) from 表 where id=@id
EXEC sp_executesql @sql,N'@count int out',@count OUT,@id
--外面就能得到@count 的值了
EXEC (@sql)
二、DBCC命令大全
DBCC DROPCLEANBUFFERS:从缓冲池中删除所有缓存,清除缓冲区
在进行测试时,使用这个命令可以从SQLSERVER的数据缓存data cache(buffer)清除所有的测试数据,以保证测试的公正性。
需要注意的是这个命令只移走干净的缓存,不移走脏缓存。由于这个原因,在执行这个命令前,应该先执行CheckPoint,将所有脏的缓存写入磁盘,
这样在运行DBCC RROPCLEANBUFFERS 时,可以保证所有的数据缓存被清理,而不是其中的一部分。
DBCC CacheStats:显示存在于当前buffer Cache中的对象的信息,例如:hit rates,编译的对象和执行计划
DBCC ErrorLog :如果很少重启mssqlserver服务,那么服务器的日志(不是数据库事务日志)会增长得很快,而且打开和查看日志的速度也会很慢
使用这个命令,可以截断当前的服务器日志,主要是生成一个新的日志。可以考虑设置一个调度任务,每周执行这个命令自动截断服务器日志。
使用存储过程sp_cycle_errorlog也可以达到同样的目的
1. 帮助类命令
DBCC HELP('?') :查询所有的DBCC命令
DBCC HELP('命令') :查询指定的DBCC命令的语法说明
DBCC USEROPTIONS :返回当前连接的活动(设置)的SET选项
2. 检查验证类命令
DBCC CHECKALLOC('数据库名称') :检查指定数据库的磁盘空间分配结构的一致性
DBCC CHECKCATALOG ('数据库名称') :检查指定数据库的系统表内和系统表间的一致性
DBCC CHECKCONSTRAINTS ('tablename') :检查指定表上的指定约束或所有约束的完整性
DBCC CHECKDB :检查数据库中的所有对象的分配和结构完整性
DBCC CHECKFILEGROUP :检查指定文件组中所有表在当前数据库中的分配和结构完整性
DBCC CHECKTABLE :检查指定表或索引视图的数据、索引及test、ntest和image页的完整性
DBCC CHECKIDENT :如果存在大量数据删除,考虑在删除后,使用 dbcc checkident 重置一下自增值
参考: http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/8fa3e3a8-2ff2-4a68-be3e-92e76c380ef9/
检查指定的当前标识值
DBCC SQLPERF(UMSSTATS):最关键的一个参考数据num runnable:表明当前有多少个线程再等待运行,如果大于等于2,考虑CPU达到瓶颈
3. 维护类命令
DBCC CLEANTABLE ('db_name','table_name') :回收Alter table drop column语句删除可变长度列或text
DBCC DBREINDEX :重建指定数据库的一个或多个索引 跟ALTER INDEX REBUILD差不多
DBCC INDEXDEFRAG:对表或视图上的索引和非聚集索引进行碎片整理
DBCC PINTABLE (db_id,object_id) :将表数据驻留在内存中
查看哪些表驻留在内存的方法是:0:没有驻留 ,1:驻留
1 USE [GPOSDB]
2 GO
3 SELECT OBJECTPROPERTY(OBJECT_ID('dbo.SystemPara'), 'tableispinned')
DBCC UNPINTABLE (db_id,object_id) :撤消驻留在内存中的表
DBCC SHRINKDATABASE(db_id,int) :收缩指定数据库的数据文件和日志文件大小
DBCC SHRINKFILE(file_name,int):收缩相关数据库的指定数据文件和日志文件大小
4. 性能调节命令
DBCC dllname(FREE) :在内存中卸载指定的扩展过程动态链接库(dll)
sp_helpextendedproc 查看加载的扩展PROC
DBCC DROPCLEANBUFFERS :从缓冲池中删除所有缓冲区
DBCC FREEPROCCACHE :从执行计划缓冲区删除所有缓存的执行计划
DBCC INPUTBUFFER :显示从客户机发送到服务器的最后一个语句
DBCC OPENTRAN (db_name) :查询某个数据库执行时间最久的事务,由哪个程序拥有
DBCC SHOW_STATISTICS :显示指定表上的指定目标的当前统计信息分布
DBCC SHOWCONTIG :显示指定表的数据和索引的碎片信息
DBCC SQLPERF (logspace) :查看各个DB的日志情况
(iostats) 查看IO情况
(threads) 查看线程消耗情况
--返回多种有用的统计信息:
DBCC CACHESTATS :显示SQL Server 2000内存的统计信息
DBCC CURSORSTATS :显示SQL Server 2000游标的统计信息
DBCC MEMORYSTATS :显示SQL Server 2000内存是如何细分的
DBCC SQLMGRSTATS :显示缓冲区中先读和预读准备的SQL语句
5. 未公开的命令
DBCC ERRLOG :初始化SQL Server 2000的错误日志文件
DBCC FLUSHPROCINDB (db_id) :清除SQL Server 2005服务器内存中的某个数据库的存储过程缓存内容
DBCC BUFFER (db_name,object_name,int(缓冲区个数)) :显示缓冲区的头部信息和页面信息
DBCC DBINFO (db_name) :显示数据库的结构信息
DBCC DBTABLE :显示管理数据的表(数据字典)信息
DBCC IND (db_name,table_name,index_id) :查看某个索引使用的页面信息
DBCC REBUILDLOG :重建SQL Server 2000事务日志文件
DBCC LOG (db_name,3) (-1~4) :查看某个数据库的事物日志信息 显示格式可以为:-1,0,1,2,3,4 每个数字代表不同的格式
DBCC PAGE :查看某个数据库数据页面信息
DBCC PROCBUF :显示过程缓冲池中的缓冲区头和存储过程头
DBCC PRTIPAGE :查看某个索引页面的每行指向的页面号
DBCC PSS (user,spid,1) :显示当前连接到SQL Server 2000服务器的进程信息
DBCC RESOURCE :显示服务器当前使用的资源情况
DBCC TAB (db_id,object_id) :显示数据页面的结构
6. DBCC跟踪标记
跟踪标记用于临时设置服务器的特定特征或关闭特定行为,常用于诊断性能问题或调试存储过程或复杂的系统:
DBCC TRACEON (3604) :打开跟踪标记
DBCC TRACEOFF :关闭跟踪标记
DBCC TRACESTATS :查看跟踪标记状态
7. 官方使用DBCC的建议
1、在系统使用率较低时运行 CHECKDB。
2、请确保未同时执行其它磁盘 I/O 操作,例如磁盘备份。
3、将 tempdb 放到单独的磁盘系统或快速磁盘子系统中。
4、允许 tempdb 在驱动器上有足够的扩展空间。 使用带有 ESTIMATE ONLY 的 DBCC 估计 tempdb 将需要多少空间。
5、避免运行占用大量 CPU 的查询或批处理作业。
6、在 DBCC 命令运行时,减少活动事务。
7、使用 NO_INFOMSGS 选项减少一些信息的输出。
8、考虑使用带有 PHYSICAL_ONLY 选项的 DBCC CHECKDB 来检查页和记录的物理结构。
PHYSICAL_ONLY 选项:只检查物理错误,不检查逻辑错误
物理错误比逻辑出更严重,因为物理错误一般SQLSERVER都不能修复的,而逻辑错误大部分SQLSERVER都可以修复
三、SQL Server 常用管理语句整理
1. 参数和权限
--SYS.DM_EXEC_CONNECTIONS保存了所有的连接信息
SELECT * FROM SYS.DM_EXEC_CONNECTIONS
--升级前,查询服务器名、实例名、版本号
select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version
--查看当前用户
select system_user
--根据id号查询某个数据库名
SELECT DB_NAME(18)
--根据id号查询某个对象名
SELECT OBJECT_NAME(1769220894)
--sqlserver查看实例级别的信息,使用SERVERPROPERTY函数
select SERVERPROPERTY ('propertyname')
--查看实例级别的某个参数XX的配置
select * from sys.configurations where name='XX'
--更改实例级别的某个参数XX的值
sp_configure 'XX','0'
RECONFIGURE WITH OVERRIDE
sp_configure --显示或更改当前服务器的全局配置设置。
RECONFIGURE表示SQL Server不用重新启动就立即生效
使用sp_configure更改设置时,请使用RECONFIGURE语句使更改立即生效,否则更改将在SQL Server重新启动后生效。RECONFIGURE后面加WITH OVERRIDE表示不管这个值是不是符合要求都会生效,比如recovery interval的范围值是 10-60 ,对应sys.configurations.minimum是10、sys.configurations.maximum是60,如果sp_configure 'recovery interval', 75设置为75,超过了这个10-60规范,但是要让75生效,则必须加上WITH OVERRIDE
====================权限=========================
--用户被grant这样操作赋予的权限
use dbname
exec sp_helprotect @username = 'username'
--授予某个用户执行某个数据库的sp的权限
use dbname
grant execute to "username"
查看某个数据下,哪些用户拥有哪些角色权限
USE DB;
WITH CTE AS
(
SELECT u.name AS UserName,
g.name AS dbRole,
'√' as 'flag'
FROM sys.database_principals u
INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id
INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id
)
SELECT * FROM CTE PIVOT(MAX(flag) FOR dbRole IN ([public],
[db_owner],
[db_accessadmin],
[db_securityadmin],
[db_ddladmin],
[db_backupoperator],
[db_datareader],
[db_datawriter],
[db_denydatareader],
[db_denydatawriter])) as rg;
--查询实例级别的角色名称
select * from sys.server_principals where type='R'
--查询某个数据库拥有的角色名称
select * from sys.database_principals where type='R'
每个数据拥有的角色是不一样的,比如msdb数据库中引入了SQLAgentUserRole、SQLAgentReaderRole、SQLAgentOperatorRole,而其他数据库并没有这几个角色
--查询某数据库下,角色拥有的成员信息(比如db_datareader这个角色,哪些登录名拥有了这个角色)
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;
--查看SQLServer用户哪些权限是使用grant命令操作赋予的
use dbname
exec sp_helprotect @username = 'username'
2. 数据库下对象
- sqlserver没有系统表可以查询所有数据库下面对象
以下内容只能在当前数据库下面查:
1.sys.all_objects、sys.sysobjects这种的视图,在每个数据库的系统视图下面都有:
select * from sys.all_objects
--查询当前数据库的所有架构范围的对象
select * from sys.sysobjects
--查询当前数据库的所有对象
2.sys.databases、sys.sysdatabases这种的视图,在每个数据库的系统视图下面都有:
select * from sys.databases
--在当前数据库下可以查询到所有数据库信息,包含是否on状态
select * from sys.sysdatabases
--在当前数据库下可以查询到所有数据库信息,不包含是否on状态,这个系统视图会在后续的版本中删除
sys.processes --没有这个视图
select * from sys.sysprocesses
--在当前数据库下可以查询所有正在SQL Server 实例上运行的进程的相关信息,也就是所有数据库上的线程,这个系统视图会在后续的版本中删除
3.sys.database_files、sys.master_files全局系统视图、单个数据库系统视图,在每个数据库的系统视图下面都有:
sys.database_files
--每个存储在数据库本身中的数据库文件在表中占用一行。 这是一个基于每个数据库的视图。
sys.master_files
--master 数据库中的每个文件对应一行。 这是一个系统范围视图。
4.一些只存在msdb的系统表,而非系统视图
dbo.backupset
dbo.log_shipping_secondary
dbo.restorehistory
dbo.sysjobs
dbo.sysjobhistory
--这些系统表只存在msdb数据库,使用的时候必须加上msdb前缀
5.sp_lock、sp_who、sp_who2、sp_helptext等一些系统存储过程存在于每个数据库中
报告有关锁的信息,会显示实例里面的所有数据库的锁信息、堵塞信息
sp_lock
6.提供有关当前用户、 会话和进程的实例中的信息,可以看到会话的状态running、SUSPENDED、sleeping、rollback,sp_who2通过CPUTime、DiskIO可以判断对应的transaction是否很大
sp_who
sp_who2
sp_who2 active (可选参数LoginName, 或active代表活动会话数)
--其中sp_who2除了显示sp_who的输出信息外,,还显示下面的信息:
CPUTime (进程占用的总CPU时间)
DiskIO (进程对磁盘读的总次数)
LastBatch (客户最后一次调用存储过程或者执行查询的时间)
ProgramName (用来初始化连接的应用程序名称,或者主机名)
7.查看某个存储过程的内容
sp_helptext pro_name
DBCC INPUTBUFFER
8.显示某个线程号发送到sqlserver数据库的最后一个语句
DBCC INPUTBUFFER (249)
--假设查询到249被锁给堵塞了,执行上面的可以查到被堵塞的SQL语句
9.查看某个数据库中是否存在活动事务,有活动事务就一定会写日志,显示结果为最早的一个事务的ID
DBCC OPENTRAN (dbname)
监视日志空间
DBCC SQLPERF (LOGSPACE)
查找无法重用日志中的空间的原因(日志无法截断导致日志文件越来越大,但是可用空间很小,无法收缩)
select name,log_reuse_wait_desc from sys.databases
10.查看虚拟日志文件信息
DBCC LOGINFO
结果有多少行,代表有多少虚拟日志文件,活动的虚拟日志文件的状态(status)为2
修复msdb数据库,比如ssms页面sql server agent丢失或看不了job view history等功能,说明msdb坏了,需要修复
dbcc checkdb (msdb);
在您当前连接到的 SQL Server 数据库中生成一个手动检查点
CHECKPOINT [ checkpoint_duration ]
--checkpoint_duration表示以秒为单位指定手动检查点完成所需的时间,一般不使用这个参数,让数据库自己控制
11.查看数据库各种设置
select name,State,user_access,is_read_only,recovery_model from sys.databases
查看某个数据库中是否存在会话
select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname')
3. 事务与阻塞
查询当前阻塞的所有请求
select * from sys.sysprocesses where blocked>0
或
SELECT t1.resource_type,db_name(t1.resource_database_id),t1.resource_associated_entity_id,t1.request_mode,
t1.request_session_id,t2.blocking_session_id,t2.wait_duration_ms
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
或
select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作
from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid
或
SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type,
[Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2,
( CASE
WHEN er.statement_end_offset = -1
THEN
LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE
er.statement_end_offset
END
- er.statement_start_offset)
2)
qt.text,program_name,Hostname,nt_domain,start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt
WHERE session_Id > 50 /* Ignore system spids.*/
AND sp.blocked>0 AND session_Id NOT IN (@@SPID)
或
SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';
--sys.dm_exec_requests返回SQL Server 中正在执行的每个请求的信息
12.查看哪些表被锁了,以及这些表被哪个进程锁了:
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC
查询某个job是否被堵塞
select b.name,a.* from master..sysprocesses a,msdb.dbo.sysjobs b where SUBSTRING(REPLACE(a.PROGRAM_NAME, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)=master.dbo.fn_varbintohexstr(CONVERT(varbinary(16), b.job_id)) and b.name='jobname'
13.检查SQL Agent是否开启
IF EXISTS (
SELECT TOP 1 1
FROM sys.sysprocesses
WHERE program_name = 'SQLAgent - Generic Refresher'
)
SELECT 'Running'
ELSE
SELECT 'Not Running'
14.查看活动线程执行的sql语句,并生成批量杀掉的语句
select 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REPLACE(hostname,' ','') as hostname ,replace(program_name,' ','') as program_name
,REPLACE(loginame, ' ', '') AS loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime
,a.status,Replace(b.text,'''','''') as sqlmessage,cpu
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where a.status<>'sleeping' AND a.spid<>@@SPID
--查询所有job的状态是否running
SELECT sj.Name,
CASE
WHEN sja.start_execution_date IS NULL THEN 'Not running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) order by RunStatus desc;
--锁表的四种用法:
TABLOCKX
SELECT * FROM table WITH (TABLOCKX)
查询过程中,其他会话无法查询、更新此表,直到查询过程结束
TABLOCK
SELECT * FROM table WITH (TABLOCK)
查询过程中,其他会话可以查询,但是无法更新此表,直到查询过程结束
HOLDLOCK
SELECT * FROM table WITH (HOLDLOCK)
查询过程中,其他会话可以查询,但是无法更新此表,直到查询过程结束
NOLOCK
SELECT * FROM table WITH (NOLOCK)
查询过程中,其他会话可以查询、更新此表
--查看XX数据库的隔离级别
(数据库隔离级别有read committed和read committed snapshot, 前者表示读已经提交的事务之后的数据,如果一个事务没有提交,则当前事务将被阻塞。后者表示通过row versioning获得旧的数据。所以前者的情况下,如果一个select语句读取的数据正在被修改,但是这个修改还没有提交,那么这个select会被堵塞。)
use XX
DBCC USEROPTIONS ;
--修改XX数据库的隔离级别为READ_COMMITTED_SNAPSHOT
ALTER DATABASE XX SET READ_COMMITTED_SNAPSHOT ON ;
--查询哪些job在某个时间段内启动
如下查询5:00和5:15启动的job
select B.name job_name,C.name job_schedule_name,C.active_start_time job_schedule_start_time from dbo.sysjobschedules A , dbo.sysjobs B,dbo.sysschedules C
where A.job_id=B.job_id and A.schedule_id=c.schedule_id and (
c.active_start_time between 50000 and 50059 or c.active_start_time between 51500 and 51559 )
and B.enabled=1
order by 1
--查询某个数据库比如dba,当前产生日志最多的5个会话
select top 5 sdtdt.transaction_id,ss.spid, sd.name dbname,
database_transaction_log_bytes_used/1024/1024 as "log_byte_used(MB)",
database_transaction_log_bytes_reserved/1024/1024 as "log_byte_reseved(MB)",
database_transaction_begin_time,
database_transaction_type,
database_transaction_state,
ss.hostname,
ss.nt_username,
text
from sys.dm_tran_database_transactions sdtdt
inner join sys.databases sd on sdtdt.database_id=sd.database_id
inner join sys.dm_tran_session_transactions sdtst on sdtst.transaction_id=sdtdt.transaction_id
inner join sys.sysprocesses ss on sdtst.session_id= ss.spid
cross apply sys.dm_exec_sql_text(ss.sql_handle)
where sd.name='dba' and sdtdt.database_transaction_state in(4,12)
order by database_transaction_log_bytes_used desc
--查询当前产生日志最多的5个且是job的会话
select top 5 sdtdt.transaction_id,ss.spid, sd.name dbname,job.name jobname,
database_transaction_log_bytes_used/1024/1024 as "log_byte_used(MB)",
database_transaction_log_bytes_reserved/1024/1024 as "log_byte_reseved(MB)",
database_transaction_begin_time,
database_transaction_type,
database_transaction_state,
ss.hostname,
ss.nt_username,
text
from sys.dm_tran_database_transactions sdtdt
inner join sys.databases sd on sdtdt.database_id=sd.database_id
inner join sys.dm_tran_session_transactions sdtst on sdtst.transaction_id=sdtdt.transaction_id
inner join sys.sysprocesses ss on sdtst.session_id= ss.spid
INNER JOIN msdb.dbo.sysjobs job
ON master.dbo.fn_varbintohexstr(CONVERT(varbinary(16), job.job_id)) =
SUBSTRING(REPLACE(ss.PROGRAM_NAME, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)
cross apply sys.dm_exec_sql_text(ss.sql_handle)
where sdtdt.database_transaction_state in(4,12)
order by database_transaction_log_bytes_used desc
4. 备份相关
15.查看备份进度
SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC
16.查看恢复进度
SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'RESTORE%' --and database_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC
17.--查看数据库的最近备份信息
SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type
备注: D 表示全备份,i 表示差异备份,L 表示日志备份
--查询备份信息
SELECT
bs.backup_set_id,
bs.database_name,
bs.backup_start_date,
bs.media_set_id,
bmf.physical_device_name,
bmf.media_family_id,
bs.*
FROM
msdb.dbo.backupmediafamily bmf
INNER JOIN
msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id
INNER JOIN
(
SELECT
database_name,
MAX(backup_start_date) as backup_start_date
FROM
msdb.dbo.backupset bs
WHERE
type = 'd'
GROUP BY
database_name
) de on bs.database_name = de.database_name
and bs.backup_start_date = de.backup_start_date
and bs.type = 'd'
18.查看数据库的历史备份记录,并生成restore语句
SELECT
CONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.expiration_date,
CASE bs.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
bs.backup_size,
bmf.logical_device_name,
bmf.physical_device_name,
bs.name AS backupset_name,
bs.description,
'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N'''
+bmf.physical_device_name+ '''WITH NORECOVERY;'
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs
ON bmf.media_set_id=bs.media_set_id
WHERE bs.backup_start_date>DATEADD(DAY,-1,GETDATE())
ORDER BY bs.backup_finish_date
19.查询XX库从YYYY-MM-DD日期开始的日志备份记录,并生成restore log的语句
SELECT TOP 1000
S.database_name [Database],
CASE [S].[type]
WHEN 'L'
THEN N'RESTORE LOG ' + QUOTENAME(S.database_name) + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;'
END [LogRestore],
F.physical_device_name,
S.[Type],
S.backup_start_date,
S.backup_finish_date
FROM msdb.dbo.backupmediafamily F
INNER JOIN msdb.dbo.backupset S
ON S.media_set_id = F.media_set_id
WHERE S.database_name = 'XX' AND
S.type = 'L' AND S.backup_start_date > 'YYYY-MM-DD' ORDER BY S.backup_start_date ASC
"sqlserver中类似oracle的dba_source的视图是sys.sql_modules"
--查询某个数据库下的表数据占用磁盘容量最大的10张表
use XX
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u')
drop table #tabName
go
create table #tabName(
table_name varchar(100),
rowsNum varchar(100),
reserved_size varchar(100),
data_size varchar(100),
index_size varchar(100),
unused_size varchar(100)
)
declare @name varchar(100)
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
insert into #tabName
exec sp_spaceused @name
fetch next from cur into @name
end
close cur
deallocate cur
select top 10 table_name, data_size,rowsNum ,index_size,unused_size ,reserved_size,convert(int,SUBSTRING(data_size,0,LEN(data_size)-2)) size
from #tabName ORDER BY size desc
--或---------------------
select top 10 a.tablename,a.SCHEMANAME,sum(a.TotalSpaceMB) TotalSpaceMB,sum(a.RowCounts) RowCounts
from (
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows) a
GROUP BY a.tablename,a.SCHEMANAME
order by sum(a.TotalSpaceMB) desc
--这个比上一个专业
--查看收缩的进度100%,此语句要到指定的数据库下执行
SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'DbccFilesCompact%' --and database_id=db_id('cardorder')
ORDER BY 2 DESC
5. 集群镜像.发布订阅
--查询always on状态是否正常
select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id and d.is_local=1
--always on查看集群各节点的信息,包含节点成员的名称,类型,状态,拥有的投票仲裁数
SELECT * FROM sys.dm_hadr_cluster_members;
--always on查看集群各节点的信息,包含节点成员的名称,节点成员上的sql实例名称
select * from sys.dm_hadr_instance_node_map
--always on查看WSFC(windows server故障转移群集)的信息,包含集群名称,仲裁类型,仲裁状态
SELECT * FROM SYS.dm_hadr_cluster;
--always on查看AG名称
select * from sys.dm_hadr_name_id_map
--always on查看集群各节点的子网信息,包含节点成员的名称,子网段,子网掩码
SELECT * FROM sys.dm_hadr_cluster_networks;
--always on查看侦听ip
select * from sys.availability_group_listeners;
--always on查看主从各节点的状态
select d.is_local,dc.database_name, d.synchronization_health_desc,
d.synchronization_state_desc, d.database_state_desc
from sys.dm_hadr_database_replica_states d
join sys.availability_databases_cluster dc
on d.group_database_id=dc.group_database_id;
--always on查看辅助副本(传说中的从库)延迟多少M日志量
select db_name(database_id),log_send_queue_size/1024 delay_M
from sys.dm_hadr_database_replica_states where is_primary_replica=0;
select ar.replica_server_name, db_name(drs.database_id),drs.truncation_lsn,
drs.log_send_queue_size,drs.log_send_rate, drs.redo_queue_size,drs.redo_rate
from sys.dm_hadr_database_replica_states drs
join sys.availability_replicas ar on drs.replica_id=ar.replica_id where drs.is_local=0
--log_send_queue_size 主数据库中尚未发送到辅助数据库的日志记录量 (KB)
--log_send_rate 在最后一个活动期间,以千字节 (KB) 的平均主副本发送实例数据的速率/秒
--redo_queue_size 辅助副本的日志文件中尚未重做的日志记录量 (KB)
--redo_rate 平均千字节 (KB) 中的给定辅助数据库做的日志记录速率 / 秒
--查看mirror镜像信息(都在这个文件中sys.database_mirroring)
SELECT
db_name(database_id),
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name,
mirroring_partner_instance
FROM sys.database_mirroring
--查询SSRS Report Subscriptions 报表订阅相关的job
SELECT
b.name AS JobName
, e.name
, e.path
, d.description
, a.SubscriptionID
, laststatus
, eventtype
, LastRunTime
, date_created
, date_modified
FROM
ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON CONVERT(SYSNAME,a.ScheduleID) = b.name
JOIN ReportServer.dbo.ReportSchedule c ON b.name = CONVERT(SYSNAME,c.ScheduleID)
JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE
e.name = 'Report Name Goes Here'
--查询某个发布XX,发布的数据库对象的2种方法
1、发布数据库上执行(数据来源这三张表distribution.dbo.MSpublications、distribution.dbo.MSarticles、sysarticlecolumns)
select a.article,a.source_object,a.destination_object,b.colid from
(select article,article_id,source_object,destination_object
from [distribution].[dbo].MSarticles where publication_id in
( select publication_id from
[distribution].[dbo].MSpublications where publication='XX'
)
) a
inner join
(select * from replicate1.dbo.sysarticlecolumns) b
on a.article_id=b.artid order by a.article
2、订阅数据库上执行
select distinct article from MSreplication_objects where publication='XX'
--查询发布信息,发布名称,发布名称对应的发布序号
Select * from distribution.dbo.MSpublications
--查询发布名里面的发布对象的信息,包含表、视图、存储过程等
Select * from distribution.dbo.MSarticles
--监控发布订阅是否有异常,执行以下5条语句即可
select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].MSrepl_errors order by 2 desc
select * from msdb.dbo.sysreplicationalerts order by 7 desc
6. 文件相关
--查看某个数据库的数据文件信息,就算是mirror从库的数据文件也可以查到,filestream目录也可以查到
SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N'DBA');
--查看某个数据文件信息
select b.name,a.type_desc,a.name,a.physical_name,a.size,a.max_size,a.is_percent_growth,a.growth from sys.master_files a join sys.databases b on a.database_id=b.database_id and a.physical_name like '%DTSWonda_1%'
--查询实例的数据文件总大小
SELECT sum(size*8/1024/1024) FROM master.sys.master_files
--查询某个目录中数据库使用的总大小
SELECT a.size*8/1024/1024 ,a.* FROM master.sys.master_files a WHERE physical_name like 'G:\DEFAULT.DATA%'
--查询某个目录中哪些数据库占用了8G以上容量
SELECT b.name dbname,a.size*8/1024/1024 sum_GB,a.type_desc,a.name datafilename,a.physical_name FROM master.sys.master_files a join sys.sysdatabases b on a.database_id=b.dbid and a.physical_name like 'G:\DEFAULT.DATA%' and a.size*8/1024/1024>8
--查询实例上的每个数据库的大小
SELECT
DB_NAME(db.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id
--查询实例的FILESTREAM 使用的DIRECTORY_NAME
SELECT SERVERPROPERTY('FilestreamShareName')
--查询FILETABLE表的数据库对应的DIRECTORY_NAME
select db_name(database_id),* from sys.database_filestream_options
"仅仅使用filestream功能时,数据库不需要对应的DIRECTORY_NAME"
--查询FILETABLE表对应的DIRECTORY_NAME
select object_name(object_id),* from sys.filetables
--查询filetable表testdb.dbo.table1中的文件完整路径名称
SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1
7. SQL语句相关
--查询总耗CPU最多的前3个SQL,且最近5天出现过
SELECT TOP 3
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())
ORDER BY total_worker_time DESC
--查询平均耗CPU最多的前3个SQL,且最近5小时出现过
SELECT TOP 3
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(hh,-5,getdate())
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC
--查看当前最耗资源的10个SQL及其spid
SELECT TOP 10
session_id,request_id,start_time AS '开始时间',status AS '状态',
command AS '命令',d_sql.text AS 'sql语句', DB_NAME(database_id) AS '数据库名',
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 d_request
CROSS APPLY
sys.dm_exec_sql_text(d_request.sql_handle) AS d_sql
WHERE session_id>50
ORDER BY cpu_time DESC
--前50号session_id一般是系统后台进程,sys.dm_exec_requests的status显示为background
--查询某个存储过程被哪些job调用了
SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N'%sp_name%'
--以上要查询某个job被哪个job调用了,把sp_name存储过程名字改成job_name作业名字即可
--命令执行某个job
EXECUTE msdb.dbo.sp_start_job N'job_name'
--查询ddl修改操作的记录
1、执行如下找到trace文件的目录和名称
select * from Sys.traces
2、使用sqlserver profiler工具打开trace文件,就可以查到相关记录
8. 自增长列相关
--查询某表标识列的列名
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1
--获取标识列的种子值
SELECT IDENT_SEED ('表名')
--获取标识列的递增量
SELECT IDENT_INCR('表名')
--获取指定表中最后生成的标识值
SELECT IDENT_CURRENT('表名')
--重新设置标识种子值为XX
DBCC CHECKIDENT (表名, RESEED, XX)
9. 索引相关
--查询XX表的索引信息
SELECT a.name index_name,c.name table_name,d.name column_name
FROM sysindexes a JOIN sysindexkeys b
ON a.id=b.id AND a.indid=b.indid
JOIN sysobjects c
ON b.id=c.id
JOIN syscolumns d
ON b.id=d.id= AND b.colid=d.colid
WHERE a.indid NOT IN(0,255) AND c.name in ('XX')
--生成sql语句的执行计划(select XXX为例,当然select XXX也可以换成执行存储过程比如exec pro_XXX,都是只生成执行计划,不产生结果集,不会执行存储过程)
SET SHOWPLAN_ALL ON;
GO
select XXX
GO
SET SHOWPLAN_ALL OFF;
GO
或
SET SHOWPLAN_XML ON;
GO
select XXX
GO
SET SHOWPLAN_XML OFF;
GO
--查看存储过程的执行计划
SELECT
d.object_id ,
DB_NAME(d.database_id) DBName ,
OBJECT_NAME(object_id, database_id) 'SPName' ,
d.cached_time ,
d.last_execution_time ,
d.total_elapsed_time/1000000 AS total_elapsed_time,
d.total_elapsed_time / d.execution_count/1000000
AS [avg_elapsed_time] ,
d.last_elapsed_time/1000000 AS last_elapsed_time,
d.execution_count ,
d.total_physical_reads ,
d.last_physical_reads ,
d.total_logical_writes ,
d.last_logical_reads ,
et.text SQLText ,
eqp.query_plan executionplan
FROM sys.dm_exec_procedure_stats AS d
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) et
CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqp
WHERE OBJECT_NAME(object_id, database_id) = 'xxxx'
ORDER BY [total_worker_time] DESC;
--查询名称为XXX的job的最后一次运行成功的时间
SELECT TOP 1 CONVERT(DATETIME, RTRIM(run_date))+ ((run_time / 10000 * 3600) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964)
FROM msdb.dbo.sysjobhistory jobhis inner join msdb.dbo.sysjobs jobs
on jobhis.job_id = jobs.job_id AND jobhis.step_id = 0 AND jobhis.run_status = 1
and jobs.name='XXX'
ORDER BY 1 DESC
--查询某张分区表的总行数和大小,比如表为crm.EmailLog
exec sp_spaceused 'crm.EmailLog';
--查询某张分区表的信息,每个分区有多少行,比如表为crm.EmailLog
select convert(varchar(50), ps.name
) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name
) as filegroup,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = object_id('crm.EmailLog')
and i.index_id in (0, 1)
order by p.partition_number
--查询分区函数
select * from sys.partition_functions
--查看分区架构
select * from sys.partition_schemes
--查询ssis包的信息
select * from msdb.dbo.sysssispackages
--查询某张表里的索引的大小,如下示例表为dbo.table1
SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.table1'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name
--重建表上的所有索引
alter index all on table_name rebuild with ()
--重建表上的某个索引
alter index index_name on table_name rebuild with ()
--重新组织表上的所有索引
alter index all on table_name reorganize
--重新组织表上的某个索引
alter index index_name on table_name reorganize
--查看数据文件可收缩空间,结果见Availabesize_MB字段值
select name ,size*8/1024 as Totalsize_MB ,CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 as Usedsize_MB,
size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB
from sys.master_files where database_id=db_id(N'DBNAME')
--查询某个表中的全部索引的信息
declare @tableName varchar(50) = 'LbaListAlertDetail'
declare @tableId int
select @tableId = object_id
from sys.objects
where name = @tableName
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
,IX.name AS Index_Name
,IX.type_desc Index_Type
,SUM(PS.[used_page_count]) * 8 IndexSizeKB
,IXUS.user_seeks AS NumOfSeeks
,IXUS.user_scans AS NumOfScans
,IXUS.user_lookups AS NumOfLookups
,IXUS.user_updates AS NumOfUpdates
,IXUS.last_user_seek AS LastSeek
,IXUS.last_user_scan AS LastScan
,IXUS.last_user_lookup AS LastLookup
,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
and IX.OBJECT_ID = @tableId
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update
--查询哪些表的哪些字段需要创建索引,并生成创建索引语句
SELECT
mig.index_handle,
mid.database_id,
mid.object_id,
mid.[statement] [object_fullname],
(migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [' AS [create_index_part1],
'missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) AS [create_index_name],
']' +
' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') +
CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(mid.inequality_columns, '') +
')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') + ' WITH (' AS [create_index_part2],
DATA_COMPRESSION = ROW, AS [create_index_part3],
migs.group_handle,
migs.unique_compiles,
migs.user_seeks,
migs.user_scans,
migs.last_user_seek,
migs.last_user_scan,
migs.avg_total_user_cost,
migs.avg_user_impact,
migs.system_seeks,
migs.system_scans,
migs.last_system_seek,
migs.last_system_scan,
migs.avg_total_system_cost,
migs.avg_system_impact
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
WHERE ((migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) > 100000)
ORDER BY (migs.avg_total_user_cost * migs.avg_user_impact) * (migs.user_seeks + migs.user_scans) DESC
--删除某个对象或某个sql语句在计划缓存中执行计划
SELECT cp.plan_handle, cp.objtype, cp.usecounts,DB_NAME(st.dbid) AS [DatabaseName],OBJECT_NAME (st.objectid) object_name,st.text
FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE OBJECT_NAME (st.objectid) LIKE N'%对象名%' or text LIKE N'%某个sql语句%' OPTION (RECOMPILE);
DBCC FREEPROCCACHE(0x05000E0044A36826A0FB6F1DEA00000001000000000000000000000000000000000000000000000000000000);
--上述括号中的内容就是sys.dm_exec_cached_plans.plan_handle的值
--查询某个数据库中是否有create index '+name+ CHAR(10)
select 'use '+name+ CHAR(10) +'select DB_NAME(),OBJECT_NAME(OBJECT_ID),definition from '+name+'.sys.sql_modules
WHERE objectproperty(OBJECT_ID, ''IsProcedure'') = 1
AND definition like ''%online%=%on%'' and definition like ''%index%''' from sys.databases;
--查看重新组织索引的100%进度
SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE '%REORGANIZE%' --and database_id=db_id('cardorder')
ORDER BY 2 DESC