Post reply

CPU utilization going 100% while running a select query.

  • September 11, 2009 at 3:14 pm

    #95774

    Hi All,

    An issue was raised by a user saying that select process is very slow on the database.

    I checked with with the same query and observed that, the CPU utilization going 100% while running that query. The same query runs fine return results soon in 2000 environment. I checked the fragmentation in the table and its normal and ran update usage also. But same problem persist. I have compared the table with 2000 and 2005, the only difference I saw is the row count is 2000 more in SQL 2005 database. The compatibility of the DB is set to 90.

    Any one has idea on this? What could be the exact problem?

    Thanks,

    KKK

  • September 11, 2009 at 3:24 pm

    #1052583

    Have you updated the statistics on the SQL Server 2005 database?

  • September 11, 2009 at 8:42 pm

    #1052629

    kiran.kamuganti (9/11/2009)

    Hi All,

    An issue was raised by a user saying that select process is very slow on the database.

    I checked with with the same query and observed that, the CPU utilization going 100% while running that query. The same query runs fine return results soon in 2000 environment. I checked the fragmentation in the table and its normal and ran update usage also. But same problem persist. I have compared the table with 2000 and 2005, the only difference I saw is the row count is 2000 more in SQL 2005 database. The compatibility of the DB is set to 90.

    Any one has idea on this? What could be the exact problem?

    Thanks,

    KKK

    A change in row counts can drastically affect the execution plan especially if stats aren't up to date as Lynn suggested. I suggest you start with a comparison of execution plans.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 12, 2009 at 10:59 am

    #1052718

    Hi,

    Thanks much for the replies. The statistics are updated. I found that, the size of the database in 2005 is 24GB out of which the Index is of size 17GB. Will this affect the performance? Do I need to do rebuld/reorgnanize. Please post your suggestions and solutions.

    FYI: I checked the update stats is fine from the maintenance plan.

    Thanks,

    KKK

  • September 12, 2009 at 1:18 pm

    #1052731

    Just the one index is 17GB??? How many rows do you have in the two different databases for the table(s) in question for this query?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 12, 2009 at 1:53 pm

    #1052735

    Hi,

    The table has total nine Indexes. May be I have given the total indexes size. The other thing I observed from the query execution plan that the 92% cost is used on table scan of a table. I don't understand why the table scan has come when indexes are used in the table.

    I am totally confused on this. Please clarify if I am wrong anywhere and suggest what can I do next.

    ~KKK

  • September 12, 2009 at 2:29 pm

    #1052738

    If SQL Server is doing a table scan it means it doesn't think any of the indexes are useful. Please read and follow the instructions in the second article I have referenced below in my signature block regarding performance problems. We really need to see the query, DDL for the table(s), indexes defined. This article will tell just what is needed and how to post it.

  • September 17, 2009 at 7:43 pm

    #1055328

    KKK-862329 (9/12/2009)

    Hi,

    The table has total nine Indexes. May be I have given the total indexes size. The other thing I observed from the query execution plan that the 92% cost is used on table scan of a table. I don't understand why the table scan has come when indexes are used in the table.

    I am totally confused on this. Please clarify if I am wrong anywhere and suggest what can I do next.

    ~KKK

    Yeah... like Lynn suggested, we'll need some more information before we can help on that problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply