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 |
|
ms1
Starting Member
2 Posts |
 Posted - 2009-01-15 : 01:22:12
|
| Hi.I have been doing some investigation into performance, and I have been reading quite a few pages on the topic, and have so far gone through RAM and CPU. These seem fine. We have users who complain about a slow system, and I have a suspicion that writing to disk is an issue for us?? If someone could please have a quick look and give me a few pointers? We have a SQLServer 2000 Ent. ed. with sp4 and latest hotfixes, 6GB ram, SAN, data-log-temp on separate disks.Any help would be very appreciated % Disk Read Time 65% Disk Time 128% Disk Write Time 63% Idle Time 77Avg. Disk Bytes/Read 54,570Avg. Disk Bytes/Transfer 39,630Avg. Disk Bytes/Write 16,895Avg. Disk Queue Length 9Avg. Disk Read Queue Length 5Avg. Disk sec/Read 0.014Avg. Disk sec/Transfer 0.017Avg. Disk sec/Write 0.021Avg. Disk Write Queue Length 4Current Disk Queue Length 8Disk Bytes/sec 21,329,764Disk Read Bytes/sec 17,723,842 Disk Reads/sec 325 Disk Transfers/sec 538 Disk Write Bytes/sec 3,605,922 Disk Writes/sec 213 Split IO/Sec 4 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-01-15 : 03:58:41
|
| I think you need to re-define "slow". In Formula 1, 200mph is often considered slow...In City Traffic, 6mph is slow. Each number is relative to the expectations of the environment. What are the users expectations in this instance?...sub-second for a 10000 line report? sub-10-seconds for a data-entry screen?Do you have any baseline stats for the performance of the application?Is the slowness, "all over the application, every day, for all time slots, for all users"...or for only parts of that statement? Do you have evidence of slowness...date/time/ portion of the application? Do you have any evidence that the application functions faster in the past or at off peak times?Do you know if your code-base and database design/implementation are optimal? Hardware would not be my first choice for investigation. Often there are far cheaper wins when looking at other areas. |
 |
|
|
ms1
Starting Member
2 Posts |
Posted - 2009-01-15 : 05:58:52
|
| AM; Thanks for the reply !! I highly appreciate it.I keep looking at the hardware, and to my best knowledge, we are following guidelines and best practices etc. In my meaning, we "should have" a well working system, but comparing our stats with other peoples stats, I am suspecting that our disks are slow(?)I know it's a hard question as there are so many environmental factors in the picture, and I also know that what is fast in one system could be slow in another.We have traced down a few items in the code (and done changes) but at the moment, there is nothing that stands out as a performance degrading candidate (It might be that all functions are equally bad)The users are experiencing 10 + secs for a screen and sometimes up to 20 secs on our busy day. We have about 200 users. The "slowness" is experienced before lunch (10:30 - 12:00) and then some after lunch, with a peak around 3pm.The stats below are averages from 1pm on a normal day.SQL Server is not using all the memory available to it.. sure enough, it grabs what it can get (about 5GB - going by memory) but it's not using it all.It leaves me with the disks... I am possibly not asking the question properly... I am hoping that someone can see what I can't. I might have looked at the numbers for so long that I have gone blind.... Is there anything that stands out? Anything that makes anyone go... "hm.. you should look at that":-) |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-01-23 : 17:53:24
|
| Is it possible the network your clients are accessing is saturated?Your disks look 50% idle to me.Are those performance counters taken from the time users experience the slowness?The timing is uncanny. My network sees a similar hit from people checking email before and after lunch.Everything slows down due to the massive number of messages and attachments flying about. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-01-23 : 17:59:55
|
| You know in my experience it's usually the tsql and sp's that are the problem, not the disks. Start with the simple things first before you start worrying about the hardware. If you know what those screens are calling on the database end I'd highly reccomend some time with profiler and see what the sp's return for times. If you're sp's return results in .5 seconds then the 10 seconds is elsewhere (web server, client, etc). What's happening at 10:30-12? Is that your peak consecutive users? Profiler should give you the poor performing queries and then take a look at their execution plan in query analzyer. I'm guessing you probably are missing some indexes and under heavy load you're bogging down because of it.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
|
|
Disk performance?