SQL Management Studio - Indexing Help

Posted on 2012-09-10
Last Modified: 2015-06-23
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,

Question by:hexisenergy
    LVL 28

    Accepted Solution

    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?
    LVL 34

    Expert Comment

    by:Seth Simmons
    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Are end users causing IT problems again?

    You’ve taken the time to design and update all your end user’s email signatures, only to find out they’re messing up the HTML, changing the font and ruining the imagery. What can you do to prevent this? Find out how you can save your signatures from end users today.

    Join & Write a Comment

    I was asked if I could set up a fax machine so that incoming faxes were delivered to people's Exchange inboxes and so that they could send faxes from their desktops without needing to print the document first.  I knew it was possible but I had no id…
    Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
    This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
    This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now