Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 maintaining SQL, defragmenting index or harddrive?

Author  Topic 

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-04-04 : 17:52:00
Hello All!

I have a asp.net website with SQL 2005 DB .
DB size of 1.5GB with ~10 tables in it. The largest table has 200k of records in it (website users table), with 500 new records every day.

I've setup this database 4 months ago and didn't touch it since then.
I really have no knowledge what SQL needs in terms of index maintenances / hard drive maintenances.

Lately , the website searches started to be really slow , and I started to get timeout error and deadlock errors.
I have a few indexes for each table based on the recommendation MS-SQL Database tuning advisor gave me.

Some of the index's are :
Page fullness : 99%
Total Fragmentation: 24%

Other are :
Page fullness : 65%
Total Fragmentation: 99%


I guess I need to start maintaining the DB , defragmenting index or hard-drive?
Can anyone help me and provide me with guide/information on what is needed to be done in order to keep SQL running fast and happily?
or a guide on defragmenting index's and how ofen do i need to defrag?

Thanks,
Shar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-04 : 18:55:34
Here's my publically available version of a stored procedure that defragments indexes:
http://weblogs.sqlteam.com/tarad/archive/2007/03/07/60130.aspx

I will be posting an update to it soon as this one has bugs in it if you have any LOB data in your database. I already have the new version in production; I just haven't posted it yet.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

shaharru
Yak Posting Veteran

72 Posts

Posted - 2007-04-08 : 14:23:05
Thank you for your response.

is this the same or similar to using MS sql maintenances plans?
i've setup some plan of sql 2005 for maintenaces and it seems to really help.
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 2007-04-08 : 19:02:42
It is not enough to have indexes, you have to make sure that they are in good shape.

For dynamic tables, you should perform reindexing with 70% fill factor every night. For not so active tables, you can do it weekly or monthly.

Also your tables should be thin for high performance. Try to avoid varchar.

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-04-09 : 15:23:10
"reindexing with 70% fill factor"

That is so general as to be useless. What about an index on an IDENTITY column?

"For dynamic tables ... reindex ... every night. For not so active tables, you can do it weekly or monthly"

Nah, check the fragmentation and reindex when it becomes unacceptable. Actually better to defrag in many (if not "most") cases, rather than reindex.

"Try to avoid varchar"

So you wouldn't index a Name column? or a Town?

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-09 : 15:30:17
quote:
Originally posted by Kristen


Nah, check the fragmentation and reindex when it becomes unacceptable. Actually better to defrag in many (if not "most") cases, rather than reindex.



Since this question is in regards to a SQL Server 2005 database, defrag == REORGANIZE and reindex == REBUILD. In 2005, we use ALTER INDEX instead of DBCC commands.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-04-09 : 15:38:02
Thanks Tara. I'm getting old ...
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 2007-04-09 : 22:14:06
Kristen,

I did not find defrag to be useful in production environment. Slow, logged operation.

You are right about checking deterioration. I gave a rule of thumb.



Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 2007-04-09 : 22:15:55
Tara,

You are right! Time to say good bye to DBCC dbreindex!



Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-04-10 : 01:39:22
"I did not find defrag to be useful in production environment. Slow, logged operation."

Our databases are 24/7 so we need to leave the table available during maintenance. In SQL 2005 that can be achieved also with REBUILD WITH (ONLINE=ON) but that is going to need free space equivalent to the whole index, which will bloat the database size for any large tables.

I haven't compared the logging requirements of an Online Rebuild compared to an Off-line one, or to a Reorganise under SQL 2005. In SQL 2000 Reindex used more logging space, as well as Data "working" space, than Defrag.

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 02:25:29
quote:
Originally posted by SQLUSA


I did not find defrag to be useful in production environment. Slow, logged operation.



Dbreindex is the one that's heavily logged and slow, not the defrag. Which one you run depends on a lot of factors. Check out this:
http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspx



Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 2007-04-10 : 07:07:52
I don't know why you say that.

On a database where defrag takes 1 hour, reindex takes only 15 min.

Granted, in SQL Server 2000, dbreindex is blocking, but in SQL Server 2005 Enterprise edition not blocking.

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 2007-04-10 : 07:11:00
Kristen,

Yes! SQL Server 2005 has the marvelous NO-BLOCKING reindex! What an improvement.

Are you saying defrag is not slower than reindex? Can you post data?

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 11:38:53
quote:
Originally posted by SQLUSA

I don't know why you say that.

On a database where defrag takes 1 hour, reindex takes only 15 min.

Granted, in SQL Server 2000, dbreindex is blocking, but in SQL Server 2005 Enterprise edition not blocking.




Defrag and reindex are pre-2005 terms. So yes we are referring to SQL Server 2000 when we use those terms. If we are purely talking 2005, then we should say reorganize and rebuild instead as those are the new options to use.

But yes rebuild is pretty fast in 2005, especially if you use the online option in Enterprise edition.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-04-10 : 15:15:10
"Yes! SQL Server 2005 has the marvelous NO-BLOCKING reindex! What an improvement."

Ermmmm .... have I got the right end of the stick here?

Do NOT drop the current index.
Build a brand new Index (in duplicate)
Allow updates to amend the original index;
and track them (sniff the logs, whatever) so that those changed can be applied, retrospectively, to the new index once completed.
Drop the original index and put the New index in its place
Bingo! nicely rebuilt index.

Its a great triumph of technology. It "flatters" the non-DBA (about which I have no-problem, making a system that allows minimal-knowledge techies to perform well is A Good Thing IMHO).

However, for "large-ish" databases there are some serious disk space issues associated with this (benign until you get tight for disk space, or have one or more mega-tables within your database).

I haven;t looked at the logging of this, and its therefore quite possible that its minimally-logged. But if PaulR and his crew have not been able to be a bunch of SQL-for-Smarties! then I think the likelihood is that its heavy on logging.

I'm gonna have to find one of our DEV databases that has minimal maintenance to get something Big-enough and Fragmented-enough for a real-world test. Our production stuff is so incrementally maintained that I will struggle to get anything that can demonstrate the different between the two approaches.

Anyone else got a decent sized table that has weeks of use and fragmentation??

Kristen
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 2007-04-10 : 16:19:15
Tara & Kristen,

I am impressed by your knowledge!!!

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page
   

- Advertisement -