Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I know which *file* is being hammered -- how do I find out *who* is doing the hammering?


I know which *file* is being hammered -- how do I find out *who* is doing the hammering?

Author
Message
jpSQLDude
jpSQLDude
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 939
I have a production SharePoint system with about 100 databases, it just got really slow, users are complaining a lot.

When I run a query against sys.dm_io_virtual_file_stats and sort by num_of_bytes_read, one file has massively more Reads than any other -- it shows 75 TB! (yes, I double-checked!) It is SharedServices_Search_DB.mdf, so I know exactly which file is causing the problems. The SAN guy says yes, that LUN is swamping the entire SAN with Read requests.

So how do I find out what application, or what process, or what procedure, or what service account is actually causing all these Reads?

Doing this type of performance analysis is my weakness. Sad

Are there scripts I can run that will answer the question: This one file has a huge number of Reads, and those Reads are caused by X"?

Any help much appreciated!
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1424
Hi - Not sure if this is what your looking for but have you ran sp_whoisactive ?
Would this not tell you what process/user is having this effect ?
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
By "sp_whoisactive", we most often mean Adam Machanic's stored procedure:
http://sqlblog.com/files/default.aspx - current version is 11.11.

You can also run sp_who2 and save the output, run it again, and see the difference in disk IO.

Alternately, watch what _time_ it's spiking so much, and run a trace (or Profiler, if you it doesn't hit your performance too much) during that time!
nick.mcdermaid
nick.mcdermaid
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 765
Find out which database this file is used by:

http://stackoverflow.com/questions/6061510/any-way-to-quickly-tell-which-database-if-any-is-attached-to-a-mdf-file

Now you have a database name / id to start checking against.
Richard Fryar
Richard Fryar
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 1171
If it's caused by one, or a handful, of procedures I would expect to see a large number for total_physical_reads in sys.dm_exec_query_stats.

select top 50 t.text, s.total_physical_reads
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle) t
order by s.total_physical_reads desc




Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 Visits: 9502
Someone previously provided methods to return what sessions are currently accessing the database and what execution plans consume the most read and writes.

Something else that might be useful is the dm_db_index_usage_stats management view, which returns total reads, writes, and last access date/time at the object level. The following example will sort the result by aggregating reads and writes so it's easier to identify what specific tables or indexes are most heavily hit.

use <DBNAME>;
select
db_name(s.database_id)db_name,
object_name(s.object_id)object_name,
(i.name)index_name,
(user_seeks + user_scans + user_lookups + user_updates)total_reads_writes,
*
from sys.dm_db_index_usage_stats s
left join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where db_name(s.database_id) = '<DBNAME>'
order by total_reads_writes desc;



http://msdn.microsoft.com/en-us/library/ms188755(v=sql.105).aspx


"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."
jpSQLDude
jpSQLDude
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 939
Thank you all, these are very helpful!

This is a production server, so I can't make *any* changes like installing software, not even Who is Active.

But what I can do is include such software/scripts when I write my build documents. No one actually ever reads them, so that's how I can sneak in basically whatever I want. The document is approved, I build the production server, and presto! My tools are included.

I will make another post showing what I include in my builds, and ask what others include. Could be helpful for all.....
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 Visits: 9502
jpSQLDude (5/14/2013)
Thank you all, these are very helpful!

This is a production server, so I can't make *any* changes like installing software, not even Who is Active.

But what I can do is include such software/scripts when I write my build documents. No one actually ever reads them, so that's how I can sneak in basically whatever I want. The document is approved, I build the production server, and presto! My tools are included.

I will make another post showing what I include in my builds, and ask what others include. Could be helpful for all.....

You should be able to query from the management views without installing anything. However, you either need SYSADMIN membership (which you probably don't and shouldn't have) or special VIEW SERVER STATE permission.
http://msdn.microsoft.com/en-us/library/ms188754(v=sql.90).aspx


"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."
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