September 23, 2003 at 11:49 am
My 2cpu production server win2k,sqlsk sp2 is having 90% or above utilization. I turned on profiler for every option available there. Apart from normal queriers, I see lots and lots of lock-aquaired & lock-released by every spid, eventhough, there are not executing any query. Is this normal ? Every other parameter seems fine. I have 2gb ram, db size is 1gb only. Any help greatly appreciated.
September 23, 2003 at 12:28 pm
First things first, install SP3 as it protects against the slammer virus, not saying you have it but at least will rule it out.
When you turn on everything for profiler you will see a lot of data and it is hard to make sense of it all.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 23, 2003 at 12:38 pm
OK. here are the perfmon results.
cache hit ratio 100% no memory issue.
No physical disk issue, all low values
Processor-%user time high.
sql access methods: index searches/sec high
sql access methods: range scans/sec high
processor queue length is high.
if my db is 1gb and ram is 2gb, then my entire db should be in ram. Why it spends time on index scans?
Most of my queries are like this:
select * from Customer where customerCode = N'11223344'
I have tried unique clustered index on customerCode; Also tried, unique clusterindex on AcctNo, and a nonclustered index on CustomerCode. No cuptime change.
September 23, 2003 at 2:47 pm
Have you checked task manager on the server to make sure it is sql server using the cpu, rather than some other process.
Steven
September 23, 2003 at 3:59 pm
bj007,
Is customercode in fact a unicode column? (ie. nchar or nvarchar). If it's char, varchar or anything else then a conversion has to be done, so an index seek won't be done.
Eg. If it's a char/varchar column check out the execution plans of
a)
select * from Customer where customerCode = N'11223344'
b)
select * from Customer where customerCode = '11223344'
or
select * from Customer where customerCode = convert(varchar, N'11223344')
The former will do an index scan while the latter two will do index seeks.
Also, confirm that this type of statement is in fact the one chewing your CPU. Rather than worrying about looking at absolutely everything in your profiler trace, I suggest just doing RPC:Completed and SQL:BatchCompleted. Trace several hundred (or thousand) lines and then save to a SQL table called say TraceResults. Run the query below and look at "% of overall CPU":
select
[Start of Query]
,count(*) [Occurences]
,sum((cpu*1.0)/[Overall_CPU]*100.0) [% of overall CPU]
,sum(cpu) [sum(cpu)]
,avg(cpu) [avg(cpu)]
,max(cpu) [max(cpu)]
from (
select
substring(textdata, 1,40) [Start of Query]
,*
from dbo.TraceResults(nolock)
cross join (select sum(cpu) [Overall_CPU] from dbo.TraceResults(nolock)) y
) x
group by [Start of Query]
having sum(cpu) > 0
order by 3 desc
Cheers,
- Mark
Edited by - mccork on 09/23/2003 4:02:55 PM
Cheers,
- Mark
September 24, 2003 at 11:16 am
mccork, Steven,
Thanks for you replies.
1. Yes. Only sqlserver is using all the cpu, and the second is the system idle process.
2. customerCode is not a unicode character.It is varchar(10). When I talk to the development team, they say they dont put the N, but the sqlserver automatically puts it.
where customerCode = '11223344' uses a index seek.
where customerCode = N'11223344' uses a index scan, if i have a non-clustered index on cCode. If I have a clustered index on cCode it is not using that index.
Could there be a db option which can be set, so that it will not send with N'...'. I think, from the frontend (java), they set some parameter, which sometimes sends with N and sometimes for other queries without N.
Yesterday, we have 100 user connections to my database. (usually 30 to 40). So it is a 200% increase in connections. In perfmon, I compared, %processor time for processor 0 & 1, and Databases/Transactions/sec. Whenever more transactions hit the server, processor spikes (directly propotional). Looks to be a processor bottleneck. What can I do to handle this user connections. I even increased the Minimum Query memory from 1mb to 4mb.
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply