locked
Does Free space in Database Affects Performance of database RRS feed

  • Question

  • HI All,

    I am having SQL 2016 STD edition Database recovery model simple.

    My database total size is 132 GB and having 73 GB free space internally. 

    1. Does this have negative impact on my database as DB engine has to scan PFS pages for data.

    2. How DB engine performs data scanning to retrieve data considering there is 50% data 50% free space.

    Regards,

    AKash Pawar

    Thursday, August 30, 2018 11:23 AM

Answers

  • Hello,

    Shrinking a database to release free space to disk will cause index fragmentation and that causes Performance issues, see https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

    No Need to worry about free space, it don't effect Performance and will be reused for new data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 30, 2018 11:31 AM
    Answerer
  • Having allocated but unused space in databases should have no ill effect on database performance.

    The only time scanning might be affected is in the rare case that there would be excessive on-disk file fragmentation due to an excessively large database file. In this day of modern SAN storage, that is usually not an issue.

    As Olaf stated, you should NOT shrink database file due to impact on index fragmentation, unless you're in an emergency 'insufficient disk space' situation and there are no other alternatives. If you do shrink database file then you need to defragment indexes afterwards.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, August 30, 2018 12:31 PM
  • Check out Phil's reply. I'd just like to add that there's no scanning of PFS pages for data. SQL Server uses IAM and possibly also indexes. And to get free pages when needed GAMs and SGAMs are used (repeated every 4 GB, so you still only have a handful of those). And when inserting into a heap, PFSs are used, but they are first cross-refered with IAMs.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, August 30, 2018 4:42 PM

All replies

  • Hello,

    Shrinking a database to release free space to disk will cause index fragmentation and that causes Performance issues, see https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

    No Need to worry about free space, it don't effect Performance and will be reused for new data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 30, 2018 11:31 AM
    Answerer

  • if the page is 50 percent full that means you have internal fragmentation and that means more files sql server need to read (and insert to the cache)  in order to return the data, is that what you meant in the second question?

    https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 30, 2018 11:39 AM
  • Having allocated but unused space in databases should have no ill effect on database performance.

    The only time scanning might be affected is in the rare case that there would be excessive on-disk file fragmentation due to an excessively large database file. In this day of modern SAN storage, that is usually not an issue.

    As Olaf stated, you should NOT shrink database file due to impact on index fragmentation, unless you're in an emergency 'insufficient disk space' situation and there are no other alternatives. If you do shrink database file then you need to defragment indexes afterwards.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, August 30, 2018 12:31 PM
  • Dear Olaf,

    Really appreciate response however, my question is about repercussion of having free space in database and how does DB engine reacts when data search happens.

    Thanks once again.


    Regards, AKash Pawar

    Thursday, August 30, 2018 3:22 PM
  • Hi Uri,

    I mean to say my database is having 50GB data with 100% page fullness and having 50 GB free space i.e. total database size 100 GB. In That case how data retrieval happens.

    Thanks and sorry for troubles on that incorrect statement.


    Regards, AKash Pawar

    Thursday, August 30, 2018 3:27 PM
  • Check out Phil's reply. I'd just like to add that there's no scanning of PFS pages for data. SQL Server uses IAM and possibly also indexes. And to get free pages when needed GAMs and SGAMs are used (repeated every 4 GB, so you still only have a handful of those). And when inserting into a heap, PFSs are used, but they are first cross-refered with IAMs.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, August 30, 2018 4:42 PM