newbieuser (7/28/2015)
Hi friends,I created a thread 2 days back on a performance problem RE non-trusted check constraints and foreign key constraints. We are planning to make them trusted to see if it helps. On the other hand, we see about 50+ clustered/non-clustered indexes with >90% fragmentation but the page counts for all these indexes are in the range between 500-900. I'm reading in the whitepaper that index fragmentation with less than 1000 pages is not a concern. Please give your suggestions if this might cause performance issue as well....
THanks a lot for all your help
This depends upon what your query is doing. If you need to scan all (or part) of the index, then fragmentation will cause more I/O. However, if the query is doing a singleton seek (returning one row), it really won't matter.
If the index is this small, what's the problem in doing a rebuild of the index? It would be done really fast, and then the fragmentation is gone.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes