October 13, 2008 at 11:55 am
Something tells me this is one of the "it depends" sorts of questions, but I have to start somewhere.
**Note: We have no DBA or DB anything staffed. I am working with what I have been learning here and via experience.
The data warehouse is having performance issues. I have spent countless hours researching, and I am 99% sure the issue is Disk I/O.
I am am fairly certain the fact that we have several DB's, the temp db, log file and backups all running on the same I/O path is a performance killer. Also I remember stumbling across a few places on this site which suggests that a RAID5 is not a great thing for performance either.
That is in addition to low CPU/Memory use, frequent page latch and IO waits, and the disc queue length spikes into the 100's each time there is a performance hit, or a fair sized report is ran.
The IT staff in general supports the idea of the problem being "Too much data". The DB is 100gig, from what I understand that is a smallish DB. Our user base is 70 users in total with less than 5 connected to the DB at any given moment.
So what I am looking for now is white papers, resources, books, articles etc, which discuss the "standards" for hardware setup for a warehouse DB. Things such as log file and DB not being on the same drive.
Anyone have any links, books, or other to suggest for me?
Thanks!!!
October 13, 2008 at 12:54 pm
Does this help?
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
Basically, in my experience:
1) Log files on a separate RAID array
2) Data files (if there are multiple) on separate raid arrays
3) TempDB on its own set of dedicated drives
4) If using a SAN, ensure that the LUNs are comprised of dedicated drives
5) RAID 10 preferably. RAID 5 has the slowest write performance of any of the common RAID levels
100 GB is a small warehouse. I used to help support a 1.2 TB pseudo-data warehouse.
The symptoms you list do sound like IO bottleneck problems. What are the average, min and max values of the following perfmon counters (monitor during high usage)
Physical Disk: Avg sec/read
Physical Disk: Avg sec/write
Physical Disk: % idle time
Physical Disk: Avg disk queue length
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 12:58 pm
Oohhh, yes Gail, that helps alot.
I will get a listing of those stats fairly soon here, as time allows for me to do so.
Thanks!
October 13, 2008 at 1:01 pm
Your problem may also be poor indexing or poorly written queries. Both of those can easily cause SQL to have to read far more data than it should have to.
You can run profiler for an hour or so during your peak usage, monitor the SQL:Batch completed and RPC:Completed events. After the trace has finished, load it into a table and look for the queries with the highest number of reads. If you're not sure how to optimise it, post the query along with the table and index definitions here and I'm sure someone will take a look at it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply