sqlserver dba常用的sql語句

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


sqlserver沒有系統表可以查詢所有資料庫下面物件
如下只能在當前資料庫下面查
select * from sys.all_objects --查詢當前資料庫的所有架構範圍的物件
select * from sys.sysobjects --查詢當前資料庫的所有物件
--sys.all_objects、sys.sysobjects這種的檢視，在每個資料庫的系統檢視下面都有


select * from sys.databases --在當前資料庫下可以查詢到所有資料庫資訊，包含是否on狀態
select * from sys.sysdatabases --在當前資料庫下可以查詢到所有資料庫資訊，不包含是否on狀態，這個系統檢視會在後續的版本中刪除
--sys.databases、sys.sysdatabases這種的檢視，在每個資料庫的系統檢視下面都有


sys.processes --沒有這個檢視
select * from sys.sysprocesses --在當前資料庫下可以查詢所有正在SQL Server 例項上執行的程式的相關資訊，也就是所有資料庫上的執行緒，這個系統檢視會在後續的版本中刪除


全域性系統檢視、單個資料庫系統檢視
sys.database_files --每個儲存在資料庫本身中的資料庫檔案在表中佔用一行。 這是一個基於每個資料庫的檢視。
sys.master_files --master 資料庫中的每個檔案對應一行。 這是一個系統範圍檢視。
--sys.database_files、sys.master_files這種的檢視，在每個資料庫的系統檢視下面都有


一些只存在msdb的系統表，而非系統檢視
dbo.backupset
dbo.log_shipping_secondary
dbo.restorehistory
dbo.sysjobs
dbo.sysjobhistory
--這些系統表只存在msdb資料庫，使用的時候必須加上msdb字首


sp_lock、sp_who、sp_who2、sp_helptext等一些系統儲存過程存在於每個資料庫中

sp_lock
報告有關鎖的資訊，會顯示例項裡面的所有資料庫的鎖資訊、堵塞資訊

sp_who
sp_who2
提供有關當前使用者、 會話和程式的例項中的資訊，兩者都沒多大意義了，可以使用sys.dm_exec_sessions或sys.sysprocesses代替他們了，sys.dm_exec_sessions和sys.sysprocesses沒啥區別，前者的is_user_process=0和後者的status='background'或cmd='TASK MANAGER'是一樣的
sp_who、sp_who2兩者都可以使用可選引數LoginName、active，不加參數列示當前所有使用者和所有會話，其中sp_who2除了顯示sp_who的輸出資訊外,還多了四列資訊：CPUTime 程式佔用的總CPU時間、DiskIO 程式對磁碟讀的總次數、LastBatch 客戶最後一次呼叫儲存過程或者執行查詢的時間、ProgramName 用來初始化連線的應用程式名稱，或者主機名


檢視sqlserver 資料庫例項啟動時間
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1


檢視某個儲存過程的內容
sp_helptext pro_name


DBCC INPUTBUFFER(session_id)：顯示某個執行緒號傳送到sqlserver資料庫的最後一個語句
DBCC INPUTBUFFER (249):假設查詢到249被鎖給堵塞了，執行上面的可以查到被堵塞的SQL語句

檢視某個資料庫中是否存在活動事務，有活動事務就一定會寫日誌，顯示結果為最早的一個事務的ID
DBCC OPENTRAN (dbname)


監視日誌空間
DBCC SQLPERF (LOGSPACE)

查詢無法重用日誌中的空間的原因(日誌無法截斷導致日誌檔案越來越大，但是可用空間很小，無法收縮)
select name,log_reuse_wait_desc from sys.databases

檢視虛擬日誌檔案資訊
DBCC LOGINFO
--結果有多少行，代表有多少虛擬日誌檔案，活動的虛擬日誌檔案的狀態(status)為2


修復msdb資料庫，比如ssms頁面sql server agent丟失或看不了job view history等功能，說明msdb壞了，需要修復
dbcc checkdb (msdb);


