December 6, 2005 at 7:11 am
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
"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
That was interesting, how do we identify this condition ? i.e which DB parameters will indicate this ?
December 8, 2005 at 8:17 am
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
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