• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

SQL Management Studio - Indexing Help

Hey Guys,

I am a relatively new programmer, and i have built a very large table in one of our databases. The size is around 4 billion records, so as you can imagine performance has been slow. I would like to properly index this table, however i got an error message when executed a query that should have implemented a clustered index and ran for 13 hrs.

any and all advice is welcome,

CJ
0
hexisenergy
Asked:
hexisenergy
1 Solution
 
Ryan McCauleyCommented:
The error was probably related to being out of space or the log being full - to implement a clustered index, you'll need at least as much free space on the drive as that table is currently consuming. If you can clear up enough space, I'd recommend that you cluster the table on some kind of ascending key, and you may also consider partitioning the table, as it will make grabbing smaller pieces of the data a bit quicker, as well as enable you to do maintenance on portions of the table without blocking the rest of the access.

However, four billion rows is a seriously large amount of data - my natural first question is whether you actually need to keep that much data online and ready to query. What's it being used for? Is it log data, and if so, is it meaningful? Could portions of it be summarized or cleaned to drop the table size to something more manageable?
0
 
Seth SimmonsSr. Systems AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now