-- 查询服务器部分特殊信息

select SERVERPROPERTY(N'edition') as Edition     --数据版本，如企业版、开发版等
    ,SERVERPROPERTY(N'collation') as Collation   --数据库字符集
    ,SERVERPROPERTY(N'servername') as ServerName --服务器名
    ,@@VERSION as Version   --数据库版本号
    ,@@LANGUAGE AS Language  --数据库使用的语言，如us_english等

-- '数据库所在机器操作系统参数'
exec  master..xp_msver

-- '数据库启动参数'
exec sp_configure

--查看所有数据库用户登录信息
exec sp_helplogins 

-- 查看所有数据库用户所属的角色信息
exec sp_helpsrvrolemember

-- 查看远端数据库用户登录信息
exec sp_helpremotelogin

-- 查看链接服务器
exec sp_helplinkedsrvlogin

-- 恢复模式查看
SELECT name, create_date, recovery_model, recovery_model_desc  
   FROM sys.databases  
      WHERE name = 'model'; 

select DATABASEPROPERTYEX('msdb', 'Recovery') AS RecoveryModel

-- 日志重用
SELECT  DB_NAME([database_id]) AS dbname ,
        [log_reuse_wait] ,
        [log_reuse_wait_desc]
FROM    sys.[databases]

-- CDC
select is_cdc_enabled,is_tracked_by_cdc from sys.[databases];

-- 复制
select log_reuse_wait_desc,log_reuse_wait from sys.[databases];
SELECT [name], create_date, lock_on_bulk_load, is_replicated, has_replication_filter, 
       is_tracked_by_cdc, lock_escalation_desc
FROM sys.tables WITH (NOLOCK) 
ORDER BY [name] OPTION (RECOMPILE);

-- 获取对象DDL语句定义：
SELECT object_definition (object_id('sys.tables'));

-- 

-- sql 执行统计
    SELECT top 10 
        (total_elapsed_time / execution_count)/1000 N'平均时间ms' 
        ,total_elapsed_time/1000 N'总花费时间ms' 
        ,total_worker_time/1000 N'所用的CPU总时间ms' 
        ,total_physical_reads N'物理读取总次数' 
        ,total_logical_reads/execution_count N'每次逻辑读次数' 
        ,total_logical_reads N'逻辑读取总次数' 
        ,total_logical_writes N'逻辑写入总次数' 
        ,execution_count N'执行次数' 
        ,creation_time N'语句编译时间' 
        ,last_execution_time N'上次执行时间' 
        ,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'执行语句' 
        ,qp.query_plan 
    FROM  sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
    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; 
-- ORDER BY total_worker_time DESC
-- ORDER BY total_logical_reads DESC

-- 查询表死锁信息

    select   
    request_session_id spid,  
    OBJECT_NAME(resource_associated_entity_id) tableName   
    from   
    sys.dm_tran_locks  
    where   
    resource_type='OBJECT'

dbcc opentran

--查看死锁的详细信息、执行的sql语句
exec sp_who2 53
--exec sp_who 'active'
DBCC inputbuffer (53)

--解除死锁
kill 53

-- 是否开启并行度的判断阈值，串行执行计划估算的执行时间大于5秒，调整：
sp_configure 'cost threshold for parallelism', 10;

-- 默认最大并行度为0，不限制，修改为8
sp_configure 'max degree of parallelism', 8;

-- 强制为并行度1：
select * from table_name option(maxdop 1)

create procedure sp_who_lock  
as  
begin  
    declare @spid int,@bl int,  
    @intTransactionCountOnEntry int,  
    @intRowcount int,  
    @intCountProperties int,  
    @intCounter int  
    create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)  
    IF @@ERROR<>0 RETURN @@ERROR  
    insert into #tmp_lock_who(spid,bl) select 0 ,blocked  
    from (select * from sys.sysprocesses where blocked>0 ) a   
    where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b   
    where a.blocked=spid)  
    union select spid,blocked from sys.sysprocesses where blocked>0  
    IF @@ERROR<>0 RETURN @@ERROR  
        -- 找到临时表的记录数  
        select @intCountProperties = Count(*),@intCounter = 1  
        from #tmp_lock_who  
    IF @@ERROR<>0 RETURN @@ERROR  
    if @intCountProperties=0  
    select '现在没有阻塞和死锁信息' as message  
    -- 循环开始  
    while @intCounter <= @intCountProperties  
    begin  
    -- 取第一条记录  
    select @spid = spid,@bl = bl  
    from #tmp_lock_who where id = @intCounter   
    begin  
    if @spid =0   
        select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'  
    else  
        select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'  
    DBCC INPUTBUFFER (@bl )  
    end  
    -- 循环指针下移  
    set @intCounter = @intCounter + 1  
    end  
    drop table #tmp_lock_who  
    return 0  
end   
go

CREATE PROCEDURE sp_who3 
(  @SessionID INT = NULL ) 
AS
BEGIN
SELECT
    SPID                = er.session_id 
    ,STATUS             = ses.STATUS 
    ,[Login]            = ses.login_name 
    ,Host               = ses.host_name 
    ,BlkBy              = er.blocking_session_id 
    ,DBName             = DB_Name(er.database_id) 
    ,CommandType        = er.command 
    ,SQLStatement       = st.text 
    ,ObjectName         = OBJECT_NAME(st.objectid) 
    ,ElapsedMS          = er.total_elapsed_time 
    ,CPUTime            = er.cpu_time 
    ,IOReads            = er.logical_reads + er.reads 
    ,IOWrites           = er.writes 
    ,LastWaitType       = er.last_wait_type 
    ,StartTime          = er.start_time 
    ,Protocol           = con.net_transport 
    ,ConnectionWrites   = con.num_writes 
    ,ConnectionReads    = con.num_reads 
    ,ClientAddress      = con.client_net_address 
    ,Authentication     = con.auth_scheme 
FROM sys.dm_exec_requests er 
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st 
LEFT JOIN sys.dm_exec_sessions ses 
ON ses.session_id = er.session_id 
LEFT JOIN sys.dm_exec_connections con 
ON con.session_id = ses.session_id 
WHERE er.session_id > 50 
    AND @SessionID IS NULL OR er.session_id = @SessionID 
ORDER BY
    er.blocking_session_id DESC
    ,er.session_id 
END
GO



    GRANT SELECT ON  sys.dm_tran_locks TO zabbix

-- 连接数
 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='mydb'；

    SELECT count(1) as icount FROM
    [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT
    [DBID]
    FROM
    [Master].[dbo].[SYSDATABASES]
    WHERE
    NAME='JSTMBS'
    );

-- 查看会话数：
 SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50;

--堵塞语句
select spid,blocked,waittime,lastwaittype,waitresource,open_tran,status,p.dbid,cpu,physical_io,memusage,login_time,last_batch
,hostname,[program_name],hostprocess,cmd,nt_domain,nt_username,net_address,net_library,loginame,sql_handle,text
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s
where blocked >0 or spid in(select sp.blocked from master.dbo.sysprocesses sp where sp.blocked>0)
go

--是否有未提交事务
select spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran
,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s
where open_tran <> 0
go

--各数据库连接数
SELECT @@ServerName AS server,NAME AS dbname,COUNT(STATUS) AS number_of_connections,GETDATE() AS timestamp
FROM sys.databases sd LEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
GO

-- 谁对对象进行了 DDL 操作 (exec sp_configure 'default trace enabled')
DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +'\log.trc'
FROM sys.traces WHERE id = 1
SELECT DatabaseID,NTDomainName,NTUserName,HostName, ClientProcessID,ApplicationName
,LoginName,StartTime,DatabaseName,ObjectName,SessionLoginName
,(CASE WHEN EventClass=46 THEN 'Object:Created' WHEN EventClass=47 THEN 'Object:Deleted' WHEN EventClass=164 THEN 'Object:Altered' END)EventClass
FROM ::fn_trace_gettable(@path, 0)
WHERE EventClass in(46,47,164) and DatabaseName<>'tempdb' and ObjectName is not null
GO

-- 系统主要等待类型
SELECT TOP 10
wait_type,waiting_tasks_count ,wait_time_ms,signal_wait_time_ms
,wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
,100.0 wait_time_ms / SUM (wait_time_ms ) OVER( )AS percent_total_waits
,100.0 signal_wait_time_ms / SUM (signal_wait_time_ms) OVER( )AS percent_total_signal_waits
,100.0 * ( wait_time_ms - signal_wait_time_ms )/SUM (wait_time_ms ) OVER( ) AS percent_total_resource_waits
FROM sys .dm_os_wait_stats
WHERE wait_time_ms > 0
go

