Analyzing Replication Performance Statistics

As mentioned in this KB article, useful data to troubleshoot replication performance is stored in the MSlogreader_history and MSdistribution_history tables in the distribution database.

I was troubleshooting latency from the distribution to the subscriber database. So here is the query to get the info from MSdistribution_history for a particular distribution agent id. Similar query can be done for the log reader.

 
declare @agent_id int = 662;

with a as(
select 
h.agent_id
,h.time
,cast(h.comments as xml) comments
from MSdistribution_history h with (nolock)     
where             
h.[comments] like '<stats state="1"%'  
and h.agent_id = @agent_id
)
select 
a.agent_id
,a.time 
,c.value('(/stats/@state)[1]', 'int') AS [state]      
,c.value('(/stats/@work)[1]', 'int') AS [work]      
,c.value('(/stats/@idle)[1]', 'int') AS [idle]            
,c.value('(/stats/reader/@fetch)[1]', 'int') AS [reader_fetch]      
,c.value('(/stats/reader/@wait)[1]', 'int')  AS [reader_wait]            
,c.value('(/stats/writer/@write)[1]', 'int') AS [writer_write]      
,c.value('(/stats/writer/@wait)[1]', 'int')  AS [writer_wait]            
,c.value('(/stats/sincelaststats/@elapsedtime)[1]', 'int') AS [sincelaststats_elapsedtime]      
,c.value('(/stats/sincelaststats/@work)[1]', 'int') AS [sincelaststats_work]      
,c.value('(/stats/sincelaststats/@cmds)[1]', 'int') AS [sincelaststats_cmds]      
,c.value('(/stats/sincelaststats/@cmdspersec)[1]', 'float') AS [sincelaststats_cmdspersec]       
,c.value('(/stats/sincelaststats/reader/@fetch)[1]', 'int') AS [sincelaststats_reader_fetch]      
,c.value('(/stats/sincelaststats/reader/@wait)[1]', 'int')  AS [sincelaststats_reader_wait]            
,c.value('(/stats/sincelaststats/writer/@write)[1]', 'int') AS [sincelaststats_writer_write]      
,c.value('(/stats/sincelaststats/writer/@wait)[1]', 'int')  AS [sincelaststats_writer_wait]    
from a
cross apply a.comments.nodes('/stats[@state="1"]') b(c)
order by time;

Replication uses a memory buffer structure to queue up the rows between the reader thread and the writer thread. Thus a high “since last stats reader wait” means it is waiting from the writer thread to consume the rows from the buffer and deliver them to the subscriber, while a high “since last stats writer wait” means it is waiting for the reader thread to produce rows from the distribution to the buffer.

Next, you can use your favorite tool (I used SSRS) to create a graph so we can visualize the information better. In the graph below, we can see there is a correlation between the number of commands replicated and the replication latency. So we can either decrease the number of commands replicated or increase the performance of the subscriber. I this particular case we focused on the subscriber side and found that IO was the bottleneck.

Automatically Manage Server Registrations

To create, update, or drop server registrations, we can either use the system tables directly:

• msdb.dbo.sysmanagement_shared_registered_servers_internal
• msdb.dbo.sysmanagement_shared_server_groups_internal

Or we can use SMO and PowerShell to automatically maintain the Central Management Servers. For  example, three group levels will be created as shown below

The central SQL Server  is named SQL1. The database is named dba_utilities. The table called instances is used to store the instances that are automatically or manually discovered in your organization. The stored procedure called sel_registered_servers is used to show the list of servers and respective groups. Additionally, sel_registered_servers_to_drop is used to show the list of servers that are no longer in your organization.

 
-- table for instances
create table instances(
logical_name varchar(255) not null constraint instances_pk primary key
,physical_name varchar(255) not null
,support_code varchar(10) not null
,sql_version varchar(10) not null
)
go

-- insert instances
insert instances values('SQL1','COMPUTER3\SQL1','TEST','2012')
insert instances values('SQL2','COMPUTER1\SQL2','PROD','2008')
insert instances values('SQL3','COMPUTER1\SQL3','PROD','2008')
insert instances values('SQL4','COMPUTER2\SQL4','PROD','2012')
insert instances values('SQL5','COMPUTER3\SQL5','TEST','2012')
insert instances values('SQL6','COMPUTER3\SQL6','TEST','2014')
go

-- stored procedure to show registered servers
create proc sel_registered_servers
as
select
'Support Version' group1
,support_code group2
,sql_version group3
,logical_name
,physical_name
from instances
where logical_name <> 'SQL1' -- central server cannot be added 
union
select
'Version Support' group1
,sql_version group2
,support_code group3
,logical_name
,physical_name
from instances
where logical_name <> 'SQL1' -- central server cannot be added 
go

-- stored procedure to show obsolete servers
create proc sel_registered_servers_to_drop
as
select distinct name 
into #temp
from msdb.dbo.sysmanagement_shared_registered_servers_internal 

select t.name logical_name
from instances i
right join #temp t
on i.logical_name = t.name
where i.logical_name is null

drop table #temp
go

Output of sel_registered_servers

The PowerShell script can read the results of the stored procedures and hierarchically navigate through the directory tree and create, update, or drop server registrations

 
function Add-RegisteredServer ($group1, $group2, $group3, $logicalName, $physicalName)
{
    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList = @($group1, $group2, $group3)
    
    # navigate down the hierarchy

    foreach ($group in $groupList) 
    {
        if ($serverGroups.ServerGroups[$group] -eq $null)
        {
         $newGroup = New-Object Microsoft.SqlServer.Management.RegisteredServers.ServerGroup($serverGroups, $group)
         $newGroup.create()
         $serverGroups.refresh()
        }
        $serverGroups = $serverGroups.ServerGroups[$group]
    }  

    # create or alter the registered server

    if($serverGroups.RegisteredServers.name -contains $logicalName)
    {
        $oldServer = $serverGroups.RegisteredServers[$logicalName]

        if ($oldServer.ServerName -ne $physicalName)
        {
            Write-Host "$group1 $group2 $group3 $logicalName Altered"
            $oldServer.ServerName = $physicalName
            $oldServer.Alter()
        }
    }
    else
    {
        Write-Host "$group1 $group2 $group3 $logicalName Created"
        $newServer = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($serverGroups, $logicalName)
        $newServer.ServerName = $physicalName
     $newServer.Create()
    }
}

function Drop-RegisteredServer ($logicalName)
{
    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList1 = $serverGroups.ServerGroups.Name

    foreach ($group1 in $groupList1) 
    {
        $groupList2 = $serverGroups.ServerGroups[$group1].ServerGroups.Name
    
        foreach ($group2 in $groupList2) 
        {
            $groupList3 = $serverGroups.ServerGroups[$group1].ServerGroups[$group2].ServerGroups.Name

            foreach ($group3 in $groupList3) 
            {
                $oldGroup = $serverGroups.ServerGroups[$group1].ServerGroups[$group2].ServerGroups[$group3]

                if($oldGroup.RegisteredServers.Name -contains $logicalName)
                {
                    Write-Host "$group1 $group2 $group3 $logicalName Dropped"
                    $oldServer = $oldGroup.RegisteredServers[$logicalName]
                    $oldServer.Drop()
                }
            }
        }
    }
}

function Drop-RegisteredServerGroup ()
{
    # Drop group3

    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList1 = $serverGroups.ServerGroups.Name

    foreach ($group1 in $groupList1) 
    {
        $groupList2 = $serverGroups.ServerGroups[$group1].ServerGroups.Name
    
        foreach ($group2 in $groupList2) 
        {
            $groupList3 = $serverGroups.ServerGroups[$group1].ServerGroups[$group2].ServerGroups.Name

            foreach ($group3 in $groupList3) 
            {
                $oldGroup = $serverGroups.ServerGroups[$group1].ServerGroups[$group2].ServerGroups[$group3]
                
                if($oldGroup.RegisteredServers.Count -eq 0)
                {
                    Write-Host "$group1 $group2 $group3 Dropped"
                    $oldGroup.Drop()
                }
            }
        }
    }

    # Drop group2

    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList1 = $serverGroups.ServerGroups.Name

    foreach ($group1 in $groupList1) 
    {
        $groupList2 = $serverGroups.ServerGroups[$group1].ServerGroups.Name
    
        foreach ($group2 in $groupList2) 
        {
            $oldGroup = $serverGroups.ServerGroups[$group1].ServerGroups[$group2]
                
            if($oldGroup.ServerGroups.Count -eq 0)
            {
                Write-Host "$group1 $group2 Dropped"
                $oldGroup.Drop()
            }
        }
    }

    # Drop group2

    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList1 = $serverGroups.ServerGroups.Name

    foreach ($group1 in $groupList1) 
    {
        $oldGroup = $serverGroups.ServerGroups[$group1]
                
        if($oldGroup.ServerGroups.Count -eq 0)
        {
            Write-Host "$group1 Dropped"
            $oldGroup.Drop()
        }
    }
}

# Main Program

$tarSrv = "COMPUTER3\SQL1"
$tarDB  = "dba_utilities"

