Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I know which *file* is being hammered -- how do I find out *who* is doing the hammering? Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 6:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 1:07 PM
Points: 115, Visits: 902
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.

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!


Post #1451791
Posted Sunday, May 12, 2013 3:27 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:10 AM
Points: 219, Visits: 854
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 ?
Post #1451943
Posted Monday, May 13, 2013 9:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:47 AM
Points: 844, Visits: 2,329
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!
Post #1452173
Posted Monday, May 13, 2013 9:39 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:32 PM
Points: 176, Visits: 527
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.
Post #1452385
Posted Tuesday, May 14, 2013 4:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:23 AM
Points: 283, Visits: 1,110
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
Post #1452490
Posted Tuesday, May 14, 2013 8:03 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,465, Visits: 4,262
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



"Winter Is Coming" - April 6, 2014
Post #1452600
Posted Tuesday, May 14, 2013 2:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 1:07 PM
Points: 115, Visits: 902
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.....
Post #1452826
Posted Tuesday, May 14, 2013 2:44 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,465, Visits: 4,262
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



"Winter Is Coming" - April 6, 2014
Post #1452846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse