Index Bloat in Postgres: Why It Matters, How to Identify, How to Resolve
Index bloat in Postgres can cause problems, but it’s easy to miss.
I’ve written about how vacuum problems can prevent PostgreSQL from …
Read MoreIndexes: Do Included Columns Always Go on a Non-Root Page? (video)
on • 1 min read
Category:
indexing
Recently, I was thinking about nonclustered indexes in SQL Server, and how included columns are stored.
Is SQL Server smart enough to optimize the storage for small indexes with includes? Find out in this free seven minute video.
Scripts Used in this Video
Want to run the demo yourself? This script works on SQL Server 2012 and higher.
Be aware that sys.dm_db_database_page_allocations is technically an undocumented procedure, and can be time consuming when run against large indexes. That means this script is suitable for test servers only.
USE SQLIndexWorkbook;
GO
CREATE TABLE dbo.pagetest (
FirstName varchar(255),
FirstNameByBirthDateId int,
Gender Char(1)
);
GO
INSERT dbo.pagetest VALUES
('Kale', 121, 'M'),
('Kris', 13, 'M'),
('Kris', 138981, 'F'),
('Leaf', 1001, 'M');
GO
CREATE INDEX nc_pagetest
on dbo.pagetest (FirstName, FirstNameByBirthDateId)
INCLUDE (Gender);
GO
SELECT
allocated_page_page_id,
page_level
from sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('pagetest'),NULL,NULL,'detailed')
where
is_allocated=1
and index_id=2
and page_type_desc <> 'IAM_PAGE'
GO
SET NOCOUNT ON;
GO
INSERT dbo.pagetest VALUES
('Kale', 121, 'M'),
('Kris', 13, 'M'),
('Kris', 138981, 'F'),
('Leaf', 1001, 'M')
GO 100
SELECT
allocated_page_page_id,
page_level
from sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('pagetest'),NULL,NULL,'detailed')
where
is_allocated=1
and index_id=2
and page_type_desc <> 'IAM_PAGE'
GO
DROP TABLE dbo.pagetest;
GO
RELATED POSTS
