SQL Server Performance

Weird Behavior

Discussion in 'Performance Tuning for DBAs' started by gortij, Sep 29, 2005.

  1. gortij New Member

    I have a table with 10 Million Rows in it. Quite heavy I/O operations take place on it.

    It has one Clustered Index on CID COlumn.
    I do a DBCC SHOWCONTIG and get a Scan Density of 16%.
    I want to recommend a DBCC DBREINDEX, but before that I wanted to show them a a poor query performance by doing a "SELECT * FROM <Table1> WHERE CID = 12345". The results came back to me in less than 0.01 Secs. I would have expected there would be some lag considering the number of rows it has and the Scan Density Number.

    Now what should be my point of argument to convince that "Hey we need to do a DBCC DBREINDEX" on that table?

  2. Adriaan New Member

    I don't think filtering on a single value is really critical here, so why not try filtering on a range like WHERE CID BETWEEN 12345 AND 45678.

    Next, create a copy of the table and copy all the data over - this should give the same result as a DBCC DBREINDEX. Now run the query for the same range on the new table, and compare the response times.
  3. mmarovic Active Member

    Is CID identity column? If so and you have bad fragmentation, you must have frequent updates on varchar significantly changing its length or text column with data in row. If that's the case you may benefit from specifying fill-factor other then 0.

    About your question, I agree with Adrian. There are other benefits, but that may be too much info for developers to consume at once. If CID is pk, it is probably used a lot in joins and they will benefit from defragmentation as much as range queries.
  4. satya Moderator

    As an interim measure you could run DBCC INDEXDEFRAG for performance.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page