{"id":76,"date":"2008-09-18T18:27:36","date_gmt":"2008-09-18T23:27:36","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/?p=76"},"modified":"2008-09-18T18:27:36","modified_gmt":"2008-09-18T23:27:36","slug":"how-to-use-mdw-to-collect-analysis-services-2008-performance-counters","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/76_how-to-use-mdw-to-collect-analysis-services-2008-performance-counters","title":{"rendered":"How to use MDW to collect Analysis Services 2008 performance counters"},"content":{"rendered":"

In one of my previous blog posts<\/a> I described SQL Server 2008 Management Data Warehouse (MDW) and how it can be used to collect performance information. This post will describe how you can create new MDW data collection sets to\u00a0capture SQL Server Analysis Services (SSAS) 2008 performance counters.<\/p>\n

SQL Server 2008 does not have a\u00a0graphical interface to create MDW data collection sets, so the best way to do that is by\u00a0scripting\u00a0an existing system data collection set\u00a0and then changing it. To script an\u00a0existing data collector,\u00a0start SQL Server Management Studio, expand “Management”->”Data Collection”->”System Data Collection Sets” folder then right mouse click-> “Script Data Collection As..”->”Create To”->”New Query Editor Window”. For my starting point I\u00a0scripted\u00a0the\u00a0data collection set\u00a0“Server Activity” and got\u00a0this script<\/a>.<\/p>\n

<\/p>\n

The original script collects information from\u00a0two different collector types: “Generic T-SQL Query Collector Type” and “Performance Counters Collector Type”. For this exercise I removed part of the script related to the first collector type. Then I updated\u00a0the data collection set name and\u00a0replaced\u00a0 the performance counters that I am interested in collecting. All performance counters are listed in\u00a0the string as XML node attributes. Here is example of one counter:<\/p>\n

<PerformanceCounters Objects=”Process” Counters=”%Processor Time” Instances=”sqlservr” \/><\/p><\/blockquote>\n

For counters you can specify value “*” and that will include all counters from\u00a0a specific object. You do not have to specify the “Instances” attribute.<\/p>\n

For this exercise I selected just a few Analysis Services related counters that I wanted to track.\u00a0I see myself having at least a few SSAS related data collection sets – for example I would want to have a separate set\u00a0to track cube processing performance that I would run just during nightly cube reprocessing.\u00a0\u00a0Here is the list of Analysis Services 2008 performance counters<\/a>\u00a0that you could use to build your data collection sets.<\/p>\n

So\u00a0below is the script to create\u00a0a new data collection set\u00a0“Server Activity SSAS Counters 1”. This script should be executed in MDW database:<\/p>\n

Begin Transaction
\nBegin Try
\nDeclare @collection_set_id_1 int
\nDeclare @collection_set_uid_2 uniqueidentifier
\nEXEC [msdb].[dbo].[sp_syscollector_create_collection_set] @name=N’Server Activity SSAS Counters 1′
\n, @collection_mode=0
\n, @description=N’Collects top-level performance indicators for the computer and the Database Engine. Enables analysis of resource use, resource bottlenecks, and Database Engine activity.’
\n, @logging_level=0, @days_until_expiration=14
\n, @schedule_name=N’CollectorSchedule_Every_15min’
\n, @collection_set_id=@collection_set_id_1 OUTPUT
\n, @collection_set_uid=@collection_set_uid_2 OUTPUT
\nSelect @collection_set_id_1, @collection_set_uid_2
\nDeclare @collector_type_uid_5 uniqueidentifier
\nSelect @collector_type_uid_5 = collector_type_uid From [msdb].[dbo].[syscollector_collector_types]
\n\u00a0Where name = N’Performance Counters Collector Type’;
\nDeclare @collection_item_id_6 int
\nEXEC [msdb].[dbo].[sp_syscollector_create_collection_item] @name=N’Server Activity – SSAS Performance Counters 1′
\n, @parameters=N'<ns:PerformanceCountersCollector xmlns:ns=”DataCollectorType”>
\n<PerformanceCounters Objects=”Processor” Counters=”%Processor Time” Instances=”_Total” \/>
\n<PerformanceCounters Objects=”Process” Counters=”%Processor Time” Instances=”msmdsrv” \/>
\n<PerformanceCounters Objects=”Process” Counters=”%Processor Time” Instances=”sqlservr” \/>
\n<PerformanceCounters Objects=”MSAS 2008:Connection” Counters=”Current connections” \/>
\n<PerformanceCounters Objects=”MSAS 2008:Connection” Counters=”Current user sessions” \/>
\n<PerformanceCounters Objects=”MSAS 2008:Memory” Counters=”Memory Usage KB” \/>
\n<PerformanceCounters Objects=”MSAS 2008:Storage Engine Query” Counters=”Current measure group queries” \/>
\n<PerformanceCounters Objects=”MSAS 2008:Storage Engine Query” Counters=”Avg time\/query” \/>
\n<PerformanceCounters Objects=”MSAS 2008:Storage Engine Query” Counters=”Aggregation hits\/sec” \/>
\n<PerformanceCounters Objects=”MSAS 2008:Storage Engine Query” Counters=”Queries answered\/sec” \/>
\n<PerformanceCounters Objects=”MSAS 2008:Storage Engine Query” Counters=”Total queries answered” \/>
\n<PerformanceCounters Objects=”MSAS 2008:MDX” Counters=”Total cells calculated” \/>
\n<\/ns:PerformanceCountersCollector>’
\n, @collection_item_id=@collection_item_id_6 OUTPUT, @frequency=60
\n, @collection_set_id=@collection_set_id_1, @collector_type_uid=@collector_type_uid_5
\nSelect @collection_item_id_6<\/p>\n

Commit Transaction;
\nEnd Try
\nBegin Catch
\nRollback Transaction;
\nDECLARE @ErrorMessage NVARCHAR(4000);
\nDECLARE @ErrorSeverity INT;
\nDECLARE @ErrorState INT;
\nDECLARE @ErrorNumber INT;
\nDECLARE @ErrorLine INT;
\nDECLARE @ErrorProcedure NVARCHAR(200);
\nSELECT @ErrorLine = ERROR_LINE(),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ErrorSeverity = ERROR_SEVERITY(),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ErrorState = ERROR_STATE(),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ErrorNumber = ERROR_NUMBER(),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ErrorMessage = ERROR_MESSAGE(),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), ‘-‘);
\nRAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);<\/p>\n

End Catch;<\/p>\n

GO<\/p><\/blockquote>\n

Here is the link to the text file<\/a> with SQL statements.<\/p>\n

After you execute this script, in SSMS you will see\u00a0a new data collection set\u00a0“Server Activity SSAS Counters 1”. To\u00a0begin collecting data, you will need to start this new collector (right mouse click, “Start Data Collection Set”).<\/p>\n

After your custom data collection set is up and running, you have to build custom reports to show data that was collected.\u00a0Creating custom report deserves a separate blog post or even two, as it is not very easy. The best way would be to follow\u00a0the steps that Microsoft did for system MDW reports. I run the profiler to capture how Microsoft does that and found that all data for reports comes back from parameterized stored procedures. For\u00a0this demo, I just quickly created a query to get the captured data and used that in the SSRS report.<\/p>\n

Captured performance counter data is stored in the MDW database tables snapshots.performance_counter_values and snapshots.performance_counter_instances. Here is the structure for these tables:\u00a0<\/p>\n

\"MDW

MDW Peformance counter related tables<\/p><\/div>\n

Microsoft also has a view\u00a0\u00a0snapshots.performance_counters that is a inner join of these two tables. I used that view for my queries. Here is sample of my query:<\/p>\n

SELECT performance_counter_id
\n\u00a0, snapshot_id
\n\u00a0, collection_time —\u00a0UTC time used!!!
\n\u00a0, RIGHT(LEFT(CONVERT(varchar, collection_time, 120),10),5) AS CollectionDate
\n\u00a0, LEFT(RIGHT(CONVERT(varchar, collection_time, 120),8),5) AS CollectionTime
\n\u00a0, path
\n\u00a0, performance_object_name
\n\u00a0, performance_counter_name
\n\u00a0, performance_instance_name
\n\u00a0, formatted_value
\n\u00a0, raw_value_first
\n\u00a0, raw_value_second
\n\u00a0 FROM [MDW].[snapshots].[performance_counters]
\n\u00a0WHERE performance_object_name like ‘MSAS 2008:%’<\/p><\/blockquote>\n

Big surprise to me was that you cannot use BIDS 2008 to build custom reports, you have to use Reporting Services with BIDS 2005. Based on this Microsoft connect feedback <\/a>it is by design, but it is quite a big inconvieniece, so I hope Microsoft will fix this in the future. If you\u00a0are building custom reports for SQL Server 2008, please make sure you leave your vote for this connect feedback, as more votes should make Microsoft consider to make this change sooner.<\/p>\n

I created a sample dashboard type report just to show 2 SSAS performance counters. Here is screenshot for my dashboard:<\/p>\n

\"SSAS<\/a>

SSAS Counters MDW report<\/p><\/div>\n

RDL code for this report can be downloaded here<\/a>. After downloading this file, please change the file extension from .txt to .rdl and copy it\u00a0to\u00a0your SQL Server server machine. Then in SSMS you should select\u00a0the data collection set, right mouse click, then “Reports”->”Custom reports”->”Add Custom Report” and select the\u00a0rdl file from\u00a0the saved location. You will need to add\u00a0your custom report just once, after that it will appear in the custom report list.<\/p>\n

Please note again, that\u00a0the above report is just a sample of how to build custom reports. To show captured SSAS performance counters for a real environment you will need to spend much more time in building\u00a0nicer looking\u00a0reports.<\/p>\n

I like MDW because\u00a0this framework provided by Microsoft takes care of data collection, storage and purge and because it is easy to integrate reports into SQL Server Management Studio. I am just waiting from Microsoft (or a third party) for\u00a0a set of data collectors and reports related to Analysis Services.<\/p>\n","protected":false},"excerpt":{"rendered":"

In one of my previous blog posts I described SQL Server 2008 Management Data Warehouse (MDW) and how it can be used to collect performance information. This post will describe how you can create new MDW data collection sets to\u00a0capture SQL Server Analysis Services (SSAS) 2008 performance counters. SQL Server 2008 does not have a\u00a0graphical […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,6],"tags":[10,11,12,21],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/76"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=76"}],"version-history":[{"count":13,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/76\/revisions"}],"predecessor-version":[{"id":90,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/76\/revisions\/90"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=76"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=76"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=76"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}