July 21, 2011 at 3:17 am
Hi,
As per below script working fine and display total number of Proportion of reads & writes since database created per database.
Could you help me and wants detail about per day how many reads & writes in database.
SELECT OBJECT_SCHEMA_NAME(ddius.object_id) + '.' + OBJECT_NAME(ddius.object_id) AS [Object Name] ,
CASE
WHEN ( SUM(user_updates + user_seeks + user_scans + user_lookups) = 0 )
THEN NULL
ELSE CONVERT(DECIMAL(38,2), CAST(SUM(user_seeks + user_scans + user_lookups) AS DECIMAL)
/ CAST(SUM(user_updates + user_seeks + user_scans
+ user_lookups) AS DECIMAL) )
END AS [Proportion of Reads] ,
CASE
WHEN ( SUM(user_updates + user_seeks + user_scans + user_lookups) = 0 )
THEN NULL
ELSE CONVERT(DECIMAL(38,2), CAST(SUM(user_updates) AS DECIMAL)
/ CAST(SUM(user_updates + user_seeks + user_scans
+ user_lookups) AS DECIMAL) )
END AS [Proportion of Writes] ,
SUM(user_seeks + user_scans + user_lookups) AS [Total Read Operations] ,
SUM(user_updates) AS [Total Write Operations]
FROM sys.dm_db_index_usage_stats AS ddius
JOIN sys.indexes AS i ON ddius.object_id = i.object_id
AND ddius.index_id = i.index_id
WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' ) --only works in Current db
GROUP BY ddius.object_id
ORDER BY OBJECT_SCHEMA_NAME(ddius.object_id) + '.' + OBJECT_NAME(ddius.object_id)
Thanks
ananda
July 21, 2011 at 3:59 am
As far as im aware you cant break it down into a per day basis.
If you run your script each day then store the results you will be able to compare read/writes per day.
Your script doesnt include the read/write of any Non Clustered Indexes though, might be an idea adding these too!
July 21, 2011 at 4:16 am
Panders (7/21/2011)
As far as im aware you cant break it down into a per day basis.If you run your script each day then store the results you will be able to compare read/writes per day.
Your script doesnt include the read/write of any Non Clustered Indexes though, might be an idea adding these too!
Thanks for reply....
Includeded Non Clustered Indexes index this script, It was working fine.
WHERE i.type_desc IN ( 'CLUSTERED','NONCLUSTERED', 'HEAP' )
I want add date column in this script, please tell me where should include in this script?
Thanks
ananda
July 21, 2011 at 4:48 am
If you populate your historic table with:-
select cast(cast(getdate() as CHAR(11)) as datetime) as RunDate,
columlist etc
This will give you the current date. then the next time you populate your historic table you can get the difference between the 2. This will then be your read/writes per day.
July 21, 2011 at 5:18 am
I've done this in a different way.
Start a trace and log the audit logout event (nothing else). So this is a very lightweight trace.
That way you can trace reads, writes, cpus, duration. Moreoever you can have access to other fields such as application name, username, database, table, etc.
It can give you a pretty darn clear picture of what's going on, segregated by APPLICATION, users & all (which makes a huge difference in the numbers once you take out maintenance jobs, reindex. There's also a massive difference between a reporting app and oltp).
Forgot 1 important point. If you run that trace continuously for 1 month you can also group by DAY or period which also gives you a good look depending on day of week and period of the month.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply