March 15, 2014 at 3:36 am
Hi,
Is there any tsql-query/DMV through which I can tell my workload type on database, whether mostly Reads or mostly writes happening?
Thanks in Advance.
March 15, 2014 at 11:43 am
Keep in mind that the counters are reset with each server restart, so the results you see may be skewed/incomplete depending on how long your server's been up and the history of what's been running on it.
Check out sys.dm_db_index_usage_stats:SELECT CASE WHEN ( SUM(user_updates + user_seeks + user_scans + user_lookups) = 0 )
THEN NULL
ELSE ( CAST(SUM(user_seeks + user_scans + user_lookups) AS DECIMAL)
/ CAST(SUM(user_updates + user_seeks + user_scans
+ user_lookups) AS DECIMAL) * 100 )
END AS RatioOfReads ,
CASE WHEN ( SUM(user_updates + user_seeks + user_scans + user_lookups) = 0 )
THEN NULL
ELSE ( CAST(SUM(user_updates) AS DECIMAL)
/ CAST(SUM(user_updates + user_seeks + user_scans
+ user_lookups) AS DECIMAL) * 100)
END AS RatioOfWrites ,
SUM(user_updates + user_seeks + user_scans + user_lookups) AS TotalReadOperations ,
SUM(user_updates) AS TotalWriteOperations
FROM sys.dm_db_index_usage_stats AS ddius
WHERE DB_NAME(database_id) LIKE 'DATABASENAME'
There are also some PerfMon counters that can help you as well:
Batch Requests/sec: number of Transact-SQL command batches received per second.
Write Transactions/sec: number of transactions that wrote to the database and committed
Transactions/sec: number of transactions started for the database
From these rates you can get a pretty good estimate of read:write ratio of your requests.
Here's a link that goes all out, returning the results, grouped accordingly - it's not historical (but you could modify it to be), it would be more ideal to run to capture results when the server is typically "busy":
http://sqlasylum.wordpress.com/2010/07/22/reads-and-writes-per-db-using-dmv%E2%80%99s/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2014 at 11:49 am
Very nice explanation. Thank you very much Jessie.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply