常用SQL语句
来源:互联网 发布:如何完美卸载软件 编辑:程序博客网 时间:2024/06/06 01:36
--Gives you the top 25 stored procedures that have been recompiled.
select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count,
dbid, objectid
into DMV_Top25_Recompile_Commands
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num >1
order by plan_generation_num desc
go
-- sys.dm_os_wait_stats
select *
into DMV_dm_os_wait_stats
from sys.dm_os_wait_stats
go
--Top 50 IO contributer.
select top 50
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,
sql_handle,
substring(sql_text.text, (statement_start_offset/2),
case
when (statement_end_offset -statement_start_offset)/2 <=0 then 64000
else (statement_end_offset -statement_start_offset)/2
end) as exec_statement,
sql_text.text,
plan_handle, plan_text.*
into DMV_Top50_IO
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
order by
(total_logical_reads + total_logical_writes) /Execution_count Desc
go
--Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
SELECT *
into DMV_dm_db_index_operational_stats
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);
GO
--Calculate Average Stalls per database file
select database_id, file_id
,io_stall_read_ms
,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms
,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
into DVM_File_Average_Stalls
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc
-- Tempdb.
-- An overview query.
Select
SUM (user_object_reserved_page_count)*8 as user_objects_kb,
SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb
into DMV_Tempdb_Overall_Usage
From sys.dm_db_file_space_usage
Where database_id = 2
go
-- Determine tempdb space used by Task
SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc)
as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated
into DMV_Tempdb_Usage_By_Session
from sys.dm_db_session_space_usage as t1,
(select session_id,
sum(internal_objects_alloc_page_count)
as task_alloc,
sum (internal_objects_dealloc_page_count)
as task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id
and t2.session_id >50
order by allocated DESC
go
- SQL语句 常用语句
- sql常用sql语句
- SQLite 常用语句 sql语句
- oracle常用SQL语句
- DBA常用SQL语句
- DBA常用SQl语句
- 常用SQL语句
- 常用SQL语句
- oracle常用SQL语句
- oracle常用SQL语句
- 管理常用SQL语句
- 管理常用SQL语句
- 管理常用SQL语句
- 常用SQL语句(Update)
- 常用SQL语句
- SQL常用语句大全
- 常用sql语句1
- 常用Sql语句
- Linux下获取IP、MAC、掩码的shell脚本
- ActionBar自定义修改无效解决方法
- TreeSet.add()方法一细节
- mac安装Genymotion模拟器教程(mac机如何在模拟器上玩googleplay游戏)
- 直接插入排序
- 常用SQL语句
- unity3D游戏开发实战原创视频讲座系列7之消消乐游戏开发
- javascript前端向python tornado后端传文件
- 如何用Tomcat和Openssl构建HTTPS双向认证环境(HTTPS客户端认证)
- MyEclipse10 SVN 插件安装
- linux中rpm.bin文件与bin文件的区别
- 例说桥接模式(Bridge Pattern)
- linux ping指令学习
- linux 下创建查看添加删除用户组