August 23, 2011 at 10:32 am
Hi,
I was poking around on a client's old SQL 2000 server, trying to discover the cause of a long-running etl job, and I noticed that one of the destination tables was reporting the "index space" incorrectly. By incorrectly, I mean terrabytes, compared to hundreds of megabytes for the data space.
I'm wondering if this is a symptom that might point me to the cause of the core problem. What might this mean?
Some background:
The data is cleared and reloaded nightly.
The table is a heap (no clustered index)
The table has several indexes.
My first inclination when I noticed that there was no clustered index was that I should add one, but I've read since then that sometimes it's better not to use a CX on warehouse tables. Nothing references this table with a FK. Thoughts?
Thanks!
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
August 23, 2011 at 10:39 am
DBCC UPDATEUSAGE
There are bugs in the space-tracking algorithms on SQL 2000. UpdateUsage will fix the incorrect space metadata. You may want to schedule it to run often, though if you have a regularly scheduled CheckDB (and you should) it will silently fix the errors itself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2011 at 11:35 am
Thanks Gail,
I was kinda hoping it meant some problem, so I could say AHA! 😉
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply