了解DMV ......或者我们应该说DMOS吗？

我最喜欢的技术会议上个月通过Grant Fritchey提供，“可怕DBA”提供。该主题是动态管理视图（DMV）以及如何使用它们在标题“SQL Server DMV”下的故障排除作为过程调整的快捷方式“。本周三会议也在“SQL Kilt”日，所以通过佩戴伪装的kilt授予授权。现在有一个可怕的dba！授予很快将会话重命名为动态管理对象（DMOS），因为技术上存在动态管理视图和功能，因此DMO是一个更好的包容性术语。DMOS允许您在实时意义上从性能角度检查SQL Server的内部状态。有数百个DMOS可用，所以大问题总是“哪些是最好的使用？”。当然，答案是“这取决于”，但批准做了很大突出他的最爱。DMOS是Server Scoped或数据库范围。要使用服务器范围的DMO，您将需要查看服务器状态权限。 To use a Database scoped DMO you will need VIEW DATABASE STATE permission. Grant recommended giving these rights to your developers so they can do their own performance analysis. This caused a bit of a stir (“give our developers what???”) but it makes sense if you wish to empower your developers and reduce your workload. Maybe not, if you don’t. Most DMOs can be joined with others to reveal more information. For instance, you can join DM_EXEC_REQUESTS and DM_TRAN_ACTIVE_TRANSACTIONS on Transaction_ID. This may uncover transactions that are being blocked since DM_EXEC_REQUESTS includes a BLOCKING_SESSION_ID. You could then join this with DM_OS_WAITING_TASKS on Session_ID to reveal waiting information such as WAIT_DURATION_MS, the wait time in milliseconds. The SSMS Activity Monitor actually uses similar DMOs to display its information graphically but you can get this information quickly through your own scripts. You could also set up a SQL Profiler trace to capture exactly what DMOs the Activity Monitor is accessing on your behalf. If you are lucky, there may even be some comments in there provided by thoughtful Microsoft developers. The flexibility of your own scripts means you can focus on what’s important to you. Also, since these are standard objects, you can reuse scripts from other helpful folks like the Scary DBA. http://scarydba.com/ Cheers Brian