Where's My Additional Free Space ?

Question

Where's My Additional Free Space ?

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

homebrew01 (12/15/2009)
GilaMonster (12/15/2009)
That's a problem. The heap can't be rebuilt, except by putting a cluster on it, and if there are pages partially used in the heap they'll stay partially used until more data is added.
So I rebuild the non-clustered indexes and hope for a small improvement ?

Probably won't be worth much if at all. Only the NC indexes will reorg. You really need to pick a good clustered index for the table. Note that a clustered index does not have to be the PK nor does it need to be unique. Of course, the higher it's cardinality, the more useful it will be for other things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

This is a 3rd party application and it's always seemed odd that they don't have any clustered indexes.

Is there a downside or risk to me creating a clustered index ?

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 3

No risks involved. Just that creating a cluster on a huge table can be quite a expensive(CPU/IO). Enusre you have enough space on the disk.

Creating a cluster on the table one of the recomended practices.

Regards,
Raj

http://Strictlysql.blogspot.com

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

No risk, just watch what you pick as the clustering key. A bad choice and everything might slow down a lot.

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

EdVassie SSC Guru Points: 60472 More actions · Answer 5

Not all database systems support the concept of a cluster index. Therefore if you have a 3-party app that was written to work on a number of database systems, it may not have any cluster indexes defined.

Changing one of the existing indexes to clustered may (or may not) cause maintenance problems in the future if you apply a service pack from your app supplier. IMHO it would be best to work out what the ideal cluster index should be and create it as a new index, even if it has the same structure as an existing index. This may be a 'waste' of disk space, but you will almost always get a performance improvement if the table has a well-designed cluster index, plus you can say you have not changed any vendor-supplied code.

In designing your cluster index, remember it does not have to be the PK or unique and can contain multiple columns, but it will be most efficient if it uses a single unique integer (or bigint, etc) column.

(PS, if you app came from an Oracle background, the term 'cluster index' means something completely different in Oracle to SQL Server, so be aware of that when you read the documentation.)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Agreed... I forget what they call the equivalent of a Clustered Index in Oracle, but a lot of developers don't know about it and there's usually no need for it because the Oracle engine handles things quite differently. For example, you can tell the Oracle engine to run as cost based or rule based for any given query (IIRC).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

EdVassie (12/18/2009)
Changing one of the existing indexes to clustered may (or may not) cause maintenance problems in the future if you apply a service pack from your app supplier. IMHO it would be best to work out what the ideal cluster index should be and create it as a new index, even if it has the same structure as an existing index. This may be a 'waste' of disk space, but you will almost always get a performance improvement if the table has a well-designed cluster index, plus you can say you have not changed any vendor-supplied code.
In designing your cluster index, remember it does not have to be the PK or unique and can contain multiple columns, but it will be most efficient if it uses a single unique integer (or bigint, etc) column.

FWIW. The particular table I'm concerned with has 130,000,000 rows, and the primary key is on a "uniqueidentifier" column. It has lots of inserts, virtually no updates, and lots of selects using non-clustered indexes such as on Customer_id. There is not a simple incrementing "int" field that could be used for a clustered index. I'm trying to free up space, so would adding a clustered index set me back, since I'm not having performance issues ?

EdVassie SSC Guru Points: 60472 More actions · Answer 8

The cluster index will help you manage free space far batter than you can at present, and having a CI may also improve your performance.

There is no way an outsider can say if you will definitely benefit from any change of this nature. You have to decide if you want to try it out yourself.

If you do want to try this, then it is best to do it first on a test system, so you can see the impact it has. You should look at space used befoer and after, the time required to do the initial index create, and any performance impact after you have completed the index build.

When you crete a cluster index, the whole table and indexes are rebuilt, so to do this on a large table will take time. On a production system you should schedule this during a quiet time, or your performance may suffer while the index is built.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

homebrew01 (12/18/2009)
EdVassie (12/18/2009)
Changing one of the existing indexes to clustered may (or may not) cause maintenance problems in the future if you apply a service pack from your app supplier. IMHO it would be best to work out what the ideal cluster index should be and create it as a new index, even if it has the same structure as an existing index. This may be a 'waste' of disk space, but you will almost always get a performance improvement if the table has a well-designed cluster index, plus you can say you have not changed any vendor-supplied code.
In designing your cluster index, remember it does not have to be the PK or unique and can contain multiple columns, but it will be most efficient if it uses a single unique integer (or bigint, etc) column.
FWIW. The particular table I'm concerned with has 130,000,000 rows, and the primary key is on a "uniqueidentifier" column. It has lots of inserts, virtually no updates, and lots of selects using non-clustered indexes such as on Customer_id. There is not a simple incrementing "int" field that could be used for a clustered index. I'm trying to free up space, so would adding a clustered index set me back, since I'm not having performance issues ?

Whatever you go, do [font="Arial Black"]NOT[/font] put the clustered index on the "uniqueidentifier" column especially since this table suffers a lot of inserts. In the absence of an incrementing column, pick a column (or columns) that are temporal in nature insofar as the order of insertion goes. That also means that you probably shouldn't include Customer_ID as a CI candidate unless the Customer_ID column is very unique and comes from an IDENTITY column or other incrementing column type.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 10

Thanks for all the tips.

I just came across this on SQL-Performance, so I don't see a clustered Index in my future since the table is 1+ Terabytes:

"...Also keep in mind that to recreate a new clustered index, you will need free disk space equivalent to 1.2 times the size of the table you are working with. This space is necessary to recreate the entire table while maintaining the old table until the new table is recreated...."