Just wanted to do a quick post of a useful query for checking heap allocation:
NOTE: You can replace the dbo.TestStructure with the real table name you're using.
SELECT index_type_desc, page_count,
record_count, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure', N'U'), NULL, NULL, 'DETAILED');
EXEC dbo.sp_spaceused @objname = N'dbo.TestStructure', @updateusage = true;
I'm currently on Chapter 15 of the Querying Microsoft SQL Server 2012 Training Kit. The first lesson talks about implementing indexes. It's definitely one of the topics that I'm still trying to get a warm-fuzzy-feeling about. Clustered and Non-clustered indexes made me dust off my old data structures book from way back in college. I plan on coming back and do a separate post about it. However, here is a nice short video presentation by Voluntary DBA from Youtube that explains it pretty well:
EXAM UPDATE:
It's been a while since my last full post. I've just been busy with family and work. I also wanted to be able to cover all the chapters from the Training Kit early so that I will have some time to go back and review. My exam is scheduled for November 2nd. So I have some time. I plan on coming back and doing more posts as I go back and review the different topics.
