In SQL Server you can make sure that the engine uses the whole extent for a single object (i.e. a table). I know how to enable the trace 1118, but that only applies to newly written data as I recall.
How can I make sure that all the data in the database is using uniform extents instead of mixed extents?
Can it be done by rebuilding the clustered indexes, and when not present creating and dropping a clustered index?
Best Answer
Plain answer is yes, if you have enabled trace flag 1118 on your instance a rebuild of those indexes will move them to full extends instead of mixed. You can get more information on this from Paul Randal's post: Are mixed pages removed by an index rebuild?
A synopsis of the comment raised by
ray herring on July 10, 2014and Paul's response:Ray asked if enabling this flag, and performing rebuilds would move all the pages in mixed extends to dedicated extends. Paul stated yes it would. However one thing I would note is his second question, on if it was worth worrying about for existing tables and indexes. Paul stated, no, since mixed pages only really make a difference in performance is in tempdb.
So you are not likely going to see a great deal of performance increase by worrying about the current pages that are mixed. Just do your normal index maintenance and at some point they will get moved.