文章來源：https://mp.weixin.qq.com/s/pEXio0MNoi1k0w9XgYECNw
1. sqlserver查看實例級別的信息，使用SERVERPROPERTY函數
<code>select SERVERPROPERTY ('propertyname')/<code>2. 查看實例級別的某個參數XX的配置
<code>select * from sys.configurations where name='XX'/<code>
3. 更改實例級別的某個參數XX的值
<code>sp_configure 'XX','0'
RECONFIGURE WITH OVERRIDE/<code>
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。
4. sqlserver沒有系統表可以查詢所有數據庫下面對象，以下只能在當前數據庫下面查
<code>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 實例上運行的進程的相關信息，也就是所有數據庫上的線程，這個系統視圖會在後續的版本中刪除/<code>
5. 全局系統視圖、單個數據庫系統視圖
<code>sys.database_files --每個存儲在數據庫本身中的數據庫文件在表中佔用一行。這是一個基於每個數據庫的視圖。
sys.master_files --master 數據庫中的每個文件對應一行。這是一個系統範圍視圖。
--sys.database_files、sys.master_files這種的視圖，在每個數據庫的系統視圖下面都有/<code>
6. 一些只存在msdb的系統表，而非系統視圖
<code>dbo.backupset
dbo.log_shipping_secondary
dbo.restorehistory
dbo.sysjobs
dbo.sysjobhistory
--這些系統表只存在msdb數據庫，使用的時候必須加上msdb前綴/<code>
7. sp_lock、sp_who、sp_who2、sp_helptext等一些系統存儲過程存在於每個數據庫中
8. 報告有關鎖的信息，會顯示實例裡面的所有數據庫的鎖信息、堵塞信息
<code>sp_lock/<code>
9. 提供有關當前用戶、 會話和進程的實例中的信息，可以看到會話的狀態running、SUSPENDED、sleeping、rollback，sp_who2通過CPUTime、DiskIO可以判斷對應的transaction是否很大
<code>sp_who
sp_who2
sp_who2 active (可選參數LoginName, 或active代表活動會話數)
CPUTime (進程佔用的總CPU時間)
DiskIO (進程對磁盤讀的總次數)
LastBatch (客戶最後一次調用存儲過程或者執行查詢的時間)
ProgramName (用來初始化連接的應用程序名稱，或者主機名)/<code>
10. 查看某個存儲過程的內容
<code>sp_helptext pro_name/<code>
11.顯示某個線程號發送到sqlserver數據庫的最後一個語句
<code>DBCC INPUTBUFFER/<code>
12.假設查詢到249被鎖給堵塞了，查詢被堵塞的SQL語句
<code>DBCC INPUTBUFFER (249)/<code>
13. 查看某個數據庫中是否存在活動事務，有活動事務就一定會寫日誌
<code>DBCC OPENTRAN (dbname)/<code>
14. 監視日誌空間
<code>DBCC SQLPERF (LOGSPACE)/<code>
15. 查找無法重用日誌中的空間的原因(日誌無法截斷導致日誌文件越來越大，但是可用空間很小，無法收縮)
<code>select name,log_reuse_wait_desc from sys.databases/<code>
16. 查看虛擬日誌文件信息
<code>DBCC LOGINFO/<code>
結果有多少行，代表有多少虛擬日誌文件，活動的虛擬日誌文件的狀態(status)為2
17. 修復msdb數據庫，比如ssms頁面sql server agent丟失或看不了job view history等功能，說明msdb壞了，需要修復
<code>dbcc checkdb (msdb);/<code>
18. 在您當前連接到的 SQL Server 數據庫中生成一個手動檢查點
<code>CHECKPOINT [ checkpoint_duration ]
--checkpoint_duration表示以秒為單位指定手動檢查點完成所需的時間，一般不使用這個參數，讓數據庫自己控制
/<code>
19. 查看數據庫各種設置
<code>select name,State,user_access,is_read_only,recovery_model from sys.databases/<code>
20. 查看某個數據庫中是否存在會話
<code>select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname')/<code>21. 查詢當前阻塞的所有請求
<code>select * from sys.sysprocesses where blocked>0
或
SELECT t1.resource_type,db_name(t1.resource_database_id),t1.resource_associated_entity_id,t1.request_mode,
t1.request_session_id,t2.blocking_session_id,t2.wait_duration_ms
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
或
select A.SPID as 被阻塞進程,a.CMD AS 正在執行的操作,b.spid AS 阻塞進程號,b.cmd AS 阻塞進程正在執行的操作
from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid
或
SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type,
[Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2,
( CASE
WHEN er.statement_end_offset = -1
THEN
LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE
er.statement_end_offset
END
- er.statement_start_offset)
/ 2),
qt.text,program_name,Hostname,nt_domain,start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt
WHERE session_Id > 50 /* Ignore system spids.*/
AND sp.blocked>0 AND session_Id NOT IN (@@SPID)
或
SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';
--sys.dm_exec_requests返回SQL Server 中正在執行的每個請求的信息
/<code>
22. 查看哪些表被鎖了，以及這些表被哪個進程鎖了
<code>select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC/<code>
23. 查詢某個job是否被堵塞
<code>select * from msdb.dbo.sysjobs where name='jobname'
select a.program_name,a.* from master..sysprocesses a where a.program_name like '%0D1CE57E8AC5%'
--把第一個語句查詢到的job_id代入第二個語句的program_name
/<code>
24. 檢查SQL Agent是否開啟
<code>IF EXISTS (
SELECT TOP 1 1
FROM sys.sysprocesses
WHERE program_name = 'SQLAgent - Generic Refresher'
)
SELECT 'Running'
ELSE
SELECT 'Not Running'/<code>
25. 查看活動線程執行的sql語句,並生成批量殺掉的語句
<code>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/<code>
26. 查看備份進度
<code>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/<code>
27. 查看恢復進度
<code>SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'RESTORE%' --and database_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC/<code>
28. 查看數據庫的最近備份信息
<code>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 表示日誌備份/<code>
29. 查看數據庫的歷史備份記錄，並生成restore語句
<code>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/<code>
30. 查詢XX庫從YYYY-MM-DD日期開始的日誌備份記錄，並生成restore log的語句
<code>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/<code>
31. 查詢always on狀態是否正常
<code>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/<code>
32. 查看mirror鏡像信息
<code>SELECT
db_name(database_id),
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name,
mirroring_partner_instance
FROM sys.database_mirroring
33. 查詢SSRS Report Subscriptions相關的job
SELECT
b.name AS JobName
, e.name
, e.path
, d.description
, a.SubscriptionID
, laststatus
, eventtype
, LastRunTime
, date_created
, date_modified
FROM
ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON CONVERT(SYSNAME,a.ScheduleID) = b.name
JOIN ReportServer.dbo.ReportSchedule c ON b.name = CONVERT(SYSNAME,c.ScheduleID)
JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE
e.name = 'Report Name Goes Here'/<code>
34. 查看某個數據庫的數據文件信息，就算是mirror從庫的數據文件也可以查到，filestream目錄也可以查到
<code>SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N'DBA');/<code>
35. 查看某個數據文件信息
<code>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%'/<code>
36. 查詢實例的數據文件總大小
<code>SELECT sum(size*8/1024/1024) FROM master.sys.master_files/<code>
37. 查詢某個目錄中數據庫使用的總大小
<code>SELECT a.size*8/1024/1024 ,a.* FROM master.sys.master_files a WHERE physical_name like 'G:\\DEFAULT.DATA%'/<code>
38. 查詢某個目錄中哪些數據庫佔用了8G以上容量
<code>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/<code>
39. 查詢實例上的每個數據庫的大小
<code>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
40. 查詢總耗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/<code>
41. 查詢平均耗CPU最多的前3個SQL，且最近5小時出現過
<code>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/<code>
42. 查看當前最耗資源的10個SQL及其spid
<code>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
/<code>
43. 查詢某個存儲過程被哪些job調用了
<code>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作業名字即可/<code>
44. 命令執行某個job
<code>EXECUTE msdb.dbo.sp_start_job N'job_name'/<code>
45. 查詢某表標識列的列名
<code>SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1/<code>46. 獲取標識列的種子值
<code>SELECT IDENT_SEED ('表名')
/<code>47. 獲取標識列的遞增量
<code>SELECT IDENT_INCR('表名')/<code>48. 獲取指定表中最後生成的標識值
<code>SELECT IDENT_CURRENT('表名')/<code>49. 重新設置標識種子值為XX
<code>DBCC CHECKIDENT (表名, RESEED, XX)/<code>
50. 升級前，查詢服務器名、實例名、版本號
<code>select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version/<code>51. 用戶被grant這樣操作賦予的權限
<code>use dbname
exec sp_helprotect @username = 'username'
/<code>
52. 授予某個用戶執行某個數據庫的sp的權限
<code>use dbname
grant execute to "username"/<code>
53. always on
<code>-查看集群各節點的信息，包含節點成員的名稱，類型，狀態，擁有的投票仲裁數
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;
select ar.replica_server_name, db_name(drs.database_id),drs.truncation_lsn,
drs.log_send_queue_size, drs.redo_queue_size
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;
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) 中的給定輔助數據庫做的日誌記錄速率 / 秒/<code>
54. 查詢實例的FILESTREAM 使用的DIRECTORY_NAME
<code>SELECT SERVERPROPERTY('FilestreamShareName')/<code>55. 查詢FILETABLE表的數據庫對應的DIRECTORY_NAME
<code>select db_name(database_id),* from sys.database_filestream_options
僅僅使用filestream功能時，數據庫不需要對應的DIRECTORY_NAME/<code>
56. 查詢FILETABLE表對應的DIRECTORY_NAME
<code>select object_name(object_id),* from sys.filetables/<code>
57. 查詢filetable表testdb.dbo.table1中的文件完整路徑名稱
<code>SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1
58. 查詢所有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;
/<code>
59. 鎖表的四種用法
<code>TABLOCKX
SELECT * FROM table WITH (TABLOCKX)
查詢過程中，其他會話無法查詢、更新此表，直到查詢過程結束
TABLOCK
SELECT * FROM table WITH (TABLOCK)
查詢過程中，其他會話可以查詢，但是無法更新此表，直到查詢過程結束
HOLDLOCK
SELECT * FROM table WITH (HOLDLOCK)
查詢過程中，其他會話可以查詢，但是無法更新此表，直到查詢過程結束
NOLOCK
SELECT * FROM table WITH (NOLOCK)
查詢過程中，其他會話可以查詢、更新此表/<code>
60. 查詢某個發佈XX，發佈的數據庫對象的2種方法
<code>發佈數據庫上執行(數據來源這三張表distribution.dbo.MSpublications、distribution.dbo.MSarticles、sysarticlecolumns)
select a.article,a.source_object,a.destination_object,b.colid from
(select article,article_id,source_object,destination_object
from [distribution].[dbo].MSarticles where publication_id in
( select publication_id from
[distribution].[dbo].MSpublications where publication='XX'
)
) a
inner join
(select * from replicate1.dbo.sysarticlecolumns) b
on a.article_id=b.artid order by a.article
訂閱數據庫上執行
select distinct article from MSreplication_objects where publication='XX'
/<code>
61. 查詢發佈信息，發佈名稱，發佈名稱對應的發佈序號
<code>Select * from distribution.dbo.MSpublications/<code>
62. 查詢發佈名裡面的發佈對象的信息，包含表、視圖、存儲過程等
<code>Select * from distribution.dbo.MSarticles/<code>
63. 監控發佈訂閱是否有異常，執行以下5條語句即可
<code>select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].MSrepl_errors order by 2 desc
select * from msdb.dbo.sysreplicationalerts order by 7 desc/<code>
64. 查詢XX表的索引信息
<code>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')/<code>
65. 生成sql語句的執行計劃(select XXX為例，當然select XXX也可以換成執行存儲過程比如exec pro_XXX，都是隻生成執行計劃，不產生結果集，不會執行存儲過程)
<code>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/<code>
66. 查詢名稱為XXX的job的最後一次運行成功的時間
<code>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
/<code>
67. 查詢某張分區表的總行數和大小，比如表為crm.EmailLog
<code>exec sp_spaceused 'crm.EmailLog';/<code>
68. 查詢某張分區表的信息，每個分區有多少行，比如表為crm.EmailLog
<code>select convert(varchar(50), ps.name
) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name
) as filegroup,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = object_id('crm.EmailLog')
and i.index_id in (0, 1)
order by p.partition_number/<code>
69. 查詢分區函數
<code>select * from sys.partition_functions/<code>
70. 查看分區架構
<code>select * from sys.partition_schemes/<code>
71. 查詢ssis包的信息
<code>select * from msdb.dbo.sysssispackages/<code>
72. 查詢某張表裡的索引的大小,如下示例表為dbo.table1
<code>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/<code>
73. 重建表上的所有索引
<code>alter index all on table_name rebuild with (online=on)
重建表上的某個索引
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/<code>
74. 查看數據文件可收縮空間，結果見Availabesize_MB字段值
<code>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')
75. 查詢某個表中的全部索引的信息
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 /<code>
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
76. 查詢某個數據庫下的表數據佔用磁盤容量最大的10張表
<code>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
--這個比上一個專業
77. 查詢某個數據庫中是否有create index '+name+ CHAR(10) /<code>
<code>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;/<code>
78. 根據id號查詢某個數據庫名
<code>SELECT DB_NAME(18)
根據id號查詢某個對象名
SELECT OBJECT_NAME(1769220894)
/<code>
79. 查看收縮的進度100%，此語句要到指定的數據庫下執行
<code>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/<code>
80. 查看重新組織索引的100%進度
<code>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/<code>
81. 查看存儲過程的執行計劃
<code>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;/<code>
82. 查看當前用戶
<code>select system_user/<code>
83. 查詢ddl修改操作的記錄
<code>-執行如下找到trace文件的目錄和名稱
select * from Sys.traces
-使用sqlserver profiler工具打開trace文件，就可以查到相關記錄/<code>
原文鏈接：http://blog.itpub.net/30126024/viewspace-2638523/
分享到:
閱讀更多 數據和雲智能 的文章