--当前锁请求脚本
select req_spid
,case req_status when 1 then '已授予' when 2 then '正在转换' when 3 then '正在等待' end as req_status
,case rsc_type when 1 then 'NULL 资源（未使用）' when 2 then '数据库' when 3 then '文件'
when 4 then '索引' when 5 then '表' when 6 then '页' when 7 then '键'
when 8 then '扩展盘区' when 9 then 'RID（行 ID)' when 10 then '应用程序' else '' end rsc_type
,coalesce(OBJECT_NAME(rsc_objid),db_name(rsc_dbid)) as [object]
,case req_mode when 1 then 'NULL' when 1 then 'Sch-S' when 2 then 'Sch-M' when 3 then 'S'
when 4 then 'U' when 5 then 'X' when 6 then 'IS' when 7 then 'IU' when 8 then 'IX' when 9 then 'SIU'
when 10 then 'SIX' when 11 then 'UIX' when 12 then 'BU' when 13 then 'RangeS_S' when 14 then 'RangeS_U'
when 15 then 'RangeI_N' when 16 then 'RangeI_S' when 17 then 'RangeI_U' when 18 then 'RangeI_X'
when 19 then 'RangeX_S' when 20 then 'RangeX_U' when 21 then 'RangeX_X' else '' end req_mode
,rsc_indid as index_id,rsc_text,req_refcnt
,case req_ownertype when 1 then '事务' when 2 then '游标' when 3 then '会话' when 4 then 'ExSession' else'' end req_ownertype
from sys.syslockinfo WHERE rsc_type<>2
GO

EXEC xp_enumerrorlogs 1 --查看 sqlserver 错误日志大小
EXEC xp_enumerrorlogs 2 --查看 代理日志大小
go
exec msdb.dbo.sp_cycle_errorlog -- "Sql Server 日志"切换
exec msdb.dbo.sp_cycle_agent_errorlog -- "代理错误日志"切换
go

-- 查看CPU数和user scheduler数目
 SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info
-- 查看最大工作线程数
 SELECT max_workers_count FROM sys.dm_os_sys_info

-- 查看当前占用 cpu 资源最高的会话和其中执行的语句(及时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

 -- 当前10个最耗CPU时间的会话---

 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',
 [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])='db_name'  
 ORDER BY [cpu_time] DESC

 -- 当前10个最耗CPU时间的SQL---

 SELECT TOP 10 
 dest.[text] AS 'sql语句'
 FROM sys.[dm_exec_requests] AS der 
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

-- 当前10个最耗时间的SQL
SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

---- 查询缺失索引 ----
-- 如果CPU负载持续很高，但内存和IO都还好的话，这种情况下，首先想到的一定是索引问题

 SELECT 
     DatabaseName = DB_NAME(database_id)
     ,[Number Indexes Missing] = count(*) 
 FROM sys.dm_db_missing_index_details
 GROUP BY DB_NAME(database_id)
 ORDER BY 2 DESC;

 SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

-- 自动创建缺失的索引：
SELECT
migs.avg_total_user_cost*(migs.avg_user_impact/ 100.0) *(migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX[missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle)
+ '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']'
+ ' 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+ ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle= mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details 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) > 10
ORDER BY migs.avg_total_user_cost* migs.avg_user_impact*(migs.user_seeks + migs.user_scans) DESC

-- 索引创建：
CREATE NONCLUSTERED INDEX Idx_table1 ON dbo.table1
 (
     col1
 )include(col2,col3) 
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- 清空错误日志
EXEC [sys].[sp_cycle_errorlog]

-- 数据文件大小

    select  convert(float,sum(size)) * (8192.0/1024.0)/1024.0/1024  from JSTMBS.dbo.sysfiles 

--查看某数据库下每个数据对象的大小 
-- @objname
exec sp_spaceused

-- 查看数据库中所有表的条数
select  b.name as tablename ,  
        a.rowcnt as datacount  from    sysindexes a ,  
        sysobjects b  where   a.id = b.id  
        and a.indid < 2  
        and objectproperty(b.id, 'IsMSShipped') = 0

