February 24, 2010 at 2:27 pm
I am puzzled by the "Reserved" column in this report. What is being reserved, why is it being reserved, and do I, as a DBA, have any control over it?
I recently converted two large heap tables to a clustered Primary Key with multiple non-clustered indices. According to the values provided by the sys.dm_db_index_physical_stats DMV, the space used by the pre-existing non-clustered indices decreased nicely, but the Primary Key's space utilization skyrocketed. I presume that's simply the change from reporting the index size to reporting the size of the leaf nodes which comprise the table. The pre-supplied "Disk Usage" report shows an overall increase in unallocated space, which I was expecting.
While attempting to formulate an explanation for management, I ran the "Disk Usage by Table" report, and noticed that the "Reserved" size is up to 4 times more than the "Data" size for the tables I modified.
If there's an article or previous forum post available, a link is sufficient. Otherwise, I would greatly appreciate an explanation from someone knowledgeable in this area.
February 25, 2010 at 5:41 am
It is same as reserved column generated in output of sp_spaceused.
reserved
varchar(18)
Total amount of space allocated by objects in the database.
February 25, 2010 at 2:47 pm
Thanks for your response; knowing it's the same as the sp_spaceused proc's column in helpful.
However, you didn't address my three questions. So, perhaps we'll both learn more, if someone familiar with the 'internals' happens across this thread.
February 25, 2010 at 4:22 pm
Reserved is nothing but size of pages that have been allocated for that object.
For example, if you create a empty table as below.
CREATE TABLE [dbo].[Table1] (
[nh] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rollno] [int] NULL
) ON [PRIMARY]
GO
And check its size using sp_spaceused you will get 16KB under 'reserved' column. 8KB is for 1 page allocated for data and other 8KB is for index page. After this, table reserved size increases in proportion to number of rows in data+index pages.
MJ
February 25, 2010 at 11:04 pm
If the PK you've selected is the Clustered Index (and they are by default), and the clustered index order doesn't resemble the same order as any INSERTS that may occur, and the FILL FACTOR is set to the default of 100 on the database, then you'll experience a lot of "Page Splits" on new inserts and, perhaps, on updates. There's a whole lot of information on "Page Splits" available on the web but the basic description is that when an new row is inserted, it will be "ordered" by the clustered index. If there's no room on the page where it's supposed to be inserted, the page will split in two (creating an "out of order" page), the correct page will be "sorted" to make room for the new row, and then the new row will be inserted. Now, where you used to have one full page, you now have two pages "reserved" and they both only about half full. That will make the clustered index "sky rocket" as say it has.
The fix I use for this is to not necessarily use the clustered index as part of the Primary Key unless the PK forms a "temporal" order such as an IDENTITY column might (that's a whole 'nother discussion). Instead, I'll save the clustered index for columns in the table that indicate the usual order of inserts so that new inserts are added to the "logical" end of the table according to the clustered index.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2010 at 7:51 am
Thanks, Jeff.
One of the reorg'd tables has a gigabyte of data, but the reserved space is FOUR times the data size. I would really like to be able to modify that, or at least to understand why SQL Server thinks that much reserved space is necessary.
The tables in question are now clustered on the IDENTITY column, and mass inserts are the norm for them.
March 1, 2010 at 1:58 pm
I was just re-reading Jeff Moden's reply from last week, and realized that he had missed that the table in question had been a HEAP, not a previously clustered table, with a lot of potential page splits.
So, I'll re-ask the most fundamental of of my several questions: What is the 'Reserved' space (4x the data size) reserved *for*?
March 2, 2010 at 3:03 am
As stated above, the reserved value is space allocated to the table but not currently used.
You should read the word 'reserved' as meaning space set aside within the filegroup for the exclusive use of this table.
It does not mean anything on the lines of space that SQL Server is reserving so you cannot use it for anything.
If you have a heap, then space management if very poor compared to a table with a clustered index. You can end up with significant amounts of space reserved for the table that is difficult to release for other purposes. The normal approach is to mark one of the indexes as clustered, which will then give you better control over the space allocated for the table. Also, you may find that having a clustered index on the table improves query performance over leaving the table as a heap.
Google can find you a lot of information about why a heap can perform badly compared to a clustered table, and provide advice on choosing the appropriate design or the clustered index.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply