SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I/O Performance on Null DB


I/O Performance on Null DB

Author
Message
DBA on Route
DBA on Route
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 31
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 ;

Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35681 Visits: 10211
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
Sue_H
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35681 Visits: 10211


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
Eric M Russell
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65986 Visits: 13024
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;



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
DBA on Route
DBA on Route
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 31
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search