sqlserver dba常用的sql語(yǔ)句有哪些

這篇文章主要講解了“sqlserver dba常用的sql語(yǔ)句有哪些”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“sqlserver dba常用的sql語(yǔ)句有哪些”吧！

sqlserver查看實(shí)例級(jí)別的信息，使用SERVERPROPERTY函數(shù)

select SERVERPROPERTY ('propertyname')

查看實(shí)例級(jí)別的某個(gè)參數(shù)XX的配置

select * from sys.configurations where name='XX'

更改實(shí)例級(jí)別的某個(gè)參數(shù)XX的值

sp_configure 'XX','0'
RECONFIGURE WITH OVERRIDE

sp_configure顯示或更改當(dāng)前服務(wù)器的全局配置設(shè)置。
RECONFIGURE表示SQL Server不用重新啟動(dòng)就立即生效

使用sp_configure更改設(shè)置時(shí)，請(qǐng)使用RECONFIGURE語(yǔ)句使更改立即生效，否則更改將在SQL Server重新啟動(dòng)后生效。RECONFIGURE后面加WITH OVERRIDE表示不管這個(gè)值是不是符合要求都會(huì)生效，比如recovery interval的范圍值是10--60對(duì)應(yīng)sys.configurations.minimum是10、sys.configurations.maximum是60，如果sp_configure 'recovery interval', 75設(shè)置為75，超過(guò)了這個(gè)10--60規(guī)范，但是要讓75生效，則必須加上WITH OVERRIDE

sqlserver沒(méi)有系統(tǒng)表可以查詢所有數(shù)據(jù)庫(kù)下面對(duì)象

如下只能在當(dāng)前數(shù)據(jù)庫(kù)下面查

select * from sys.all_objects --查詢當(dāng)前數(shù)據(jù)庫(kù)的所有架構(gòu)范圍的對(duì)象

select * from sys.sysobjects --查詢當(dāng)前數(shù)據(jù)庫(kù)的所有對(duì)象

--sys.all_objects、sys.sysobjects這種的視圖，在每個(gè)數(shù)據(jù)庫(kù)的系統(tǒng)視圖下面都有

select * from sys.databases --在當(dāng)前數(shù)據(jù)庫(kù)下可以查詢到所有數(shù)據(jù)庫(kù)信息，包含是否on狀態(tài)

select * from sys.sysdatabases --在當(dāng)前數(shù)據(jù)庫(kù)下可以查詢到所有數(shù)據(jù)庫(kù)信息，不包含是否on狀態(tài)，這個(gè)系統(tǒng)視圖會(huì)在后續(xù)的版本中刪除

--sys.databases、sys.sysdatabases這種的視圖，在每個(gè)數(shù)據(jù)庫(kù)的系統(tǒng)視圖下面都有

sys.processes --沒(méi)有這個(gè)視圖

select * from sys.sysprocesses --在當(dāng)前數(shù)據(jù)庫(kù)下可以查詢所有正在SQL Server 實(shí)例上運(yùn)行的進(jìn)程的相關(guān)信息，也就是所有數(shù)據(jù)庫(kù)上的線程，這個(gè)系統(tǒng)視圖會(huì)在后續(xù)的版本中刪除

全局系統(tǒng)視圖、單個(gè)數(shù)據(jù)庫(kù)系統(tǒng)視圖

sys.database_files --每個(gè)存儲(chǔ)在數(shù)據(jù)庫(kù)本身中的數(shù)據(jù)庫(kù)文件在表中占用一行。 這是一個(gè)基于每個(gè)數(shù)據(jù)庫(kù)的視圖。

sys.master_files --master 數(shù)據(jù)庫(kù)中的每個(gè)文件對(duì)應(yīng)一行。 這是一個(gè)系統(tǒng)范圍視圖。

--sys.database_files、sys.master_files這種的視圖，在每個(gè)數(shù)據(jù)庫(kù)的系統(tǒng)視圖下面都有

一些只存在msdb的系統(tǒng)表，而非系統(tǒng)視圖

dbo.backupset

dbo.log_shipping_secondary

dbo.restorehistory

dbo.sysjobs

dbo.sysjobhistory

--這些系統(tǒng)表只存在msdb數(shù)據(jù)庫(kù)，使用的時(shí)候必須加上msdb前綴

sp_lock、sp_who、sp_who2、sp_helptext等一些系統(tǒng)存儲(chǔ)過(guò)程存在于每個(gè)數(shù)據(jù)庫(kù)中

報(bào)告有關(guān)鎖的信息，會(huì)顯示實(shí)例里面的所有數(shù)據(jù)庫(kù)的鎖信息、堵塞信息

sp_lock

提供有關(guān)當(dāng)前用戶、 會(huì)話和進(jìn)程的實(shí)例中的信息，可以看到會(huì)話的狀態(tài)running、SUSPENDED、sleeping、rollback，sp_who2通過(guò)CPUTime、DiskIO可以判斷對(duì)應(yīng)的transaction是否很大
sp_who
sp_who2
sp_who2 active (可選參數(shù)LoginName, 或active代表活動(dòng)會(huì)話數(shù))

其中sp_who2除了顯示sp_who的輸出信息外,，還顯示下面的信息：
CPUTime (進(jìn)程占用的總CPU時(shí)間)
DiskIO (進(jìn)程對(duì)磁盤讀的總次數(shù))
LastBatch (客戶最后一次調(diào)用存儲(chǔ)過(guò)程或者執(zhí)行查詢的時(shí)間)
ProgramName (用來(lái)初始化連接的應(yīng)用程序名稱，或者主機(jī)名)

查看某個(gè)存儲(chǔ)過(guò)程的內(nèi)容

sp_helptext pro_name

DBCC INPUTBUFFER

顯示某個(gè)線程號(hào)發(fā)送到sqlserver數(shù)據(jù)庫(kù)的最后一個(gè)語(yǔ)句

DBCC INPUTBUFFER (249)

假設(shè)查詢到249被鎖給堵塞了，執(zhí)行上面的可以查到被堵塞的SQL語(yǔ)句

查看某個(gè)數(shù)據(jù)庫(kù)中是否存在活動(dòng)事務(wù)，有活動(dòng)事務(wù)就一定會(huì)寫日志，顯示結(jié)果為最早的一個(gè)事務(wù)的ID

DBCC OPENTRAN (dbname)

監(jiān)視日志空間

DBCC SQLPERF (LOGSPACE)

查找無(wú)法重用日志中的空間的原因(日志無(wú)法截?cái)鄬?dǎo)致日志文件越來(lái)越大，但是可用空間很小，無(wú)法收縮)

select name,log_reuse_wait_desc from sys.databases

查看虛擬日志文件信息

DBCC LOGINFO

結(jié)果有多少行，代表有多少虛擬日志文件，活動(dòng)的虛擬日志文件的狀態(tài)(status)為2

修復(fù)msdb數(shù)據(jù)庫(kù)，比如ssms頁(yè)面sql server agent丟失或看不了job view history等功能，說(shuō)明msdb壞了，需要修復(fù)

dbcc checkdb (msdb);

在您當(dāng)前連接到的 SQL Server 數(shù)據(jù)庫(kù)中生成一個(gè)手動(dòng)檢查點(diǎn)

CHECKPOINT [ checkpoint_duration ]

--checkpoint_duration表示以秒為單位指定手動(dòng)檢查點(diǎn)完成所需的時(shí)間，一般不使用這個(gè)參數(shù)，讓數(shù)據(jù)庫(kù)自己控制

查看數(shù)據(jù)庫(kù)各種設(shè)置

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

查看某個(gè)數(shù)據(jù)庫(kù)中是否存在會(huì)話

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

查詢當(dāng)前阻塞的所有請(qǐng)求

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 被阻塞進(jìn)程,a.CMD AS 正在執(zhí)行的操作,b.spid AS 阻塞進(jìn)程號(hào),b.cmd AS 阻塞進(jìn)程正在執(zhí)行的操作

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 中正在執(zhí)行的每個(gè)請(qǐng)求的信息

查看哪些表被鎖了，以及這些表被哪個(gè)進(jìn)程鎖了：

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

查詢某個(gè)job是否被堵塞

select * from msdb.dbo.sysjobs where name='jobname'

select a.program_name,a.* from master..sysprocesses a where a.program_name like '%0D1CE57E8AC5%'

--把第一個(gè)語(yǔ)句查詢到的job_id代入第二個(gè)語(yǔ)句的program_name

檢查SQL Agent是否開啟

IF EXISTS (

SELECT TOP 1 1

FROM sys.sysprocesses

WHERE program_name = 'SQLAgent - Generic Refresher'

)

SELECT 'Running'

ELSE

SELECT 'Not Running'

查看活動(dòng)線程執(zhí)行的sql語(yǔ)句,并生成批量殺掉的語(yǔ)句

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

