一、基础命令

查看当前数据库的版本

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

查看当前用户查看当前用户

select system_user

检查SQL Agent是否开启

IF EXISTS (
SELECT TOP 1 1
FROM sys.sysprocesses
WHERE program_name = 'SQLAgent - Generic Refresher'
)
SELECT 'Running'
ELSE
SELECT 'Not Running'

查看是否做了镜像

select
    a.database_id
    ,a.name 数据库名称
    ,case when b.mirroring_guid is null then '否' else '是' end 是否镜像
    ,b.mirroring_partner_name 镜像服务器名称
from 
[sys].[databases] a
left join [sys].[database_mirroring] b on a.database_id=b.database_id

分离数据库

USE master;
EXEC sp_detach_db @dbname = 'test';#test指需要分离的数据库

附加数据库

SELECT type_desc, name, physical_name from sys.database_files;#查看物理数据库文件的位置
#使用带 FOR ATTACH 子句的 CREATE DATABASE 语句附加之前分离的test数据库
CREATE DATABASE test   
    ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf'),   
    (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log.ldf')   
    FOR ATTACH;

查看主从复制关系

SELECT * FROM msdb.dbo.sysjobs；
SELECT 
    serverproperty('servername') AS ServerName,
    CASE 
        WHEN serverproperty('servername') = '主服务器名称' THEN '主服务器'
        ELSE '从服务器'
    END AS ServerRole
#也可以通过以下语句来查询
    SELECT * FROM sys.objects WHERE name = 'MSreplication_options'

查看实例级别的信息

select SERVERPROPERTY ('test')

查看实例级别的某个参数allow updates的配置

select * from sys.configurations where name='allow updates'

查询当前数据库的所有架构范围的对象

select * from sys.all_objects

查询当前数据库的所有对象

select * from sys.sysobjects

在当前数据库下可以查询到所有数据库信息，包含是否on状态

select * from sys.databases

查询所有数据库信息

select * from sys.sysdatabases

查询当前数据库下所有正在SQL Server 实例上运行的进程的相关信息

select * from sys.sysprocesses

监控日志空间

DBCC SQLPERF (LOGSPACE)

查看数据库各种设置

select name,State,user_access,is_read_only,recovery_model from sys.databases

查询当前数据库是否有会话

select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('test')

查询当前阻塞的所有请求

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)

查看活动线程执行的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

查看数据库的最近备份信息

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

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

查看mirror镜像信息

SELECT
db_name(database_id),
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name,
mirroring_partner_instance
FROM sys.database_mirroring

查看每个数据库实例的数据量大小

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

查询总耗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

查看当前最耗资源的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

always on

查看集群各节点的信息，包含节点成员的名称，类型，状态，拥有的投票仲裁数

SELECT * FROM  sys.dm_hadr_cluster_members;

查看集群各节点的信息，包含节点成员的名称，节点成员上的sql实例名称

select * from sys.dm_hadr_instance_node_map

查看WSFC(windows server故障转移群集)的信息，包含集群名称，仲裁类型，仲裁状态

SELECT * FROM SYS.dm_hadr_cluster;

查看AG名称

select * from sys.dm_hadr_name_id_map

查看集群各节点的子网信息，包含节点成员的名称，子网段，子网掩码

SELECT * FROM  sys.dm_hadr_cluster_networks;

查看侦听ip

select * from sys.availability_group_listeners;

查看主从各节点的状态

复制

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;

查看辅助副本(传说中的从库)延迟多少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;

查看DDL操作的记录

select * from Sys.traces

查询返回当前配置的内存值和当前使用的值的相关信息

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

修改内存的大小

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'max server memory (MB)', 4096; -- 设置最大内存限制为4GB
RECONFIGURE;

启用对表的压缩

EXEC sp_estimate_data_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW';
ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);
GO

启用索引压缩

SELECT name, index_id
FROM sys.indexes
WHERE OBJECT_NAME (object_id) = N'TransactionHistory';
EXEC sp_estimate_data_compression_savings
    @schema_name = 'Production',
    @object_name = 'TransactionHistory',
    @index_id = 2,
    @partition_number = NULL,
    @data_compression = 'PAGE';
ALTER INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
GO

读取错误日志

EXEC sp_readerrorlog 0, 1, 'database', 'start'

限制错误日志大小

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

CHECKPOINT检查点

CHECKPOINT [ checkpoint_duration ]

查询是否有死锁

WITH    CTE_SID ( BSID, SID, sql_handle )        
  AS ( SELECT   blocking_session_id ,
                        session_id ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    blocking_session_id <> 0
               UNION ALL
               SELECT   A.blocking_session_id ,
                        A.session_id ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                       JOIN CTE_SID B ON A.SESSION_ID = B.BSID
             )
    SELECT  C.BSID ,  C.SID , S.login_name , S.host_name , S.status ,S.cpu_time ,
            S.memory_usage ,S.last_request_start_time , S.last_request_end_time ,S.logical_reads ,
            S.row_count ,
            q.text
    FROM    CTE_SID C
            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
ORDER BY sid

查看阻塞超时设置

SELECT @@LOCK_TIMEOUT

查看数据库的负载

SELECT substring (a.name,0,20) as [数据库名],
[连接数] = (SELECT COUNT(*) FROM master..sysprocesses b WHERE a.dbid = b.dbid), [阻塞进程] = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE a.dbid = b.dbid AND blocked <> 0),
[总内存] = ISNULL((SELECT SUM(memusage) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),
[总IO] = ISNULL((SELECT SUM(physical_io) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),
[总CPU] = ISNULL((SELECT SUM(cpu) FROM master..sysprocesses b
WHERE a.dbid = b.dbid),0), [总等待时间] = ISNULL((SELECT SUM(waittime)
FROM master..sysprocesses b
WHERE a.dbid = b.dbid),0) FROM master.dbo.sysdatabases a WITH (nolock)
WHERE  DatabasePropertyEx(a.name,'Status') = 'ONLINE'
ORDER BY [数据库名]

整理索引碎片

第一步：查询表的索引碎片情况
DBCC SHOWCONTIG WITH ALL_INDEXES；
第二步：删除并重建索引 
第三步：使用DROP_EXISTING子句重建索引 
第四步：执行DBCC DBREINDEX
第五步：执行DBCC INDEXDEFRAG
Select 'DBCC INDEXDEFRAG ('+DB_Name()+','+Object_Name(ID)+','+Cast(INDID As Varchar)+')'+Char(10)
From SysIndexes
Where ID Not IN (Select ID From SYSObjects Where xType='S')

查询索引

SELECT CASE  WHEN t.[type] = 'U' THEN'表'
             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',
       i.[name] AS 索引名称,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',
       CASE WHEN i.[type] = 1 THEN '聚集索引'
            WHEN i.[type] = 2 THEN  '非聚集索引'
            WHEN i.[type] = 3 THEN  'XML索引'
           WHEN i.[type] = 4 THEN '空间索引'
           WHEN i.[type] = 5 THEN '聚簇列存储索引'
           WHEN i.[type] = 6 THEN '非聚集列存储索引'
       WHEN i.[type] = 7 THEN'非聚集哈希索引'
           END AS '索引类型',
      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS '索引是否唯一'
FROM sys.objects t
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    CROSS APPLY
(
    SELECT col.[name] + ', '
    FROM sys.index_columns ic
        INNER JOIN sys.columns col
           ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
    WHERE ic.object_id = t.object_id
          AND ic.index_id = i.index_id
    ORDER BY col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0
ORDER BY i.[name];

整理索引

select 'drop index ' + index_name + ' on ' + tab_name + ';' ,a.tab_name, a.index_Type,a.index_colum FROM (
SELECT CASE  WHEN t.[type] = 'U' THEN'表'
             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',
       i.[name] AS index_name,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',
       CASE WHEN i.[type] = 1 THEN '聚集索引'
            WHEN i.[type] = 2 THEN  '非聚集索引'
            WHEN i.[type] = 3 THEN  'XML索引'
           WHEN i.[type] = 4 THEN '空间索引'
           WHEN i.[type] = 5 THEN '聚簇列存储索引'
           WHEN i.[type] = 6 THEN '非聚集列存储索引'
       WHEN i.[type] = 7 THEN'非聚集哈希索引'
           END AS index_Type,
      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'
FROM sys.objects t
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    CROSS APPLY
(
    SELECT col.[name] + ', '
    FROM sys.index_columns ic
        INNER JOIN sys.columns col
           ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
    WHERE ic.object_id = t.object_id
          AND ic.index_id = i.index_id
    ORDER BY col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0
) a
where a.index_TYpe = '非聚集索引' and a.index_only = '不唯一';
select 'create index ' + 'idx_' + tab_name + '_' + a.index_colum + ' on ' + tab_name + '('  + a.index_colum + ');',
       a.tab_name, a.index_Type,a.index_colum FROM (
SELECT CASE  WHEN t.[type] = 'U' THEN'表'
             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',
       i.[name] AS index_name,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',
       CASE WHEN i.[type] = 1 THEN '聚集索引'
            WHEN i.[type] = 2 THEN  '非聚集索引'
            WHEN i.[type] = 3 THEN  'XML索引'
           WHEN i.[type] = 4 THEN '空间索引'
           WHEN i.[type] = 5 THEN '聚簇列存储索引'
           WHEN i.[type] = 6 THEN '非聚集列存储索引'
       WHEN i.[type] = 7 THEN'非聚集哈希索引'
           END AS index_Type,
      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'
FROM sys.objects t
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    CROSS APPLY
(
    SELECT col.[name] + ', '
    FROM sys.index_columns ic
        INNER JOIN sys.columns col
           ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
    WHERE ic.object_id = t.object_id
          AND ic.index_id = i.index_id
    ORDER BY col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0
) a
where a.index_TYpe = '非聚集索引' and index_only = '不唯一'

查看哪些表占用了比较大的磁盘空间

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 DataPages, 
              SUM( case when (p.index_id<2) then row_count else 0 end ) as rowCounts
from sys.dm_db_partition_stats p
inner join sys.objects o on p.object_id=o.object_id group by o.name order by rowCounts desc

查看表的占用情况

SELECT
name '表名', 
convert (char(11), row_Count) as '数据条数', 
(reservedpages * 8) '已用空间(KB)', 
(pages * 8) '数据占用空间(KB)', 
(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '索引占用空间(KB)', 
(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '未用空间(KB)', 
LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' GB') as '已用空间(GB)' 
from( 
SELECT name, 
SUM (reserved_page_count) as reservedpages , 
SUM (used_page_count) as usedpages , 
SUM ( 
    CASE
        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 
        ELSE lob_used_page_count + row_overflow_used_page_count
    END 
    ) as pages, 
SUM ( 
    CASE
        WHEN (index_id < 2) THEN row_count
        ELSE 0 
    END 
    )  as row_Count
FROM sys.dm_db_partition_stats
inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id
where type='U' 
group by sys.objects.name
union 
SELECT sys.objects.name, 
sum(reserved_page_count) as reservedpages, 
sum(used_page_count) as usedpages, 
0 as pages, 
0 as row_count
from sys.objects inner join sys.internal_tables on
 sys.objects.object_id = sys.internal_tables.parent_id
inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id=sys.internal_tables.object_id
where sys.internal_tables.internal_type IN (202,204,211,212,213,214,215,216) 
group by sys.objects.name) t
order by '已用空间(KB)' desc