SQL Server Performance

Lots of blocker processes are making applicaiton slow

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by DaddyYankee, May 30, 2009.

  1. DaddyYankee New Member

    I have one reporting application which was running smoothly for last 3 years without any problem.For last 3 days I observed lots of blocker
    processes which brought down the performance
    Every maintainence activity(like re indexing,update statistics,reorganizing,shrinking etc) is working fine.
    There is no changes done on db server,no schema change,no new table created, sp added,deleted etc.
    Other specification of the enviorment are:
    Size of mdf file is 300GB on a SAN box.
    Free space available is 2TB
    RAID is 5.
    RAM is 32 Gigs.
    OS version is win 2003 64bit

    SQL 2005 65bit
    Lots of blocker process exists on this db and this is the reason for the slow performance of application.I cant keep killing process every second.
    neither this help.I restarted the db sever and app server,but no help.
    I am wondering what is the root cause of the problem and how to fix this.I cant blame the application developers who wrote sp's as these sp are running for last 4 yrs and are tuned.
    I cant change the default isolation level as per business requirement
  2. madhuottapalam New Member

    I think you need to start trobleshooting by configuring 'blocked process threshold' which triggers an event when a process is blocked for a more than a specified time. Read about this feature more in BOL. This will give you a starting point.
    exec sp_configure 'show advanced options', 1;
    reconfigure;
    go
    exec sp_configure 'blocked process threshold', 30;
    reconfigure;
    There are many resource available in internet .
    Check these
    Troubleshooting Performance Problems in SQL Server 2005
    http://blogs.technet.com/rob/archive/2008/05/26/detecting-sql-server-2005-blocking.aspx
    Madhu
  3. satya Moderator

    Follow what Madhu is suggested and on top of that I would like to know the service pack level on SQL Server and Reporting services (which is being used for this report process).
    Further see if there are any recent changes to the environment such as hotfix, firmware upgrade to hardware or service pack release.

Share This Page