I/O Performance on Null DB

  • Hello Everyone,

    I'm currently investigating potential intermittent performance issues with an ERP application. I've used the below script like I have done many times in the past and somehow ended up with the top offender being NULL db.

    SELECT TOP 10 [Total Reads] = SUM(total_logical_reads),

              [Execution count] = SUM(qs.execution_count),
              DatabaseName = DB_NAME(qt.dbid)

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    GROUP BY DB_NAME(qt.dbid)

    ORDER BY [Total Reads] DESC;

    SELECT TOP 10 [Total Writes] = SUM(total_logical_writes),

               [Execution count] = SUM(qs.execution_count),
               DatabaseName = DB_NAME(qt.dbid)

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    GROUP BY DB_NAME(qt.dbid)

    ORDER BY [Total Writes] DESC;

    I know this sounds like a joke but can anyone explain where it gets the null value from??
    I tried querying  SQL Server but it doesn't come up in the list.

    SELECT name, database_id, create_date
    FROM sys.databases ;

  • DBA on Route - Thursday, February 1, 2018 6:22 AM

    Hello Everyone,

    I'm currently investigating potential intermittent performance issues with an ERP application. I've used the below script like I have done many times in the past and somehow ended up with the top offender being NULL db.

    SELECT TOP 10 [Total Reads] = SUM(total_logical_reads),

              [Execution count] = SUM(qs.execution_count),
              DatabaseName = DB_NAME(qt.dbid)

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    GROUP BY DB_NAME(qt.dbid)

    ORDER BY [Total Reads] DESC;

    SELECT TOP 10 [Total Writes] = SUM(total_logical_writes),

               [Execution count] = SUM(qs.execution_count),
               DatabaseName = DB_NAME(qt.dbid)

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    GROUP BY DB_NAME(qt.dbid)

    ORDER BY [Total Writes] DESC;

    I know this sounds like a joke but can anyone explain where it gets the null value from??
    I tried querying  SQL Server but it doesn't come up in the list.

    SELECT name, database_id, create_date
    FROM sys.databases ;

    Database id isn't always populated in sys.dm_exec_sql_text. One forum post by MS says it's only populated when it's stored procedures. And then another item on connect/voice or whatever the bug report site has it listed as being under review as a bug. So who knows which but it's not always populated. With your query , you can view the nulls with:
    SELECT qt.*
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    where dbid is null

    Sue

  • And just to add...it really should be null at times so it's more likely that it's not a bug in SQL Server but that the documentation isn't correct - maybe that's the bug.
    Anyway for ad hoc queries you can't always identify a unique database from the handle as the same statement (text) may be executed against different databases.

    Sue

  • Rather than analyzing by database, I'd suggest focusing on what sql batches or execution plans are causing the spikes. Try selecting top 10 from query stats order descending by (total_physical_reads + total_logical_writes) and then look at sql text and execution plan.

    Also, as an alternative to aggregating query stats by logical database name, you can instead leverage sys.dm_io_virtual_file_stats to get IO stats by file, which can then be reliably linked to a specific database. Knowing reads, writes, ms stalls, etc. by file, that's probably more useful in the end.


    SELECT
        DB_NAME(mf.database_id) AS database_name
        , mf.name AS logical_name
        , mf.type_desc
        , mf.state_desc
        , mf.physical_name
        , fs.file_handle AS windows_file_handle
        , fs.size_on_disk_bytes
        , fs.num_of_bytes_read
      , fs.num_of_reads
        , fs.io_stall_read_ms
        , fs.num_of_bytes_written
      , fs.num_of_writes
        , fs.io_stall_write_ms
    FROM sys.master_files AS mf
    CROSS APPLY sys.dm_io_virtual_file_stats( mf.database_id, mf.file_id ) AS fs;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Cheers Eric for the new script.

    In the end I queried the ring buffers and found that SQL Server had more RAM assigned to it than it currently needed, and the OS was suffering. (last year it was the other way around).

    Amended the Max memory so that the OS  wasn't starved and everything seems to be performing better.....at least for now.

    Below is an example of the blocking issues I was getting

    And this is after I adjusted the memory allocation

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply