Vendor claims unused table space impacts performance

  • 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

  • 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".

  • 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

  • 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.

    https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/database-file-operations/troubleshoot-bulk-load-causes-unused-space

    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.

    https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

    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


    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)

  • 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

    • This reply was modified 1 month, 2 weeks ago by  webrunner.
    • This reply was modified 1 month, 2 weeks ago by  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

Viewing 5 posts - 1 through 4 (of 4 total)

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