Exploring the MDW Components

After enabling Data Collection, there are 4 System Data Collection Sets in the Data Collection folder in SSMS, as shown below. The last one, Utility Information, is in a stopped state, we can ignore it, it was not intended to be started, as mentioned by Kendra Little and Bill Ramos in this post

In fact, Utility Control Point jobs have different user interface called Utility Explorer so not sure why Microsoft left it in the Data Collection folder.

The SQL Server Agent jobs will be configured automatically by the System Data Collection Set properties. Do not modify the job properties directly. These jobs will call dcexec.exe that uses several SSIS packages to collect and upload the data. If the collection job uses cached data, the default temp directory is C:\Users\sqlserveragent\AppData\Local\Temp. We configured it as C:\Temp.

The upload job will insert the data to the respective tables in the centralized data warehouse. The tables in the schema “core” are used for lookups and the ones with schema “snapshot,” as name implies, store snapshots of DMVs or performance counters.

There are a couple of tables named snapshots.distinct_queries and snapshots.distinct_query_to_handle that seem to be not used.

The biggest table will be snapshots.notable_query_plan because it will store the query plans in varchar max data type so it cannot be compressed. On our systems, this table has an average size of 3 GB for 2 day retention.

Speaking of retention, the defaults are as follows:

If we look at the mdw_purge_data job, we will see it executes core.sp_purge_data that deletes the core.snapshot_internal and relies in the foreign key constraints to cascade deletes to the dependent tables.

A couple of exceptions of the cascade deletes, which are worth mentioning, are the snapshot.notable_query_text and the snapshot.notable_query_plan tables. If we look at the primary keys, we will see that they are not depended on the snapshot_id.

The data collection process will insert a row if an entry does not exists for the notable query text or plan, instead of inserting the same notable query text or plan for each snapshot. Thus, the core.sp_purge_data procedure calls other stored procedure to delete the orphan rows if no other snaphot.query_stats references the sql_handle or plan_handle respectively

We can easily visualize the table relationships by creating a database diagram using SSMS

From the database diagram we can deduct that each time a data collection is executed, the data is stored in the respective snaphot.* table, as well as , in the following tables core.* tables:

• core.source_info_internal
• core.snapshot_timetable
• core.snapshots_internal

For example, if we want to get snapshots.query_stats for a particular instance and time, we can use the following query:

 
select *
from core.snapshots_internal i
join core.snapshot_timetable_internal t
on i.snapshot_time_id = t.snapshot_time_id
join core.source_info_internal s
on i.source_id = s.source_id
join snapshots.query_stats q
on i.snapshot_id = q.snapshot_id
where s.instance_name = ''
and t.snapshot_time = ''

Uninstalling the MDW

In case we move the SQL instance to another machine, we need to uninstall the MDW and then reinstall it because some of the components store the name of the machine. The following T-SQL script will clean up what we set up using the script in the previous post.

 
-- disable collector

exec msdb.dbo.sp_syscollector_disable_collector

-- reset entries

update msdb.dbo.syscollector_config_store_internal
set parameter_value = null
where parameter_name in (
'CacheDirectory'
,'MDWDatabase'
,'MDWInstance'
)

-- reset job ids so jobs can be deleted

update msdb..syscollector_collection_sets_internal
set collection_job_id = null
,upload_job_id = null

-- drop the mdw database

drop database msmdw

-- drop role

use msdb
alter role dc_report_reader drop member [guest]
drop role dc_report_reader

-- get a copy of the collector schedules because they get dropped when the jobs are dropped

select * into #copy from msdb.dbo.sysschedules where schedule_id in (1,4,7)

-- drop jobs

exec msdb..sp_delete_job @job_name = 'collection_set_1_noncached_collect_and_upload'
exec msdb..sp_delete_job @job_name = 'collection_set_2_collection'
exec msdb..sp_delete_job @job_name = 'collection_set_2_upload'
exec msdb..sp_delete_job @job_name = 'collection_set_3_collection'
exec msdb..sp_delete_job @job_name = 'collection_set_3_upload'
exec msdb..sp_delete_job @job_name = 'mdw_purge_data_[msmdw]'

-- copy the schedules back

set identity_insert msdb.dbo.sysschedules on
insert msdb.dbo.sysschedules( 
schedule_id
, schedule_uid
, originating_server_id
, name
, owner_sid
, enabled
, freq_type
, freq_interval
, freq_subday_type
, freq_subday_interval
, freq_relative_interval
, freq_recurrence_factor
, active_start_date
, active_end_date
, active_start_time
, active_end_time
, date_created
, date_modified
, version_number
)
select * from #copy
set identity_insert msdb.dbo.sysschedules off
drop table #copy

-- reset SSIS package ids

truncate table msdb.dbo.syscollector_tsql_query_collector

To clean up the SSIS package, execute the query below and copy the output in a CMD window on the machine.
Replace server_name with the name of your server

 
select
'DTUTIL /SQL "\Data Collector\Generated\' + name + '" /DELETE /SourceServer server_name'
from msdb.dbo.sysssispackages where description like 'Auto-generated%'

Example

 
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_2_Collect" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_1_Collect" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_2_Upload" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Upload" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Collect" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_1_Upload" /DELETE /SourceServer server_name