Select Content Type
What kind of article do you want to create?
Close Window
Article
Schedule
ArticleID
55592 - Click to preview
Canonical URL
https://www.dbta.com/Columns/SQL-Server-Drill-Down/The-Ebb-and-Flow-of-SQL-Server-Instrumentation-55592.aspx
Title
StandOut Url
Author(s)
Kevin Kline
Images
Related Articles
Summary
Microsoft SQL Server's relational engine has offered new instrumentation that improves by light years with each new release. The introduction of Dynamic Management Views (DMVs) in SQL Server 2005 provided a much-needed equivalent to Oracle's long-standing and capable V$ and X$ system views. SQL Server 2008 has provided another dramatic improvement to its instrumentation with Extended Events (also known as XEvents) that promises to offer even greater opportunities to tune, trace and troubleshoot the inner workings of a SQL Server application. All of this stands in stark contrast with the anemic instrumentation offered in SQL Server Analysis Services, Microsoft's wonderful multi-dimensional data repository that is a free feature-set within the SQL Server product.
Page 1
Page 2
Page 3
Page 4
Page 5
Page 6
Page 7
Page 8
Page 9
Page 10
Page 11
Page 12
Page 13
Page 14
Page 15
Video
<p>Microsoft SQL Server's relational engine has offered new instrumentation that improves by light years with each new release. The introduction of Dynamic Management Views (DMVs) in SQL Server 2005 provided a much-needed equivalent to Oracle's long-standing and capable V$ and X$ system views. SQL Server 2008 has provided another dramatic improvement to its instrumentation with Extended Events (also known as XEvents) that promises to offer even greater opportunities to tune, trace and troubleshoot the inner workings of a SQL Server application. All of this stands in stark contrast with the anemic instrumentation offered in SQL Server Analysis Services, Microsoft's wonderful multi-dimensional data repository that is a free feature-set within the SQL Server product.</p><p>SQL Server has long offered a two-part toolkit for basic instrumentation of its engines, both relational and analytical: SQL Profiler and Windows Performance Monitor (PerfMon), also known as Windows System Monitor. While each of these tools has its uses, they're also the focus of a long-running love/hate relationship within the community. Yes, they help to diagnose what's happening (or failing to happen) inside SQL Server. But they also are confounded by obtuse interfaces and cryptic information that, without long years of experience, mean almost nothing to the novice troubleshooter or performance tuner. Thankfully, Microsoft heard the weeping and gnashing of teeth among its user community and greatly advanced the basic instrumentation in the last couple of releases of SQL Server.</p><p>While SQL Profiler and PerfMon have seen incremental improvements with each release, the addition of DMVs has offered a leapfrogging technology to peer into the database engine and see what's happening. A simple Google search for "useful DMV query SQL Server" will turn up dozens of excellent DMV queries that retrieve internal information that simply wasn't exposed in any way just one release ago. Add in the extremely granular, event-driven nature of XEvents, making it now possible for you to track overall system utilization down to the individual SQL statement level.</p><p>While there is some nascent capability to do troubleshooting of this nature in SQL Server Analysis Services (SSAS), you're largely left in the dark. If you search the SQL Server Books Online for help diagnosing and monitoring the health of SSAS, you'll be pointed to PerfMon, SQL Profiler, and the even more elementary tools of Windows Debugger, Task Manager, and the Windows Event Viewer (at TechNet article <a href="http://technet.microsoft.com/en-us/library/cc966423.aspx#EHAA">http://technet.microsoft.com/en-us/library/cc966423.aspx#EHAA</a>). </p><p>There's hope on the horizon if you're an SSAS user. Be sure to bookmark Carl Rabeler's content on the website <a href="http://www.sqlcat.com/">http://www.sqlcat.com</a> by starting at <a href="http://sqlcat.com/members/CarlRabeler.aspx">http://sqlcat.com/members/CarlRabeler.aspx</a> (registration or LiveMeeting account required). From there, you can go to the CodePlex Web site and see the very nice SSAS Samples provided by the SQLCAT team at <a href="http://www.codeplex.com/SQLSrvAnalysisSrvcs">http://www.codeplex.com/SQLSrvAnalysisSrvcs</a>. There are several tools in the SSAS Samples pack that greatly ease the collection and evaluation SSAS performance metrics. Until Microsoft adds a complete, in-the-box set of diagnostic and troubleshooting tools and instrumentation for SSAS, this is a very badly needed add-on to the current SSAS toolkit and one that every SSAS analyst should utilize.</p>
Newsletter Name
Issue Name
Article Type
Article SubType
DBTA E-Edition
August 2009
Columns
SQL Server Drill Down
Please Choose
5 Minute Briefing : Blockchain
5 Minute Briefing: Cloud
5MB: Data Center
5MB: Information Management
5MB: MultiValue
5MB: Oracle
5MB: SAP
Big Data Quarterly Issue
Cloud Strategies
DBTA E-Edition
ExaBriefing
Headlines from AIOUG
IBM LinuxLine
Infrastructure Wisdom
IOUG Storage Systems
Linux Executive Report from IBM
Magazine Issue
Oracle Enterprise Manager
Unisphere Five Minute Briefing
Columns
Editorial
A Wider View
Applications Insight
Big Data Notes
Database Elaborations
DBA Corner
Defining Data
Emerging Technologies
From 30,000 Feet
MongoDB Matters
My View
MySQL Musings
New Directions
Next-Gen Data Management
Notes on NoSQL
Oracle Data Strategies
Oracle Observations
Quest IOUG Database & Technology Insights
SQL Server Drill Down
The Enterprise Environment
The Open DBA
The Philosophy of PL/SQL
Trends and Observations
Categories
Topics
Artificial Intelligence
Big Data
Blockchain
Business Intelligence and Analytics
Cloud Computing
Data Center Management
Data Integration
Data Modeling
Data Quality
Data Warehousing
Database Management
Database Security
Hadoop
Internet of Things
Master Data Management
MultiValue Database Technology
NoSQL Central
Virtualization
×
Authors
×
Search Articles
×
Image Helper
Upload an Image
Name
File
Browse…
Image Resize (width):
90
120
135
250
No resize
Preview (Click image to select)
Select the Image
ID
Image Name