Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Unanswered: Report hangs 4 CPU server

    Forgive my lack of knowledge on SQL here...

    We have a SQL server, version 7, that has 4 CPU's. Some of our staff have reports that must be run at random times before cutting checks etc. and these reports bog down the servers CPU's big time, enough so that we can not access the server via Enterprise Manager and at times not even through Query Analyzer. During this time of course other users can not do standard work of saving or accessing the database. Our Processor queue length never seems to go over 1 now. (We recently went from 2 to 4 CPU's do to a 1.4 average queue length).

    I have heard that is is possible to limit the number of CPU's that certain query's or events can use. What is the proper terminology for this and can a person do this with limited knowledge? If this can be done during "working hours" so that during off hours query's can use all CPU's, even better.

    Mike Gilbert

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    You probably want to think HARD before you do this, but you can set the Max Degree of Parallelism.


  3. #3
    Join Date
    Mar 2004
    Greenville, SC
    How much ram does this machine have? Is upgrading to SQL2000 an option? What are the processor speeds?

  4. #4
    Join Date
    May 2002
    This is a typical situation, where everyone looks at the DBA for the solution. My gutt feeling would be to run the SQL Profilier, capture the query and do an analysis of the Query. Before thinking about changing the SQL Server HW set up, make sure that you do the following:
    1. Test the Query running for the report
    2. Run update Stats
    3. Recompile the Query
    4. Check for the Locks

    Also how Big is your databases?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts