high batch requests/second

• PearlJammer1

  SSCrazy Eights

  Points: 8495

  More actions

  November 24, 2014 at 8:21 am

  #397683

  Hi

  Today we had a performance issue whereby the database seemed to grind to a halt - user complaining that normal actions like selecting a drop down menu was now taking 30secs - 1 minute to populate.

  The cpu levels for sql server seemed within normal boundaries but the batch requests per second where hitting 1000 , when they normally stay at around 50 - 100 even on a busy day !!

  I ran sp_whoisactive in a loop and logged the results over half an hour time period and found the following from one of our developers:

  select * from OpenRowset(TrcData,@traceid,@records) coming from Express Profiler - all the other queries captured look 'normal'

  Could this be the source of the high Batch Requests/second ?

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 24, 2014 at 8:31 am

  #1760546

  Running SQL Profiler against a production server should be a fireable offense. It puts horrible load onto a server, especially if the person hasn't been careful about filtering their trace. Whether or not it caused the high batch requests or not, it probably caused the performance problem.

  Suggestion, revoke that developer's ALTER TRACE rights (and make sure they don't have a sysadmin login) and educate them about server-side traces.

  I've crashed a production server by starting Profiler with a light-weight trace. Crashed as in the server froze and the cluster failed over.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Grant Fritchey

  SSC Guru

  Points: 399061

  More actions

  November 24, 2014 at 10:14 am

  #1760579

  And if you're on a 2008 server, consider using extended events to gather data instead of trace. It has better filtering and a reduced footprint.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning