SQL Server 第二章 第十三节 动态管理视图DMV.docx
- 文档编号:16890191
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:30
- 大小:384.65KB
SQL Server 第二章 第十三节 动态管理视图DMV.docx
《SQL Server 第二章 第十三节 动态管理视图DMV.docx》由会员分享,可在线阅读,更多相关《SQL Server 第二章 第十三节 动态管理视图DMV.docx(30页珍藏版)》请在冰点文库上搜索。
SQLServer第二章第十三节动态管理视图DMV
1.1动态管理视图DMV
动态管理视图和函数返回可用于监视服务器实例的运行状况、诊断故障以及优化性能的服务器状态信息。
1.1.1与执行有关的动态管理视图和函数:
sys.dm_exec_sessions--:
是服务器范围的视图,显示了有关所有活动用户连接和内部任务的信息。
此信息包含客户端版本、客户端程序名称、客户端登录时间、登录用户、当前会话设置等。
sys.dm_exec_requests--:
返回有关在SQLServer中执行的每个请求的信息。
sys.dm_exec_connections--:
返回与SQLServer实例建立的连接有关的信息以及每个连接的详细信息。
sys.dm_exec_cursors--:
返回有关在各种数据库中打开的游标的信息。
比如;select*fromsys.dm_exec_cursors(0)
sys.dm_exec_sql_text--:
返回由指定的sql_handle标识的SQL批处理的文本。
比如:
selectsql_text.*fromsys.dm_exec_requestsCROSSAPPLY(SELECT*FROMsys.dm_exec_sql_text(sys.dm_exec_requests.sql_handle))sql_text
sys.dm_exec_background_job_queue--:
对计划异步(后台)执行的每个查询处理器作业返回一行。
sys.dm_exec_background_job_queue_stats--:
对于每个为异步(后台)执行而提交的查询处理器作业,相应地返回一行,以提供聚合统计信息。
sys.dm_exec_cached_plans--:
针对SQLServer为了加快查询执行而缓存的每个查询计划返回一行。
比如:
selectsql_text.*fromsys.dm_exec_cached_plansCROSSAPPLY(SELECT*FROMsys.dm_exec_sql_text(sys.dm_exec_cached_plans.plan_handle))sql_text
sys.dm_exec_plan_attributes--:
针对计划句柄所指定计划的每个计划属性返回一行。
比如:
select*fromsys.dm_exec_plan_attributes(0x05000500AEB9A870B881125A000000000000000000000000)
sys.dm_exec_text_query_plan--:
为Transact-SQL批查询或批查询中的特定语句返回文本格式的显示计划。
sys.dm_exec_query_plan--:
以XML格式返回计划句柄指定的批查询的显示计划。
计划句柄指定的计划可以处于缓存或正在执行状态。
比如:
select*fromsys.dm_exec_query_plan(0x05000500AEB9A870B881125A000000000000000000000000)
sys.dm_exec_query_stats--:
返回缓存查询计划的聚合性能统计信息。
1.1.2与索引有关的动态管理视图和函数
sys.dm_db_index_usage_stats--:
返回不同类型索引操作的计数以及上次执行每种操作的时间。
sys.dm_db_index_operational_stats--:
返回数据库中表或索引的每个分区的当前低级I/O、锁定、闩锁和访问方法活动。
sys.dm_db_index_physical_stats--:
返回指定表或视图的数据和索引的大小和碎片信息。
sys.dm_db_missing_index_columns--:
返回与缺少索引(不包括空间索引)的数据库表列有关的信息。
sys.dm_db_missing_index_details--:
返回有关缺失索引的详细信息,不包括空间索引。
sys.dm_db_missing_index_groups--:
返回有关特定缺失索引组中包含的缺失索引(不包括空间索引)的信息
sys.dm_db_missing_index_group_stats--:
返回有关缺失索引组的摘要信息,
1.1.3与I/O有关的动态管理视图和函数:
sys.dm_io_virtual_file_stats--:
返回数据和日志文件的I/O统计信息。
比如:
SELECT*FROMsys.dm_io_virtual_file_stats(db_id('taobao'),null)
sys.dm_io_pending_io_requests--:
对于SQLServer中每个挂起的I/O请求,返回与其对应的一行。
sys.dm_io_cluster_shared_drives--:
如果当前服务器实例为群集服务器,则此视图返回每个共享驱动器的名称。
如果当前服务器实例不是群集实例,则返回空行集。
sys.dm_io_backup_tapes--:
返回磁带设备的列表和用于备份的装入请求的状态。
1.1.4与数据库有关的动态管理视图:
sys.dm_db_file_space_usage--:
返回数据库中每个文件的空间使用信息。
该视图只适用于tempdb数据库。
sys.dm_db_partition_stats--:
返回当前数据库中每个分区的页和行计数信息。
比如:
SELECT*FROMsys.dm_db_partition_statsWHEREobject_id=OBJECT_ID('taobaoitem_0003');
sys.dm_db_session_space_usage--:
返回每个会话为数据库分配和释放的页数。
该视图只适用于tempdb数据库
sys.dm_db_task_space_usage--:
返回数据库任务所执行的页分配和释放活动。
该视图只适用于tempdb数据库
1.1.5与事务有关的动态管理视图和函数
sys.dm_tran_locks--:
返回有关当前活动的锁管理器资源的信息。
sys.dm_tran_database_transactions--:
返回有关数据库级的事务的信息。
sys.dm_tran_session_transactions--:
返回关联事务和会话的相关信息。
1.1.6所有对象:
SELECTname,object_id,principal_id,schema_id,parent_object_id,type,type_desc,
create_date,modify_date,is_ms_shipped,is_published,is_schema_published
FROMsys.all_objects
ORDERBYname
1.1.7索引对象:
SELECTobject_id,name,index_id,type,type_desc,is_unique,data_space_id,
ignore_dup_key,is_primary_key,is_unique_constraint,fill_factor,is_padded,
is_disabled,is_hypothetical,allow_row_locks,allow_page_locks
FROMsys.indexes
1.1.8索引使用情况:
SELECTdatabase_id,object_id,index_id,user_seeks,user_scans,user_lookups,
user_updates,last_user_seek,last_user_scan,last_user_lookup,last_user_update,
system_seeks,system_scans,system_lookups,system_updates,
last_system_seek,last_system_scan,last_system_lookup,
last_system_update
FROMsys.dm_db_index_usage_stats
WHERE(database_id=DB_ID('taobao'))
1.1.9某数据库下索引使用情况:
SELECTa.database_id,a.object_id,a.index_id,b.name,a.user_seeks,a.user_scans,
a.user_lookups,a.user_updates,a.last_user_seek,a.last_user_scan,
a.last_user_lookup,a.last_user_update,a.system_seeks,a.system_scans,
a.system_lookups,a.system_updates,a.last_system_seek,a.last_system_scan,
a.last_system_lookup,a.last_system_update
FROMsys.dm_db_index_usage_statsASaINNERJOIN
sys.indexesASbONa.object_id=b.object_id
WHERE(a.database_id=DB_ID('taobao'))
1.1.10事务锁使用情况:
SELECTresource_type,resource_subtype,resource_database_id,resource_description,
resource_associated_entity_id,resource_lock_partition,request_mode,request_type,
request_status,request_reference_count,request_lifetime,request_session_id,
request_exec_context_id,request_request_id,request_owner_type,
request_owner_id,request_owner_guid,request_owner_lockspace_id,
lock_owner_address
FROMsys.dm_tran_locks
WHERE(resource_database_id=DB_ID('taobao'))
1.1.11数据库所有会话情况:
SELECTsession_id,login_time,host_name,program_name,host_process_id,
client_version,client_interface_name,security_id,login_name,nt_domain,
nt_user_name,status,context_info,cpu_time,memory_usage,total_scheduled_time,
total_elapsed_time,endpoint_id,last_request_start_time,last_request_end_time,
reads,writes,logical_reads,is_user_process,text_size,language,date_format,
date_first,quoted_identifier,arithabort,ansi_null_dflt_on,ansi_defaults,
ansi_warnings,ansi_padding,ansi_nulls,concat_null_yields_null,
transaction_isolation_level,lock_timeout,deadlock_priority,row_count,prev_error,
original_security_id,original_login_name,last_successful_logon,
last_unsuccessful_logon,unsuccessful_logons
FROMsys.dm_exec_sessions
1.1.12所有请求情况:
SELECTsession_id,request_id,start_time,status,command,sql_handle,
statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,
connection_id,blocking_session_id,wait_type,wait_time,last_wait_type,
wait_resource,open_transaction_count,open_resultset_count,transaction_id,
context_info,percent_complete,estimated_completion_time,cpu_time,
total_elapsed_time,scheduler_id,task_address,reads,writes,logical_reads,
text_size,language,date_format,date_first,quoted_identifier,arithabort,
ansi_null_dflt_on,ansi_defaults,ansi_warnings,ansi_padding,ansi_nulls,
concat_null_yields_null,transaction_isolation_level,lock_timeout,deadlock_priority,
row_count,prev_error,nest_level,granted_query_memory,
executing_managed_code
FROMsys.dm_exec_requests
1.1.13所有连接情况:
SELECTsession_id,most_recent_session_id,connect_time,net_transport,protocol_type,
protocol_version,endpoint_id,encrypt_option,auth_scheme,node_affinity,
num_reads,num_writes,last_read,last_write,net_packet_size,client_net_address,
client_tcp_port,local_net_address,local_tcp_port,connection_id,
parent_connection_id,most_recent_sql_handle
FROMsys.dm_exec_connections
1.1.14所有查询情况:
SELECTsql_handle,statement_start_offset,statement_end_offset,plan_generation_num,
plan_handle,creation_time,last_execution_time,execution_count,total_worker_time,
last_worker_time,min_worker_time,max_worker_time,total_physical_reads,
last_physical_reads,min_physical_reads,max_physical_reads,total_logical_writes,
last_logical_writes,min_logical_writes,max_logical_writes,total_logical_reads,
last_logical_reads,min_logical_reads,max_logical_reads,total_clr_time,
last_clr_time,min_clr_time,max_clr_time,total_elapsed_time,last_elapsed_time,
min_elapsed_time,max_elapsed_time
FROMsys.dm_exec_query_stats
1.1.15性能统计:
SELECTobject_name,counter_name,instance_name,cntr_value,cntr_type
FROMsys.dm_os_performance_counters
1.1.16索引丢失情况:
SELECTindex_handle,database_id,object_id,equality_columns,inequality_columns,
included_columns,statement
FROMsys.dm_db_missing_index_details
WHERE(database_id=DB_ID('taobao'))
1.1.17锁详细信息:
SELECTSessionID=s.Session_id,resource_type,DatabaseName=DB_NAME(resource_database_id),
request_mode,request_type,a.text,login_time,host_name,program_name,client_interface_name,
login_name,nt_domain,nt_user_name,s.status,last_request_start_time,
last_request_end_time,s.logical_reads,s.reads,request_status,request_owner_type,objectid,dbid,a.number,
a.encrypted,a.blocking_session_idFROMsys.dm_tran_lockslJOINsys.dm_exec_sessionss
ONl.request_session_id=s.session_idLEFTJOIN(SELECT*FROMsys.dm_exec_requestsr
CROSSAPPLYsys.dm_exec_sql_text(sql_handle))aONs.session_id=a.session_id
WHEREs.session_id>50
1.1.18查询阻塞情况:
SELECT
blocked_query.session_idASblocked_session_id,
blocking_query.session_idASblocking_session_id,
sql_text.textASblocking_text,
waits.wait_typeASblocking_resource
FROMsys.dm_exec_requestsblocked_query
JOINsys.dm_exec_requestsblocking_queryON
blocked_query.blocking_session_id=blocking_query.session_id
CROSSAPPLY
(
SELECT*FROMsys.dm_exec_sql_text(blocking_query.sql_handle)
)sql_text
JOINsys.dm_os_waiting_taskswaitsON
waits.session_id=blocking_query.session_id
1.1.19查找CPU占用率高的语句
SELECTTOP100execution_count,
total_logical_reads/execution_countAS[AvgLogicalReads],
total_elapsed_time/execution_countAS[AvgElapsedTime],
db_name(st.dbid)as[databasename],
object_name(st.dbid)as[objectname],
object_name(st.objectid)as[objectname1],
SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((CASEstatement_end_offsetWHEN-1THENDATALENGTH(st.text)ELSEqs.statement_end_offsetEND-qs.statement_start_offset)
/2)+1)ASstatement_text
FROMsys.dm_exec_query_statsASqsCROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)ASst
WHEREexecution_count>100
ORDERBY1DESC;
1.1.20检测存储过程使用频率
SELECTCASEwhendbid=32767
then'Resource'
elseDB_NAME(dbid)end[DB_NAME],
OBJECT_SCHEMA_NAME(objectid,dbid)AS[SCHEMA_NAME],
OBJECT_NAME(objectid,dbid)AS[OBJECT_NAME],
SUM(usecounts)AS[Use_Count],
SUM(total_elapsed_time)AS[total_elapsed_time],
SUM(total_elapsed_time)/SUM(usecounts)*1.0AS[avg_elapsed_time],
substring(convert(char(
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server 第二章 第十三节 动态管理视图DMV 第二 第十 三节 动态 管理 视图 DMV
![提示](https://static.bingdoc.com/images/bang_tan.gif)