数据库维护管理和监视新特性

最新推荐文章于 2024-08-26 16:18:15 发布

weixin_33920401

最新推荐文章于 2024-08-26 16:18:15 发布

阅读量93

点赞数

文章标签：数据库运维操作系统

原文链接：http://blog.51cto.com/yejunsheng/164305

版权

我的查询没有响应为什么? 哪一个运行T-SQL的连接阻塞了我的查询? 阻塞查询的连接正在运行哪个T-SQL? 哪个T-SQL在等待什么资源?

诊断工具: Profiler Performance Monitor(PerfMon) DBCC command Activity Monitor Stored procedures(sp_who,sp_who2,sp_lock...) 其它实用工具(Read80Trace...)

SQL Server 2005维护管理和监视新特性: 动态管理对象DMO-->Dynamic Management Objects DMO(SQL Server早期版本中的分布式管理对象)与SMO(SQL Server 2005中的管理对象)

什么是DMV & DMF? DMO动态管理对象(Dynamic Management Objects) DMV--动态管理视图(Dynamic Management Views) DMF--动态管理函数(Dynamic Management Functions) 获取用于监视服务器实例的运行状况、诊断故障以及优化性能的服务器状态信息

DMV & DMF的异同-->相同点: 以T-SQL方式查询数据库运行状态的动态信息结果都以表的形式返回最大的区别: DMF需要提供一些参数来获取不同的输出

分类: DMV & DMF分成两类-->服务器范围内数据库范围内存储在Master数据库的SYS架构中命名规则: 以"dm_"开头共有85个,其中76个为DMV,9个为DMF

功能分组-->见下列表:

dm_broker_* - 与Service Broker有关的DMV
dm_clr_* - 与公共语言运行时有关的DMV

dm_db_* - 与数据库有关的DMV

dm_db_index_* - 与索引有关的DMV & DMF

dm_db_mirroring_* 与数据库镜像有关的DMV

dm_exec_* - 与执行有关的DMV & DMF

dm_fts_* - 与全文搜索有关的DMV

dm_io_* - 与I/O有关的DMV & DMF

dm_os_* - 与SQL操作系统有关的DMV

dm_qn_* - 与查询通知有关的DMV

dm_repl_* - 与复制有关的DMV

dm_tran_* - 与事务有关的DMV

用法: 使用Select语句来查询与性能计数器类似,通过DMV & DMF检索到的信息也不是你访问当时的最新信息。查询的结果是动态的。使用两部分、三部分或四部分所组成的名称来引用查询MSDN帮助时需要使用两部分命名法任何动态对象在未来的SQLSERVER版本中可能会发生改变,所以任何涉及DMV及DMF的代码可能需要重写或弃用,如果Microsoft改变DMV或DMF。

权限: 授权访问-->SELECT VIEW SERVER STATE VIEW DATABASE STATE 禁止访问-->首先在master中创建用户,然后拒绝该用户对不希望被访问的动态管理视图或函数的SELECT权限

如何查看所有的DMV & DMF、获取DMV的字段信息、获取DMF的参数信息和授予权限呢?

1.如果你想查看当前数据库有那些DMV的话就把查看所有的DMV & DMF的语句全选--按执行之后就可以看到总共有多少个DMV了 2.全选获取DMV的字段信息的语句--按执行之后就可以看到DMV字段的详细信息了

全选获取DMF的参数信息的语句--按执行之后就可以看到DMF的参数信息了

展开SQL2005(本地计算机)--安全性--对着登录名右键--选择新建登录名--登录名就叫做user1吧--选择SQL Server身份验证,输入密码--在默认数据库里面选择AdventureWorks 按确定

展开数据库--AdventureWorks--安全性--对着用户右键--选择新建用户--用户名就叫做user1吧--按确定

在对象资源管理器里面按连接--选择数据库引擎--在身份验证里面选择SQL Server身份验证--输入登录名(user1)和密码按连接

展开数据库--系统数据库--对着master这个数据库右键--选择新建查询--输入select * from sys.dm_clr_tasks这条语句--按执行之后可以看到有一个提示-->用户没有执行此操作的权限现在怎么办呢? 我们知道使用DMV需要有浏览数据库和服务器的权限此时我们需要增加一些权限

在对象资源管理器里面按连接--选择数据库引擎--在身份验证里面选择Windows身份验证--按连接

展开安全性--登录名--对着user1右键--选择属性--按安全对象--在安全对象里面按添加--在要添加什么对象里面选择特定类型的所有对象按确定

