Forum,
I have a question regarding compression .. what is the difference between table and index compression (I have googled but it returns we have both table and index )do we need to have both or just index compression . I have heap tables which im compressing with adding a Clustered index with the following script .
--Index compression
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF,DATA_COMPRESSION = PAGE)
--table compression
ALTER TABLE [dbo].[B_sometable_huge] REBUILD WITH (DATA_COMPRESSION=PAGE);
Do i have to do both which would go first and why ? please shed some light on this.
Most of the tables are fragmented nearly 100% and once rebuilding indexes is done will it eliminate fragmentation or do we need to run defrag when does this have to be run . how do we eliminate fragmentation if im doing the incorrect way.
-- the scanning mode for index statistics
--available values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED'
Best practice to use for mode for index statistics -- Detailed /Sampled (I used Detailed to be through). I'm aware of the Ola maintenance scripts but our policy does not allow to use them so have to use custom scripts ..
thanks in advance
There is a lot in this question.
First, a "heap" with a "clustered index" is no longer a heap. A heap is an unordered set of data. A clustered index orders the data.
Next, if your clustered index has data compression turned on, then the table does too as they are the same thing on disk. NON-clustered indexes though would not benefit from table data compression. See: https://dba.stackexchange.com/questions/49757/clustered-index-compression-vs-table-compression-are-they-the-same-thing
This page has a good reference on fragmentation:
https://www.mssqltips.com/sqlservertip/2263/sql-server-fragmentation-what-causes-it-part-3-of-9/
And for the last question about the scanning mode for index statistics, this is just for reporting purposes. I am assuming this is when you run something like:
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED')
Microsoft is going to be your best bet for details on that: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15
But basically, it just changes how hard it looks at the indexes to ensure data accuracy. Limited doesn't look at the leaf level, sampled looks at the leaf level but estimates the compression, detailed looks at everything to get you accurate information. Limited will be the fastest, sampled will be slower, detailed will be the slowest. To quote the article:
With LIMITED mode, compressed_page_count is NULL because the Database Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for compressed_page_count. The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Results in SAMPLED mode should be regarded as approximate. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.
Does the above answer your questions?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 15, 2020 at 3:46 pm
@Brain -- Thanks for clarifying ..appreciate all your help !!
September 15, 2020 at 5:38 pm
To clarify, you can compress non-clus indexes, and should often do so, but you must compress each one separately.
Also, 80% is a very low number overall, are you sure you want to leave 20% of the table empty?
Once you've rebuilt an index, it's done. Definitely no need to do a REORG after a REBUID.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply