Server goes slow roughly every 24 hours

Server goes slow roughly every 24 hours

• Josh Cochran

  Few things to look at here. First of all you mention that there is IO to (presumably) a file used in one of your databases. What executable is generating this IO If it is sqlservr.exe then there is definately activity in SQL Server. If it's not sqlservr.exe then find out what it is and stop it.

  Instead of running the report execute this statement from managment studio:

  select text, * from sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by reads, logical_reads

  If you're having issues getting in with management studio run this from the command line:

  sqlcmd -S<servername> -E -A

  the -A option will connection you to an adminstrative session used for troubleshooting. You will be able to execute the command from there.

  The query will give you a list of all currently running user batches, including the full text of the batch. From there you should be able to track down the session causing the issue.

  
  
  

• Shaik Abdullah K

  It has taken a while but I have eventualy got something back from the query you suggested, other than the query its self.

  Response 1
  SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [filetypeflag] AS [SC0] FROM [dbo].[#grouped_devices____________________________________________________________________________________________________000000001108] WITH (READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL
  
  Response 2
  truncate table #intermediate_sysfiles insert into #intermediate_sysfiles select upper(substring(filename, 1, 1)) as driveletter, case when status&1000000 = 0 then 1 else 0 end as datafile, maxsize, size, growth from [model]..sysfiles truncate table #grouped_devices insert into #grouped_devices select driveletter, filetypeflag, maxsize, size, growth, 0 from #intermediate_sysfiles where maxsize <> -1 UNION select driveletter, filetypeflag, -1, sum(size), sum(growth), 0 from #intermediate_sysfiles where maxsize = -1 and growth <> 0 group by driveletter, filetypeflag UNION select driveletter, filetypeflag, -1, sum(size), 0, 0 from #intermediate_sysfiles where maxsize = 0 or (maxsize = -1 and growth = 0) group by driveletter, filetypeflag update #grouped_devices set expansion = isnull(CASE when maxsize > 0 and (convert(dec(12,0),maxsize)*8)-(convert(dec(12,0),size)*8) <= (convert(dec(12,0),d.unused_size)*1024) then (convert(dec(12,0),maxsize)*8)-(convert(dec(12,0),size)*8) when maxsize = 0 or (maxsize = -1 and growth = 0) then 0 else (convert(dec(12,0),d.unused_size)*1024) end , 0) from #disk_drives d where driveletter = upper(d.drive_letter) select case when filetypeflag = 0 then 'Log Expansion' else 'DB Expansion' end, max(expansion) from #grouped_devices group by filetypeflag

  I would guess it has something to do with statistics. Any Idea.

  Denton

  
  

• Selim

  Ok, That makes sense, sorry.

  But, the query you first sugested does not return anything other than it's self. sqlserver.exe is definatly going bolistic with drive activity durring this time. According to filemon they are 99% reads, virualy no writes.

  Got another query I could try.

  Denton

  
  

• Stefanie

  Assuming you are correct in that physical i/o is your problem, you could try something like the following changing the last_execution_time values to cover a window of time when your server exhibits the problem:

  select top 10 text, * from sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

  where last_execution_time between '2006-05-02 10:13:05.357' and '2006-05-02 10:21:00.000'

  order by last_physical_reads desc

  Once you have identified the statement text, look at the plan via the plan handle to see what is driving the excessive i/o.

  This whitepaper has some useful info : http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EDRAE

  thx, Simon.

  
  

• johnlayer

  Jerome Halmans wrote:

  Few things to look at here. First of all you mention that there is IO to (presumably) a file used in one of your databases. What executable is generating this IO If it is sqlservr.exe then there is definately activity in SQL Server. If it's not sqlservr.exe then find out what it is and stop it. Instead of running the report execute this statement from managment studio: select text, * from sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by reads, logical_reads If you're having issues getting in with management studio run this from the command line: sqlcmd -S<servername> -E -A the -A option will connection you to an adminstrative session used for troubleshooting. You will be able to execute the command from there. The query will give you a list of all currently running user batches, including the full text of the batch. From there you should be able to track down the session causing the issue.

  Thanks,

  Hopefully that will get me started. I'll see what happens in the morning.

  Denton

  
  

• Jakob R&amp;#248;jel

  I'm not sure these are going to be the problem batches.

  The first is generating auto-statistics over a temp table, which is going to be a very small impact.

  The second batch appears to be some managment tool getting a list of files from model..sysfiles. Was this during some time when you were seeing a problem on the machine If so your problem may not be inside sql server.

  
  
  
• Server goes slow roughly every 24 hours