Tags: SQL Server Internals, webinar
Thanks to everyone who attended! You can get the slides, demo and homework files here:
Webinar 2.1 — Tables, pages and rows
If you missed the session, and/or would like to watch the recorded version, you can get a link to the recording here.
Here are solutions to the homework:
1. Given a table name, schema, and database, write a query that will return the page address (file and page number) of the first data page of the table.
Here is one solution:
1 2 3 4 5 | SELECT allocated_page_file_id AS PageFID, allocated_page_page_id AS PagePID, object_id AS ObjectID, partition_id AS PartitionID, allocation_unit_type_desc AS AU_type, page_type AS PageType FROM sys.dm_db_database_page_allocations(db_id('pubs'), object_id('authors'), 1, 1, 'DETAILED') WHERE page_type = 1 AND previous_page_page_id IS NULL; |
Of course, your choice of which columns to return can mean your query is a little different. Just make sure it has the allocated_page_file_id and allocated_page_page_id. Substitute for pubs and authors the database and table name of your choosing.
Note that I am only looking at index_id =1 which is the clustered index. If the table is a heap there is no index_id=1 and you’ll get no rows back. You could try substituting 0 for the 3rd parameter, which indicates just the heap, but there is no ordering for a heap, so no concept of a ‘first’ page, and the results may be unreliable. The 4th parameter indicates I want the first partition. Each partition has its own first page, but the first page of the table is the first page of the first partition.
2. Given a table name, schema, and database, write a query that will return the number of each kind of page that belongs to the table.
1 2 3 4 | SELECT page_type AS PageType, COUNT(*) AS NUMBER FROM sys.dm_db_database_page_allocations(db_id('pubs'), object_id('authors'), NULL, NULL, 'DETAILED') WHERE page_type IS NOT NULL GROUP BY page_type; |
Note that I am filtering out NULLs because those pages do not have know page type (yet). They are part of allocated extents that belong to the table, but haven’t been used for any purpose yet.
3. What happens to the column offsets of the remaining columns when a fixed length column is dropped? What happens when a fixed length character column is increased in size? What happens when a fixed length character column is decreased in size?
I blogged about this a few years ago:
http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/alter-table-will-not-reclaim-space.aspx
But you can also just investigate for yourself with the code I gave in the last demo. This is the code that shows the column offsets:
1 2 3 4 5 6 7 8 9 | SELECT c.name AS column_name, column_id, max_inrow_length, pc.system_type_id, leaf_offset FROM sys.system_internals_partition_columns pc JOIN sys.partitions p ON p.partition_id = pc.partition_id JOIN sys.columns c ON column_id = partition_column_id AND c.object_id = p.object_id WHERE p.object_id=object_id('<table_name>'); |
I used a table named LookAtColumns in my demo.
1 2 3 4 5 6 7 | USE test; GO DROP TABLE IF EXISTS LookAtColumns; GO CREATE TABLE LookAtColumns (col1 SMALLINT, col2 VARCHAR(2000), col3 CHAR(1000), col4 CHAR(1000), col5 VARCHAR(2000)); GO |
To test dropping a column I used this command:
1 2 | ALTER TABLE LookAtColumns DROP COLUMN col3; GO |
I noticed that when I dropped col3, nothing at all happened to the other columns that were in the row after col3. They would stay at the same offset.
To test increasing the length of a fixed length column I used this command (after rebuilding the table):
1 2 | ALTER TABLE LookAtColumns ALTER COLUMN col3 CHAR(2000); GO |
This was the most interesting case and the topic of the blog post I referenced. If you increase the length, SQL Server moves the column to after all the other fixed length columns. The original space in the row is NOT reused.
I used this command to test decreasing the size of a fixed length column (after rebuilding the table):
1 2 | ALTER TABLE LookAtColumns ALTER COLUMN col3 CHAR(500); GO |
In this case, the offsets did not change. All that changed in the metadata was length of col3, but the offset of following columns stayed the same.
