SQL Server Performance

Locking problems

Discussion in 'SQL Server Clustering' started by jbrochu, Apr 20, 2004.

  1. jbrochu New Member

    Hi. I am having some locking problems on my cluster server. The database is accessed by both inside employees using customized software as well as web users who run queries, enter new info, etc. etc.

    I have purchased Net IQ's SQL Diag manager, but the software has a limitation. It does not show you the actual query that caused the lock, it just informs you as to the user and basic stuff like that. Well all web users come across as " sa " so we have no idea what user or what queries are causing the file locks. We are confident that it is the website causing the locks.

    I am a network administrator, not a SQL DBA or a programmer, so I do not know all the ins and outs, but I am pretty knowledgeable.

    Does anyone know of a way I can track what queries are causing my locking problems? I would greatly appreciate any help.

    Thanks.

    My config:

    I have an Active/Passive cluster server environment, purchased from Dell, about $70000. I will try and give you detailed hardware:

    Hardware---
    Dual Xeon 2.8Ghz Processors
    4GB DDR-SYNCHRONOUS Memory
    PERC QC RAID Controller running RAID 1, for local disks
    PERC QC attached to the Dell Powervault Storage System, RAID 10

    Software---
    Windows 2000 Advanced Server
    Microsoft SQL Server 2000 - 8.00.760 Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
    Microsoft Cluster Service

    Database details---
    24GB in size ( mdf )
    2400 sql transactions per minute, peak. Reported by NetIQ's Monitoring program.
  2. gaurav_bindlish New Member

    This is an article for SQL Server 7.0 but I think it'll help you....

    http://support.microsoft.com/default.aspx?scid=kb;en-us;289276

    Then there is this webcast.....
    http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc011502/wcblurb011502.asp

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. jbrochu New Member

  4. Luis Martin Moderator

    If you know when it happens, you can use who2 in Query Analyzer to see who is locking.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  5. satya Moderator

    How about database maintenance checks and REINDEXING on the tables involved.
    Also PROFILER is handy tool to know the queries performance cause.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page