April 10, 2008 at 2:18 am
Hi,
I'm working on Performance Tuning problem on a machine with at the moment 3 Databases with a size of 25 GB, 40 GB and 70 GB, growing each by 10 GB per month. Each database has one huge table, that takes about 90 % of the DB.
1st I checked the fragmentation of the indexes. All of them were fragmented with more than 99%.
OK, I should do a rebuild, but it's not possible to do it online 'cause of Standard Edition. I got a maintenance window of 3 hours, but I had no chance to rebuild at least one index of the biggest table. Then I tried to reorganize. The results are not good as with doing a rebuild, but you can do it online. The reorganize is now running since 15 hours, log-file has now 40 GB more than normal. The file-system got full that's why I adviced to stop the reorganize, to make a backup-log and then a shrink of the log-file. Now we could restart the reorg, cause it will continue its work.
Maybe it will be finished after a few days with one of the indexes. Then I can start the next one ... All will be finished in a few weeks. But after that I should start again with reorganizing or trying to rebuild ... It will be a neverending story always keeping the fragmentation low for a better performance but doing the defragmentation decreases the performance by itself.
Btw a few issues of the database design of these databases (schemas are equal):
PK of the biggest table is a Cluster-Index and has one DateTime column and 3 nvarchar(50) columns combined.
1. nvarchar is nonsens, the database is collecting data from a machine, no input of unicode stuff ...
2. one nvarchar column is always just a bigint ...
There's a second Index that has all columns from the PK (in another order) and two other nvarchar(50) columns. If you keep in mind that the clustered-index will always be stored with each non-clustered-index you can imagine the size of the indexes! That's not the only thing in this database ...
I'm trying to convince the vendor of the machine and the designer of the database to change a few things in the next update but first I have to solve the performance problem without a possibility to make a change on the design. Hopefully there will be soon another server where I can setup SQL-Server 2005 Enterprise Edition to use rebuild online and partitioned tables. That should it make easier to handle hundreds of million datasets. But at the moment this setup has to run in an acceptable way ...
Every tipp or advice is highly appreciated!
Best regards
Helmut (www.initpro.de)
April 10, 2008 at 5:57 am
It's possible that it could be as problematic as anything else, but you might try dropping & recreating the index. That will also remove fragmentation. The Storage Engine team at Microsoft has a whole series of posts in their blog on index fragmentation that are worth checking out. You may get some good ideas there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 10, 2008 at 12:51 pm
If you rebuild a table with a clustere didnex you'll probably need 1.5 times the size in the t log. You could build yourself a fast tempdb and do the rebuild there. Faster disk arrays and/or more memory are really your only ways forward. I suspect lots of spindles would be best. If you're on a raid 5 then forget it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 11, 2008 at 6:39 am
Helmut Rothtauscher (4/10/2008)
Hi,I'm working on Performance Tuning problem on a machine with at the moment 3 Databases with a size of 25 GB, 40 GB and 70 GB, growing each by 10 GB per month. Each database has one huge table, that takes about 90 % of the DB.
1st I checked the fragmentation of the indexes. All of them were fragmented with more than 99%.
OK, I should do a rebuild, but it's not possible to do it online 'cause of Standard Edition. I got a maintenance window of 3 hours, but I had no chance to rebuild at least one index of the biggest table. Then I tried to reorganize. The results are not good as with doing a rebuild, but you can do it online. The reorganize is now running since 15 hours, log-file has now 40 GB more than normal. The file-system got full that's why I adviced to stop the reorganize, to make a backup-log and then a shrink of the log-file. Now we could restart the reorg, cause it will continue its work.
Maybe it will be finished after a few days with one of the indexes. Then I can start the next one ... All will be finished in a few weeks. But after that I should start again with reorganizing or trying to rebuild ... It will be a neverending story always keeping the fragmentation low for a better performance but doing the defragmentation decreases the performance by itself.
Btw a few issues of the database design of these databases (schemas are equal):
PK of the biggest table is a Cluster-Index and has one DateTime column and 3 nvarchar(50) columns combined.
1. nvarchar is nonsens, the database is collecting data from a machine, no input of unicode stuff ...
2. one nvarchar column is always just a bigint ...
There's a second Index that has all columns from the PK (in another order) and two other nvarchar(50) columns. If you keep in mind that the clustered-index will always be stored with each non-clustered-index you can imagine the size of the indexes! That's not the only thing in this database ...
I'm trying to convince the vendor of the machine and the designer of the database to change a few things in the next update but first I have to solve the performance problem without a possibility to make a change on the design. Hopefully there will be soon another server where I can setup SQL-Server 2005 Enterprise Edition to use rebuild online and partitioned tables. That should it make easier to handle hundreds of million datasets. But at the moment this setup has to run in an acceptable way ...
Every tipp or advice is highly appreciated!
Best regards
Helmut (www.initpro.de)
1) That is a ridiculously large clustered index! 🙂
2) Yep, the non-clustered index is quite silly too - since it contains all the PK columns and two others.
3) I recommend doubling, tripling or even quadrupling your database size. This will give room for index building and for them to actually get laid down on disk in a contiguous manner.
4) Consider setting indexes to SORT IN TEMPDB. If you do this, make damn sure you have a big honkin, fast tempdb (and tempdb log). It can really improve index maint performance and also increases the likelyhood of contiguous extents in the primary database.
5) Unlesss you have some serious I/O capabilities and good CPUs, 3 hours may not cut it for full rebuilds.
6) Did you try dropping all NC indexes, rebuilding the CI and then recreating the NC indexes?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2008 at 12:52 pm
Thanks for your replies!
@sqlguru: I didn't drop the NC-Indexes, because I had just a 3 hours maintenance window. If the re-creation of the NC indexes wouldn't be finished after that, the company would have been angry with me 😉 Maybe I get a longer window on weekend, but the DB is collecting data from machines, that are running 24/7 but they can buffer the data up to 6 hours.
Hopefully the new machine for the DBs will arrive very soon, that I can setup it in a better way.
Next week I will have telc
on with the vendor of the machines (and DBs) to discuss about the ridicolous CI and NC-Indexes. On the new machine I will also use Partitioned Tables with a Sliding Window because only 3 months to the past have to be only further 6 months have to available.
Thanks to all for the great support
December 29, 2008 at 9:43 pm
Before rebuild index, backup you database ( just for caution), change your database recovery mode to *simple* so that the rebuild index operation will not be logged, you don't need to worry about log file full and it also speeds up rebuild index. After rebuild index , change your database recovery mode back to *full*, and then do a full backup.
December 30, 2008 at 6:04 am
Lin Zhang (12/29/2008)
Before rebuild index, backup you database ( just for caution), change your database recovery mode to *simple* so that the rebuild index operation will not be logged, you don't need to worry about log file full and it also speeds up rebuild index. After rebuild index , change your database recovery mode back to *full*, and then do a full backup.
If you're working on a live production environment that has an expectation of point in time data recovery, I would not use this approach. Yes, you help out the log by setting recovery to simple, but you also lose the ability to go to a point in time. Better to tune the mechanisms around your index rebuilds and any other operations to ensure you have the space & time you need than to compromise recovery mechanisms.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 30, 2008 at 7:07 am
You are right. But I mentioned that you need to do backup ( transactional backup) before change recovery mode, and do full backup after changing mode back to full. Yes, there is a gap in transactional backup between changing mode. The only time to point that is not covered by the transaction log is when you are rebuilding the index. But between the gap, no data should be changed. According to the original question, it sounds that he is using non Enterprise Edition , which means that you have to do rebuild index off-line ( Only Enterpise Edition supports on-line rebuild index), so when you are rebuilding index, no one should be able to access to database, right?
December 30, 2008 at 7:20 am
On the point of the clustered index, I cover this in presentations I do on indexing and there's some mention of this in part 4 of index analysis on my website http://www.grumpyolddba.co.uk/sql2005/working%20with%20indexes%204.mht,
you might want to consider making your PK non clustered and just clustering on the int - this will also make all your secondary indexes smaller.
I have no issue with 30 or 40gb tables, but you will need a fast disk subsystem to get a quick rebuild You might also consider making use of partitioning. times are always relative but I can do a full rebuild of every table ( and index ) of a 28GB database in about 16 mins; ok so it's not one table. on-line rebuilds are unlikely to be much use unless you have very low activity. If you're going to rebuild in tempdb make sure tempdb drives are fast too - typically my data drives are 10/12 spindles in raid 10 ( san storage )
If you have raid 5 for your database mdf files then it's likely you'll never rebuild your indexes
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 30, 2008 at 7:21 am
Lin Zhang (12/30/2008)
You are right. But I mentioned that you need to do backup ( transactional backup) before change recovery mode, and do full backup after changing mode back to full. Yes, there is a gap in transactional backup between changing mode. The only time to point that is not covered by the transaction log is when you are rebuilding the index. But between the gap, no data should be changed. According to the original question, it sounds that he is using non Enterprise Edition , which means that you have to do rebuild index off-line ( Only Enterpise Edition supports on-line rebuild index), so when you are rebuilding index, no one should be able to access to database, right?
People can still access the database, just not the index being rebuilt, which can mean a given table, depending on what type of index is being rebuilt.
You may have to do things like this, especially with very large databases, but a very large degree of caution should be used.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply