Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
idleflow
Starting Member
4 Posts |
 Posted - 2009-10-10 : 01:59:46
|
| Hi All,We're currently experiencing some sql performance issues which im pretty sure is due to file i/o latency however i wanted to run it past here to make sure im on the right track and also for advice on the best way to resolve.We are running sqkl server 2005 enterprise 64bit. MDF file is sitting at 145GB, log file is at 35GB. The database files sit on a SAN drive along with another 4 databases which are much much smaller - yes they all sit on the same disk.We run transaction logs every 15 minutes for the larger db and 20 to 30 minutes for other databases.Here are some stats i pulled out of sql and perf. mon.perf mon showed high disk time %, over 50% and that was during a low load. Avg disk read/write was also higher than the suggested .015 figure - more like .75, sometimes over 1. wait tasks_cnt wait_time max_wait signalCXPACKET 1419079752 13189533265 8018265 1163642218LCK_M_S 447511 2346693046 8046125 111421LATCH_EX 786932502 2300871906 174968 301287343LCK_M_IS 47132 2020854796 2919468 653484WRITELOG 122057595 290347171 28078 20196265io_stall_read_ms 9987378672num_of_reads 58693814avg_read_stall_ms 170.2io_stall_write_ms 3148040359num_of_writes 66132464avg_write_stall_ms 47.6io_stalls 13135419031 total_io 124826278 avg_io_stall_ms 105.2 any help is appreciatedthanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-10 : 19:58:37
|
| IO issues can be caused by lack of sufficient memory. How much memory do you have on this box? Also, how many CPUs does the OS see?Are you able to work with the SAN admins on collecting performance statistics from the backend SAN? That's what we did recently, the SAN diagnostics provides much better information than Performance Monitor.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
idleflow
Starting Member
4 Posts |
Posted - 2009-10-10 : 22:43:03
|
| Server has 32GB of ram with 8 intel xeon E5345 2.33ghz processors.The network engineers have been looking for san problems lately but haven't found anything as of yet. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-11 : 00:00:30
|
| The recommendation is to use multiple data files when your database gets over 64GB. Each data file should not exceed 64GB. So you may want to consider adding two additional data files (typically .ndf as the extension). You don't necessarily need to use filegroups with multiple data files as you can simply just rebuild indexes to move data around. SQL Server will use a proportional fill algorithm. You could even spread the load of each of these data files on to separate mount points pointing to separate LUNs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
idleflow
Starting Member
4 Posts |
Posted - 2009-10-11 : 02:01:29
|
| thanks. i'll do that and i'll also move the log file to a different disk. |
|
|
|
|
|
|
sql Bottlenecks