在对象类型里面把服务器沟上按确定

在SQL2005的显式权限里面把View server state的授予这一项权限沟上按确定

展开数据库--系统数据库--master--安全性--对着用户右键--选择新建用户--用户名和登录名都叫做user1吧按确定

对着user1右键--选择属性--按安全对象--在安全对象里面按添加--在要添加什么对象里面选择特定类型的所有对象--按确定

在选择要查找的对象类型里面把数据库沟上按确定

在master的显式权限里面把View database state的授予这一项权限沟上按确定

按master这个数据库--在查询里面输入select * from sys.dm_clr_tasks这条语句--按执行之后可以看到现在已经不存在权限的问题了可以看到当前的内容了但是现在没有clr这个任务的内容

DMV & DMF在诊断性能问题上的典型应用: SQL Server有关的性能问题-->资源瓶颈(CPU,memory,disk I/O and network) Tempdb瓶颈用户查询诊断瓶颈的相关工具-->System Monitor(PerfMon) SQL Server Profiler DBCC commands DMVs & DMFs

应用之一: CPU瓶颈-->System Monitor: Processor: % Processor Time 可运行任务的数量-->见下列表:
Select Scheduler_id,

Current_tasks_count,

Runnable_tasks_count

from sys.dm_os_schedulers

where Scheduler_id<255

哪一组T-SQL或过程占用了最多的CPU时间-->见下列语句:

select top 50 sum(qs.total_worker_time)
as total_cpu_time,

       sum(qs.execution_count)as
       total_execution_count, count(*)
       as number_of_statements,

qs.plan_handle
from sys.dm_exec_query_stats qs

group by qs.plan_handle

order by sum(qs.total_worker_time)desc

过度的编译和重编译问题引起的CPU性能问题: 导致重编译的原因-->架构的改变统计信息的改变 SET选项的改变临时表的改变带RECOMPILE选项的存储过程诊断-->System Monitor(Perfmon): SQL Server: SQL Statistics: Batch Requests/sec SQL Server: SQL Statistics: SQL Compilations/sec SQL Server: SQL Statistics: SQL Recompilations/sec

SQL Server Profiler: 跟踪事件: SP: Recompile SQL: StmtRecompile 对事件数据的分析-->见下列语句:

select spid, StartTime, Textdata,

EventSubclass, ObjectID,

DatabaseID, SQLHandle

from fn_trace_gettable('e:\recompiletrace.trc',1)

where EventClass in(37,75,166)

使用DMVs-->select * from sys.dm_exec_query_optimizer_info

select top 25 sql_text.text, sql_handle,

plan_generation_num,execution_count,

dbid, objectid

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

发现过度重编译的解决办法-->避免在存储过程中修改SET选项避免在存储过程中使用recompile,考虑改用语句级recompile选项使用Database Engine Tuning Advisor(DTA) 关闭相关索引的自动更新统计信息选项 ...

应用之二: TempDB瓶颈-->Tempdb的用途与Tempbdb有关的问题: 空间不足查询速度缓慢大量的DDL/DML操作引起系统表的瓶颈问题 Tempbdb空间分配: 用户对象内部对象版本存储区可用空间

诊断tempdb磁盘空间问题: SQL Server错误日志-->见下列表:

错误引发错误的情况

1101或1105 任何会话都必须分配tempdb中的空间。

3959 版本存储区已满。此错误在日志中通常出现在错误1105或1101之后。

3967 由于tempdb已满,版本存储区被强制收缩。

3958或3966 事务在tempdb中找不到所需的版本记录。

监视tempdb磁盘空间: 获取Tempdb中的用户对象,内部对象,版本存储区使用的空间量以及可用空间量-->见下列语句:

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

From sys.dm_db_file_space_usage

Where database_id=2

输出样例: user_objects_kb internal_objects_kb version_store_kb freespace_kb

------------ -------------- ------------ --------

8736 128 64 448

如果版本存储区使用了tempdb中的大量空间,则需要确定运行时间最长的事务。-->见下列语句:

SELECT transaction_id
FROM
sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

使用大量空间的大型查询-->获取每个会话中当前运行的所有任务中的内部对象占用的空间-->见下列语句:

CREATE VIEW all_task_usage AS

                                                        SELECT session_id,
                                                          SUM(internal_objects_alloc_page_count)AS
                                                          task_internal_objects_alloc_page_count,
                                                          SUM(internal_objects_dealloc_page_count)
                                                          AS
                                                          task_internal_objects_dealloc_page_count

FROM sys.dm_db_task_space_usage

GROUP BY session_id;

获取当前会话中正在运行的任务和已完成任务的内部对象占用的空间-->见下列语句:

CREATE VIEW all_session_usage

AS SELECT R1.session_id,
       R1.internal_objects_alloc_page_count+
       R2.task_internal_objects_alloc_page_count AS
       session_internal_objects_alloc_page_count,
       R1.internal_objects_dealloc_page_count+
       R2.task_internal_objects_dealloc_page_count
       AS
       session_internal_objects_dealloc_page_count

FROM sys.dm_db_session_space_usage AS R1,
all_task_usage AS R2
WHERE R1.session_id=R2.session_id;

情景构设: Script 1: Create stored procedure

USE AdventureWorks
GO

CREATE PROCEDURE dbo.updater
AS
BEGIN TRAN
UPDATE Person.Contact WITH(HOLDLOCK)
SET FirstName = 'Justin'
WAITFOR DELAY '01:00:00'
ROLLBACK TRAN
GO

          Script 2: Execute Sp
            select@@spid
            EXEC dbo.updater

Script 3: Select the blocked table
Select * from Person.Contact

获取信息: Script 4: What's going on
DECLARE @sessionid INT
SET @sessionid=54-to be changed

DECLARE @sqlhandle VARBINARY(64)
DECLARE @planhandle VARBINARY(64)

          SELECT @sqlhandle=sql_handle,@planhandle=plan_handle
          FROM sys.dm_exec_requests
          WHERE session_id=@sessionid

SELECT * FROM sys.dm_exec_sql_text(@sqlhandle)

SELECT * FROM sys.dm_exec_query_plan(@planhandle)

获取答案: Script 5: Who blocked me

          SELECT blocked_query.session_id AS blocked_session_id,
                  blocking_query.session_id AS blocking_session_id,
                  sql_text.text AS blocking_text,waits.wait_type AS
                  blocking_resource

          FROM sys.dm_exec_requests blocked_query
          JOIN sys.dm_exec_requests blocking_query ON
                blocked_query.blocking_session_id=blocking_query.session_id
          CROSS APPLY
                  (SELECT*
                   FROM sys.dm_exec_sql_text(blocking_query.sql_handle)sql_text
          JOIN sys.dm_os_waiting_tasks waits ON
                waits.session_id=blocking_query.session_id

1.全选第一条语句--按执行来建立存储过程 2.全选第二条语句--按执行之后就执行我们刚才创建的存储过程了如果执行select @@spid这条语句的话就可以看到当前会话的ID是56 3.全选第三条语句--按执行之后会发现查询没有响应

4.全选第四条语句--按执行之后可以看到这个存储过程二条信息了按ShowPlanXML.xmlns="http://schemas.microsoft.com...来打开里面的内容

按文件--按保存--我把它存储在C盘里面--文件的扩展名叫做sqlplan--保存类型为XML文件(*.xml)--按保存

打开C盘--把query_plan2.sqlplan.xml的扩展名修改成query_plan2.sqlplan

双击query_plan2.sqlplan这个文件来打开它可以看到不是刚才看到的XML格式的内容了而是图形的格式了让大家了解这个查询计划是怎么样执行的

如果你想查询究竟是什么SQL阻塞了我的查询的话可以执行它的语句来查看 5.全选第五条语句--按执行之后可以看到阻塞查询的会话ID是55 55这个ID就是我查询Select * from Person.Contact这条语句的会话ID 谁阻塞了55这个会话ID呢? 是56这个ID执行存储过程阻塞的它停留什么资源呢？它运行了一条语句叫做WAITFOR这样的等待一个小时的语句大家可以看到DMV和DMF提供了一个非常详细的信息可以非常方便地解决一个查询没有响应的问题

更多信息...
SQL Server 2005联机丛书: SQL Server语言参考->Transact_SQL参考->系统视图(Transact_SQL)->动态管理视图和函数

http://msdn2.microsoft.com/zh-cn/library/ms188754.aspx

转载于:https://blog.51cto.com/yejunsheng/164305

weixin_33920401

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库维护管理和监视新特性

我的查询没有响应为什么? 哪一个运行T-SQL的连接阻塞了我的查询? 阻塞查询的连接正在运行哪个T-SQL? 哪个T-SQL在等待什么资源? 诊断工具: Profiler Performance Monitor(PerfMon) DBCC command Activity Monitor Stored procedures(sp_who,sp_who2,sp_...
复制链接

扫一扫