SQL Server using processor.

Question

Post reply

SQL Server using processor.

rmcomics

Old Hand

Points: 332
More actions
May 26, 2011 at 10:28 am

#240360

Hello, I have a Windows 2008 Server running a Sql Server 2005 SP4 and I don't know whats happening since yesterday, but every 5 minutes SQL Server uses for about to 40 seconds it uses 95~100% of the processor in the SQL server process.
Is there a way to discover what is happening on SQL to use this incredible ammount of processor?
My machine is a Server with 2 Zeon E 5430 2.6 (4 cores each) and 16gb.
Usually SQL uses between 30 and 40% of the processor.
We're trying here to disable some services, because we have more than 20 DBs running on this server, But we don't have a clue of what's killing the server performance.
I hope that I made my self understandable, I don't write in English very well.
Thanks a lot.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rmcomics Old Hand Points: 332 More actions · Answer 2

rmcomics

Old Hand

Points: 332

May 26, 2011 at 10:49 am

#1330500

Thanks a Lot Gila Monster

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

For Disk Delays

===========

select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads

,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'

,io_stall_write_ms,num_of_writes

,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'

,io_stall_read_ms + io_stall_write_ms as io_stalls

,num_of_reads + num_of_writes as total_io

,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'

from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id

where DB_NAME(database_id) = 'your database name'

order by avg_io_stall_ms desc

For Queries

========

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE STATUS !='SLEEPING'

ORDER BY CPU DESC

For Memory Pressure

================

select * from sys.dm_os_performance_counters

where counter_name like 'page life%'

My Blog
www.aureus-salah.com

rmcomics Old Hand Points: 332 More actions · Answer 4

Thanks Syed.

I'm Sorry for take that long to thank you.