I know which *file* is being hammered -- how do I find out *who* is doing the hammering?
-
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!
-
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 ?
-
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!
-
Find out which database this file is used by:
Now you have a database name / id to start checking against.
-
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_readsfrom sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle) t
order by s.total_physical_reads desc
-
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
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
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.....
-
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
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply