Recent Posts

  • Extended Event duration Millisecond or Microsecond?

    Extended Event duration Millisecond or Microsecond?

    There was a question in dba.stackexchange.com titled “module_end extended events duration in microseconds?” that I answered and it was Microsecond in that instance.
    Later on I questioned myself if the duration is always in Microseconds for extended events. I found, it is a mix of Millisecond, Microsecond and some are unknown meaning famous NULL. I wrote below tsql code to determine which is what.

    SELECT p.name package_name,
           o.name event_name,
           c.name event_field,
           DurationUnit= CASE
                             WHEN c.description LIKE '%milli%' 
                             THEN SUBSTRING(c.description, CHARINDEX('milli', c.description),12)
                             WHEN c.description LIKE '%micro%' 
                             THEN SUBSTRING(c.description, CHARINDEX('micro', c.description),12)
                             ELSE NULL
                         END,
           c.type_name field_type,
           c.column_type column_type
    FROM sys.dm_xe_objects o
    JOIN 

  • NULL Values in Histogram

    During my cardinality estimator presentation in DBA virtual group there was a question asked about NULL values and histogram. I am quoting the question as is “SQL Server include null values in the index. How are statistics handle for null values?”

    I will answer the question in this blog post.

    For demo purpose I will be using WideWorldImporters database which you can download and restore from here. In table [Sales].[Orders] column ‘PickingCompletedWhen’ does allow NULL values.

    How many rows are NULL and how many are NOT NULL?

    USE [WideWorldImporters];
    GO
    SELECT 'NULL' AS [Value], COUNT(*) AS [NumOfRecord]
    FROM [Sales].[Orders]
    



  • Warning: Non-existent step referenced While Creating SQL Agent Job

    Warning: Non-existent step referenced While Creating SQL Agent Job

    Today there was a question submitted in dba.stackexchange.com with heading “Suppressing non-ANSI warnings in SQLCMD script”. Details here. In summary, this happens when you use a script to create a SQL Agent job and one of the job step has conditions (under advanced properties) to go to certain steps in case of success or failure. Exact text of the warning:

    Warning: Non-existent step referenced by @on_success_step_id.
    Warning: Non-existent step referenced by @on_fail_step_id.

    I searched for the keywords “Warning: Non-existent step referenced” and looked at the top 10 results. Most of them talked about properly setting advance property of each …