84 GB of Unused Space 1

[Ovatvvon]

I have a client with a table in their database that is 97 GB in size: 12 is the data, 84 is Unused space.

I'm unable to shrink the DB. The db is comprised of three columns:

1) int
2) nvarchar(250)
3) float

Question: I know int and nvarchar shouldn't be a problem. Does anyone know if Float would act like a char datatype, in the sense that it will reserve a set amount of space regardless of whether that space is physically used or not?

This is by far and away the largest table in the database. The next largest database is approximately 600 MB. Crazy.

Anyone have any ideas on how I can push out that unused space?








-Ovatvvon :-Q

 

[ESquared]

You need to reindex the table. I bet the table is fragmented to heck and the fill factor is not right for the usage pattern, causing bad splits often. When you do sp_spaceused make sure to use 'true' as the second parameter.

All numeric data types take a set amount of space based on their definition.

What is the usage pattern on the table? Frequent updates? Is there a clustered index on the table? If so, do inserts occur in clustered index order or some other order? Is there any pattern to the inserts?

For example, if you have a transaction history table where the date performed is the first column of a primary key, then all inserts will be occurring at the "end" of the table, so this is good and the fill factor could be high. But if updates are made that radically change the length of varchar columns upward, then the fill factor should be lower.

One way to think about it is, whatever your interval for reindexing the table, you should target for the pages to have just barely (or almost) filled up when the reindex process occurs. That is, if the pages are only 90% full when you reindex them to a 70% fill factor, then a fill factor of 80% might be more appropriate. On the other side, however, if your pages get 99% full and have any significant fragmentation, then the fill factor needs to be lowered. This kind of fine tuning requires frequent care by a db admin.

You're trying to find a fill factor that avoids page splits but avoids wasted space, both of which hurt performance though in different ways.

Ultimately, it's all about your reindexing schedule. And if you don't have one at all, this would create the problem you've landed in.

Look into the following commands:

DBCC SHOWCONTIG
DBCC INDEXDEFRAG
DBCC DBREINDEX

And check out an article on automatic reindexing.

You need to get a reindexing schedule going ASAP!

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[olahallengren]

If you're on SQL Server 2005 you could check the index fragmentation levels with sys.dm_db_index_physical_stats. This way you only have to do something about the indexes with a high fragmentation level. You could also dynamically do a rebuild or a reorganize based on the fragmentation level. I have a stored procedure that could help you with this.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html

Ola Hallengren

http://ola.hallengren.com

 

[Ovatvvon]

This is good information everyone. I will be getting back to your shortly, as I passed up recommendations put a clustered index (instead of just a standard index) on the table, and they need to discuss with the vendor.


-Ovatvvon :-Q

 

[ESquared]

>>> "where the date performed is the first column of a primary key"

should be

"where the date performed is the first column of a clustered primary key

 

[ESquared]

P.S.

There's no clustered index on the table? This is first-grade stuff! The situations that call for no clustered index are so rare that few people have ever seen them.

 

[Ovatvvon]

I didn't design the database. I know that's first-grade stuff. This is a vendor application. Blame them! I've identified it, and they're bringing it back to the vendor. We administer it, but do not make alterations to it. Some of our apps are like this, many are not. Believe me, if I had developed the db, it would've had the clustered index.


-Ovatvvon :-Q

 

[ESquared]

Sorry, I didn't mean to sound insulting to you! I knew that it was the vendor and said what I did based on that. I deal with all sorts of vendor shenanigans, myself, making me perhaps a little too quick to get frustrated over their silliness.

We have one database that is full of char() columns instead of varchar(). I once wrote a script to calculate how much wasted space that was, and so I just ran it again now: 10497114847 extra spaces in the entire database server (all databases & tables) because of the use of char columns. Yes, that's 9.78 gigabytes, almost 10.5 billion extra spaces stored in the database. If any of them are nchar, that's 2 bytes per space.

 

[SgtJarrow]

ESquared,

I would be interested in seeing that script.....any chance you would share it with us?

I have a vendor database on one of my servers I would like to see what numbers your script would produce...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[ESquared]

Okay I'll post it in a new thread. It's breaking on one table that has like 40 char columns so I'm revamping it a little...

 

