SQL Server Performance

How would you troubleshoot this issue?

Discussion in 'Performance Tuning for DBAs' started by Jaybee from his castle, May 8, 2006.

  1. One of our applications is sticking for 8 seconds at a particular page, I've been able to correlate that page to a few tables of similar names, containing 700,000 rows. As there's been no DBA since 2001 - oh yeah - so I immediately thought, "fragmentation".

    I've run dbcc showcontig on the 10 largest tables - of which the above were included - and then dbcc indexdefrag against all the tables in the database. To be honest, I hadn't kept the baselines, but I have reason to believe that indexdefrag wasn't effective on the table below, which is by far the worst of the 10.

    Here is the post-indexdefrag result for it - the tablename has been changed to protect the thieving swine who sold us this system:

    DBCC SHOWCONTIG scanning 'BookPayLink' table...
    Table: 'BookPayLink' (178099675); index ID: 0, database ID: 7
    TABLE level scan performed.
    - Pages Scanned................................: 1624
    - Extents Scanned..............................: 381
    - Extent Switches..............................: 380
    - Avg. Pages per Extent........................: 4.3
    - Scan Density [Best Count:Actual Count].......: 53.28% [203:381]
    - Extent Scan Fragmentation ...................: 99.48%
    - Avg. Bytes Free per Page.....................: 1385.4
    - Avg. Page Density (full).....................: 82.88%

    This table contains nearly 700,000 rows, and clearly the most suspicious reading is the scan density, with the Avg Page density coming a distant second.

    The table has 2 columns and 3 non-clustered indexes, of which one is a composite. I don't know whether this table is the sole source or even a source of the performance woes,

    The way I see it, there are a few ways forward:

    1) DBCC dbreindex - don't know if this is more effective than indexdefrag?
    2) Performance Monitor analysis;
    3) Index Tuning
    4) Add a clustered index to the table.

    Your thoughts, good people?


    Jaybee.


    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  2. FrankKalis Moderator

  3. satya Moderator

    DBCC DBREINDEX is most effective solution to reduce the fragmentation (as referred the link by Frank) and the process must be tested to add a necessary index on the tables. The database maintenance tasks must be scheduled regularly to avoid the intermittent loss of performance by the application.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. cmdr_skywalker New Member

    it can be fragmentation, locking or something else. If after you ran the DBREINDEX and it seems nothing hapens, check the perf mon. I suggest you create a baseline.

    May the Almighty God bless us all!
    www.empoweredinformation.com
  5. To my amazement, the databases previous 'caretaker' (for DIRE want of a better word, as I risk insulting my fellow DBA's and implying that whoever looked at the database ever actually took CARE of it!!) was thoughtful enough to include a test database, I just ran the dbreindex against it
    , took a total of 21 minutes at 90% of the size of the production db, so I think I can fire this off when I leave at 5.30 and have it finish before the 6pm full backup.

    I've never investigated locking before outside the 'blocked by' and 'blocking' columns in the 'Process Info' section, any 'how-to's on it?


    Jaybee

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  6. Luis Martin Moderator

    The information you show is table itself (index ID 0), no and index.
    So, the only way I know to defrag that table is to drop and create again.
    I suggest to look indexes fragmentation ( id <>0)


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


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



  7. satya Moderator

  8. ramkumar.mu New Member

    if that is a frequently inserted/Updated table, try lowering the fillfactor. something around 80%

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  9. Findings so far:

    1) Stats were auto-updated last night as part of the Maint plan
    2) Fill factors are at a default of 90%

    I tried to schedule a DB Reindex yesterday, it worked first time, and since then it hasn't fired off when it's supposed to!!! Apart from the obvious stuff like me not being able to get times/dates right, any reason for this behaviour?

    Lastly, I was going to do a manual Reindex, but that got delayed by a meeting. I'll do it tonight if worst comes to worst.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  10. ramkumar.mu New Member

    Can you tell me how frequently the table is getting inserted/updated and how many rows are inserted/updated in average...

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  11. quote:Originally posted by ramkumar.mu

    Can you tell me how frequently the table is getting inserted/updated and how many rows are inserted/updated in average...

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."

    How would I determine those?


    Jaybee.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  12. ramkumar.mu New Member

    if you have a date columns then group by those column to get the count. i know this is a bad idea. <br />any good ideas jeeves? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
  13. satya Moderator

    Or Server side trace for a period of time.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. Amulya New Member

    I too face the same problem in my environment. Few tables which does n't have any index will have the scan density even as 9%. If I create a clusterd index and drop and again see DBCC SHOWCONTIG it will be around.

    No of insertions and updations will be high .Mostly 500 - 1000 per a day.

    This behaviour is even increasing my backup time & size



    Regards,

  15. satya Moderator

    Have you tried using DBCC INDEXDEFRAG in your case to get the performance gain.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. Amulya New Member

    It does n't have any indexes ..

    Regards,
  17. satya Moderator

  18. cmdr_skywalker New Member

    Amulya, make sure you have primary key on the table. Use identity. even if it sequential, it brings a big difference when selecting specially in join with big tables.

    May the Almighty God bless us all!
    www.empoweredinformation.com
  19. ramkumar.mu New Member

    As i said earlier, you can try with some lower fill factor. you can run the dbcc reindex even if you dont have index on these tables...

    dbcc dbreindex(tablename,'',80)


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  20. mmarovic Active Member

    quote:Originally posted by ramkumar.mu

    As i said earlier, you can try with some lower fill factor. you can run the dbcc reindex even if you dont have index on these tables...

    dbcc dbreindex(tablename,'',80)


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    I guess you can run the command, but what would you accomplish running it? I mean on a table without indexes.
  21. quote:Originally posted by satya

    Or Server side trace for a period of time.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

    Hi Satya,

    The Reindex ran without errors, but was not effective, in fact the figures are slightly worse!!

    DBCC SHOWCONTIG scanning 'BookingPayLink' table...
    Table: 'BookingPayLink' (178099675); index ID: 0, database ID: 7
    TABLE level scan performed.
    - Pages Scanned................................: 1636
    - Extents Scanned..............................: 386
    - Extent Switches..............................: 385
    - Avg. Pages per Extent........................: 4.2
    - Scan Density [Best Count:Actual Count].......: 53.11% [205:386]
    - Extent Scan Fragmentation ...................: 99.48%
    - Avg. Bytes Free per Page.....................: 1420.4
    - Avg. Page Density (full).....................: 82.45%

    I haven't noticed anything odd in Perfmon, you mention a server-side trace, what would be the best way to proceed on that?

    Thanks,


    Jaybee.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  22. satya Moderator

    Links, fyi:
    http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
    http://www.dbazine.com/sql/sql-articles/larsen6
    KBAhttp://support.microsoft.com/?kbid=822853

    http://www.sql-server-performance.com/dt_dbcc_showcontig.asp - on Showcontig results.

    I believe you are aware about thishttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx link on Index defrag. best practices, still worth referring again.

    Lastly, the fragmentation occurs due to the page splits. A page split occurs when an insert or update statement is issued, yielding data from one page that exceeds the total capacity for the page. An overflow to another page is necessary to support the data storage. When the page split occurs, SQL Server leaves the first half of the data on the original page and splits the remainder of the data to another page. So check the fillfactor option set on the indexes and do not leave the default value 0 or 100. Related to index fragmentation is disk-level fragmentation, a non-contiguous file system based on file deletions and reorganization of the logical disk file system, which can yield poor performance for I/O intensive processes.

    HTH

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page