SQL Server Performance

Urgent: Sql Performance.

Discussion in 'Performance Tuning for DBAs' started by Roswell, Jun 22, 2007.

  1. Roswell New Member

    Dear All,

    I've been running into the strange problem since a month. Sometimes, an stored procedure seems to fail. It is not specific to only one SP though, sometimes a view that is fetching data seems to run longer than normal.

    One big problem I see is that, there are four tables that contain huge data including image binary data. and When I see the index relationship it was like there are two or three indexes were there and on primary key index, it was set on unique, not as clustered index. However there was other indexes on tables that contains the rest of the columns excluding the primary column and they were set on "Create cluster" . I unselected this and things work fine for few days then again, things seem to having problems again and CPU gets high when running on simple SP. However that happens with random SP or views.

    My database size is 22 GB and log file and mdf file are on the same partition along with other databases.
    SQl 2000 with SP4
    Windows 2003 Server
  2. Roswell New Member

    Although i'm working on archiving process and already archived 3 GB of data.
  3. Adriaan New Member

    The clustered index is "repeated" on the nonclustered indexes, eventhough it's not presented as such in the index definition.

    The PK doesn't have to be clustered. The ideal candidate for a clustered index is an identity column, because new values never have to be inserted between existing values.

    The official line about clustered indexes is that you should use them for columns on which you often do range searches, but I think that the true benefit is in maximum efficiency for the nonclustered indexes.
  4. Roswell New Member

    so Should I put the PK off from "as clustered" ?
  5. Adriaan New Member

    What kind of data do you have in the PK column?
  6. Roswell New Member

    Only Ids for that table
  7. Adriaan New Member

    Is it defined as an identity column? Then make it clustered.
  8. Roswell New Member

    yes identity column.
  9. Roswell New Member

    Getting random error. General Network error. Check your documentation. Sometimes works sometimes doesnt.
  10. Adriaan New Member

    Might be a hardware issue.
  11. Roswell New Member

    Named pipes issue?
  12. Adriaan New Member

    Are you doing this from a remote computer?
  13. Roswell New Member

Share This Page