[Ovatvvon]

That's a lot of spaces.

I have you beat for unused space, with 84 GB!

Yes, I would like to see that script too. I'll look for it.

Oh, guess what the vendor came back and said...

They wanted me to use a select into to copy all the data into another [backup] table, delete the current table, recreate it, and then put the data back there. Even if it did fix the unused space issue (by putting as much of the data onto the same memory pages as possible), that doesn't fix the problem in the long run, as this would still be considered a heap, and it would start having the problem again as the table continues to grow.

I wrote back saying I wasn't going to do it, and I want a clustered index on there. (You'd think we could just do it. Funny how these situations work, when it's our servers, we purchased the app, but we're still clearing it through them, for support reasons.) grr >:-<


-Ovatvvon :-Q

 

[Ovatvvon]

btw, once I get them to put a clustered index on there, I'll reevaluate everything, and then look at the suggestions above as well. If it all works, you'll definately get stars. (I'm not stingy. )

-Ovatvvon :-Q

 

[Ovatvvon]

Soo, just to be clear on this. If data is spread over many [memory] pages on disk, so it has all this unused space (and no clustered index as mentioned above)...

will adding a clustered index fix the memory holes, and compress all the data together as the clustered index shifts around the data in physical order? Or will it simply reshift the order, but still preserving the physical layout on disk so the unused space remains?

I'm pretty sure adding the clustered index would fix the unused space problems, but I guess it is good to run it by others as a verification. I don't need to actually copy the data to a temp table, destroy the table, recreate a new one, and copy the data back to make the unused space disappear...is that correct?

When others start pushing on how things should be done, I second guess myself, but I think I'm right and shouldn't need to copy anything. But please let me know if I'm wrong.

Thanks!

-Ovatvvon :-Q

 

[ESquared]

When you add the clustered index, I believe it will effectively rebuild the table. However, the space in the database data files won't necessarily be recovered until you do a DBCC SHRINKDATABASE.

You still need to create some maintenance plans for defragging and reindexing the table. Depending on the usage pattern, it could end up getting bloated over time, as well, though probably not to the degree you're dealing with now.

 

[Ovatvvon]

I added the clustered index, and shrank the MDF file. It transfered the 84GB of unused space into unallocated space, which allowed me to shrink it now.

Overall, the database is 100 GB smaller post-maint. It went from around 300 GB down to 200 GB. AMZAING!! (And the vendor didn't want to do this!)

Thanks a bunch for your help. Things are looking a lot better now. (And I'm going to fix the reindex job I had going. Looks like somehow it was missing a Stored Proc that the job called. We do that weekly on the databases in our environments, unless a specific application requires more.

Thanks again!

-Ovatvvon :-Q

 

[olahallengren]

>Thanks a bunch for your help. Things are looking a lot better now. (And I'm going to fix the reindex job I had going.)

With a database of this size you could save a lot of resources, if you only do something about the indexes with fragmentation. You could also do a reorganize instead of a rebuild if the fragmentation is not so high. Microsoft has some recommendations on this.

http://msdn2.microsoft.com/en-us/library/ms189858.aspx

I think that my stored procedure could help you with this.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

Ola Hallengren

http://ola.hallengren.com

 

[ESquared]

Ola, I haven't looked at your code, but after reading the documentation and everything, your solution looks really super! I've saved the code to my library. (I'm not a dba at the moment, but if I need it some day I'll be glad to have it.)

There are some new subtleties in SQL 2005 of offline vs. online rebuilding, and it uses some new terms, so the MSDN article you linked is helpful to anyone used to SQL 2000 terms (like me).

Defragging and reindexing are different (reorganizing and rebuilding), and I agree that sensible maintenance uses both. It's definitely best to only do them when needed instead of on all tables all the time.

Ovatvvon, sometimes it seems people just use a prepackaged solution without understanding or real learning, but it's obvious that you have been doing research on the concepts in this thread. That is really the best reward for taking time to help you: knowing that you really learned something and added materially to your toolbox and knowledge. Thank you for that.

 

[olahallengren]

Thanks, ESquared.

Please feel free to contact me directly if you have any questions about my solution.

http://blog.ola.hallengren.com/About.html

Ola Hallengren

http://ola.hallengren.com