try
{
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($tarSrv)
    $registeredServer = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($server.ConnectionContext.SqlConnectionObject)

    $tarCon = New-Object System.Data.SQLClient.SQLConnection("Data Source=$tarSrv;Initial Catalog=$tarDB; Integrated Security=True;")
    $tarCon.Open()

    $tarCmd = New-Object system.Data.SqlClient.SqlCommand("exec sel_registered_servers", $tarCon)   
    $instances = $tarCmd.ExecuteReader()

    while ($instances.Read()) 
    {
        $group1 = $instances.GetValue(0)
        $group2 = $instances.GetValue(1)
        $group3 = $instances.GetValue(2)
        $logicalName = $instances.GetValue(3)
        $physicalName= $instances.GetValue(4)
    
        Add-RegisteredServer $group1 $group2 $group3 $logicalName $physicalName
    }
    $instances.Close()

    $tarCmd.CommandText = "exec sel_registered_servers_to_drop"
    $instances = $tarCmd.ExecuteReader()

    while ($instances.Read()) 
    {
        $logicalName = $instances.GetValue(0)
    
        Drop-RegisteredServer $logicalName 
    }
    $instances.Close()

    Drop-RegisteredServerGroup

    $tarCon.Close()
    $tarCon.Dispose()

}
catch
{
    $e = $_ | select -ExpandProperty InvocationInfo
    $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
    throw $m
}

Speaking at 24 Hours of PASS: Growing our Community

Last month I was selected to speak at 24 Hours of PASS. It was my first time speaking at a PASS event and I am glad I did. It was well organized, the staff was friendly, and the GoToWebinar software worked pretty well.
About 263 people attended my session, 91 filled out the survey. I got mostly positive feedback, only one person was disappointed that the content was a bit beginner level which it was. I intended it to be 200 level session. I realized that list of sessions didn't show the levels. So next time I will add it to my slide deck. Also I would add more examples, time better the content, and practice more. Overall I enjoyed the experience and will look forward to speak again.

My session abstract

Trend Analysis of SQL Error Logs: Seeing Beyond the Error

You carefully search for errors in the logs daily and fix them as they come. Recently, you noticed some errors are reoccurring and wonder when they started and what you could do differently. However the logs have already recycled and the information is gone, so you are left with the inquiry and go on with the next task. In this session, you will learn how to automatically parse the errors from multiple SQL error logs using PowerShell, store the data in a central database, and create reports to visualize the information using SQL Server Reporting Services. Analyzing error trends will allow you to see solutions that will lead to greater process efficiency.

Session recordings, scripts and slides are posted here

Data Collection (MDW) Errors after Mirror or Availability Group Failover

Symptoms

After mirror or availability group failover, we get the following errors on the data collection jobs on the mirror or secondary server

Sysssislog messages:

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "The target database, 'xxxx', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.".

OLE DB error occurred while fetching parameterized rowset. Check SQLCommand and SqlCommandParam properties.

SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "LKUP - Look up query plans on target server" (2) failed with error code 0xC0208253 while processing input "Lookup Input" (16). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

Root Cause 

When the collector finds an “interesting” query from sys.dm_exec_query_stats, it calls the stored procedure msdb.dbo.sp_syscollector_text_query_plan_lookup to get the query plan from cache using sys.dm_exec_text_query_plan. That function will error out if the plan references a database that has the role mirror or secondary. As explained in this blog, a failover does not flush the plan cache.

Workaround

We can add error handling to the procedure to continue when the respective error occurs

 
USE [msdb]
GO
ALTER PROCEDURE [dbo].[sp_syscollector_text_query_plan_lookpup]
    @plan_handle varbinary(64),
    @statement_start_offset int,
    @statement_end_offset int
AS
BEGIN
    SET NOCOUNT ON
 BEGIN TRY
  SELECT    
   @plan_handle AS plan_handle,
   @statement_start_offset AS statement_start_offset,
   @statement_end_offset AS statement_end_offset,
   [dbid] AS database_id,
   [objectid] AS object_id,
   OBJECT_NAME(objectid, dbid) AS object_name,
   [query_plan] AS query_plan
  FROM    
   [sys].[dm_exec_text_query_plan](@plan_handle, @statement_start_offset, @statement_end_offset) dm
 END TRY
 BEGIN CATCH
  DECLARE @ErrorNumber INT = ERROR_NUMBER();
  DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
  DECLARE @ErrorState INT = ERROR_STATE();
  DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
  --when a plan in cache refereces a db with role = mirror or role = secondary, dm_exec_text_query_plan will return this error:
  --Msg 954, Level 14, State 1, Procedure sp_syscollector_text_query_plan_lookpup, Line 8
  --The database "xxxx" cannot be opened. It is acting as a mirror database.
  --Msg 976, Level 14, State 1, Line 5
  --The target database, 'xxxx', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.
  IF @ErrorNumber <> 954 and @ErrorNumber <> 976
   RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
 END CATCH
END

Connect item link

Data Collection (MDW) Errors after Applying SQL 2012 SP2 CU4

Symptoms

After applying CU4, we get the following errors.

Sysssislog messages:

There was an error with ODS - Get snapshot of dm_exec_requests.Outputs[OLE DB Source Output].Columns[command] on ODS - Get snapshot of dm_exec_requests.Outputs[OLE DB Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

The "ODS - Get snapshot of dm_exec_requests.Outputs[OLE DB Source Output].Columns[command]" failed because truncation occurred, and the truncation row disposition on "ODS - Get snapshot of dm_exec_requests.Outputs[OLE DB Source Output].Columns[command]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Truncation may occur due to retrieving data from database column "command" with a length of 32 to data flow column "command" with a length of 16.

Syscollector_execution_log_internal message:

Failed to create kernel event for collection set: {2DC02BD6-E230-4C05-8516-4E8C0EF21F95}. Inner Error ------------------>
Cannot create a file when that file already exists.

Root cause

CU4 contains this fix:

"An update enables you to exclude system databases from Query Statistics collection sets when you use the management data warehouse in SQL Server"

To implement it, Microsoft added stored procedures, one of them is sp_syscollector_snapshot_dm_exec_requests_internal that returns the column "command" nvarchar(32). However, the SSIS packages are expecting the "command" field to be nvarchar(16).

Workaround

A workaround is to modify this line of code in the stored procedure to return "command" nvarchar(16)

 
CAST(ISNULL (req.command, 'AWAITING COMMAND') AS nvarchar(16)) AS command

Eventually Microsoft needs to modify the "command" length on both the stored procedure and the packages to be nvarchar(32)

Connect item link

Enabling Additional Tracing for Data Collection (MDW)

If sysssislog or syscollector_execution_log_internal_message don’t give us a clue of what is the issue, we can enable additional tracing by adding a registry key as posted here

To enable tracing in a remote computer, we can execute this PowerShell script

 
$computer = "mycomputer"
$cred = Get-Credential mydomain\myadminaccount
Enter-PSSession $computer -Credential $cred

Push-Location 
if (-not(Test-Path("HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools")))
{
    Set-Location "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server"
    New-Item -Name SQLTools
}
if (-not(Test-Path("HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools\dcexec")))
{
    Set-Location "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools"
    New-Item -Name dcexec
    New-ItemProperty -Name Components -PropertyType string -Path dcexec -Value "DCEXEC,TxDataCollector,DataCollectorController,DataCollectorTasks,Microsoft.SqlServer.Management.CollectorTasks.dll"
    New-ItemProperty -Name Tracelvl -PropertyType dword -Path dcexec -Value "4294967287"
    New-ItemProperty -Name Traceloc -PropertyType dword -Path dcexec -Value 3
    New-ItemProperty -Name Prefix -PropertyType string -Path dcexec -Value "date,time,pid,tid"
    New-ItemProperty -Name LogDir -PropertyType string -Path dcexec -Value "c:\temp\tracing"
    New-ItemProperty -Name LogFileMode -PropertyType string -Path dcexec -Value "Unique"
}
Pop-Location
Exit  

Then restart data collection using the script of a previous post

A trace file per process id will be created on c:\temp\tracing, once the error in question occurs, we can open the respective file to see the information logged. File name example:

c:\Temp\Tracing\dcexec_04_27_2015_09_22_28_PID30316_n.log

To disable tracing in a remote computer, we can execute this PowerShell script

 
$computer = "mycomputer"
$cred = Get-Credential mydomain\myadminaccount
Enter-PSSession $computer -Credential $cred

Push-Location 
if (Test-Path("HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools"))
{
    Remove-Item "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools" -Recurse 
}
Pop-Location  
Exit  

Then restart data collection using the script of a previous post

Troubleshooting Data Collection (MDW) Errors

This is documented in books online

In a nutshell, data collector SSIS package errors are logged in the table sysssislog.

 
select * from msdb..sysssislog where event = 'OnError'

And dcexec.exe errors are logged in the table syscollector_execution_log_internal. We can use the view syscollector_execution_log_full but it is a bit slow to query.

 
select * from msdb.dbo.syscollector_execution_log_internal with(nolock) where failure_message is not null

The most common SSIS errors are shown below and they may cause the dcexec.exe to hang. Thus, those errors can be resolved by stopping the data collection jobs, killing any rogue dcexec.exe processes, deleting all cache files, and restarting the jobs. These tasks were scripted in the previous post.

Connection timeout error

Sysssislog messages:

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unable to complete login process due to delay in login response".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Shared Memory Provider: Timeout error [258]. ".

Syscollector_execution_log_internal message:

Failed to create kernel event for collection set: {49268954-4FD4-4EB6-AA04-CD59D9BB5714}. Inner Error ------------------>
Cannot create a file when that file already exists.

Deadlock error

Sysssislog messages:

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Transaction (Process ID 106) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Syscollector_execution_log_internal message:

Failed to create kernel event for collection set: {2DC02BD6-E230-4C05-8516-4E8C0EF21F95}. Inner Error ------------------>
Cannot create a file when that file already exists.

Corrupt cache file error

Sysssislog messages:

The binary field is too large. The adapter attempted to read a binary field that was 134220032 bytes long, but expected a field no longer than 64 bytes at offset 57711. This usually occurs when the input file is not valid. The file contains a string length that is too large for the buffer column.
SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "RFS - Read Current Upload Data" (1) returned error code 0x80004005.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

Syscollector_execution_log_internal message:

SSIS error. Component name: DFT - Upload collection snapshot, Code: -1073450952, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "RFS - Read Current Upload Data" (1) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code

What does the error “Cannot create a file when that file already exists” mean?

If we enable additional tracing, as shown in the previous post, we can see that there was an unhandled exception pointing to the line in the source code file that does not exist in our machine. However the root cause was the previous error.

DataCollectorController!766c!4318!2015/04/27!09:49:43:: e             ERROR: SSIS Error. Code: -1071636471, Subcomponent: Connection manager "ConfigConnection", Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unable to complete login process due to delay in prelogin response".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: Timeout error [258]. ".

DataCollectorController!766c!1208!2015/04/28!05:05:20:: e          ERROR: Generic Error: retCode=0x5, function CDataCollectorController::ExecuteMasterPackage, line 1164, file e:\sql11_main_t\sql\mpu\shared\dc\runtime\controller\src\datacollectorcontroller.cpp
DataCollectorController!766c!1208!2015/04/28!05:05:20:: e          ERROR: The master package exited with error, previous error messages should explain the cause.

DataCollectorController!766c!7e18!2015/04/28!05:05:30:: e        ERROR: Win32 Error: GetLastError=183, retCode=0x4, function CDataCollectorController::CreateControlEvents, line 476, file e:\sql11_main_t\sql\mpu\shared\dc\runtime\controller\src\datacollectorcontroller.cpp
DataCollectorController!766c!7e18!2015/04/28!05:05:30:: e        ERROR: Failed to create kernel event for collection set: {49268954-4FD4-4EB6-AA04-CD59D9BB5714}. Inner Error ------------------>
Cannot create a file when that file already exists.

Restarting Data Collection (MDW)

Most data collection errors can be resolved by restarting data collection. A PowerShell script can be used to:

1. Stop data collection in all the SQL Server instances running on the machine
2. Kill dcexec.exe processes
3. Delete all cache files (our cache directory is c:\temp)
4. Start data collection in all the SQL Server instances running on the machine

 
function RestartDataCollection($SrcSrv)
{
    try
    {
        $SrcCon  = New-Object System.Data.SqlClient.SQLConnection("Data Source=$SrcSrv;Initial Catalog=master;Integrated Security=True;Connection Timeout=60") 
        $SrcCon.Open() 
                
        $SrcCmd = New-Object system.Data.SqlClient.SqlCommand("select @@version", $SrcCon)  
        $SrcCmd.CommandTimeout = 300

        $query = "
        declare @instances table( 
        value nvarchar(100)
        ,instance_name nvarchar(100)
        ,data nvarchar(100)
        );

        declare @count int;

        with a as(
        select *, ROW_NUMBER() over(partition by l.collection_set_id order by l.log_id desc) row_num
        from msdb.dbo.syscollector_execution_log_internal l with(nolock)
        )
        select @count = count(*)
        from a
        where a.row_num = 1
        and a.failure_message is not null

        --if @count > 0 

        insert into @instances
        exec xp_regread
        @rootkey = 'HKEY_LOCAL_MACHINE'
        ,@key = 'SOFTWARE\Microsoft\Microsoft SQL Server'
        ,@value_name = 'InstalledInstances'

        select 
        case when instance_name = 'MSSQLSERVER' then SERVERPROPERTY('ServerName') else instance_name end instance_name 
        ,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') computer
        from @instances
        "

        $SrcCmd.CommandText = $query
        $instances = $SrcCmd.ExecuteReader()
        $a = New-Object System.Collections.ArrayList
        $i = 0

        $query = "
        exec msdb.dbo.sp_syscollector_stop_collection_set @name = 'Server Activity'
        exec msdb.dbo.sp_syscollector_stop_collection_set @name = 'Query Statistics'
        exec msdb.dbo.sp_syscollector_stop_collection_set @name = 'Disk Usage'
        "
        
        "stop collection"

        $computer = ""

        while ($instances.Read()) 
        {
            $SrcSrv = $instances.GetValue(0) 
            $computer = $instances.GetValue(1)
            $r = $a.Add($i) 
            $a[$i] = $SrcSrv
            $i = $i + 1
            $SrcSrv
            try
            {
                Invoke-Sqlcmd -ServerInstance $SrcSrv -Query $query -QueryTimeout 300
            }
            catch
            {
                $e = $_ | select -ExpandProperty InvocationInfo
                $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
                $m                
            }
        }
        $instances.Close()

        "killing dcexec processes"

        $processes = Get-Process -ComputerName $computer -Name "DCEXEC" -ErrorAction SilentlyContinue
        
        foreach ($process in $processes)
        {
            try
            {
                taskkill /F /T /S $computer /PID $process.Id
            }
            catch
            {
                $e = $_ | select -ExpandProperty InvocationInfo
                $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
                $m                
            }
        }

        "deleting cache files"

        $files = get-childitem "\\$computer\c$\temp\*.cache" -ErrorAction SilentlyContinue

        foreach ($file in $files)
        {
            try
            {
                $file.Delete()
            }
            catch
            {
                $e = $_ | select -ExpandProperty InvocationInfo
                $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
                $m                
            }
        }

        $query = "
        exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Server Activity'
        exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Query Statistics'
        exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Disk Usage'
        "
        
        "start collection"

        foreach ($SrcSrv in $a)
        {
            $SrcSrv
            try
            {
                Invoke-Sqlcmd -ServerInstance $SrcSrv -Query $query -QueryTimeout 300
            }
            catch
            {
                $e = $_ | select -ExpandProperty InvocationInfo
                $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
                $m                
            }
        }

        $SrcCon.Close() 
        $SrcCon.Dispose() 
    }
    catch
    {
        $e = $_ | select -ExpandProperty InvocationInfo
        $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
        $m
    }
}

("Server1", "Server2", "Server3") | foreach{RestartDataCollection $_} 

Find subscriptions soon to be deactivated

By default, a transaction that has been delivered to the subscriber is deleted from the distribution database by the Distribution clean job that runs every 10 minutes. If for some reason the transaction cannot be delivered it will be deleted after the max retention of 72 hrs, the subscription will be marked inactive, and we will get this error message:

"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."

Once it is deactivated only reinitializing it will activate it back because data will be missing so it is the only way to assure consistency.

To avoid the deactivation, we should find why the transaction cannot be delivered and fix it before the max retention. However, depending on how the subscriptions are being monitored, an error may not surface until it is too late, for example if the max number of retries of the distribution agent is set to the default, replication monitor won’t show an error until 2,147,483,647 retries.

To find the subscriptions soon to be deactivated, we can use the same logic the stored procedure sp_MSsubscription_cleanup uses, but instead of using the max retention period of the distribution database, we can use a custom value on the variable @cutoff_time.

 
use distribution

declare 
@ACTIVE   tinyint
,@INACTIVE   tinyint
,@SUBSCRIBED  tinyint
,@VIRTUAL   smallint
,@SNAPSHOT_BIT  int
,@max_time   datetime
,@cutoff_time   datetime
,@max_distret   int

select 
@ACTIVE   = 2
,@INACTIVE   = 0
,@SUBSCRIBED  = 1
,@VIRTUAL   = -1
,@SNAPSHOT_BIT  = 0x80000000

-- set @cutoff_time to how old the transaction need to be in the queue to show results

select @cutoff_time = dateadd(hour, -1, getdate())
select @max_time = dateadd(hour, 1, getdate())

-- get the distribution agent with the oldest record in the queue

select derivedInfo.agent_id, derivedInfo.xact_seqno, derivedInfo.publisher_database_id
into #temp
 
-- exact code from sp_MSsubscription_cleanup
 from (
   -- Here we are retrieving the agent id, publisher database id, 
   -- min subscription sequence number, and the transaction seqno 
   -- related to the max timestamp row in the history table. this is
   -- important since the tran seqno can go back to lower values in 
   -- the case of reinit with immediate sync.
   select s.agent_id as agent_id,
    s.publisher_database_id as publisher_database_id,
    min(s.subscription_seqno) as subscription_seqno,
    isnull(h.xact_seqno, 0x0) as xact_seqno
   from MSsubscriptions s with (nolock)
    left join (MSdistribution_history h with (nolock)
      join (select agent_id, 
         max(timestamp) as timestamp
        from MSdistribution_history with (nolock)
        group by agent_id) as h2 
       on h.agent_id = h2.agent_id 
        and h.timestamp = h2.timestamp)
     on s.agent_id = h.agent_id
   where s.status = @ACTIVE                       
    and s.subscriber_id >= 0  -- Only well-known agent
   group by s.agent_id,            -- agent and pubdbid as a pair can never be differnt
    s.publisher_database_id,      
    isnull(h.xact_seqno, 0x0) -- because of join above we can include this
  ) derivedInfo
 where @cutoff_time >= (
      -- get the entry_time of the first transaction that cannot be
      -- cleaned up normally because of this agent.
      -- use history if it exists and is larger
      case when derivedInfo.xact_seqno >= derivedInfo.subscription_seqno
      then
       -- join with commands table to filter out transactions that do not have commands
       isnull((select top 1 entry_time 
          from MSrepl_transactions t with (nolock), 
            MSrepl_commands c with (nolock), 
            MSsubscriptions sss with (nolock)
          where sss.agent_id = derivedInfo.agent_id 
           and t.publisher_database_id = derivedInfo.publisher_database_id 
           and c.publisher_database_id = derivedInfo.publisher_database_id 
           and c.xact_seqno = t.xact_seqno
           -- filter out snapshot transactions not for this subscription 
           -- because they do not represent significant data changes
           and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT 
             or (c.xact_seqno >= sss.subscription_seqno 
              and c.xact_seqno <= sss.ss_cplt_seqno)) 
           -- filter out non-subscription articles for independent agents
           and c.article_id = sss.article_id 
           -- history xact_seqno can be cleaned up
           and t.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 ) 
           and c.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
          order by t.xact_seqno asc), @max_time)
      else
       isnull((select top 1 entry_time 
          from MSrepl_transactions t with (nolock), 
            MSrepl_commands c with (nolock), 
            MSsubscriptions sss with (nolock)
          where sss.agent_id = derivedInfo.agent_id 
           and t.publisher_database_id = derivedInfo.publisher_database_id 
           and c.publisher_database_id = derivedInfo.publisher_database_id
           and c.xact_seqno = t.xact_seqno
           -- filter out snapshot transactions not for this subscription 
           -- because they do not represent significant data changes
           and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT 
             or (c.xact_seqno >= sss.subscription_seqno 
              and c.xact_seqno <= sss.ss_cplt_seqno))
           -- filter out non-subscription articles for independent agents
           and c.article_id = sss.article_id
           -- sub xact_seqno cannot be cleaned up
           and t.xact_seqno >= derivedInfo.subscription_seqno
           and c.xact_seqno >= derivedInfo.subscription_seqno
          order by t.xact_seqno asc), @max_time)
      end)

