Blocked, Blocking and duration monitoring

• gustavo.gonzalez

  Newbie

  Points: 3

  More actions

  December 16, 2009 at 2:03 pm

  #228554

  I need to monitor a lock issue and is required to monitor blocked sentence, blocking sentence and duration as well, to realize this I try to use profiler but our hosting provider deny all remote access to the database, when I start profiler on the database server immediately general performance falls to an unacceptable level, any advice would be highly appreciated.

  Thanks & Regards,

  Gustavo

• Apurva

  SSCrazy

  Points: 2412

  More actions

  December 16, 2009 at 3:58 pm

  #1093345

  You can try to narrow down your profiler for specific database or specific table or specific stored procedure. Also I have read some time back that using t-sql for tracing will not create too much load on the server. I am not sure of this though.

  http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  December 17, 2009 at 12:38 am

  #1093428

  You can try server side tracing. Create traces that write to a file and be as specific as you can (mentioned in previous post).

  one article i can point out is http://www.sqlservercentral.com/articles/Performance+Tuning/68419/

  "Keep Trying"

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 17, 2009 at 12:40 am

  #1093429

  Don't use profiler on a busy production server. It has an overhead. A server-side trace is much lighter impact.

  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