In general, defragging will improve performace - defrag at the file system or the application level - try it and see if it helps. I remember from a while back that there use to be something called "reorg" that did something like defrag for databases.

There is also a "defrag" at the array Raid Group level - I would recommend that you do NOT do that unless you are trying to get a larger contiguous free space - see the build-in help in Navisphere for more information.

glenk

defrag will not help much for SQL...it will help SQL daemon to access the files quiet fast...but the database internally may be fragmented, causing jumps in i/o access for SQL.

this is a good pointer by microsoft for tuning db indices, worth having a look.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

its worth having a look at the db configuration for any performance bottlenecks...e.g. tables without indices

here is one link (not the only though)
http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/3/

as said above, checking with emc best practices for sql setup will help too...from san view point, make sure that the i/os for data and log file go to different raid groups on clariion. do not try to use rais group on first 5 disks of the array and do not put too many i/o intensive LUNs on same RG etc.

most often its found that database design and layout plays a major role in performance so try to see things from database to array rather than the other way round..

oh btw, have you been experiencing the perf-blame-game? developer blaming dbas, dbas blaming san and the saga continues...welcome to the world of performance tuning!

oh yeah ... i tell my dba's after checking everything on the SAN side. "listen guys, disk can only spin that fast, if you stop doing full table scan with every query you run ..maybe it will perform better "

Thanks for all the Input, At this point the logs and database are on different LUNs but, I have not been give the lowdown of how the LUNs, disks, etc, are broken up.

In the mean time I do defrag the indexes weekely but, the DB grows and shrinks often, mostly shrinking, as I am finding millions of rows of old orphaned data that needs to go. I am guessing I need to defrag because of that. I need to download something like Diskeeper and run an analyze to know for sure.

once I know what the current SAN setup is, I may go back and rearrange them into smaller drives across more controllers and have more file groups in SQL Server withtables and indexes spread out separately across them.

At this point there are not often cries about poor performance but, I see high disk queue lengths allot and the data managed to grow by 15GB over last year with no explanation.

My goal is to get it all tuned and running smooth really before anyone notices.

Maybe it could help you some tips.

Watch the LUN if they are in the same Raid Group or not. If so they are sharing spindles so it will impact in performance.
Take the LUNs on Raid 1/0 better than in Raid5 although i guess the are already in Raid1/0 if you read the best practices white paper.

Through analyzer (once the advanced statistics are enabled) see if cache hits or reads from reads cache or writes cache hits take a high value. If not, SQL sentences are always asking the whole of the disk instead of CX memory.

Tune this SP memory increasing the write cache or read cache as you usually best use the sql database.

- Talk to developers to make less expensive sql sentences (avoid select *).
- Increase if you can the number of LUNs acrooss Raid Groups to add more disk spindles, make tests.
- Analyze periods to locate the problem...
- See the system or application event viewer in order to find something wrong.
- Define the performance monitor metrics (disk queues, memeory) of the OS to know where and why this performance problem occurs.

And pray...

Hope it helps you.

Regards.