查看備份進(jìn)度

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

查看恢復(fù)進(jìn)度

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

查看數(shù)據(jù)庫(kù)的最近備份信息

SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type

備注： D 表示全備份，i 表示差異備份，L 表示日志備份

查詢備份信息
SELECT
    bs.backup_set_id,
    bs.database_name,
    bs.backup_start_date,
    bs.media_set_id,
    bmf.physical_device_name,
    bmf.media_family_id,
    bs.*
FROM
    msdb.dbo.backupmediafamily bmf
    INNER JOIN
    msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id
    INNER JOIN
    (
    SELECT
        database_name,
        MAX(backup_start_date) as backup_start_date
    FROM
        msdb.dbo.backupset bs
    WHERE
        type = 'd'
    GROUP BY
        database_name
    ) de on bs.database_name = de.database_name
        and bs.backup_start_date = de.backup_start_date
        and bs.type = 'd'

查看數(shù)據(jù)庫(kù)的歷史備份記錄，并生成restore語(yǔ)句

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庫(kù)從YYYY-MM-DD日期開始的日志備份記錄，并生成restore log的語(yǔ)句

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

查詢always on狀態(tài)是否正常

select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id and d.is_local=1

查看mirror鏡像信息

SELECT

db_name(database_id),

mirroring_state_desc,

mirroring_role_desc,

mirroring_partner_name,

mirroring_partner_instance

FROM sys.database_mirroring

查詢SSRS Report Subscriptions相關(guān)的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'

查看某個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)文件信息，就算是mirror從庫(kù)的數(shù)據(jù)文件也可以查到，filestream目錄也可以查到

SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N'DBA');

查看某個(gè)數(shù)據(jù)文件信息

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

查詢實(shí)例的數(shù)據(jù)文件總大小

SELECT sum(size*8/1024/1024) FROM master.sys.master_files

查詢某個(gè)目錄中數(shù)據(jù)庫(kù)使用的總大小

SELECT a.size*8/1024/1024 ,a.* FROM master.sys.master_files a WHERE physical_name like 'G:\DEFAULT.DATA%'

查詢某個(gè)目錄中哪些數(shù)據(jù)庫(kù)占用了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

查詢實(shí)例上的每個(gè)數(shù)據(jù)庫(kù)的大小

SELECT

DB_NAME(db.database_id) DatabaseName,

(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,

(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,

(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,

(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB

FROM sys.databases db

LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

查詢總耗CPU最多的前3個(gè)SQL，且最近5天出現(xiàn)過(guò)

SELECT TOP 3

total_worker_time/1000 AS [總消耗CPU 時(shí)間(ms)],execution_count [運(yùn)行次數(shù)],

qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 時(shí)間(ms)],

last_execution_time AS [最后一次執(zhí)行時(shí)間],max_worker_time /1000 AS [最大執(zhí)行時(shí)間(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的語(yǔ)法], qt.text [完整語(yǔ)法],

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個(gè)SQL，且最近5小時(shí)出現(xiàn)過(guò)

SELECT TOP 3

total_worker_time/1000 AS [總消耗CPU 時(shí)間(ms)],execution_count [運(yùn)行次數(shù)],

qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 時(shí)間(ms)],

last_execution_time AS [最后一次執(zhí)行時(shí)間],min_worker_time /1000 AS [最小執(zhí)行時(shí)間(ms)],

max_worker_time /1000 AS [最大執(zhí)行時(shí)間(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的語(yǔ)法], qt.text [完整語(yǔ)法],

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

查看當(dāng)前最耗資源的10個(gè)SQL及其spid

SELECT TOP 10

session_id,request_id,start_time AS '開始時(shí)間',status AS '狀態(tài)',

command AS '命令',d_sql.text AS 'sql語(yǔ)句', DB_NAME(database_id) AS '數(shù)據(jù)庫(kù)名',

blocking_session_id AS '正在阻塞其他會(huì)話的會(huì)話ID',

wait_type AS '等待資源類型',wait_time AS '等待時(shí)間',wait_resource AS '等待的資源',

reads AS '物理讀次數(shù)',writes AS '寫次數(shù)',logical_reads AS '邏輯讀次數(shù)',

row_count AS '返回結(jié)果行數(shù)'

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號(hào)session_id一般是系統(tǒng)后臺(tái)進(jìn)程，sys.dm_exec_requests的status顯示為background

查詢某個(gè)存儲(chǔ)過(guò)程被哪些job調(diào)用了

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

--以上要查詢某個(gè)job被哪個(gè)job調(diào)用了，把sp_name存儲(chǔ)過(guò)程名字改成job_name作業(yè)名字即可

命令執(zhí)行某個(gè)job

EXECUTE msdb.dbo.sp_start_job N'job_name'

自增長(zhǎng)列相關(guān)

查詢某表標(biāo)識(shí)列的列名

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1

獲取標(biāo)識(shí)列的種子值

SELECT IDENT_SEED ('表名')

獲取標(biāo)識(shí)列的遞增量

SELECT IDENT_INCR('表名')

獲取指定表中最后生成的標(biāo)識(shí)值

SELECT IDENT_CURRENT('表名')

重新設(shè)置標(biāo)識(shí)種子值為XX

DBCC CHECKIDENT (表名, RESEED, XX)

升級(jí)前，查詢服務(wù)器名、實(shí)例名、版本號(hào)
select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version

用戶被grant這樣操作賦予的權(quán)限
use dbname
exec sp_helprotect @username = 'username'

授予某個(gè)用戶執(zhí)行某個(gè)數(shù)據(jù)庫(kù)的sp的權(quán)限

use dbname
grant execute to "username"

always on查看集群各節(jié)點(diǎn)的信息，包含節(jié)點(diǎn)成員的名稱，類型，狀態(tài)，擁有的投票仲裁數(shù)

SELECT * FROM  sys.dm_hadr_cluster_members;

always on查看集群各節(jié)點(diǎn)的信息，包含節(jié)點(diǎn)成員的名稱，節(jié)點(diǎn)成員上的sql實(shí)例名稱

select * from sys.dm_hadr_instance_node_map

always on查看WSFC(windows server故障轉(zhuǎn)移群集)的信息，包含集群名稱，仲裁類型，仲裁狀態(tài)

SELECT * FROM SYS.dm_hadr_cluster;

always on查看AG名稱

select * from sys.dm_hadr_name_id_map

always on查看集群各節(jié)點(diǎn)的子網(wǎng)信息，包含節(jié)點(diǎn)成員的名稱，子網(wǎng)段，子網(wǎng)掩碼

SELECT * FROM  sys.dm_hadr_cluster_networks;

always on查看偵聽(tīng)ip

select * from sys.availability_group_listeners;

always on查看主從各節(jié)點(diǎn)的狀態(tài)

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查看輔助副本(傳說(shuō)中的從庫(kù))延遲多少M(fèi)日志量

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 主數(shù)據(jù)庫(kù)中尚未發(fā)送到輔助數(shù)據(jù)庫(kù)的日志記錄量 (KB)

--log_send_rate 在最后一個(gè)活動(dòng)期間，以千字節(jié) (KB) 的平均主副本發(fā)送實(shí)例數(shù)據(jù)的速率/秒

--redo_queue_size 在最后一個(gè)活動(dòng)期間，以千字節(jié) (KB) 的平均主副本發(fā)送實(shí)例數(shù)據(jù)的速率/秒

--redo_rate 平均千字節(jié) (KB) 中的給定輔助數(shù)據(jù)庫(kù)做的日志記錄速率 / 秒

查詢實(shí)例的FILESTREAM 使用的DIRECTORY_NAME
SELECT  SERVERPROPERTY('FilestreamShareName')

查詢FILETABLE表的數(shù)據(jù)庫(kù)對(duì)應(yīng)的DIRECTORY_NAME

select db_name(database_id),* from sys.database_filestream_options

僅僅使用filestream功能時(shí)，數(shù)據(jù)庫(kù)不需要對(duì)應(yīng)的DIRECTORY_NAME

查詢FILETABLE表對(duì)應(yīng)的DIRECTORY_NAME
select object_name(object_id),* from sys.filetables

查詢filetable表testdb.dbo.table1中的文件完整路徑名稱
SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1

查詢所有job的狀態(tài)是否running
SELECT sj.Name,
    CASE
        WHEN sja.start_execution_date IS NULL THEN 'Not running'
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
    END AS 'RunStatus'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) order by RunStatus desc;

鎖表的四種用法

TABLOCKX
SELECT * FROM table WITH (TABLOCKX)
查詢過(guò)程中，其他會(huì)話無(wú)法查詢、更新此表，直到查詢過(guò)程結(jié)束

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

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

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

查詢某個(gè)發(fā)布XX，發(fā)布的數(shù)據(jù)庫(kù)對(duì)象的2種方法

1、發(fā)布數(shù)據(jù)庫(kù)上執(zhí)行(數(shù)據(jù)來(lái)源這三張表distribution.dbo.MSpublications、distribution.dbo.MSarticles、sysarticlecolumns)
select a.article,a.source_object,a.destination_object,b.colid from
(select article,article_id,source_object,destination_object
from [distribution].[dbo].MSarticles where publication_id in
( select publication_id from
[distribution].[dbo].MSpublications where publication='XX'
)
) a
inner join
(select * from replicate1.dbo.sysarticlecolumns) b
on a.article_id=b.artid order by a.article

2、訂閱數(shù)據(jù)庫(kù)上執(zhí)行
select distinct article  from MSreplication_objects where publication='XX'

查詢發(fā)布信息，發(fā)布名稱，發(fā)布名稱對(duì)應(yīng)的發(fā)布序號(hào)
Select * from distribution.dbo.MSpublications

查詢發(fā)布名里面的發(fā)布對(duì)象的信息，包含表、視圖、存儲(chǔ)過(guò)程等
Select * from  distribution.dbo.MSarticles

監(jiān)控發(fā)布訂閱是否有異常，執(zhí)行以下5條語(yǔ)句即可

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

查詢XX表的索引信息
SELECT a.name index_name,c.name table_name,d.name column_name
FROM sysindexes a JOIN sysindexkeys b
ON a.id=b.id AND a.indid=b.indid
JOIN sysobjects c
ON b.id=c.id
JOIN syscolumns d
ON b.id=d.id= AND b.colid=d.colid
WHERE a.indid NOT IN(0,255) AND c.name in ('XX')

生成sql語(yǔ)句的執(zhí)行計(jì)劃(select XXX為例，當(dāng)然select XXX也可以換成執(zhí)行存儲(chǔ)過(guò)程比如exec pro_XXX，都是只生成執(zhí)行計(jì)劃，不產(chǎn)生結(jié)果集，不會(huì)執(zhí)行存儲(chǔ)過(guò)程)
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

查詢名稱為XXX的job的最后一次運(yùn)行成功的時(shí)間
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

查詢某張分區(qū)表的總行數(shù)和大小，比如表為crm.EmailLog
exec sp_spaceused 'crm.EmailLog';

查詢某張分區(qū)表的信息，每個(gè)分區(qū)有多少行，比如表為crm.EmailLog
select convert(varchar(50), ps.name
) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name
) as filegroup,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = object_id('crm.EmailLog')
and i.index_id in (0, 1)
order by p.partition_number

查詢分區(qū)函數(shù)
select * from sys.partition_functions

查看分區(qū)架構(gòu)
select * from sys.partition_schemes

查詢ssis包的信息
select * from msdb.dbo.sysssispackages

查詢某張表里的索引的大小,如下示例表為dbo.table1
SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.table1'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name

重建表上的所有索引
alter index all on table_name rebuild with ( 
重建表上的某個(gè)索引
alter index index_name on table_name rebuild with (online=on)

重新組織表上的所有索引
alter index all on table_name reorganize
重新組織表上的某個(gè)索引
alter index index_name on table_name reorganize

查看數(shù)據(jù)文件可收縮空間，結(jié)果見(jiàn)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')

查詢某個(gè)表中的全部索引的信息
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

查詢某個(gè)數(shù)據(jù)庫(kù)下的表數(shù)據(jù)占用磁盤容量最大的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
--這個(gè)比上一個(gè)專業(yè)

查詢某個(gè)數(shù)據(jù)庫(kù)中是否有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; 根據(jù)id號(hào)查詢某個(gè)數(shù)據(jù)庫(kù)名
SELECT DB_NAME(18)

根據(jù)id號(hào)查詢某個(gè)對(duì)象名
SELECT OBJECT_NAME(1769220894)

查看收縮的進(jìn)度100%，此語(yǔ)句要到指定的數(shù)據(jù)庫(kù)下執(zhí)行

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%進(jìn)度

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

查看存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃
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;

查看當(dāng)前用戶
select system_user

查詢ddl修改操作的記錄
1、執(zhí)行如下找到trace文件的目錄和名稱
select * from Sys.traces
2、使用sqlserver profiler工具打開trace文件，就可以查到相關(guān)記錄

感謝各位的閱讀，以上就是“sqlserver dba常用的sql語(yǔ)句有哪些”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)sqlserver dba常用的sql語(yǔ)句有哪些這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！