SQL Server Performance

A deadlock was detected while trying to lock variable "User::CollectionPackageName" for read access.

Discussion in 'Getting Started' started by ericblair, Jun 8, 2009.

  1. ericblair New Member

    Hi,
    New to using Data Collectors on SQL 2008, I'm pulling my hair on this one.
    First here is the error I'm getting on the collector the second time it tries to run the T-SQL Query Collector item when I change the set's Perfmon collector item from what's in the main set to the set below it:
    A deadlock was detected while trying to lock variable "User::CollectionPackageName" for read access. A lock could not be acquired after 16 attempts and timed out.
    This is the Collector Set that works just fine, but when I replace the Performance Counters xml with the XML at the bottom of this post, then the mayhem ensues. The second time the collector is triggered, the T-SQL portion will fail with above error.
    Begin Transaction
    Begin Try
    Declare @collection_set_id_1 int
    Declare @collection_set_uid_2 uniqueidentifier
    EXEC [msdb].[dbo].[sp_syscollector_create_collection_set]
    @name=N'Disk Performance and SQL CPU 2',
    @collection_mode=1,
    @description=N'Collects logical disk performance counters and SQL Process CPU',
    @logging_level=0,
    @days_until_expiration=7,
    @schedule_name=N'CollectorSchedule_Every_5min',
    @collection_set_id=@collection_set_id_1 OUTPUT, @collection_set_uid=@collection_set_uid_2 OUTPUT
    Select @collection_set_id_1, @collection_set_uid_2

    Declare @collector_type_uid_3 uniqueidentifier
    Select @collector_type_uid_3 = collector_type_uid From [msdb].[dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';
    Declare @collection_item_id_4 int
    EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
    @name=N'Logical Disk Collection and SQL Server CPU 2',
    @parameters=N'<ns:performanceCountersCollector xmlns:ns="DataCollectorType">
    <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk Bytes/Read" Instances="*" />
    <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk Bytes/Write" Instances="*" />
    <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk sec/Read" Instances="*" />
    <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk sec/Write" Instances="*" />
    <PerformanceCounters Objects="LogicalDisk" Counters="Disk Read Bytes/sec" Instances="*" />
    <PerformanceCounters Objects="LogicalDisk" Counters="Disk Write Bytes/sec" Instances="*" />
    <PerformanceCounters Objects="Process" Counters="% Privileged Time" Instances="sqlservr" />
    <PerformanceCounters Objects="Process" Counters="% Processor Time" Instances="sqlservr" />
    </ns:performanceCountersCollector>',
    @collection_item_id=@collection_item_id_4 OUTPUT,
    @frequency=5,
    @collection_set_id=@collection_set_id_1,
    @collector_type_uid=@collector_type_uid_3
    Select @collection_item_id_4
    Declare @collector_type_uid_21 uniqueidentifier
    Select @collector_type_uid_21 = collector_type_uid
    From [msdb].[dbo].[syscollector_collector_types]
    Where name = N'Generic T-SQL Query Collector Type';
    Declare @collection_item_id_22 int

    EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
    @name=N'General Server Activity DMV Snapshots 2',
    @parameters=N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
    <Query>
    <Value>
    SET NOCOUNT ON
    SELECT
    pm.physical_memory_in_use_kb AS sql_physical_memory_in_use_kb,
    pm.memory_utilization_percentage AS sql_memory_utilization_percentage,
    sm.available_physical_memory_kb AS system_available_physical_memory_kb,
    sm.system_high_memory_signal_state AS system_high_memory_signal_state,
    sm.system_low_memory_signal_state AS system_low_memory_signal_state
    FROM sys.dm_os_process_memory AS pm
    CROSS JOIN sys.dm_os_sys_memory AS sm
    </Value>
    <OutputTable>ebi_sql_process_and_system_memory</OutputTable>
    </Query>
    </ns:TSQLQueryCollector>',
    @collection_item_id=@collection_item_id_22 OUTPUT,
    @frequency=5,
    @collection_set_id=@collection_set_id_1,
    @collector_type_uid=@collector_type_uid_21
    Select @collection_item_id_22

    Commit Transaction;

    End Try

    Begin Catch
    Rollback Transaction;
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    DECLARE @ErrorNumber INT;
    DECLARE @ErrorLine INT;
    DECLARE @ErrorProcedure NVARCHAR(200);
    SELECT @ErrorLine = ERROR_LINE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
    RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

    End Catch;

    GO

    This is the counter set to replace above that creates the error, and yes, I do have these defined in my system:
    N'<ns:performanceCountersCollector xmlns:ns="DataCollectorType">
    <PerformanceCounters Objects="Process" Counters="% Processor Time" Instances="DTExec"/>
    <PerformanceCounters Objects="Process" Counters="% Processor Time" Instances="SQLServr"/>
    <PerformanceCounters Objects="Process" Counters="Working Set" Instances="DTExec"/>
    <PerformanceCounters Objects="Process" Counters="Private Bytes" Instances="DTExec"/>
    <PerformanceCounters Objects="Memory" Counters="Page Reads/sec" />
    <PerformanceCounters Objects="Network Interface" Counters="Current Bandwidth" Instances="*"/>
    <PerformanceCounters Objects="Network Interface" Counters="Bytes Total/sec" Instances="*"/>
    <PerformanceCounters Objects="Logical Disk" Counters="Avg. Disk sec/Transfer" Instances="_Total"/>
    <PerformanceCounters Objects="Logical Disk" Counters="Disk Read Bytes/sec" Instances="_Total"/>
    <PerformanceCounters Objects="Logical Disk" Counters="Disk Write Bytes/sec" Instances="_Total"/>
    <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Total Server Memory (KB)" />
    <PerformanceCounters Objects="SQLServer:SSIS Pipeline 10.0" Counters="Buffers spooled" />
    <PerformanceCounters Objects="SQLServer:SSIS Pipeline 10.0" Counters="Rows read" />
    <PerformanceCounters Objects="SQLServer:SSIS Pipeline 10.0" Counters="Rows written" />
    </ns:performanceCountersCollector>'
    Thanks in advance!
  2. ericblair New Member

    What I ended up doing was breaking up the collector set into two seperate collectors and that fixed it. This is only a workaround though as the full collector with both items should work.
  3. satya Moderator

    I believe this have been reported on Connect site, I will chase up the post with MS.

Share This Page