Articles Posted by the Author:

  • WMI issue with SQL Server 2014

    WMI issue with SQL Server 2014

    Issue: I installed sql2014 enterprise edition with cu3 in following machine.  Just as a disclaimer I have been running a sql2012 edition on the same  host without any issue with WMI counters.

    capture1

     

    I could not see any wmi counters for sql2014.  I tried perfmon.exe and also querying:

    SELECT * FROM sys.dm_os_performance_counters
    SELECT * FROM sys.sysperfinfo
    

    Then I snipped through sql server error log and found this:

    Cannot query value ‘First Counter’ associated with registry key ‘HKLMSYSTEMCurrentControlSetServicesMSSQL$SQL2014Performance’. SQL Server performance counters are disabled.

    So I went to registry and added a new key with value 4090 under  HKEY_LOCAL_MACHINESYSTEMCurrentControlSetservicesMSSQL$SQL2014Performance

    capture2

    Restarted sql …


  • Deadlock Detection and Reporting part-II

    Deadlock Detection and Reporting part-II

    This is a continuation of the series about deadlock.  First part was how you detect deadlock in SQL Server and collect that information.  This part will be about reporting from the collected information.

    On the 2nd step of the job the code below will purse the xml and send .xdl file along with information in tabular format.

    2nd step on the job:

    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER ON
    IF EXISTS ( SELECT *
    FROM tempdb.sys.tables
    WHERE name LIKE '##tmpDeadlockEvents%' )
    DROP TABLE ##tmpDeadlockEvents
    CREATE TABLE ##tmpDeadlockEvents ( [deadlock-list] XML )
    DECLARE @subjectText VARCHAR(250) ,
    @bodyText NVARCHAR(MAX)
    DECLARE @x XML
    DECLARE 

  • Deadlock Detection and Reporting part-I

    Deadlock Detection and Reporting part-I

    This will be a 2 part series about deadlock. First part will be about how you detect deadlock in SQL Server and collect that information. Second part will be about reporting from the collected information.

    Deadlock is a transient event till one of the process becomes a victim. Unless you persist all the details about the deadlock and get notified it is not easy to solve a problem. This process will help you detecting deadlock as it happen, get details about the deadlock and also find the frequency. Eventually you will be able to identify if there is a trend/pattern …