December 13, 2016 at 7:30 am
I have a database that is 73GB but in the SSMS database usage report it reports 82.44% unused.
According the the disk usage by top tables report, 1 table has 2610 records and 70GB reserved, 8GB data, 152KB indexes, and 61GB unused. (screenshot attached)
The table definition looks like this:
[slmi_smsguid] [uniqueidentifier] NOT NULL,
[slmi_MachineName] [nvarchar](64) NOT NULL,
[slmi_MachineGroup] [nvarchar](255) NOT NULL,
[slmi_SmsCreationDate] [datetime] NOT NULL,
[slmi_LastHW_ScanDate] [datetime] NULL,
[slmi_TopUser] [nvarchar](255) NULL,
[slmi_OperatingSystem] [nvarchar](128) NULL,
[slmi_Model] [nvarchar](40) NULL,
[slmi_ChassisType] [int] NULL,
[slmi_Role] [nvarchar](50) NULL,
[slmi_cores_count] [int] NULL,
[slmi_procs_count] [int] NULL,
[slmi_cpu_manufacturer] [nvarchar](255) NULL,
[slmi_cpu_name] [nvarchar](255) NULL
And it takes 2m39s to do a SELECT TOP 1 on it.
According to technet:
Unused = The amount of disk space allocated to one or more objects, but not yet used.
https://technet.microsoft.com/en-us/library/cc280506(v=sql.105).aspx
CHECKDB reports 0 errors.
What exactly is this unused space and why is this table so slow? Is there something wrong here? Can I improve something?
thanks!
December 13, 2016 at 7:38 am
Does the table have a clustered index on it? If not it is a HEAP table. Deleting records from those (without TABLOCK) will NOT deallocate empty pages. I have seen multi-gigabyte SINGLE ROW HEAP TABLES because of this.
Also, those data type choices are almost certainly suboptimal. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 13, 2016 at 7:49 am
no clustered index. it's a vendor application. would putting a clustered index on it fix this usused space issue?
thanks again.
December 13, 2016 at 9:44 am
i restored a copy of the db to our QA server, added the clustered index. unused space is GONE.
thanks!
December 13, 2016 at 9:51 am
JarJar (12/13/2016)
no clustered index. it's a vendor application. would putting a clustered index on it fix this usused space issue?thanks again.
Yep, but it would also almost certainly void your supportability. You could put it on and then immediately remove it I suppose. You may wish to check with the vendor on this and make them aware of this dreadful flaw in their application.
You can also do an ALTER TABLE REBUILD since you are post SQL 2008, which leaves it as a HEAP table.
NOTE!!!!!!!! Doing either of these changes pointers to rows so any non-clustered indexes get a double-whammy hammering to change the pointers back to the data(EDIT: just double whammy for clustered index then drop. single whammy for REBUILD). Personally I would script them out (getting ALL the options for them!!), drop them, fix the space issue, then recreate them.
ALSO NOTE!! Doing any/all of this will affect both performance and lock stuff out of access on the table for some period of time. Take that into consideration or suffer the consequences. There is also transaction log activity/size to consider given the apparent size of the actual data.
BTW, did you actually count the records in the table via something like this:
select count(*) from mytable with (nolock)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 14, 2016 at 1:17 am
TheSQLGuru (12/13/2016)
JarJar (12/13/2016)
no clustered index. it's a vendor application. would putting a clustered index on it fix this usused space issue?thanks again.
Yep, but it would also almost certainly void your supportability. You could put it on and then immediately remove it I suppose. You may wish to check with the vendor on this and make them aware of this dreadful flaw in their application.
You can also do an ALTER TABLE REBUILD since you are post SQL 2008, which leaves it as a HEAP table.
NOTE!!!!!!!! Doing either of these changes pointers to rows so any non-clustered indexes get a double-whammy hammering to change the pointers back to the data(EDIT: just double whammy for clustered index then drop. single whammy for REBUILD). Personally I would script them out (getting ALL the options for them!!), drop them, fix the space issue, then recreate them.
ALSO NOTE!! Doing any/all of this will affect both performance and lock stuff out of access on the table for some period of time. Take that into consideration or suffer the consequences. There is also transaction log activity/size to consider given the apparent size of the actual data.
BTW, did you actually count the records in the table via something like this:
select count(*) from mytable with (nolock)
thanks, kevin. that's exactly what i did last night (did it on a copy in QA first, no issues). i created a clustered index, then immediately dropped it.
the issue is being forwarded to vendor support. from talking to the app owner, when the app was first setup, a huge import was done on this table erroneously, then they deleted it all and reloaded with what they really needed (it's a type of monitoring tool). after reload, the number of records is only 2610.
weird issue but fun to learn something new. thanks again!
December 14, 2016 at 8:40 am
Easy way to get back a bunch of GBs of free space, eh??
Now, you did NOT shrink the database, right?!?! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 14, 2016 at 8:48 am
TheSQLGuru (12/14/2016)
Easy way to get back a bunch of GBs of free space, eh??Now, you did NOT shrink the database, right?!?! :hehe:
but of course i did. what would be the point of having 70GB of free space in a database with only 3GB of data? 😉 plus it's on alwayson so x3 the disk space.
i know, i know. never shrink. except when you have to. and when you do, rebuild your indexes afterwards. it's all good if you do it right.
thanks again
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply