I/O Performance on Null DB

Question

I/O Performance on Null DB

DBA on Route

SSC-Addicted

Points: 432
More actions
February 1, 2018 at 6:22 am

#333190

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 ;

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

Sue_H SSC Guru Points: 90860 More actions · Answer 2

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

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

DBA on Route SSC-Addicted Points: 432 More actions · Answer 4

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