SQL Server Performance

Delayed data retrieval

Discussion in 'ALL SQL SERVER QUESTIONS' started by Brahma, Feb 23, 2012.

  1. Brahma New Member

    User complaints that data retrieval is being delayed by application it doesn’t have any interval, it just happens randomly once in 3 days, and that is effecting the production wroth thousands of dolors.

    More info:

    We have index rebuild job running every night followed by update stats. And I have checked index fragmentation levels on the indexes I have seen heavy fragmentation on non-clustered and heap indexes,

    Is it heap indxex are not defragged in rebuild index ? if so plz tell me an alternative ? if not why am I seeing high fragmentation ?

    WE CANT RUN PROFILER TRACE, AS IT WOULD IMPACT PRODUCTION, HOWEVER I AM IN TALK WITH APP TEAM.

    Please shed some light on this issue.

    Thanks,
    Brahma
  2. FrankKalis Moderator

    You are providing very little information.
    • What version of SQL Server are on talking about?
    • What statement are you using to rebuild the indexes?
    Couple of things worth noting:
    • When your index rebuild procedure treats all indexes equally each run, updating the stats after rebuilding the indexes doesn't make much sense. In fact, it could make things worse. When you rebuild your indexes, SQL Server does internally an equivalent operation as UPDATE STATISTICS WITH FULLSCAN anyway. So, doing an additional UPDATE STATISTICS is not needed and wastes ressources. If you however rebuild your indexes selectively, combining this with updating statistics selectively might make sense. Check this: http://www.sqlskills.com/BLOGS/PAUL...building-Indexes-and-Updating-Statistics.aspx
    Of course, running profiler has an impact on production. This can be minimized by setting up a server side trace. If you even can't run such a trace, how does your management expect you to provide adequate support?
  3. Brahma New Member

    THANKS FOR REPLY

    Regarding heap index/non-clustered , I see it has fragmentatition level value of >99% in most of tables, its very bad, so how should I deal with it ?
    I would run server side trace, but how would that help me in identifying the issue ? any light on that ? And some counters which can help me?
  4. Brahma New Member

    I have got approval for running trace, can someone help me out what needs to be confugured to fix this issue?
  5. Shehap MVP, MCTS, MCITP SQL Server

    You have 2 tiers to work with in parallel to assure performance consistency :

    1- Assure all exists clustered + non clustered indexes are rebuilt well as in other words you have no errors while running that like :

    · Errors of online Rebuild indexes containing text,next,varchar (max),nvarchar (max),image,XML since they need for offline index rebuild.

    · Deadlocks with end users transactions and they were selected as deadlocks victim.

    2- If it still persist , check all table scan or in other words Heaps where no clustered indexes are there that might end up with much RID lookups , you could do this through the attached DMVs

    3- Then , you could either :

    · Create additional identity columns and put clustered indexes there (That would boost OLTP performance )

    · Create clustered indexes on any appropriate columns already exists on relevant tables.

    Attached Files:

    Brahma likes this.

Share This Page