SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 maintaining SQL, defragmenting index or harddrive?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shaharru
Yak Posting Veteran

72 Posts

Posted - 04/04/2007 :  17:52:00  Show Profile  Reply with Quote
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

USA
35940 Posts

Posted - 04/04/2007 :  18:55:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/08/2007 :  14:23:05  Show Profile  Reply with Quote
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 - 04/08/2007 :  19:02:42  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 04/09/2007 :  15:23:10  Show Profile  Reply with Quote
"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

USA
35940 Posts

Posted - 04/09/2007 :  15:30:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 04/09/2007 :  15:38:02  Show Profile  Reply with Quote
Thanks Tara. I'm getting old ...
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/09/2007 :  22:14:06  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
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 - 04/09/2007 :  22:15:55  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 04/10/2007 :  01:39:22  Show Profile  Reply with Quote
"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

USA
35940 Posts

Posted - 04/10/2007 :  02:25:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/10/2007 :  07:07:52  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
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 - 04/10/2007 :  07:11:00  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
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

USA
35940 Posts

Posted - 04/10/2007 :  11:38:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 04/10/2007 :  15:15:10  Show Profile  Reply with Quote
"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 - 04/10/2007 :  16:19:15  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000