shrinking database files improves i/o performance?

  • I had some issues regarding i/o bottlenecks on a production database. Queries seemed to respond very slow for a few days.

    I reindexed the database and did all other things that was necessary to find the piece of system that was not working well. I even changed the hard drive.

    One of my collegues shrinked the database and than, surprise, queries seemed to work just fine.

    Why is that?

  • "why is that?"

    Less empty space to search over to find the real data?

    Less head-movement of the disk spindles?

    Better organised data - contiguous instead of randomly ordered.

  • That was interesting, how do we identify this condition ? i.e which DB parameters will indicate this ?

     

  • If you run dbcc showcontig, it will show you the info for each table.

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

  • To view how much space is ocupied you could click the database name in Enterprise Manager and then View->Taskpad.

    For as much as I know SQL Server Engine find a page by looking into the headers of files and then finding an offset. It doesn't just full scan it. It's exactly the same as the File System of a Hard Drive. It doesn't really matter the size of the disk and how much data it holds, the file is found allways in exactly the same amount of time. What really counts is the fragmentation of the hard drive when you copy a file.

    But fragmentation isn't an issue here. Before I shrinked the database, I reindexed it.

    Thanks for your answers!

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

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