May 25, 2004 at 8:15 am
my sql server service is getting 100% of cpu usage and very very slow.
it is a dual processors and 800 gb hd and 4gb memory
enterprise edition, latest service packs.
my app is used by 80 users inserting data all the time 24x7
thanks, gilberto from dr
May 25, 2004 at 8:33 am
Use the following to get a list of the current connections, ordered by cpu descending. Take the top 1, run a dbcc inputbuffer against it, see what it's doing, try to optimize it.
Take the next one on the list, do the same thing.
You need to identify the queries that are causing the problem, while not a complete solution in the slightest, it will give you an idea of somewhere to start.
SELECT SPID, RTRIM(STATUS), CMD, RTRIM(LOGINAME), RTRIM(HOSTNAME),* FROM MASTER..SYSPROCESSES WHERE LOGINAME NOT LIKE 'SA%' ORDER BY CPU DESC
May 25, 2004 at 8:34 am
Since tables are experiencing heavy inserts check if you have indexes on these tables, if yes try to minimize index usages because server may be spending most of the time updating indexes in each insert operation.
Mohamed
May 25, 2004 at 9:22 am
thanks, i'll try both
May 26, 2004 at 5:50 am
You may want to run SQL Profiler and find the queries using the most CPU time and optimize them. Try:
Events: "RPC:Completed" and "SQL:BatchCompleted"
Data Columns: Move "CPU" up into the "Groups"
Filters: Try CPU Greater than or equal to 100 (ms)
Run it for a little while and you'll be able to see the worst offending queries/SPs affecting the CPU. I'd start optimizing the from the higest CPU usage on down. You may want to tweak the Filter up or down depending to show more/less results in the profiler.
May 26, 2004 at 7:30 am
it was a poor select, i fix it and everything is all rigth, thanks everybody
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply