没有合适的资源?快使用搜索试试~ 我知道了~
首页SQL Server DMV 使用
SQL Server DMV 使用
需积分: 17 18 下载量 200 浏览量
更新于2023-03-16
评论
收藏 2.95MB PDF 举报
SQL server从2005开始,引入了DMV和DMF来监控数据库的信息。可以用于监控服务器实例的运行状况、诊断问题和优化性能。 DMF和DMV有挺多,做为DBA应该掌握经常用到的语句。。
资源详情
资源评论
资源推荐
SQL Server DMV
Starter Pack
Glenn Berry, Louis Davidson and Tim Ford
Red Gate Books
SQL Server DMV Starter Pack
By Glenn Berry, Louis Davidson, Tim Ford
First published by Simple Talk Publishing July 2010
Copyright Glenn Berry, Lousi Davidson, Tim Ford 2010
ISBN 978-1-906434-51-9
The right of Glenn Berry, Louis Davidson and Tim Ford to be identified as the authors of this work has been
asserted by him in accordance with the Copyright, Designs and Patents Act 1988
All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval sys-
tem, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or oth-
erwise) without the prior written consent of the publisher. Any person who does any unauthorized act in
relation to this publication may be liable to criminal prosecution and civil claims for damages.
This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent, re-sold, hired
out, or otherwise circulated without the publisher’s prior consent in any form other than which it is pub-
lished and without a similar condition including this condition being imposed on the subsequent publisher.
Editor: Tony Davis
Additional Material: Adam Machanic
Typeset and Designed by Matthew Tye
Cover Photograph by Paul Vlaar
Table of Contents
About the Contributors ................................................................... v
Glenn Berry (Author) ...............................................................................................................v
Louis Davidson (Author) .........................................................................................................v
Tim Ford (Author) ....................................................................................................................v
Adam Machanic (Additional Material).................................................................................vi
Introducing Dynamic Management Objects ...................................7
What sort of data is available? ................................................................................................8
Point in time versus cumulative ............................................................................................9
Performance Tuning with DMOs .........................................................................................11
Connections, Sessions, Requests, Queries ...................................15
Optimizing your SQL Workload ..........................................................................................16
DMV#1: Are you Connected? ............................................................................................... 17
DMV#2: Session Ownership ................................................................................................19
DMV#3: Current expensive, or blocked, requests ............................................................ 20
DMV#4: Query Stats – Find the "top X" most expensive cached queries ..................... 21
DMV#5: How many single-use ad-hoc Plans? ................................................................... 23
DMV#6: Ad-hoc queries and the plan cache .....................................................................24
DMV#7: Investigate expensive cached stored procedures ..............................................26
DMV#8: Find Queries that are waiting, or have waited, for a Memory Grant ............29
Transactions ................................................................................... 31
DMV#9: Monitor long-running transactions ................................................................... 32
DMV#10: Identify locking and blocking issues ................................................................. 33
Databases and Indexes..................................................................36
DMV#11: Find Missing Indexes............................................................................................37
DMV#12: Interrogate Index Usage ......................................................................................39
DMV#13: Table Storage Stats (Pages and Row Counts) ...................................................43
DMV#14: Monitor TempDB ................................................................................................ 44
Disk I/O ...........................................................................................46
DMV#15: Investigate Disk Bottlenecks via I/O Stalls .......................................................47
DMV#16: Investigate Disk Bottlenecks via Pending I/O ................................................ 48
Operating System .......................................................................... 50
A Brief Overview of SQL Server Waits ................................................................................50
DMV#17: Why are we Waiting? ........................................................................................... 52
DMV#18: Expose Performance Counters ........................................................................... 55
DMV#19: Basic CPU Configuration ....................................................................................56
DMV#20: CPU Utilization History .....................................................................................57
DMV#21: Monitor Schedule activity ...................................................................................59
DMV#22: System-wide Memory Usage ............................................................................. 60
DMV#23: Detect Memory Pressure ....................................................................................61
DMV#24: Investigate Memory Usage Across all Caches .................................................63
DMV#25: Investigate memory use in the Buffer Pool ..................................................... 64
Other Useful DMVs ........................................................................ 67
DMV#26: Rooting out Unruly CLR Tasks .........................................................................67
DMV#27: Full Text Search ................................................................................................... 68
DMV#28: Page Repair attempts in Database Mirroring ................................................. 70
Conclusion ...................................................................................... 72
剩余83页未读,继续阅读
沉默肥牛
- 粉丝: 3
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 收起
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
会员权益专享
最新资源
- 27页智慧街道信息化建设综合解决方案.pptx
- 计算机二级Ms-Office选择题汇总.doc
- 单链表的插入和删除实验报告 (2).docx
- 单链表的插入和删除实验报告.pdf
- 物联网智能终端项目设备管理方案.pdf
- 如何打造品牌的模式.doc
- 样式控制与页面布局.pdf
- 武汉理工Java实验报告(二).docx
- 2021线上新品消费趋势报告.pdf
- 第3章 Matlab中的矩阵及其运算.docx
- 基于Web的人力资源管理系统的必要性和可行性.doc
- 基于一阶倒立摆的matlab仿真实验.doc
- 速运公司物流管理模式研究教材
- 大数据与管理.pptx
- 单片机课程设计之步进电机.doc
- 大数据与数据挖掘.pptx
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论0