-- 查看作业 备份JSTMBS 的状态

    select top 1
    CASE HIST.run_status
        WHEN 0 THEN 'Failed' 
        WHEN 1 THEN 'Succeeded'  
        WHEN 2 THEN 'Retry'  
        WHEN 3 THEN 'Canceled'  
        END as status
    FROM msdb.dbo.sysjobs JOB
    INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id
    WHERE
    JOB.name = '备份JSTMBS'
    and HIST.step_id =0
    ORDER BY HIST.run_date 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

--查看最近失败的SqlServer作业
select top 10 run_date,run_time,run_duration,step_name,message
from msdb..sysjobhistory where run_status = 0
order by run_date desc,run_time desc
go

-- 数据库完整备份：
 BACKUP DATABASE [GPOSDB]
 TO DISK='D:\FULLBACKUP.bak'
 WITH INIT
 go

-- 日志备份：
 BACKUP LOG [GPOSDB] TO  DISK = N'D:\LOGBACKUP.bak' 
 WITH  NO_TRUNCATE , NOFORMAT,  NAME = N'事务日志备份', 
 SKIP,  NORECOVERY ,  STATS = 10, CHECKSUM;

-- NORECOVERY: 备份活动日志（尾日志备份），使数据库进入还原状态
-- TRUNCATE: 截断过程只是做标记，以便重新使用旧日志记录使用过的空间
-- 日志备份完毕后，如果日志文件空间不够，则可以移去事务日志中不活动的部分
不活动：已备份，活动：未备份

-- 备份查看：
SELECT
 -- TOP 1
 MAX(backup_set_id)
 FROM  msdb..backupset
 WHERE database_name = N'MSDB'
 order by [backup_set_id] DESC ;

--各DB最近备份情况
SELECT database_name
,MAX(CASE WHEN type='D' THEN backup_finish_date ELSE NULL END) AS 完整备份时间
,MAX(CASE WHEN type='I' THEN backup_finish_date ELSE NULL END) AS 差异备份时间
,MAX(CASE WHEN type='L' THEN backup_finish_date ELSE NULL END) AS 日志备份时间
FROM(
SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date
FROM msdb.dbo.backupset
GROUP BY database_name,type
) T GROUP BY database_name
go

-- 用户名 
select name from master.dbo.sysusers
-- 登陆名
select name from master.dbo.syslogins

-- 将数据库设置为单用户模式
USE master;
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- 将数据库设置为只读readonly
ALTER DATABASE AdventureWorks2012 SET READ_ONLY;
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;

-- 查看数据库的兼容级别
USE AdventureWorks2012; 
SELECT compatibility_level FROM sys.databases WHERE name = 'AdventureWorks2012';
-- 更改数据库的兼容级别
ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL = 120;

-- sqlserver 统计碎片

    select B.name,C.name,A.avg_fragmentation_in_percent from  sys.dm_db_index_physical_stats(DB_ID() ,object_id('WF_WorkItem') ,NULL,NULL,NULL) A,sysobjects B,sys.indexes  C
    where A.object_id = B.id  and A.object_id = C.object_id and C.index_id = A.index_id
    and C.name is not null
    and avg_fragmentation_in_percent > 90

-- 重建索引：
ALTER INDEXindexname on tablename  REORGANIZEwith(online=on);
ALTER INDEXindexname on tablename  REBUILD With(FillFactor = 90 , Online= On);

-- 查看触发器
    SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id
    WHERE tb1.type='TR';

-- 查看数据库连接用户
Select * From sys.dm_exec_connections

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

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  

-- 查询SQLSERVER内存使用情况
select * from sys.dm_os_process_memory

-- 从所有缓存中释放所有未使用的缓存条目
DBCC FREESYSTEMCACHE('ALL');

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

select      type,
sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,--保留的内存  
sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,--提交的内存  
sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,--开启AWE后使用的内存  
sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,--共享的保留内存  
sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb--共享的提交内存  
from    sys.dm_os_memory_clerks
group by type
order by type

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

-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
-- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？
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（预定义语句）的缓存量
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

-- 查询缓存中具体的执行计划，及对应的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 LOGIN_TIME FROM MASTER..SYSPROCESSES WHERE SPID= 1

-- 服务器pid
SELECT SERVERPROPERTY('ProcessID');

当前数据库连接情况的快捷键是crtl+1（sp_who）
锁情况的快捷键是ctrl+2（sp_lock）
alt+f1可以查看表的属性（sp_help）
新建一个查询窗口的快捷键是alt+n
ctrl+shift+r来重新感知

-- 查看一下数据库信息,数据文件信息
EXEC  sp_helpdb @dbname='master'

-- shrink
DBCC SHRINKFILE(DBNAME_log,10)

--查询sql server内存整体使用情况
  SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name
  FROM sys.dm_os_performance_counters t
  WHERE counter_name = 'Total Server Memory (KB)';

-- 一次性清除数据库所有表的数据

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  

-- SQL调用扩展存储过程
--参数1: QueryState 检查服务状态/ Start启动服务/ Stop停掉服务
--参数2: 服务名
exec master.dbo.xp_servicecontrol 'QueryState', 'MSSQLServer'
exec master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent'
exec master.dbo.xp_servicecontrol 'QueryState', 'SQLBrowser'
exec master.dbo.xp_servicecontrol 'QueryState', 'NetLogon'

EXEC xp_servicecontrol N'Stop', N'SQLServerAGENT'
EXEC xp_servicecontrol N'Start',N'SQLServerAGENT'

-- SQL调用操作系统命令
if OBJECT_ID('tempdb..#tmp_started_services') is not null
    drop table #tmp_started_services
create table #tmp_started_services (started_services varchar(255))

insert into #tmp_started_services(started_services)   
exec master..xp_cmdshell 'net start' 

select * from #tmp_started_services where LTRIM(RTRIM(started_services)) like 'SQL%'

--  操作系统磁盘可用空间
wmic logicaldisk get caption,freespace,size
exec xp_fixeddrives
select sys.dm_os_volume_stats(f.database_id, f.file_id);

-- 跟踪trace flag：
DBCC TRACEON (2528, -1)
DBCC TRACEOFF (2528, -1)
DBCC TRACESTATUS


DBCC CHECKDB
DBCC CHECKTABLE
DBCC CHECKCONSTRAINTS 

-- 日志查看
EXEC xp_readerrorlog 0,1,NULL,NULL,'2019-09-21','2019-10-10','DESC'

-- SQLCMD安装
https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

-- SQLCMD使用方法
https://www.red-gate.com/simple-talk/sysadmin/powershell/introduction-to-powershell-with-sql-server-using-invoke-sqlcmd/

-- SQLCMD:
-- SQLCMD    -E 远程连接使用身份信任 去除标题，宽度100，去除多余空格，以逗号分割
sqlcmd -E -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt
-h-1 -w 100 -W -s ','
SQLCMD -dmaster -ic:\Scripts\create_db.sql 

-- SQLCMD 变量参数传递
$DATABASEFILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008INSTANCE\MSSQL\DATA\myDB.mdf"
$DATABASELOGNAME = "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008INSTANCE\MSSQL\DATA\myDB_log.ldf"
$DBUSEROWNER = "domain\spsetup
try {
        $createDatabaseScript = ($scriptsFolder,$eachRelease,$DeployEnvironment,"Config" -join "\") + "\JM SiteRequest Database.sql"
        $sqlVariable = "DATABASEFILENAME=$DATABASEFILENAME", "DATABASELOGNAME=$DATABASELOGNAME", "DBUSEROWNER=$DBUSEROWNER"
        Invoke-Sqlcmd -ServerInstance "$MySQLServer" -InputFile "$createDatabaseScript" -ErrorAction Stop -Variable $sqlVariable
}
catch [Exception] {
        Write-Error "Database error: $_.Exception"

-- 凭据
CREATE CREDENTIAL cmdshell_agent WITH IDENTITY = 'account', SECRET = 'password';
GO

SELECT * FROM sys.credentials

-- 使用凭据创建代理，然后使用代理执行job任务

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'tst_Proxy',@credential_name=N'cmdshell_agent', @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'tst_Proxy', @subsystem_id=3
GO

-- 只启动SQLCMD模块 /m 单用户模式 /f 最小配置
sqlservr -s MSSQLSERVER -mSQLCMD
net start MSSQLServer /mSQLCMD /f
SQLCMD

EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory', '40960'
GO
RECONFIGURE WITH OVERRIDE
GO
go

-- bcp
bcp Sales.Customer out c:\temp\customers_data.bcp -S MYSERVER -d adventureworks2016 -T -E -n

--各数据库日志大小及使用百分比
dbcc sqlperf(logspace)
go

--当前DB虚拟日志数量
DBCC loginfo
go

--数据库活动游标
DBCC activecursors
go

--查看操作系统逻辑磁盘可用空间
EXEC master.dbo.xp_fixeddrives
go

-- 数据库大小
select name,sum(size)*8/1024 from sys.database_files where type=0 group by name order by name
go
exec master.dbo.proc_getdbspaceused
go

--数据库表大小及行数（部分不算太准确，但可作为参考）
SELECT OBJECT_NAME(id) as tab,rows,(reserved*8)/1024 as size_MB
FROM SYS.sysindexes WHERE indid IN(0,1) and id in(select object_id from sys.tables )
order by size_MB desc
go

--数据库文件默认设置情况
select DB_NAME(database_id) as dbName,file_id,(size8/1024) as [size(mb)]
,case when is_percent_growth = 1 then '10%' else CONVERT(varchar(10),growth8/1024)+'M' end as growth
,type_desc,physical_name
from sys.master_files
where state = 0
go

--各数据库 buffer pool 的分配情况
SELECT
CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS Database_name
,count() AS cached_pages_count
,count()*8/1024 AS cached_space_in_mb
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb
FROM sys.dm_os_buffer_descriptors(nolock)
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
GO

--当前内存脏页数量及大小
SELECT db_name(database_id) AS 'Database'
,count(page_id) AS 'Dirty Pages'
,count(page_id)*8/1024 AS 'Dirty Pages(MB)'
FROM sys.dm_os_buffer_descriptors(nolock)
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY 'Dirty Pages' DESC
GO

--缓存类型数量大小
select cacheobjtype as [Cached Type]
,COUNT(*) [Number of Plans]
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]
from sys.dm_exec_cached_plans
group by cacheobjtype
order by [Plan Cache Size(MB)] desc
GO

--缓存对象数量大小
select objtype as [Cached Object Type]
,COUNT(*) as [Number of Plans]
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]
from sys.dm_exec_cached_plans
group by objtype
order by [Plan Cache Size(MB)] desc
GO

-- 前N行则表示最近的N分钟内CPU使用情况
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK));
SELECT TOP(60)
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
,SQLProcessUtilization AS [SQL Server Process CPU Utilization]
,SystemIdle AS [System Idle Process]
,(100 - SystemIdle - SQLProcessUtilization) AS [Other Process CPU Utilization]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS[SystemIdle]
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
GO

--复制相关
--事务复制：未分发命令数(分发服务器执行)
SELECT 'EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N'''

a.publisher + ''', @publisher_db = N''' + a.publisher_db
''', @publication = N''' + a.publication + ''', @subscriber = N'''
c.name + ''', @subscriber_db = N''' + b.subscriber_db
''', @subscription_type =' + CAST(b.subscription_type AS VARCHAR)
FROM distribution.dbo.MSreplication_monitordata a ( NOLOCK )
INNER JOIN (
SELECT publication_id ,subscriber_id ,subscriber_db ,subscription_type
FROM distribution.dbo.MSsubscriptions (NOLOCK)
GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type
) b ON a.publication_id = b.publication_id
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id
WHERE a.agent_type = 1
go
--查看前10个等待分发命令最多的事务数 及 查看命令
use distribution
go
SELECT top 10 A.xact_seqno,A.entry_time,COUNT(*) AS cmds
FROM distribution.dbo.MSrepl_transactions A(NOLOCK)
INNER JOIN distribution.dbo.MSrepl_commands B(NOLOCK)
ON A.xact_seqno=B.xact_seqno
GROUP BY A.xact_seqno,A.entry_time
ORDER BY cmds DESC
go

--查看出现错误的事务序列号(历史记录) (分发服务器执行)
SELECT 'EXEC distribution.dbo.sp_helpsubscriptionerrors N'''

a.publisher + ''', N''' + a.publisher_db + ''', N''' + a.publication + ''', N''' + c.name + ''',N''' + b.subscriber_db + ''''
FROM distribution.dbo.MSreplication_monitordata a ( NOLOCK )
INNER JOIN (
SELECT publication_id ,subscriber_id ,subscriber_db ,subscription_type
FROM distribution.dbo.MSsubscriptions (NOLOCK)
GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type
) b ON a.publication_id = b.publication_id
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id
WHERE a.agent_type = 1
GO