SQL Server Performance

RESOURCE_SEMAPHORE

Discussion in 'Performance Tuning for DBAs' started by itbhushan, Sep 24, 2003.

  1. itbhushan New Member

    Mnay of the application users are getting the error "A time out occured while waiting for memory resources to execute the query. Rerun the query". Also when iam viewing the server performance by firing the query "select * from sysprocesses" i can see the following description "RESOURCE_SEMAPHORE" under last lastwaittype column.

    Can any one tell me the reason for the problem.

    Thanks

    Bhushan
  2. satya Moderator

    What are timeout settings for query and login?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. itbhushan New Member

    (1) remote login timeout (s) 0(MIN) 2147483647(MAX) 5(CONFIG_VALUE)5(RUN_VALUE)
    (2) remote query timeout (s) 0(MIN) 2147483647(MAX) 0(CONFIG_VALUE)0(RUN_VALUE)

    are the settings of the server. Also the server details are mentioned below,

    Processor : Intel Xeon 2.40 Ghz 512 Kb Processor option kit
    Memory : 3.8 gb
    OS : Windows 2000
    SQL Server : SQL Server 7.0 standard edition


    Bhushan

  4. satya Moderator

    Thats handful information, have you monitored thru PROFILER for this slow activity.
    How about locking hints while using the query?

    ... and forgot to mention about using PERFMON for H/w & SQL counters.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. gaurav_bindlish New Member

    Check if the system is running low on worker threads. Are there too many usres working on the system? This can be verified by increasing the maximum number of worker threads using sp_dboption.

    How do the perfmon counters look like? Is the system running to full capacity?

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

    Not possible to run the profiler due to overhead, also this kind of problem occurs once or twice in a month. Average total number of users working are around 30, out of which around 10 users were concurrently having some transactions, through the sysprocesses query i watched out for the locking problem & surprisingly there were none.

    Obviously during these transactions the CPU utilisation was 100 %.

    Also i have made some investigation & found that Microsoft has also accepted that there has been a bug relating to the said problem with sql server 7.0.

    http://support.microsoft.com/defaul...port/kb/articles/Q230/0/36.asp&NoWebContent=1

    Bhushan
  7. satya Moderator

    Are the queries involved in FULLTEXT search catalogs?
    And try to follow the workaround defined for 'remote query timeout'.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. gaurav_bindlish New Member

    I would say I see a chance of processor bottleneck. Do you see 100% CPU utilization for periods over 10 minutes. IF yes, I would recommend you to think about upgrading your CPU. Wait a minute before we go to hardware upgrade....

    IS this is single CPU machine? What are the Service packs for OS and SQL Server?

    What are the queries that are being run on the server? Have you analyzed the queries' execution plan?

    If there are too many hash joins in the query, there may be a chance that CPU maxes out.

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

    How about memory settings for SQL Server?
    Any parallel applications running?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  10. itbhushan New Member

    The processor is a very high powered processor(as mentioned above) compared to the number of users using the database, also as i said it happens occasionally say 2 or 3 times a month, so i do not think it could be a processor bottleneck.
    The CPU is a dual processor, OS is Windows 2000 advanced server with service pack3 & the SQL version is SQL Server 7.0 with service pack SP3, regarding the queries i will have to run a profiler trace to look for queries which would not be possible currently, but since i have said the same queries r running almost daily, but these problems occur occasionally.
    Memory settings have been kept dynamic & there are no other applications running apart from the MSSQL database.

    Bhushan
  11. satya Moderator

    May be this is not related, but still you will gain after you upgrade service pack level to 4.
    Is the FULLTEXT queries involved in this performance issue?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  12. Scooter New Member

    Howdy - I noticed this topic and was very interested in whether there had been more discussion. My project recently started seeing this same issue on our copy of SQL Server (2000 - Enterprise Edition) - things will be running fine, and then we suddenly see a number of RESOURCE_SEMAPHORS (0x0040) and the SQL box pegs CPU and Memory at 100%, so high we can't do diagnostics (although I've managed to catch some of the info. with the MS PSS Diagnostic procedure, which is how we can see what's up). It happened to us about a month ago, twice within a week, and then again a month later - it doesn't seem related to our load, as it's happened both during peak hours and in the morning when there are almost no users on the system. We're at a little of a loss - there's not much on the web about the issue.

    - Scott McVay
  13. satya Moderator

    As prescribed has any of the FULLTEXT process is involed?
    Keep an eye on SQL error log & event viewer for information.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page