SQL Server 数据库部分常用语句小结
1. 查詢某存儲過程的訪問情況
SELECT TOP 1000 db_name(d.database_id) as DBName, s.name as 存儲名字, s.type_desc as 存儲類型,?
d.cached_time as SP添加到緩存的時間, d.last_execution_time AS 上次執(zhí)行SP的時間,?
d.last_elapsed_time as [上次執(zhí)行SP所用的時間(微秒)], d.total_elapsed_time [完成此SP的執(zhí)行所用的總時間(微秒)],?
d.total_elapsed_time/d.execution_count AS [平均執(zhí)行時間(微秒)], d.execution_count as 自上次編譯以來所執(zhí)行的次數(shù)
FROM sys.procedures s INNER JOIN sys.dm_exec_procedure_stats d
ON s.object_id = d.object_id where s.name='存儲過程的名稱'? ----請?jiān)诖颂幪鎿Q要統(tǒng)計(jì)的SP
ORDER BY d.total_elapsed_time/d.execution_count DESC
?
2. 查詢包含關(guān)鍵字（字符串）的存儲過程 SP
Select distinct o.* from sysobjects o, syscomments s?
where o.xtype='P' and s.id=o.id and text like '%關(guān)鍵字%' order by o.name
?
3. 查詢包含關(guān)鍵字（字符串）的Job?
Select * from msdb.dbo.sysjobs?
where job_id in(Select job_id from msdb.dbo.sysjobsteps where command like '%XXXXXX%' )
?
4. 查詢哪些SQL語句占用CPU的頻率高
SELECT TOP 10 [cpu_time],
[session_id],
[request_id],
[start_time] AS '開始時間',
[status] AS '狀態(tài)',
[command] AS '命令',
dest.[text] AS 'sql語句',?
DB_NAME([database_id]) AS '數(shù)據(jù)庫名',
[blocking_session_id] AS '正在阻塞其他會話的會話ID',
[wait_type] AS '等待資源類型',
[wait_time] AS '等待時間',
[wait_resource] AS '等待的資源',
[reads] AS '物理讀次數(shù)',
[writes] AS '寫次數(shù)',
[logical_reads] AS '邏輯讀次數(shù)',
[row_count] AS '返回結(jié)果行數(shù)'
FROM sys.[dm_exec_requests] AS der?
CROSS APPLY?
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest?
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='數(shù)據(jù)庫名字'? ----請?jiān)诖颂庉斎胍y(tǒng)計(jì)的數(shù)據(jù)庫名字
ORDER BY [cpu_time] DESC
?
5. DB的單用戶模式更新成多用戶模式
alter database?數(shù)據(jù)庫名字?set multi_user;
?
6. 查詢某表的列的詳情
select * from syscolumns where id=object_id('要統(tǒng)計(jì)的表') ---請?zhí)鎿Q
?
7. 查詢當(dāng)前SQL Server日志信息?
Exec xp_readerrorlog 0
其實(shí)?xp_readerrorlog 一共有7個參數(shù)
（1）? ?存檔編號；
（2）? 日志類型(1為SQL Server日志,2為SQL Agent日志)；
（3）? 查詢包含的字符串；
（4）? 查詢包含的字符串；
（5）? LogDate開始時間；
（6）? 結(jié)果排序,按LogDate排序(可以為降序"Desc" Or 升序"Asc")；
（7） 結(jié)果排序,按LogDate排序(可以為降序"Desc" Or 升序"Asc") 。
?
8. 查詢Job 明細(xì)
SELECT b.[name] [JobName]?
,b.enabled [Enabled]?
,a.step_id [StepID]?
,b.description [JobDescription]?
,a.step_name [StepName]?
,a.command [Script]?
FROM msdb.dbo.sysjobsteps a?
INNER JOIN msdb.dbo.sysjobs b?
ON a.job_id=b.job_id
總結(jié)
以上是生活随笔為你收集整理的SQL Server 数据库部分常用语句小结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
