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

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Know read and write ratio before you tune database performance

By Michael Zhang,

SELECT object_name(s.object_id) as usertable,
       SUM(user_seeks + user_scans + user_lookups) as reads, 
  SUM(user_updates) as writes, 
  SUM(user_seeks + user_scans + user_lookups+user_updates)  as totalIO,
CASE 
WHEN SUM(user_seeks + user_scans + user_lookups+user_updates) >0
then round(SUM(user_seeks + user_scans + user_lookups)*100/SUM(user_seeks + user_scans + user_lookups+user_updates),0)
else 0
END
AS readratio,
CASE 
WHEN SUM(user_seeks + user_scans + user_lookups+user_updates) >0
then SUM(user_updates)*100/SUM(user_seeks + user_scans + user_lookups+user_updates)
else 0
END
AS writeratio
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
--AND s.database_id = @dbid
--GROUP BY object_name(s.object_id)
GROUP BY s.object_id
--order by totalIO desc,readratio desc,reads desc,writeratio desc,writes desc
order by totalIO DESC, writes desc,writeratio desc

Total article views: 1619 | Views in the last 30 days: 4
 
Related Articles
FORUM

How to update the lookup rows into the table

How to update the lookup rows into the table

FORUM

dynamic lookup

update and lookup at the same time

FORUM

Lookup Parametrization

Lookup

FORUM

Lookup Help

I can't seem to get lookups to work in SSIS

FORUM

Lookup Transformation

Help needed for Lookup Transformation

Tags
 
Contribute