Post reply

shrinking database files improves i/o performance?

  • December 6, 2005 at 7:11 am

    #115629

    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?

  • December 7, 2005 at 5:48 am

    #608143

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

  • December 7, 2005 at 5:53 am

    #608145

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

     

  • December 8, 2005 at 8:17 am

    #608429

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

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

  • December 8, 2005 at 8:23 am

    #608435

    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