Delayed data retrieval | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Delayed data retrievalUser 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,
You are providing very little information.
- What version of SQL Server are on talking about?
- What statement are you using to rebuild the indexes?
- 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
- There is no such thing as a heap-index, as far as I know. A heap is a table without a clustered index.Whether it makes sense to try to rebuild such tables in your environment, I can’t tell. I guess I would rather go and find a good clustered index and maintain that rather than dealing with a heap beast. Check this:http://sqlskills.com/BLOGS/PAUL/pos…h-a-day-(2930)-fixing-heap-fragmentation.aspx
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?
I have got approval for running trace, can someone help me out what needs to be confugured to fix this issue?
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.