Performance Tuning ... Pictures Provided
-
We have a large SQL Server database that gets hit by Business OBjects. Monitoring performance in windows (Windows->Programs->Administrative Tools->Performance) on the server that houses the SQL Server Database, I am trying to anlyze my results. Questions:
The green line indicating Average Disk Queue Length is usally topped out or drastically up and downs. What might be wrong or is there no issue here?
The blue line indicating Pages/Sec is unually at ground 0 all the time. What might be wrong or is there no issue here?
I'm thinking that paging were memory and we did more of that we could do less disk id. What do you think?
-
The value for Avg. Disk Queue length is number of requests being queued for the selected disk. Theoretically it should be lower than 2* no.of processors. This value is the accepted value for optimal performance. All it says is that your disk subsytem is not timely serving the requests. It is possible that the number of request being sent now have increased because of increased data/ bad queires and the existing disk subsytem cannot scale to those requests. Or it can also indicate some issues on the disk subsystem which is resulting in the requests not getting served.
-
How do I nail the issue down so I can go speak with the right person?
-
Also, if the Pages/sec is usually around 0, that means your server is not having to page to the harddrive to service RAM requests. I'd say this was a good thing. Or... there's something wrong with your paging file.
-
Was this high disk queue length witnessed during regular health check or you ran into some performance issues and withnessed this.
-
We ran into some performance issues. We're applying a HotFix now and watching poor performance queries to see in index's would help. I thought you-all might know of some SQLServer or System Settings though would result in better management of our system resources.
HotFIX: Article: Microsoft Knowledge Base Article - 810885, High-End disk subsystems may experience error 17883. http://support.microsoft.com/default.aspx?scid=kb;en-us;810885.
-
I once ran into this error and resticting max degree of parallelism resolved our issue. One of the behaviors which i saw was that sysprocesses would show huge no. of CX_PACKET wait types.
-
Regarding high queue lengths, from what i have seen in our shop is that this value is generally a good place to look at when you would like to tune your application more precisely. With this what i mean is say you see the counter to be spiking to a value of 100. But the spikes generally last for under 1 second. I would definetely work on resolving this but during crisis there might be still some other underlying issue which might be more dominant compared to this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply