Re: SQL Server (?) performance issues
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/02/04
- Next message: Ray Higdon: "Re: index ..optimize query pls"
- Previous message: Don: "MSDE to SQL 2000"
- In reply to: mike: "Re: SQL Server (?) performance issues"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 1 Mar 2004 19:27:55 -0500
Maybe yes, maybe no. If I see long-running queries with little or no CPU or
Reads, it's usually that those queries are being blocked by something else.
One ugly scenario I saw once was where a transaction was begun and over
1,260 separate SQL statements were sent before the transaction was
committed. The grand sum total time of the query can amount to quite a bit
if any of the contributors to that transaction went for a while. Even so,
if all of the statements above each went for 0.1 sec, the transaction would
have lasted 126 sec = 2.1 minutes.
You may need to run the profiler under controlled conditions to see if you
can observe the same behaviour. Then pick through the trace to pick up the
cause. I will also concede that perhaps you have a very slow disk
subsystem.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql . "mike" <nospam@nospam.com.au> wrote in message news:%23y5vnk4$DHA.640@TK2MSFTNGP09.phx.gbl... Hi Tom, Thanks for your reply. I agree, but correct me if I am wrong please, but if it was a query problem then would you not see the lock placed by the resources used by the query. Often when I look at the locking at times of bad performance, there is very little activity. THis is why I was starting to think it was more environment related rather than specific app related. Mike "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:ubat6WZ$DHA.2576@tk2msftngp13.phx.gbl... I'd suggest that your DBA use the SQL Profiler to trace which specific queries are giving you trouble. It could be that the queries need rewriting or you need better indexing. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql . "mike" <nospam@nospam.com.au> wrote in message news:ePrmNRZ$DHA.2072@TK2MSFTNGP11.phx.gbl... Hi there, We have an internally developed application running at our company which regularly experiences poor performance. The finger is being pointed at the app, but I am not convinced. Unfortunately we are not permitted to watch performance counters, and instead have to rely of the DBA to do so. I am not 100% certain of which exact counters are being watched, but we are being told that all the relevant counters are being watched and are within acceptable limits. The counters I know of are disk queue length, memory, processor, user count, cache hit amongst others. The message from the DBA is that from a db perspective, all is fine and there is plenty of spare capacity. The box is very powerful, being a Compaq ML570 (I think) with quad zeon processors, 8gb RAM, raid array etc. We only have about 100 concurrent users, so there should be plenty of horsepower to go around. When we get reports of poor performance, I am querying the amount of locking on the system, and there is often very little. This makes me think the DBA's assertion that the database has plenty of spare capacity is correct. So, I am not sure where to go from here. If it was an application issue, then I would expect to see lots of blocking and plenty of queued up requests. But I do not see this. I am beginning to wonder if it is a network related issue? Can anyone suggest how to go about solving this problem. i.e. how to get some idea of what proportion of an applications request is attributable to network latency, and what proportion is attributable to db activity? What specific counters should be watched? Any suggestions would be greatly appreciated. Thanks Mike Mortensen
- Next message: Ray Higdon: "Re: index ..optimize query pls"
- Previous message: Don: "MSDE to SQL 2000"
- In reply to: mike: "Re: SQL Server (?) performance issues"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
