SQL Server index fragmentation

Hi all,
I'm at a client that is having performance problems with their database. (locks, slow queries, long running UBE's that sometimes run fast,...) and one of the things I was looking at was index fragmentation. Indexes of certain big files (F4101, F4102, F4211,...) go up to 100% but average around 90%
I was thinking about reorganizing the indexes on the biggest files but what is best practice to do so? Do you people do this too? Do you use a script? How often do you do this?

Thanks in advance for sharing the info


E1 8.10, SQL Server 2005, WAS 6.0, Win2003

One client I was at had a script run every week that rebuilt all their indexes. It seemed to work well for them.

any chance I can have a look at the script? Or anyone have an example? Because I'm not the scripting guru I'd like to be

I found one using google but it doesn't seem to do anything...

Should I reorganize or rebuild the indexes? and this should be done on an idle database, I presume?

wadie,

We created a maintenance plan in SQL Server Enterprise Manager to reorganize our production database including indexes. We scheduled it for the 4th Sunday of every month. We didin't do any particular custom scripting.

Jer

There are many topics related to SQL Server performance, such as disk IO, memory, data files allocation, OS/SQL Server configuration, indexes fragmentation, update statistics, tempdb, etc. You can use SQL Profiler and PerfMon tools to get the baseline and tune from there on.

Here are some good articles you can refer to:

http://www.sqlskills.com/blogs/paul/2008/01/10/WhitepaperOnPredeploymentIOBestPracticesPlusAFewUrbanLegendsAroundSQLServerIO.aspx

http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx

http://www.sqlskills.com/blogs/paul/2008/07/08/TechNetMagazineEffectiveDatabaseMaintenanceArticleAndAugustSQLQAColumn.aspx

Try avoid using the maintenance plan in SQL Enterprise Manager, especially the auto truncate option.

Thx for the links, I will read these through!