Sql-server – SQL Server – Extremely low avg_page_space_used_in_percent for heap table

When you rebuild the index set MAXDOP(1). This will only use 1 CPU and although it will take longer you will have much lower fragmentation.

I suspect that currently, where the index is "fairly small" the operation is being over parellelsed. Each thread used will cause additional fragmentation, as each thread orders its own pages only, and for smaller indexes this can be more noticeable.

SQL Server 2012 has vastly changed the way it controls and manages memory. Before 2012, max server memory controlled only 8K pages, and now it controls much more than that.

Here are some good articles about this from the SQLOS team:

Article 1 | Article 2 | Article 3 | Article 4

With these changes, what you see in working set, max server memory, etc. will almost certainly be different between versions. The key is to focus on what "normal" is for these figures and see if they deviate vastly when there is a specific performance issue. I would not just look at a number when the system is working perfectly fine and panic and assume something is wrong (this happens a lot with magic numbers like 300 for page life expectancy - maybe PLE on your system is always 120).

A few queries that might help ballpark and set some baselines:

SELECT TOP (10) page_type, 
  numpages = COUNT(*), 
  size_kb = 8*COUNT(*) 
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY page_type
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_process_memory;

SELECT TOP (10) [type], 
  size_kb = SUM(pages_kb) 
FROM sys.dm_os_memory_clerks 
GROUP BY [type]
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%Memory Broker%'
   OR object_name LIKE '%Buffer Manager%'
   OR object_name LIKE '%Memory Manager%'
   OR object_name LIKE '%Memory Node%';