在您當前連線到的 SQL Server 資料庫中生成一個手動檢查點
CHECKPOINT [ checkpoint_duration ]
--checkpoint_duration表示以秒為單位指定手動檢查點完成所需的時間，一般不使用這個引數，讓資料庫自己控制


檢視資料庫各種設定
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')


查詢當前阻塞的所有請求
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 db_name(a.resource_database_id) dbname,object_name(a.resource_associated_entity_id,a.resource_database_id) tablename,
b.spid blocked_spid,a.request_session_id blocker_spid,a.request_mode,a.request_owner_type,a.resource_type
from sys.dm_tran_locks  a
inner join sys.sysprocesses b on a.request_session_id=b.blocked
and a.resource_type='OBJECT' and b.blocked<>0


查詢某個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'



檢查SQL Agent是否開啟
IF EXISTS (
SELECT TOP 1 1
FROM sys.sysprocesses
WHERE program_name = 'SQLAgent - Generic Refresher'
)
SELECT 'Running'
ELSE
SELECT 'Not Running'


檢視活動執行緒執行的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 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


檢視恢復進度
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 'Backup%'
ORDER BY 2 DESC


檢視資料庫的最近備份資訊
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 表示日誌備份

單獨查詢某個資料庫DB1的備份資訊
SELECT bs.database_name,bs.backup_start_date,bs.backup_finish_date,bmf.physical_device_name,
case bs.type when 'D' then  'FUll' when 'I' then 'DIFF' when 'L' then 'LOG' end,
bs.*
FROM msdb.dbo.backupmediafamily bmf INNER JOIN msdb.dbo.backupset bs
ON bs.media_set_id = bmf.media_set_id and bs.database_name='DB1' order by 3 desc


查詢備份資訊
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'


檢視資料庫的歷史備份記錄，並生成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


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



查詢mirror映象資訊包含主從伺服器名稱，資料庫的同步模式和同步狀態
select mirroring_partner_name,mirroring_partner_instance,mirroring_witness_name,db_name(database_id) db_name,mirroring_role_desc,mirroring_safety_level_desc,mirroring_state_desc from sys.database_mirroring


查詢SSRS Report相關資訊，根據subscription的description查詢report和job和report報表層級路徑
select a.Name ReportName,a.path ReportPath,b.Description Subscriptions_name,b.SubscriptionID,c.ScheduleID Sqlagentjob_name,
b.ModifiedDate,d.LastRunTime,b.LastStatus
from [ReportServer].[dbo].[Catalog] a
inner join [ReportServer].[dbo].[Subscriptions] b
on a.ItemID=b.Report_OID
inner join [ReportServer].[dbo].[ReportSchedule] c
on b.SubscriptionID=c.SubscriptionID
inner join [ReportServer]. [dbo].[Schedule] d
on c.ScheduleID=d.ScheduleID and b.Description='LSE01 - 1st of Every Month'


檢視某個資料庫的資料檔案資訊，就算是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 DataSizeMB,
(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


查詢平均耗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'

查詢例項上所有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;

查詢名稱為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


自增長列相關

查詢某表標識列的列名
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)


升級前，查詢伺服器名、例項名、版本號
select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version


檢視SQLServer使用者哪些許可權是使用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，而其他資料庫並沒有這幾個角色

SQLAgentReaderRole對檢視msdb.dbo.sysjobs_view有SELECT許可權(繼承SQLAgentUserRole的許可權)，GUI介面操作時從檢視獲取資料。所以新增到此角色後，展開作業就能返回所有作業。
我們用語句查詢作業時，習慣直接從msdb.dbo.sysjobs這類表入手。但SQLAgentUserRole角色並沒有對此類表有SELECT許可權，因此常規語句會報拒絕對物件的SELECT許可權。

查詢某資料庫下，角色擁有的成員資訊(比如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;  


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 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) 中的給定輔助資料庫做的日誌記錄速率 / 秒


查詢例項的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


鎖表的六種用法
TABLOCKX
SELECT * FROM table WITH (TABLOCKX)
查詢過程中，其他會話無法查詢、更新此表，直到查詢過程結束

XLOCK
SELECT * FROM table WITH (XLOCK)
查詢過程中，其他會話無法查詢、更新此表，直到查詢過程結束

UPDLOCK
SELECT * FROM table WITH (UPDLOCK)
查詢過程中，其他會話可以查詢，但是無法更新此表，直到查詢過程結束

TABLOCK
SELECT * FROM table WITH (TABLOCK)
查詢過程中，其他會話可以查詢，但是無法更新此表，直到查詢過程結束

HOLDLOCK
SELECT * FROM table WITH (HOLDLOCK)
查詢過程中，其他會話可以查詢，但是無法更新此表，直到查詢過程結束

NOLOCK
SELECT * FROM table WITH (NOLOCK)
查詢過程中，其他會話可以查詢、更新此表


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




查詢某張分割槽表的總行數和大小，比如表為crm.EmailLog
exec sp_spaceused 'crm.EmailLog';


查詢某分割槽表每個分割槽的行數,RangePFun表示分割槽函式，Funcol表示分割槽欄位
SELECT $PARTITION.RangePFun(Funcol) AS Partition, COUNT(*) AS [COUNT]
FROM dbname.dbo.PartitionTable
GROUP BY dbname.$PARTITION.RangePFun(Funcol)  
ORDER BY Partition ;  


查詢第三個分割槽的所有行,RangePF表示分割槽函式，Rangecolumn表示分割槽欄位
SELECT * FROM dbname.dbo.PartitionTable
WHERE dbname.$PARTITION.RangePF(Rangecolumn) = 3 ;


查詢某分割槽表XX的分割槽欄位
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,t.name AS TableName,ic.column_id AS PartitioningColumnID,
c.name AS PartitioningColumnName,i.name as IndexName,i.type,i.type_desc
FROM sys.tables AS t   
JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[type] <= 1 -- clustered index or a heap   
JOIN sys.partition_schemes AS ps ON ps.data_space_id = i.data_space_id   
JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column   
JOIN sys.columns AS c ON t.[object_id] = c.[object_id] AND ic.column_id = c.column_id   
WHERE t.name = 'XXX';   
備註：如果此語句查不到，有可能表本身和表的聚集索引都沒有分割槽，而只是其他普通索引進行了分割槽，這個時候請把AND i.[type] <= 1註釋掉再查


查詢某個分割槽架構下的分割槽表的大小和總行數
select a.SchemaName,a.tablename,a.TotalSpaceGB,b.RowCounts from
(
SELECT
    s.Name AS SchemaName,
    t.NAME AS TableName,
    SUM(a.total_pages)*8/1024/1024 AS TotalSpaceGB,
    SUM(a.used_pages)*8/ 1024/1024 AS UsedSpaceGB,
    (SUM(a.total_pages)-SUM(a.used_pages))*8/1024/1024 AS UnusedSpaceGB
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 INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE t.object_id in
(SELECT distinct i.object_id
FROM sys.indexes AS i
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS PS ON PS.data_space_id = i.data_space_id
JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id
JOIN sys.objects o ON i.object_id=o.object_id
JOIN sys.schemas AS s ON o.schema_id=s.schema_id
JOIN sys.tables AS t ON t.object_id = i.object_id
JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT JOIN sys.partition_range_values AS r ON PF.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE ds.type = 'PS' AND o.type_desc='USER_TABLE'
)
GROUP BY s.Name,t.Name
) a
inner join
(
SELECT
    s.Name AS SchemaName,
    t.NAME AS TableName,
    sum(p.rows) AS RowCounts
FROM
    sys.tables t INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id and i.index_id in (0,1) 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.object_id in
(SELECT distinct i.object_id
FROM sys.indexes AS i
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS PS ON PS.data_space_id = i.data_space_id
JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id
JOIN sys.objects o ON i.object_id=o.object_id
JOIN sys.schemas AS s ON o.schema_id=s.schema_id
JOIN sys.tables AS t ON t.object_id = i.object_id
JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT JOIN sys.partition_range_values AS r ON PF.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE ds.type = 'PS' AND o.type_desc='USER_TABLE'
)
GROUP BY t.Name, s.Name
) b
on a.SchemaName=b.SchemaName and a.TableName=b.TableName
order by a.SchemaName,b.RowCounts desc
--備註：此次需要a和b兩個表，a表前部分不能用index_id in (0,1)，因為index_id等於非0,1的時候比如普通索引也會佔用磁碟空間，也需要把這部分磁碟空間算進來;而b表前部分需要用index_id in (0,1)條件，因為index_id等於非0,1的時候比如普通索引，這個時候這個普通索引的行數也會算入該表，這樣就會使算出來的表的行數翻倍。
--如果此語句查的表的行數結果有問題，有可能表本身和表的聚集索引都沒有分割槽，而只是其他普通索引進行了分割槽，這個時候請把b表中index_id in (0,1)的0或1改成其他值，這個值可以透過檢視分割槽表的分割槽欄位對應的索引資訊sys.indexes的type欄位看到


查詢所有分割槽表每個分割槽的範圍和行數，如果要查某張特定的表加上where t.name即可
select distinct ps.name as partition_scheme,s.name as Schemaname,t.name as tablename,p.partition_number,ds2.name as filegroup,v.value as range_boundary,p.rows as rows
from sys.tables t
inner join sys.indexes i on t.OBJECT_ID = i.object_id
inner join  sys.partition_schemes ps on i.data_space_id = ps.data_space_id
inner join  sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id
inner join  sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
inner 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
inner join  sys.partition_functions pf on ps.function_id = pf.function_id
inner join  sys.schemas s ON t.schema_id = s.schema_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 t.name='RAY_FinalEPSAnnualFCHistory'
order by ps.name,s.name,t.name,p.partition_number
或
SELECT distinct PF.name AS PartitionFunction,ds.name AS PartitionScheme,s.name AS Schemaname,o.name AS PartitionedTable,p.partition_number,r.value,p.rows
FROM sys.indexes AS i
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS PS ON PS.data_space_id = i.data_space_id
JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id
JOIN sys.objects o ON i.object_id=o.object_id
JOIN sys.schemas AS s ON o.schema_id=s.schema_id
JOIN sys.tables AS t ON t.object_id = i.object_id
JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT JOIN sys.partition_range_values AS r ON PF.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE ds.type = 'PS' AND o.type_desc='USER_TABLE'
--t.name='RAY_FinalEPSAnnualFCHistory'
order by PF.name,ds.name,s.name,o.name,p.partition_number



查詢資料庫下所有的分割槽表的表名
select distinct ps.name as partition_scheme,s.name as Schemaname,t.name as tablename
from sys.tables t
inner join sys.indexes i on t.OBJECT_ID = i.object_id
inner join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
inner join sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id
inner join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
inner 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
inner join sys.partition_functions pf on ps.function_id = pf.function_id
inner join sys.schemas s ON t.schema_id = s.schema_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
order by ps.name,s.name,t.name
或
SELECT distinct PF.name AS PartitionFunction,ds.name AS PartitionScheme,s.name AS Schemaname,o.name AS PartitionedTable
FROM sys.indexes AS i
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS PS ON PS.data_space_id = i.data_space_id
JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id
JOIN sys.objects o ON i.object_id=o.object_id
JOIN sys.schemas AS s ON o.schema_id=s.schema_id
JOIN sys.tables AS t ON t.object_id = i.object_id
JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT JOIN sys.partition_range_values AS r ON PF.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE ds.type = 'PS' AND o.type_desc='USER_TABLE'
order by PF.name,ds.name,s.name,o.name

查詢分割槽函式
select * from sys.partition_functions

檢視分割槽架構
select * from sys.partition_schemes

查詢ssis包的資訊
select * from msdb.dbo.sysssispackages


查詢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')


查詢某張表裡的索引的大小,如下示例表為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 (online=on)

重新組織表上的所有索引
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')


查詢某個表中的全部索引的使用資訊，比如表名為LbaListAlertDetail，它下面的索引的最後一次訪問時間，最後一次掃描時間，最後一次搜尋時間等
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



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
--這個比上一個專業


查詢某個資料庫中的儲存過程程式碼是否包含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;

查詢某個資料庫中的檢視程式碼是否包含linked server名稱為Linkedserver，objectproperty函式只能查當前資料庫的資訊，需要加'use '+name+ CHAR(10)
select 'use '+name+ CHAR(10) +'select DB_NAME(),OBJECT_NAME(OBJECT_ID),''View'',definition from '+name+'.sys.sql_modules
WHERE object_id in (select OBJECT_ID from sys.objects where type=''V'')
AND definition like ''%Linkedserver.%''' from sys.databases;

查詢某個資料庫中的儲存過程程式碼是否包含linkedserver名稱為Linkedserver，objectproperty函式只能查當前資料庫的資訊，需要加'use '+name+ CHAR(10)
select 'use '+name+ CHAR(10) +'select DB_NAME(),OBJECT_NAME(OBJECT_ID),''SP'',definition from '+name+'.sys.sql_modules
WHERE object_id in (select OBJECT_ID from sys.objects where type=''P'')
AND definition like ''%FirstCallEMX.%''' from sys.databases;


根據id號查詢某個資料庫名
SELECT DB_NAME(18)

根據id號查詢某個物件名
SELECT OBJECT_NAME(1769220894)

檢視收縮的進度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


檢視重新組織索引的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

檢視儲存過程的執行計劃
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;




查詢ddl修改操作的記錄
1、執行如下找到trace檔案的目錄和名稱
select * from Sys.traces
2、使用sqlserver profiler工具開啟trace檔案，就可以查到相關記錄


檢視當前使用者
select system_user




查詢每個例項是否都有名稱為DBA的資料庫
select A.servername,B.name from
(select @@servername as servername) as A left join
(select name, @@servername servername from sys.databases where name='DBA') as B
on A.servername=B.servername


檢視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 ;


檢視某個資料下，哪些使用者擁有哪些角色許可權
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'


查詢哪些表的哪些欄位需要建立索引，並生成建立索引語句
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的值


查詢哪些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


檢視本地伺服器是否因為訪問linked server慢，從而導致本地伺服器的程式慢
select * from sys.sysprocesses where waitresource like '%SPID=%'

查詢會話被堵塞blocking了多長時間
select  session_id, blocking_session_id,command,db_name(database_id) dbname,wait_type,wait_time/1000 wait_time_second,wait_resource
from sys.dm_exec_requests where blocking_session_id<>0

查詢哪些資料庫的哪些表產生了鎖升級
SELECT db_name(database_id),object_name(object_id,database_id),index_lock_promotion_count
FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL) where index_lock_promotion_count > 0;    




查詢當前資料庫名稱
Select db_name(Dbid) From sys.sysprocesses Where Spid = @@spid





查詢資料庫級別的change_tracking是否開啟
SELECT DB_NAME(database_id) DataBaseName,is_auto_cleanup_on,retention_period,retention_period_units_desc FROM sys.change_tracking_databases

查詢表級別的change_tracking是否開啟
SELECT OBJECT_NAME(object_id) TableName,is_track_columns_updated_on FROM sys.change_tracking_tables

查詢某段sql XXX在執行計劃快取中的資訊
SELECT cp.memory_object_address, cp.objtype, refcounts, usecounts,
    qs.query_plan_hash, qs.query_hash,
    qs.plan_handle, qs.sql_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle)
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle)
INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE text LIKE '%sql XXX%'





查詢資料庫最近幾個小時內的死鎖資訊，如果跨度時間太長，比如想查10個小時前的死鎖資訊可能查不到
SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
            FROM sys.dm_xe_session_targets AS xt
            INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
            WHERE xs.name = N'system_health'
              AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC

查詢某個會話411執行的sql語句(會話狀態包含running、sleeping)
DECLARE @Handle varbinary(64);  
SELECT @Handle = sql_handle from sys.sysprocesses  where spid=411
SELECT db_name(dbid),* FROM sys.fn_get_sql(@Handle);  
或
select Replace(b.text,'''','''') ,a.*
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b where a.spid=411


查詢例項下面有哪些加密資料庫
SELECT name,DEK.* FROM master.sys.databases D
JOIN master.sys.dm_database_encryption_keys DEK ON DEK.database_id = D.database_id
ORDER BY name


SELECT SUM(user_object_reserved_page_count) * 8/1024/1024 as user_object_size_GB,
       SUM(internal_object_reserved_page_count) * 8/1024/1024 as internal_object_size_GB,
       SUM(unallocated_extent_page_count) * 8/1024/1024 as unallocated_size_GB,
       SUM(allocated_extent_page_count) * 8/1024/1024 as allocated_size_GB,
       SUM(mixed_extent_page_count) * 8/1024/1024 as mixed_size_GB,
       SUM(version_store_reserved_page_count) * 8/1024/1024 as version_store_size_GB,
       SUM(total_page_count) * 8/1024/1024 as total_size_GB
  FROM tempdb.sys.dm_db_file_space_usage;
--查詢tempdb的tempfile的使用情況
--user_object_reserved_page_count表示：全域性臨時表和索引、區域性臨時表和索引、表變數
--internal_object_reserved_page_count表示：遊標、臨時大型物件 (LOB) 、雜湊聯接、排序等操作
--version_store_reserved_page_count表示:版本儲存


use tempdb
go
SELECT top 100 t1.session_id, 'KILL '+CAST(t1.session_id AS NVARCHAR(100)) AS KillCmd ,                                                 
t1.internal_objects_alloc_page_count,t1.user_objects_alloc_page_count, t3.host_name,t3.login_name,t3.login_time,
(t1.internal_objects_alloc_page_count-t1.internal_objects_dealloc_page_count)*8/1024/1024 internal_nodealloc_GB ,
(t1.user_objects_alloc_page_count-t1.user_objects_dealloc_page_count)*8/1024/1024 user_nodealloc_GB ,
t3.last_request_start_time,t3.status,t3.total_elapsed_time
from sys.dm_db_session_space_usage  t1
inner join sys.dm_exec_sessions as t3
on t1.session_id = t3.session_id
where (t1.internal_objects_alloc_page_count>0
or t1.user_objects_alloc_page_count >0
or t1.internal_objects_dealloc_page_count>0
or t1.user_objects_dealloc_page_count>0)
order by t1.internal_objects_alloc_page_count-t1.internal_objects_dealloc_page_count desc
--查詢消耗tempdb的session資訊

select Replace(b.text,'''','''') ,a.*
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b where a.spid in (XX)
--查詢session中執行的sql語句，XX就是第一個語句結果中的第一列t1.session_id


SELECT a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds, b.program_name
 , b.open_tran, b.STATUS, b.login_time, b.hostname, db_name(b.dbid) dbname
FROM sys.dm_tran_active_snapshot_database_transactions a
JOIN sys.sysprocesses b ON a.session_id = b.spid
ORDER BY elapsed_time_seconds DESC
--查詢生成行版本或可能訪問行版本的活動事務，即涉及使用version_store_reserved_page_count的會話


select db_name(database_id),* from sys.dm_tran_version_store_space_usage order by 3 desc
查詢每個資料庫在tempdb中佔有的消耗的版本儲存空間


SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count,
  SUM(internal_objects_dealloc_page_count*8/1024/1024) AS task_internal_objects_dealloc_size_GB
FROM sys.dm_db_task_space_usage
GROUP BY session_id
order by 4 desc;
--獲取每個會話中當前執行的所有任務的內部物件所消耗的空間

SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  order by 2 desc;
--獲取當前會話中的內部物件在執行和完成任務時所消耗的空間


select name,snapshot_isolation_state_desc from sys.databases order by 2
--查詢資料庫的隔離級別


UPDATE J SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = 'XX';
--對category名稱為XX的所有job設定為啟用


select a.name job_name,c.name schedule_name,
case c.freq_type when 1  then 'One time only'
when 4 then 'daily'
when 8 then 'weekly'
when 16 then 'monthly'
when 32 then 'monthly'
when 64 then 'Runs when the SQL Server Agent service starts'
when 128 then 'Runs when the computer is idle' end freq_type,
c.freq_interval,
case c.freq_subday_type
when 1 then 'At the specified time'
when 2 then 'Seconds'
when 3 then 'Minutes'
when 4 then 'Hours' end freq_subday_type,
c.freq_subday_interval,
case c.freq_relative_interval
when 1 then 'First'
when 2 then 'Second'
when 4 then 'Third'
when 8 then 'Fourth'
when 16 then 'Last' end freq_relative_interval,
c.freq_recurrence_factor
from [dbo].[sysjobs] a
inner join [dbo].[sysjobschedules] b on a.job_id=b.job_id
inner join [dbo].[sysschedules] c on b.schedule_id=c.schedule_id
--查詢所有sql agent job作業的schedule排程計劃


SELECT JOB.name job_name,STP.database_name,STP.step_name,STP.command,*
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 '%XX%' or STP.database_name like '%XX%'
--查詢某個資料庫XX相關的sqlserver agent job


SELECT DISTINCT
    J.name [JobName],
    DATEDIFF(SS, P.login_time, GETDATE()) [CurrentRun]
FROM [DBDEV15\BACKTESTING].msdb.dbo.sysjobs J
INNER JOIN [DBDEV15\BACKTESTING].master.dbo.sysprocesses P
ON J.job_id =
    CONVERT(UNIQUEIDENTIFIER,
        CONVERT(VARBINARY(128),
            SUBSTRING(
                P.[program_name],
                CHARINDEX(
                    '(Job ',
                    P.[program_name]
                ) + 5,
                CHARINDEX(
                    ' ',
                    P.[program_name],
                    CHARINDEX(
                        '(Job ',
                        P.[program_name]
                    ) + 5
                ) -
                (
                    CHARINDEX(
                        '(Job ',
                        P.[program_name]
                    ) + 5
                )
            ),
            1
        )
    ) AND
    J.[name] = 'DBA - Restore Wondb'
WHERE P.[program_name] LIKE 'SQLAgent%(Job %)%'
--檢視當前正在執行的Job 'DBA - Restore Wondb'的開始執行時間



SELECT Job.name,case job.enabled when 0 then 'No' else 'Yes' end as 'Job Enable',
       case when JobActivity.run_requested_date is null then 'No' else 'Yes' end as 'Job have Schedule',
       case JobHistory.RUN_STATUS when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when 3 then 'Cancled'
       when 4 then 'In progress' else 'Job have no status,mabye never running'end as 'Job Running Result'
     FROM MSDB.DBO.SYSJOBS Job
     INNER JOIN MSDB.DBO.SYSJOBACTIVITY JobActivity ON (Job.job_id = JobActivity.job_id)
     INNER JOIN (SELECT MAX(SESSION_ID) AS SESSION_ID FROM MSDB.DBO.SYSSESSIONS) AS JobSesssion ON (JobActivity.session_ID = JobSesssion.SESSION_ID)
     LEFT JOIN MSDB.DBO.SYSJOBHISTORY JobHistory ON (JobActivity.job_history_id = JobHistory.instance_id)
--檢視例項上所有job在MSDB.DBO.SYSJOBHISTORY期限內的最近一次執行結果


select name,product,provider,data_source from sys.servers where data_source in(select data_source from sys.servers where is_linked=1 group by data_source having count(data_source)>1) order by data_source
--查詢linked server名稱不同但是使用的是相同的data source的linked server


select a.name trigger_name, b.type_desc trigger_type,a.*,b.* from sys.server_triggers a inner join sys.server_trigger_events b on a.object_id=b.object_id
--查詢觸發器名稱和觸發器型別