July 9, 2007 at 1:18 pm
The Background:
I have a SQL Serever with more than 10 million records.
I have to update/delete/insert records on a daily basis.
I am using the standard edition of SQL Server.
The tables are constantly having data inserted into them and the server has different jobs running all day long.
My Problem:
I cannot create index on my database and the database is getting really slow as each month/year passes.
Any/All suggestions are welcomed.
July 9, 2007 at 2:48 pm
Why are you unable to create indexes on the system?
July 9, 2007 at 3:04 pm
July 9, 2007 at 3:04 pm
If you are using 2005 ( as this forum name suggests ) you should be able to create the index with the "ONLINE" option.
* Noel
July 10, 2007 at 12:14 am
Noel,
Online index operations are available only in SQL Server 2005 Enterprise Edition.
July 10, 2007 at 3:24 am
He is using Standard Edition so onlie indexing is not possible. But he has to say what s the error that he gets when he tries to create an index or are there no indexes in any tables i the database.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 10, 2007 at 5:03 am
Here is what I am thinking about!
My Solution 1:
I create 2 databases db1 and db2.
Both the db's are replica of each other.
What I can do is take db1 down and idex it and shrink the log files while db2 is attached to my applications.
Then I backup db1.
Set db1 as the active db for all applications.
Restore this back up on db2.
Now I can do all the tasks as inserts and updates etc on the db2 while indexing could be done before its live again.
I keep repeating this process after every 12 or 24 hours as my application requires.
(setting db1 active or db2 active through the code)
Now I uess theres more food for though anyone please can you suggest another alternate method or pin point any pros and cons of the solution I am about to implement ?
July 10, 2007 at 5:26 am
July 10, 2007 at 5:42 am
Taking the database down is out of the question the size of the database is huge and the downtime would not be acceptable.
What could go wrong with the above proposed solution?
I know implememting it would be difficult but is it worth going through all this pain?
Or does anyone have an alternate solution; Please share with me?
July 10, 2007 at 5:48 am
Sorry Sugesh I skipped your reply!
I cannot bring the database down for indexing. Because If I want to index a standard edition then I have to bring down the database.
Any suggestions Please???
July 10, 2007 at 6:51 am
Depending on excatly what you want to index, indexing 10 million records should not take that long (assuming on decent hardware). Only that table you are indexing would be locked during the process, there is no need to take the entire database down to put an index on a table.
Not sure how your solution would work. If you truly are a 24/7 operation then there is a chance that more records are written into the database that is not being indexed (i.e. after the snapshot is set to be active).
If the database is that heavily used in a true 24/7 fashion, I would suggest getting it onto Enterprise Edition so you can do an online index. Of course that would require some downtime too.....
If it truly is 24/7, how do you get time to install patches to Windows and SQL? If you have downtime for that, why not sneak in putting in the index during one of the patches?
July 10, 2007 at 7:42 am
Then try to index your database in hours that you have little production overhead andyways you have to go ahead and do that someday may be a week end should be good choice.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 10, 2007 at 12:15 pm
I'm impressed that no one has suggested putting the database on a diet! If the database is constantly active, and constantly having growth, then at some point, no matter what you do, it will need to be slimmed down.
Do you have an archiving plan in effect? It sounds like either the wrong (or no) presentation has been made to management to argue for upgrading the environment, or a 'big picture' view has not been taken on the database. At some point, SOME of the content will no longer be of current interest. When that happens, the 'aged' content should be moved off the live database - it can still be accessible online, but by putting it in a quieter location then only when it's wanted will it be accessed, and it will be out of the way of the day--to-day activity.
If you cannot take the database down, how are you taking back ups? If the system is really that critical, how do you know that the backups you have work? I would advocate doing a restore into a new table. INDEX the new table. Then using the log bring it current and cut over. Can this work for you?
July 11, 2007 at 11:46 am
Like most people are saying, you are going to have to end up with a little bit of down time for this unless you upgrade to SQL Enterprise.
My suggestion would be to figure out exactly how much down time. It is on 10 million records - I would love to be dealing with something that small. With some preparation in a test environment, you should be able to determine what indexes you need to add. Pay close attention to the clustered index especially with constant inserts and updates because you can get table fragmentation pretty quickly. Then add the indexes in your test environment to see how long your tables will be offline. Break it into some logical pieces and possibly turn it into a few steps.
Once you know exactly what index you need and how long it took to build them in your test environment, you are going to need to go back to your company and expain that they have three options - upgrade to SQL Enterprise so this can be done online (which will force you into a minute or two of down time), select some down time and add the indexes during this time, or let the database continue to get slower until it simply stops working and then add the indexes.
If you really want to make a good argument, trace your database for a few days and find the longest time with the lowest usage on a daily basis and suggest this as your window.
July 12, 2007 at 5:43 am
From the way it sounds, your database will be crawling in short time, so it will be down anyway as it will become unusable? Whoever is the owner of the data is, needs to understand this.
You should copy the database to a DEV environment. Make the necessary modfications (indexes, query tuning,...). Based on the trace suggestion above.
After this is complete, clear out the new database and import the PROD data. This will require down time. Production database changes always require some down time. Have it scheduled on a weekend, or when there is the least amount of impact.
There are many techniques for production pushes, I'm just suggesting one. Michael Earl's sounded good, as well.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply