1 / 26

Monitoring SSAS cube performance and usage

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

vera
Download Presentation

Monitoring SSAS cube performance and usage

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Monitoring SSAS cube performance and usage Amit Bansal CTO |Peopleware India (unit of eDominer Systems) www.amitbansal.net | www.WeTogether.in | www.peoplewareindia.com

  2. <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

  3. 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.

  4. 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

  5. DEMO Using Task Manager to monitor CPU usage

  6. Tools • Task Manager • Windows Reliability And Performance Monitor • SQL Server Profiler • Dynamic Management Views

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. DEMO Monitoring using Performance Monitor

  13. Monitoring Processing Performance • Using Dynamic Management Views (DMVs) SELECT TOP 10 * FROM $System.DISCOVER_OBJECT_ACTIVITY ORDER BY OBJECT_CPU_TIME_MS DESC

  14. DEMO Monitoring using DMVs

  15. 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

  16. 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.

  17. 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.

  18. 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.

  19. 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

  20. DEMO Monitoring Query Performance using SQL Server Profiler

  21. Monitoring Query Performance • Can you monitor query performance using Performance Monitor? • MSOLAP: Cache • MSOLAP:MDX

  22. Summary • Monitoring Processing Performance • Performance Monitor • SQL Server Profiler • DMVs • Monitoring Query Performance • Performance Monitor • SQL Server Profiler • DMVs

  23. 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!

  24. SQLServerGeeks.com • Bangalore – 20th November 2010 • Mumbai – 28th November 2010 • Gurgaon – Dec 5, 2010 • Visit www.SQLServerGeeks.com to register

  25. 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

More Related