SQl Performance - specifically IO SAN

[hmckillop]

We currently have a large database - 64GB Data file, (which I know could be split into different data files).

Our indexing job is taking between 4.5 - 5 hrs long, as we use dbcc dbreindex , this can have an impact on other tasks.

We have SAN IO figures using SQLIO tool - write performance of 4MB/s, which my local hard disc operates at 2M/s.

Can anyone let me know if they have encountered similiar indexing issues and IO issues and if so what way they went about identifying and resolving the issues.

Here is some of the things we have done to date:

Run SQL IO stress tests.
Added additional LUNs to the SAN and allocated them to the drive which the datafile is stored on.
Replaced discs on SAN

Any help appreciated.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[MDXer]

SQL Server magazine had a great article a few months ago on reindexing based upon threshold values. They even provided the code to get you strated. My 5 hour reindex jobs are now down to 10-30 minutes because I control what tables and at what point those tables get reindexed.

[URL unfurl="true"]http://www.sqlmag.com/Article/ArticleID/43783/Automatic_Reindexing.html[/url]

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[hmckillop]

Thanks for the response, we already do something similiar, where we run a defrag report (using DBCC SHOW Contig) and then index according to another configuration table. I even posted a FAQ on it.

Any other takers with the original problem.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[moonshadow]

Try looking at this webcast from Microsoft:

http://support.microsoft.com/kb/895614/en-us

It discusses I/O issues with SQL Server (& SANS).

I'm also having a problem with SAN performance for which I've started a seperate post (thread962-1249273)

 

[mrdenny]

What are your disk queues on the san drives? At both the host and storage level.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com