SQL Server Performance

Advice on re-indexing please

Discussion in 'Performance Tuning for DBAs' started by chiangfj, Jul 4, 2005.

  1. chiangfj New Member

    The following shows the result for running the showcontig on my MS SQL 2000 database

    dbcc showcontig(Article) WITH ALL_INDEXES

    DBCC SHOWCONTIG scanning 'Article' table...
    Table: 'Article' (734625660); index ID: 1, database ID: 7
    TABLE level scan performed.
    - Pages Scanned................................: 52113
    - Extents Scanned..............................: 6530
    - Extent Switches..............................: 6531
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 99.74% [6515:6532]
    - Logical Scan Fragmentation ..................: 0.20%
    - Extent Scan Fragmentation ...................: 0.60%
    - Avg. Bytes Free per Page.....................: 69.1
    - Avg. Page Density (full).....................: 99.15%
    DBCC SHOWCONTIG scanning 'Article' table...
    Table: 'Article' (734625660); index ID: 2, database ID: 7
    LEAF level scan performed.
    - Pages Scanned................................: 7421
    - Extents Scanned..............................: 930
    - Extent Switches..............................: 929
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 99.78% [928:930]
    - Logical Scan Fragmentation ..................: 12.51%
    - Extent Scan Fragmentation ...................: 2.90%
    - Avg. Bytes Free per Page.....................: 2.8
    - Avg. Page Density (full).....................: 99.97%
    DBCC SHOWCONTIG scanning 'Article' table...
    Table: 'Article' (734625660); index ID: 3, database ID: 7
    LEAF level scan performed.
    - Pages Scanned................................: 7474
    - Extents Scanned..............................: 939
    - Extent Switches..............................: 1030
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 90.69% [935:1031]
    - Logical Scan Fragmentation ..................: 0.71%
    - Extent Scan Fragmentation ...................: 0.32%
    - Avg. Bytes Free per Page.....................: 60.2
    - Avg. Page Density (full).....................: 99.26%
    DBCC SHOWCONTIG scanning 'Article' table...
    Table: 'Article' (734625660); index ID: 4, database ID: 7
    LEAF level scan performed.
    - Pages Scanned................................: 7437
    - Extents Scanned..............................: 936
    - Extent Switches..............................: 960
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 96.77% [930:961]
    - Logical Scan Fragmentation ..................: 0.22%
    - Extent Scan Fragmentation ...................: 0.53%
    - Avg. Bytes Free per Page.....................: 20.2
    - Avg. Page Density (full).....................: 99.75%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Every time I do a select on this table, it shows duplicate record. But when I execute the DBCC DBREINDEX on this article and do the same select again, the result is ok.

    Please advice on how to solve this problem?

    Thank you
  2. dineshasanka Moderator

  3. chiangfj New Member

    SQL Server 2000, i need to check on the service pack.
  4. dineshasanka Moderator

  5. satya Moderator

    I advice you to run DBCC INDEXDEFRAG for sometime and see the results and also keep up the schedule of DBCC DBREINDEX for all the indexes that are frequently accessed.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. chiangfj New Member

    My system suppose to have records coming in anytime of the day (24 hrs standby). If I do DBCC DBREINDEX, it will lock up the table, right? Any alternative for this.
    Also, from the dbcc showcontig, is there any problem that you all see?
  7. chiangfj New Member

    I am using SQL Server 2000 SP3. Any problem?
  8. FrankKalis Moderator

  9. mmarovic Active Member

    Not sure what caused the problem but you can try:

    0. Backup db
    1. Create table table tmp_article
    2. Copy distinct rows from article to tmp_article
    3. exec sp_rename 'article', 'article_old'
    4. exec sp_rename 'tmp_article', 'article'

    Before and/or after that you can try dbcc checkTable 'article'.
  10. dineshasanka Moderator

    quote:Originally posted by chiangfj

    I am using SQL Server 2000 SP3. Any problem?

    that cannot be a problem
    did you try the satya's suggestion
  11. FrankKalis Moderator

    Hm, just another thought.
    What's your hardware look like? Are you using a SAN? If so, I would team up with you network admins and really closely examine the SAN configuration. I remember one or two questions that go into your direction (duplicates, even duplicate PRIMARY KEYs, but no DBCC error). These happened due to SAN misconfiguration.



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. chiangfj New Member

    No, I am not using SAN.
  13. satya Moderator

    DBCC INDEXDEFRAG is an online operation and will not have locks on the tables, for most effective defrag indexes DBCC DBREINDEX is a best option provided if you can get a maintenance window.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. chiangfj New Member

    The problem is that after insert/update of the records in that table and upon select it, there will be duplicate record. Actually, I am scheduling a DBCC DBREINDEX at 6am in the morning. Funny thing is that after doing a DBCC DBREINDEX, the select is ok. But still, this won't solve my problem and I cannot be doing a dbreindex on every update/insert? I really can't figure out what's causing this. Turning crazy soon and my user is pushing me for a solution.
  15. mmarovic Active Member

    Have you overlooked my post?
  16. satya Moderator

    For fuerther assessment can you capture PERFMON counters for physical disk, memory, cpu, SQL server memory etc and PROFILER to see the activity on server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  17. chiangfj New Member

    Hi mmarovic, I have not overlooked your post. But this is live data that we are talking about. I don't think I can do that. But anyway, all the records are distinct because it has a unique id as the primary key, but somehow when selecting after an update/insert, it shows duplicate record. Once reindex, it is ok.
    The schema of the table is
    id int PK
    name varchar 100
    ranking decimal(5,2)

    Initially, this table consists of id and name only with around 1 million records. Later, I use the design table to add the ranking column into it (instead of using the alter table ...).
    Not sure, whether this is the one that caused the problem.
    If yes, how to resolve without any downtime?
    Thanks.
  18. derrickleggett New Member

    During your downtime, manually drop ALL the indexes off this table, including the primary key. Make sure you have a backup before doing it. Remove the statistics. Recreate the index. Update statistics.

    Do you have auto create and update of statistics on?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  19. satya Moderator

    Alterting the table schema using GUI or TSQL is same and it is recommended to use TSQL rather than GUI tool where it will not hogup the CPU resource of server when using EM.

    As suggested by Derrick its better to drop and recreate indexes afresh on this table and see the performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  20. gurucb New Member

    How many processors are there on the server?
    Is it happening due to parallelism?
    Have you tried running the same query with Maxdop 1?

  21. ghemant Moderator

    is it also possible while creating pk if we have 'ignore duplicate record / key' option enable .
    isn't it ?

    Regards.



    hsGoswami
    ghemant@gmail.com
  22. FrankKalis Moderator

    No, AFAIK, there is no way to insert a duplicate into a PRIMARY KEY column. At least, when things go their usual way.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  23. chiangfj New Member

    Sorry to say that I am not a DBA and also we don't have a DBA.

    So as suggested by Derrick, "During your downtime, manually drop ALL the indexes off this table, including the primary key. Make sure you have a backup before doing it. Remove the statistics.ecreate the index. Update statistics."
    Err.... is this risky? Because the table includes more than 1 million records.

    "Do you have auto create and update of statistics on?"
    Yes, this is on
  24. chiangfj New Member

    Re: gurucb, I have only 1 CPU in my server, thus I don't think it is caused by parallelism.

    By the way, is there any difference between doing redbindex and "dropping and recreating index manually"?
  25. mmarovic Active Member

    My solution doesn't require downtime. exec sp_rename takes a few ms. If user tries to select from that table during that very short period he would recieve an error. That way you will have old table renamed to investigate more and new table that hopefully will not have the same problem. I applied that technique when I had similar problem on mssql server 6.5.
  26. Luis Martin Moderator

    quote:Originally posted by chiangfj

    Sorry to say that I am not a DBA and also we don't have a DBA.

    So as suggested by Derrick, "During your downtime, manually drop ALL the indexes off this table, including the primary key. Make sure you have a backup before doing it. Remove the statistics.ecreate the index. Update statistics."
    Err.... is this risky? Because the table includes more than 1 million records.

    "Do you have auto create and update of statistics on?"
    Yes, this is on

    Agree with Derrick. No risk if backup was done.
    About auto update statistics, I prefer to update using scheduled job, in a windows time.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



Share This Page