sqlserver2008數(shù)據(jù)庫優(yōu)化常用腳本2資料

1、sqlserver2008數(shù)據(jù)庫優(yōu)化常用腳本kanni查詢某個數(shù)據(jù)庫的連接數(shù)select count(*) from Master.dbo.SysProcesses where dbid=db_id()前 10 名其他等待類型SELECT TOP 10 *from sys.dm_os_wait_stats ORDER BY wait_time_ms DESCSELECT *FROM sys.dm_os_wait_stats WHERE wait_type like PAGELATCH% OR wait_type like LAZYWRITER_SLEEP%-CPU 的壓力SELECT sche

2、duler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulersWHERE scheduler_id 500 beginselect text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) frommaster.sys.sysprocessesaendselect text,a.* from master.sys.sysprocesses a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handl

3、e) where a.spid = 51dbccinputbuffer(53)with tbas(select blocking_session_id,session_id,db_name(database_id) as dbname,text frommaster.sys.dm_exec_requestsaCROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle),tb1 as(selecta.*,login_time,program_name,client_interface_name,login_name,cpu_time ,memory_

4、usage*8 as memory_usage(KB), total_scheduled_time,reads,writes,logical_readsfrom tb a inner join master.sys.dm_exec_sessions b on a.session_id=b.session_id )select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id當

5、前進程數(shù)select * from master.dbo.sysprocessesorder by cpu desc查看當前活動的進程數(shù)sp_who active查詢是否由于連接沒有釋放引起 CPU過高 select * from master.dbo.sysprocesses where spid 50and waittype = 0 x0000 and waittime = 0and status = sleeping and last_batch dateadd(minute, -10, getdate() and login_time 50and waittype = 0 x0000a

6、nd waittime = 0and status = sleeping and last_batch dateadd(minute, -60, getdate()and login_time 1ORDER BY qs.plan_generation_numSELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,SUM(qs.execution_count) AS total_execution_count,SUM(qs.total_worker_time)/SUM(qs.execution_

7、count) AS avg_cpu_time, COUNT(*) AS number_of_statementsFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt GROUP BY qt.textORDER BY total_cpu_time DESC - 統(tǒng)計總的 CPU時間-ORDER BY avg_cpu_time DESC - 統(tǒng)計平均單次查詢CPU時間計算可運行狀態(tài)下的工作進程數(shù)量SELECT COUNT(*) as workers_waiting_for_cpu,s

8、.scheduler_id FROM sys.dm_os_workers AS oINNER JOIN sys.dm_os_schedulers AS sON o.scheduler_address=s.scheduler_addressAND s.scheduler_id 0-order by記錄數(shù) descORDER BY使用空間 MB DESCDROP TABLE #tb查詢是否由于連接沒有釋放引起 CPU過高 select * from master.dbo.sysprocesses where spid 50and waittype = 0 x0000and waittime = 0

9、and status = sleeping and last_batch dateadd(minute, -10, getdate()and login_time 50and waittype = 0 x0000and waittime = 0and status = sleeping and last_batch dateadd(minute, -60, getdate()and login_time 1ORDER BY qs.plan_generation_numSELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS

10、total_cpu_time,SUM(qs.execution_count) AS total_execution_count,SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time, COUNT(*) AS number_of_statementsFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt GROUP BY qt.textORDER BY total_cpu_time DESC - 統(tǒng)計總的

11、CPU時間-ORDER BY avg_cpu_time DESC - 統(tǒng)計平均單次查詢CPU時間計算可運行狀態(tài)下的工作進程數(shù)量SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id FROM sys.dm_os_workers AS oINNER JOIN sys.dm_os_schedulers AS sON o.scheduler_address=s.scheduler_addressAND s.scheduler_id255WHERE o.state=RUNNABLEGROUP BY s.scheduler_idSELECTcr

12、eation_timeN 語句編譯時間 ,last_execution_timeN 上次執(zhí)行時間 ,total_physical_reads N物理讀取總次數(shù) ,total_logical_reads/execution_count N每次邏輯讀次數(shù) ,total_logical_readsN 邏輯讀取總次數(shù) ,total_logical_writes N邏輯寫入總次數(shù) , execution_countN 執(zhí)行次數(shù) , total_worker_time/1000 N所用的 CPU總時間 ms, total_elapsed_time/1000N 總花費時間 ms, (total_elapse

13、d_time/ execution_count)/1000N 平均時間ms,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, (CASE statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offset END- qs.statement_start_offset)/2) + 1) N 執(zhí)行語句 FROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where SUBSTRING(st.text, (qs.statement_star