260 likes | 391 Views
Monitoring SSAS cube performance and usage. Amit Bansal CTO | Peopleware India (unit of eDominer Systems) www.amitbansal.net | www.WeTogether.in | www.peoplewareindia.com. <about> Amit Bansal </about>. CTO, eDominer Systems & Peopleware India Corporate Trainer/Consultant & Evangelist
E N D
Monitoring SSAS cube performance and usage Amit Bansal CTO |Peopleware India (unit of eDominer Systems) www.amitbansal.net | www.WeTogether.in | www.peoplewareindia.com
<about> Amit Bansal </about> • CTO, eDominer Systems & Peopleware India • Corporate Trainer/Consultant & Evangelist • Conducted more than 200 workshops on SQL Server & BI for top notch IT companies world wide • Microsoft MVP for SQL Server, UG Lead • Microsoft Certified Trainer Advisory Council member • Speaker at TechED India, TechED US & TechED Europe • Technical Reviewer – MSL courses on SQL Server • SME – SQL Server 2008 certifications • Manager – www.WeTogether.in, www.BlogBoard.in • President – SQLServerGeeks.com
Introduction • When you create large, enterprise-scale databases, you may encounter performance problems caused by poor database design or hardware limitations. • When you have performance issues, it is critical to first properly diagnose the problem before you take corrective measures.
Shared resources • CPU • For processing & querying • During partition processing, CPU is used mainly to build aggregations • Memory • Almost all processes within SSAS need memory • I/O • SSAS generates I/O operations both directly and indirectly
DEMO Using Task Manager to monitor CPU usage
Tools • Task Manager • Windows Reliability And Performance Monitor • SQL Server Profiler • Dynamic Management Views
Windows Reliability & Performance Monitor • Provides tools to analyze operating system, application, and hardware performance. • Consists of three components: • Resource View • Reliability Monitor • and Performance Monitor
Windows Reliability & Performance Monitor • What do you monitor? • CPU • % Processor Time • Memory • Pages/sec is the number of pages read from and written to disk to resolve hard page faults • Committed Bytes is the amount of virtual memory requested by all running processes
Windows Reliability & Performance Monitor • What do you monitor? • I/O • Current Disk Queue Length represents the current number of queued requests to the disk. If this number is constantly above the number of spindles plus 2, the disk subsystem could be improved by adding spindles (to a RAID 0/10 configuration) or by using faster disks • Disk Read Bytes/sec & Disk Write Bytes/sec
Windows Reliability & Performance Monitor • What do you monitor? • SSAS counters: • MSAS 2008: Memory – Memory Limit High KB • MSAS 2008: Memory – Memory Limit Low KB • MSAS 2008: Memory – Memory Usage KB • MSAS 2008: Processing • MSAS 2008: Proc Aggregations • MSAS 2008: Proc Indexes
Monitoring Processing Performance • SSAS counters: • MSAS 2008: Processing • Total Rows Converted • Total Rows Read • Total Rows Written • SQL Server Profiler • Command Events • Command Begin • Command End • Progress Reports • Progress Report Begin • Progress Report End • Progress Report Error
DEMO Monitoring using Performance Monitor
Monitoring Processing Performance • Using Dynamic Management Views (DMVs) SELECT TOP 10 * FROM $System.DISCOVER_OBJECT_ACTIVITY ORDER BY OBJECT_CPU_TIME_MS DESC
DEMO Monitoring using DMVs
Monitoring Query Performance • Regardless of the tool we use, trace data is the most important source of information on query related operations. • Trace data provides information on the internal operations of the Storage Engine and the Formula Engine, for example showing if aggregations are used or not or if calculations are evaluated in bulk mode or not
Monitoring Query Performance • Progress Report Begin (5) / Progress Report End (6) • Query (14) shows when the Storage Engine accesses a partition or aggregation to get data. This will only happen if the data required is not available in the Storage Engine cache. • Query Begin (9) / Query End (10) – are raised at the start and end of query evaluation. • MDXQuery(0) shows the MDX statement sent to Analysis Services. For the Query End event, the Duration column shows the overall amount of time taken to run the query and return its results back to the client.
Monitoring Query Performance • Calculate Non Empty Begin (72) / Calculate Non Empty End (74) – have no related subclass events. • The Calculate Non Empty events are raised when Analysis Services performs non empty filtering operations, for example when the NonEmpty MDX function or the Non Empty statement is used in a query, and these operations are often the cause of slow query performance.
Monitoring Query Performance • Get Data From Aggregation (60) – has no related subclass events. This event is raised when the Storage Engine reads data from an aggregation. • Get Data From Cache (61) – is raised when data is read from cache.
Monitoring Query Performance • Query Cube Begin (70) / Query Cube End (71) • Query Dimension (81) - this event is raised when queries retrieve members from dimension hierarchies • Query Subcube (11) • Query Subcube Verbose (12) – is functionally identical to Query Subcube, but it adds more information about the multidimensional
DEMO Monitoring Query Performance using SQL Server Profiler
Monitoring Query Performance • Can you monitor query performance using Performance Monitor? • MSOLAP: Cache • MSOLAP:MDX
Summary • Monitoring Processing Performance • Performance Monitor • SQL Server Profiler • DMVs • Monitoring Query Performance • Performance Monitor • SQL Server Profiler • DMVs
Feedback / QnA • Your Feedback is Important! Please take a few moments to fill out our online feedback form at: << Feedback URL – Ask your organizer for this in advance>> For detailed feedback, use the form at http://www.connectwithlife.co.in/vtd/helpdesk.aspx Or email us at vtd@microsoft.com • Use the Question Manager on LiveMeeting to ask your questions now!
SQLServerGeeks.com • Bangalore – 20th November 2010 • Mumbai – 28th November 2010 • Gurgaon – Dec 5, 2010 • Visit www.SQLServerGeeks.com to register
Join my network • Join my network: • Personal Site – http://www.amitbansal.net • LinkedIn – http://www.linkedin.com/in/AmitBansal2010 • Forum – http://www.WeTogether.in • Blog – http://www.BlogBoard.in/AmitBansal • Twitter – http://www.twitter.com/A_Bansal • FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346