-- get max distribution retention

select @max_distret = d.max_distretention
from msdb.dbo.MSdistributiondbs d with (nolock)
where d.name = db_name()

-- get subscription name of oldest record in the queue soon to be deactivated

select 
ps.name publisher
,a.publisher_db
,a.publication
,ss.name subscriber
,'Last replicated transaction time: ' 
+cast(r.entry_time as varchar(25))
+'. Hours left before deactivation : ' 
+cast(@max_distret - datediff(hour, r.entry_time, getdate()) as varchar(10)) status_message
from #temp t
join MSrepl_transactions r with (nolock)
on t.xact_seqno = r.xact_seqno
and t.publisher_database_id = r.publisher_database_id
join MSdistribution_agents a
on t.agent_id = a.id
join sys.servers ps
on a.publisher_id = ps.server_id
join sys.servers ss
on a.subscriber_id = ss.server_id

drop table #temp

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

Installing the MDW automatically

If we have hundreds of SQL Servers, a centralized MDW may not scale, see my previous post here. So we will need to install it on each SQL Server.

The following PowerShell script loops through each SQL Server name stored in a table named msmdw_central.core.instances, and for each instance does the following:

1. Create the MDW database called msmdw
2. Install the supporting objects
3. Compress the tables if Enterprise edition is used
4. Create an index to speed up purges
5. Enable data collection
6. Grant access to the reports to guest, so users can have access to it
7. Ignore some waits, from Paul Randal’s blog

Note that the T-SQL for the supporting objects is located in the SQL Server install directory e.g.

C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Install\instmdw.sql

So I just copy it to a local path C:\scripts along with the stored procedure script for rpt_query_stats, see my post here.

 
function MDWInstall($SrcSrv)
{

    $SrcCon  = New-Object System.Data.SqlClient.SQLConnection("Data Source=$SrcSrv;Initial Catalog=master;Integrated Security=True;Connection Timeout=60") 
    $SrcCon.Open() 
                
    $SrcCmd = New-Object system.Data.SqlClient.SqlCommand("select @@version", $SrcCon)  
    $SrcCmd.CommandTimeout = 300

    $query = "
    begin try
    if not exists(select name from sys.databases where name = 'msmdw')
    begin
    create database msmdw
    alter database msmdw modify file (name='msmdw', size = 5000MB, maxsize = UNLIMITED, filegrowth = 100MB)
    alter database msmdw modify file (name='msmdw_log', size = 500MB, maxsize = UNLIMITED, filegrowth = 100MB)
    alter database msmdw set recovery simple
    exec msmdw.dbo.sp_changedbowner @loginame = N'sa', @map = false
    select compatibility_level from sys.databases where name = 'master'
    end
    "

    Start-Sleep -s 1

    $r = Invoke-Sqlcmd -ServerInstance "$SrcSrv" -ConnectionTimeOut 10 -Query "$query" -AbortOnError -QueryTimeout 300

        
    if ($r.compatibility_level -eq 100)
    {
        "install mdw 2008"
        sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\instmdw2008.sql" -x > null
    }
    elseif ($r.compatibility_level -eq 110)
    {
        "install mdw 2012"
        sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\instmdw2012.sql" -x > null
        sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\rpt_query_stats.sql" > null
    }
    elseif ($r.compatibility_level -eq 120)
    {
        "install mdw 2014"
        sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\instmdw2014.sql" -x > null
        sqlcmd -S $SrcSrv -d msmdw -i "C:\scripts\rpt_query_stats.sql" > null
    }

    $query = "
        
    if cast(serverproperty('Edition') as varchar) like 'Enterprise%'
    begin

    -- enable page compression

    exec msmdw.sys.sp_MSforeachtable 'alter index all on ? rebuild with (data_compression=page)'

    -- create index to speed up purge job

    create index IDX_query_stats on msmdw.snapshots.query_stats(sql_handle) with (data_compression=page)
    
    end

    else

    -- create index to speed up purge job

    create index IDX_query_stats on msmdw.snapshots.query_stats(sql_handle)


    -- set up data collection

    exec msdb.dbo.sp_syscollector_set_warehouse_database_name @database_name = 'msmdw'
    exec msdb.dbo.sp_syscollector_set_warehouse_instance_name @instance_name = @@servername
    exec msdb.dbo.sp_syscollector_set_cache_directory @cache_directory = 'c:\temp'

    exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Disk Usage', @days_until_expiration = 2
    exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Server Activity', @days_until_expiration = 2
    exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Query Statistics', @days_until_expiration = 2

    -- start collection creates the jobs needed before enabling the collector

    exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Server Activity'
    exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Query Statistics'
    exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Disk Usage'

    exec msdb.dbo.sp_syscollector_enable_collector

    -- change purge schedule to avoid blocking with collection sets

    exec msdb.dbo.sp_update_schedule @name='mdw_purge_data_schedule', @active_start_time=20500

    -- grant access on reports to guest

    use msmdw
    grant connect to guest
    grant view definition to mdw_reader
    exec msmdw.sys.sp_addrolemember N'mdw_reader', N'guest'

    use msdb
    create role dc_report_reader authorization dbo
    grant select on dbo.syscollector_collection_sets to dc_report_reader
    grant select on dbo.syscollector_execution_log to dc_report_reader
    grant select on dbo.syscollector_config_store to dc_report_reader
    grant connect to guest
    exec msdb.sys.sp_addrolemember N'dc_report_reader', N'guest'

    -- waits to ignore

    delete msmdw.core.wait_types where wait_type IN (
        N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
        N'CHKPT',                           N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                        N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
        N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')

    "

    if ($r.compatibility_level -ge 100)
    {
        "enable data collection"
        $SrcCmd.CommandText = $query
        $r = $SrcCmd.ExecuteNonQuery()
    }

    $SrcCon.Close() 
    $SrcCon.Dispose() 
}

# execute on a list of servers

("Server1", "Server2", "Server3") | foreach{MDWInstall $_}