What Maintenance task am I missing?

Question

What Maintenance task am I missing?

DamianC

SSCrazy Eights

Points: 8023
More actions
January 14, 2016 at 9:12 am

#399571

Hello
I have a few tables that seem to be using an excessively high amount of disk space
They also return results inefficiently
An example:
I have a table called E_DIM_POLICY
This has around 450k rows in it
It has 8 columns all varchar(255) datatypes
If I look at the datasize (sp_spaceused) it's coming in at 11.8GB!
If I copy this table to another database, the size is (a more realistic) 176MB
Question is, why is it taking up so much space?
Also, am I missing something from my standard, fairly simplistic, maintenance activities
I have 2 maintenances plans
One that that verifies DB Integrity and Backs up DAILY
One that rebuilds indexes (handles indexes and statistics) WEEKLY
Thanks
Damian.
- Damian

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What indexes does that table have?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

In this instance, there are no indexes on the table

- Damian

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Then that's your problem, not the maintenance. With no indexes at all, no clustered index, the table is a heap. Firstly index rebuilds won't touch heaps, second deletes don't deallocate empty pages unless the delete runs with a table lock. Hence you have a table that's mostly full of empty space.

I've seen an empty table (0 rows) that when read processed 600MB of pages.

At minimum put a clustered index on that, and every other table in your DB. Consider other indexes too.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DamianC SSCrazy Eights Points: 8023 More actions · Answer 4

Thanks for the advice Gail. I'll take a look at the indexes

- Damian

DamianC SSCrazy Eights Points: 8023 More actions · Answer 5

Perfect

Added a clustered index

Rebuilt stats for that table

Looked at space used and it's what I would expect

Will look at other tables

Thanks

Damian.

- Damian

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

You don't need to update statistics. A freshly built index will have up to date stats already.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass