3

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?

| improve this question | |
  • 3
    What is the actual problem you're trying to solve? – Remus Rusanu Aug 9 '15 at 19:43
  • The actual problem I'm trying to resolve is that I want to make sure that the database doesn't use mixed extends anymore. If it does the required actions after I set the trace flag than I want to be sure that I don't have any mixed extends anymore. – SQLStad Aug 9 '15 at 19:47
  • 4
    Again, what si the actual problem you're trying to solve? – Remus Rusanu Aug 9 '15 at 19:57
  • The thing I want to know if the mixed extents would be removed by and index rebuild after TF 1118. It turn out that it does. It would potentially be a performance issue but using mixed and uniform pages together but it turns out there isn't a peformance issue unless it's in tempdb. – SQLStad Aug 9 '15 at 20:05
  • 2
    Remember that extents are not contiguous and extent fragmentation will affect only readaheads to a very less degree. If you have lot of heap tables, then creating a clustered index will help address extent fragmentation. As @RemusRusanu said, you need to revisit the problem you are trying to solve ! Read this myth and stop worrying about fragmentation – Kin Shah Aug 9 '15 at 20:05
1

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, 2014 and 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.

| improve this answer | |
  • I just found the same post 30 seconds before your answer, lol. This makes sense and will make things easier for me when enabling the TF – SQLStad Aug 9 '15 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.