Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2011-02-09 : 22:43:19
|
| Hello everyone,The database is (or was) size 70 GBI added a Primary Key to a table that contains 22 million rows. Data size is 6 GB and Index space is 2 GB. Before adding the PK, the index size was approximately 1 GB.Below is my alter table statement that completed successfully, although it ran for almost an hour.What could have caused the data file in this db to grow to a size of 158 GB? And I know that immediately before altering the table the data file size was 70 GB but afterwards it is 158 GB. I didn't shrink it but it has about 4 % space available.I cannot make sense of this. I suspect that, after I run the maint plan, it may clean up the size. Has anyone experienced something like this?Thanks, Johnthe 22 million record table:ALTER TABLE [dbo].[MP_MPACWU_AC_WORKUNITS] ADD CONSTRAINT [MP_AC_WRKUNITS_PK] PRIMARY KEY CLUSTERED ( [MPACWU_PROGRAM_NO] ASC, [MPACWU_AIRCRAFT_REG_NO] ASC, [MPACWU_SEQ_NO] ASC, [MPACWU_OUINSTANCE] ASC, [MPACWU_REVISION_NO] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-09 : 23:07:52
|
A primary key is a clustered index. It stores the physical data in the order of the index. this is an expensive I/O operation causing a lot of logs. WIthout knowing the length of the columns in your primary key, I can only surmise that it is is excessive. Some of the database size is likely the log file.My guess is your 5 column key is very "wide" and as a composite key, doesn't make a ton of sense for a primary key due to that. You can estimate the size of the clustered index http://msdn.microsoft.com/en-us/library/ms178085.aspxHowever, it shouldn't be more than 100% of the size of the physical data, but if it includes every column..it will be 100% the size of the data itself.Also, when altering a 22 million row table to add a clustered index primary key, it will drop the table, recreate and re-order the table, drop the old table, and replace with the new one. what are the datatypes of the index columns? I would suspect a unique non clustered index may be better, even one with included columns...but a 5 column primary key is likely not the best choice. Better to choose an integer column as an ID primary key which is based on the uniqueness of the other 5 columns. This would take less space, and be incrementally way more efficient.158 GB from a 6 GB table...seems unlikely. My guess is a vast majority of that is in the log file which will be cleaned up when you do a backup.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-02-14 : 22:26:32
|
| Thankds dataguru1972.It was the actual data file, not the log, that had doubled in size. A full backup followed by log backups did not reduce the size.But I ran my Db Maint Plan (reindexes all tables/views) and then the data file had about 50 % free space (versus 4% before the maintenance).I still don't understand what happened to cause the data file to explode in size.John |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-02-14 : 23:56:30
|
| The answer is fairly simple and expected.When you create a clustered index SQL Server has to make a new copy of the table because the leaf pages of the index contain the data pages. Since there was no space for a new copy of the table, the database file had to grow to make room. Once the clustered index was created, the old copy of the data was deleted, and that is why you have 50% free space.Running a reindex was not necessary; it just did the same operation over again.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Adding a PK Caused Database Size to Double