Strange Performance Problem...
-
I have a Windows 2003 server dedicated to SQL SERVER 2000 (SP3).
Sql is the only process running on that server (I've checked in the task manager).
The server has 4 CPU and 8GB of RAM (4GB ram for SQL SERVER).
There are 50 databases under the SQL instance and 10 application servers runing against it with no connection pooling (for now - developers are working to change that).
All the CPUs are very busy (70-100%).
Other monitored parameters:
SQL Cache Hit Ratio - 87-90%
Disks - 10-20% busy
Network - < 10% busy
Many logins / logouts (one login/logout per second)
500 concurrent connections to sql
No locks, no deadlocks
few re-compilations (not too many)
few sorts, no table scans
A SQL command with only 900 Reads, for example, can take 40 seconds from the application (duration taken from SQL Profiler), if I run it from query analyzer it runs 3-40 seconds (different each time, first time is not always the worst one...). No physical IO. No lock timeouts at that time... Showplan and statistics io look great... No parallelism in this case.
What else can I check for...? Can it be the the logins/logouts are the problem here...?
Thanks...
-
Come on, guys...
I heard that this is the best WEB site for SQL SERVER questions...
-
It is but friday's not always the best day. Your answer will have a better chance of being answered when it's been presented in the newsletter (next monday).
-
...And what's the excuse today...? Monday, Monday...?
BTW, I read that cursors can also produce this behaviour. A cursor is opened once or twice a day.
-
Better late than never.
1)
If sql server is the only running application, why don't you give it 7GB to work with?
->SQL Cache Hit Ratio - 87-90% is quite low.
Have a look at http://www.sql-server-performance.com/sql_server_performance_audit2.asp
for a performance audit.
The taskmanager isn't viable to monitor sql server memory >2GB, use the performance counters of sql server.
2) Cpu usage high:
Is the incoming sql mostly ad-hoc or by stored procedures?
Are your indexes optimized? (all clustered indexes are necessary?)
Is there any layout-code (concatenations,... )that could be done by the application?
-
What is the code? It sounds like maybe a different execution plan is being chosen on each run. Is this dynamic sql? Hardware specs don't tell us that your selection 10,000,000 rows from a 100,000,000 row table. Give us more details about the app.
Tom
-
I think we are in the same boat. The problem started around 3 weeks ago and once it starts to happen it continues until a full re-boot.
Our nightly batch processing normally takes 4 hours to re-build the data warehouse. In the middle of the job last night everything started slowing down andn it is now up to 12 hours and running.
Queries that normally run fine all of a sudden start taking a little longer. Queries that return many columns slow down by a factor of 20+. When Sql Server gets in the mode we can run the following query. It runs ok, but if we add another column, the execution time goes from 2 seconds to 60 seconds and the processor gets pegged at 100%. Yikes!
Help! We may have to open a ticket at Microsoft.
drop table ttt
go
create table ttt (term int)
go
set rowcount 0
declare @i int
select @i = 0
while @i < 22000
begin
insert into ttt values (@i)
select @i = @i + 1
end
go
select
term, term, term, term, term, term, term, term, term, term,
term, term, term, term, term, term, term, term, term, term,
term, term, term, term, term, term, term, term, term, term,
term, term, term, term, term, term, term, term, term, term,
term, term, term, term, term, term, term, term, term, term,
term, term, term, term, term, term, term, term, term, term,
term, term, term, term, term, term, term, term, term, term,
term, term, term, term, term, term, term, term, term, term,
term, term, term, term, term--, term
from ttt
-
You're better off starting a new thread on this board. It'll get much more exposure that way. Greatly improving your chances of getting an answer.
-
Will do ... thx!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply