July 1, 2021 at 4:56 pm
Hello,
We have are trying to reduce the size of our database. I know shrinking is generally bad but please ignore that fact for now we have our reasons. There are several tables that use TEXT data types and a bunch more that use VARCHAR(MAX) which in many cases caused the data to spill and be stored in OFF-ROW-DATA allocations. Everything is in the Primary filegroup. I believe this off-row and lob data (text) is what makes a shrink take 30 minutes per MB (roughly). Usually it causes blocking so we have never been able to keep it running.
My plan is to move the LOB data by recreating the table in a new file group and then copying over all the data to that file group. My questions are two.
Is OFF-ROW storage and Lob_Data storage the same thing? I ask this question because when I move the tables I would like to know if I should just move tables with TEXT data type or also move VARCHAR(MAX) tables that have been identified as having off-row storage allocations?
My other question is about the indexes. Should also move the indexes for said tables or can I recreate them in the original file group or does this not matter in terms of improving the amount of time it takes to shrink?
Lastly I know TEXT, NTEXT types have been replaced. And as part of the moving tables to a new file group, I would likely recreate these tables as VARCHAR(MAX).
If anyone has experience with a good strategy please share. When I say off-row and lob-data, I attached the script that I am using to identify a starting point (aka what tables biggies)
July 1, 2021 at 4:58 pm
Did not let me attach so here is the code...
SELECT DISTINCT
OBJECT_NAME(p.object_id),
au.type_desc,
au.filegroup_id,
CAST(ROUND((SUM(au.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(au.total_pages) - SUM(au.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(au.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB,
p.rows
FROM sys.system_internals_allocation_units au
JOIN sys.partitions p ON au.container_id = p.partition_id
WHERE type_desc <> 'IN_ROW_DATA' AND total_pages > 8
AND p.rows > 0
GROUP BY p.object_id,au.type_desc,au.filegroup_id,p.rows
July 1, 2021 at 5:37 pm
(1) No, they are not the same. SQL will move non-LOB data to out of row storage if otherwise a row cannot fit onto its 8K page. For example, if you have three varchar(6000) columns and fill them all, SQL will move two of them off-row, to keep the main row to 8,060 bytes or less, but those columns are still not LOB data.
(2) Generally there's no need to move the indexes unless the indexes happened to contain LOB (MAX) columns (yes, yuck, but it is technically allowed for MAX data (not old TEXT data)).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 1, 2021 at 5:40 pm
Sadly, on 2014, you don't have the COMPRESS function available to help you out. But when you upgrade to 2016 (or later), be sure to look into that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 1, 2021 at 8:30 pm
Thank you Scott! If I am understanding you correctly, I will recreate the tables that have legacy data types and use the TEXTIMAGE_ON syntax to put LOB on a different file group. I will then try and shrink the primary file again.
However if lob data and off row data are not the same, would changing text to varchar(max) or nvarchar(max) be another way of removing lob data? I tried this and the shrink did not seem to go any faster and keep finding conflicting information saying that varchar(max) is considered lob data while varchar(n) is not.
July 1, 2021 at 9:46 pm
To get TEXT data off the main page, before you load the table, issue this command:
EXEC sys.sp_tableoption '<your_tablename>', 'text in row', 0
That will force all TEXT data into the LOB area, leaving just a pointer to that data in the main table area.
When you shrink, you should NEVER shrink the whole database. Instead, shrink any specific file(s) that actually need to be shrink.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 7, 2021 at 1:23 pm
Thanks Scott. I think that is the default, it is already set to 0 and I never changed it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply