December 23, 2024 at 5:46 pm
Hello experts,
I got a report from an application owner of degraded performance for an application.
To paraphrase: The unused table space is quite large when compared to the actual data space used. Per the vendor, this would impact performance.
Has anyone ever heard of unused table space causing performance issues? I haven't. In addition, if this is a real issue, is there a way to shrink unused table space, as opposed to shrinking the entire database (which I have often heard is not a good idea anyway)?
Thanks for any help or information that can allow me to resolve this issue.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 23, 2024 at 7:17 pm
It depends. If the unused space is significant and is embedded in pages that are being read, i.e. contain live data, then it could slow down access (somewhat) because more pages would need read.
IF you have this situation, then rebuilding the table indexes will re-compact the space. Be sure to specify a fillfactor lower than 100 if you need to make changes to rows that will data. If you're unsure, try using FILLFACTOR=95 when you rebuild the indexes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 24, 2024 at 9:06 pm
How are you determining 'unused table space'?
If this is actually unusable space allocated to the table - then it could possibly cause performance issues. If that is the case - then you definitely need to rebuild the indexes to recover that unusable space. If this is part of a HEAP - then you might be able to recover that space using ALTER TABLE. However, that often doesn't work and the only way to recover the space is to create a clustered index on that table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 26, 2024 at 10:45 am
The following supposedly applies only when your database is in the BULK_LOGGED or SIMPLE Recovery Models.
There is a feature known as "Fast Inserts". I wrote about that hot mess back when I upgraded to SQL Serer 2016. Here's the link to my post about it. Look for the section header of "Automatic Forced Fast Inserts During Bulk Loading" in that post to see what I'm talking about.
https://www.sqlservercentral.com/forums/topic/how-sql-server-can-just-go-faster#post-3702250
The biggest problem appears to be with front-end code, which can use a thing called "Insert Bulk" (not be be confused with BULK INSERT. Basically, it allocates and entire Extent (a full 9 pages) to hold as little as just one row. I explain all that in the write-up I gave the link for above.
Here's the link for how "Fast Inserts" work in SQL Server. Unfortunately, since front-end code isn't a part of SQL Server, they mention nothing about "Insert Bulk" in the article BUT it has the same impact.
Here's the link for Trace Flags. Search for Trace Flag 692. I strongly recommend enabling it to disable "Fast Inserts". Fast Inserts didn't really help our import processes but it kills us when it comes to "Insert Bulk" from the front end when it inserts one bloody row at a time. Since Trace Flag 692 is a "global only" Trace Flag, we added it to the startup for SQL Server.
A lot of people don't notice the problem because they do index maintenance on a too-regular basis. Rebuilding a CI or Heap is the treatment to recover the wasted "allocated but unused" space we're talking about here.
Also, this problem can also occur if you do a lot of deletes or the table is actually a heap with a lot of forwarded rows. Trace Flag 692 will NOT fix that. You'll need to do some index maintenance to recover that space. For reasons that are way too long to go into here, I recommend using a REBUILD instead of a REORGANIZE. You have to use an ALTER TABLE REBUILD for Heaps, anyway.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2024 at 6:21 pm
Thanks, everyone. I'll review the info you provided. The vendor is using this query to determine unused table space:
SELECT TableName
,NumRows
,reservedpages * 8192 / 1024 AS TotalSpace
,pages * 8192 / 1024 AS DataSpace
,(usedpages - pages) * 8192 / 1024 AS IndexSpace
,(reservedpages - usedpages) * 8192 / 1024 AS UnusedSpace
FROM (
SELECT t.[name] AS tablename
,avg([rows]) AS NumRows
,sum(total_pages) AS reservedpages
,sum(used_pages) AS usedpages
,sum(CASE
WHEN it.internal_type IN (202, 204)
THEN 0
WHEN a.type < 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END) AS pages
FROM sys.allocation_units AS a
JOIN sys.partitions AS p ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id
JOIN sys.tables AS t ON p.object_id = t.object_id
GROUP BY t.[name]
) AS subselect
ORDER BY UnusedSpace DESC
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply