|
|
Posted by Neil on 12/29/07 06:57
> Possibly if you created the tables and keys through some table designer,
> you may have gotten non-clustered PKs.
I believe that's what happened. When I originally converted the old MDB file
to SQL Server, DTS created clusted PKs, as well as timestamp fields. The
tables I created since have been created through an Access ADP file, which,
apparently doesn't create clustered PKs (nor timestamp fields).
>> I then ran the "sp_spaceused NULL, true" command again. But the results
>> were pretty much the same as they had been previously. I then ran the
>> optimization job and then reran the "sp_spaceused NULL, true" command,
>> and the results were still pretty much the same.
>
> One would have expected the numbers here to fall as well.
>
There were still 37 tables that didn't have clustered indexes. Most of them
were tiny lookup tables. Nevertheless, I added clustered indexes to all
tables, except for seven that were created by sql server than had no indexes
at all. These were:
sysfiles1
sysindexkeys
sysforeignkeys
sysmembers
sysprotects
sysfiles
Trace_References
After creating all the clustered indexes, I noticed that the tables that
were taking up the most space were the ones that I was expecting to (the
previous "top five" were surprises to me that they were at the top of the
list). So things seem much more how I would have expected them now, in terms
of which tables are taking up the most amount of space.
Nevertheless, running "sp_spaceused NULL, true" still didn't reveal any
changes. Also ran optimization and reran it again, but still no change.
So this gets back to my original question. If there is about 1 GB of data in
the db; and the database is showing 2.5 GB of data; then where's that other
1.5 GB of data coming from? Fragmentation seems to have been reduced
greatly. So what is that other 1.5 GB that is being reported? This is very
strange.
> And there is another thing that is fishy as well. The "Pages Scanned"
> in the old output was nowhere close to the numbers for Reserved. Now,
> Reserved also includes non-clustered indexes, but I can't see how an
> non-clustered index could be that badly fragmented when you run
> defragmentation regularly.
>
> So I am a little uncertain that you really gained that much. Maybe
> something is flaky with SQL 7. But if you really gained that much space
> by adding clustering indexes - then I would say that you really have a
> case for shrinking. But that would be if sp_spaceused reported over
> 1GB of space unallocated.
Yeah, there was still only about 300 MB unallocated. But I ran the shrink
database anyway. No change with sp_spaceused.
So, I dont know. This is very strange -- especially the way it just happened
overnight like that.
Thanks,
Neil
Navigation:
[Reply to this message]
|