CPU usage can be caused by a lot of reasons like:
- Bad execution plans caused by bad statistics
- Locks and blocks
- DBCC CHECKDB
- Index rebuild
- etc
1. select * from sys.sysprocesses where blocked>=1 (kill unwanted spid)
2. restarted sql services
3. Sp_who2 'active'
Referred following articles :
http://www.sqlservercentral.com/Forums/Topic1411982-391-1.aspx
http://raaviblog.com/how-to-investigate-100-cpu-usage-problem-in-sql-server-2008/
"after 8 hour search found link and offline dba support to arrive the lack of index. in maintenance activity sp_updatestats missing."
====================================================================
Query execution causing CPU spike:
Query
execution takes long times and spikes CPU commonly because of
in-correct carnality estimates caused by outdated statistics, Lack of
Index, Server configuration, Distributed queries, etc.
When
the server is experiencing this problem run the query in below link to
list all the queries which are executing in the server order by CPU time
desc along with plan.
{
}
It
could be one query which is driving the majority CPU time or Multiple
queries each driving the CPU. Look at the CPU time of the above query
output.
If it is single query/Store procedure which is driving the majority of CPU.
1. Update
the stats of tables and indexes used by the query (If the stats are up
to date Estimated rows and estimated execution will be approximately
same in execution plan .If there is huge difference stats are out dated and requires update
=======.=====================================================
Thanks god found link and read first step and stick-out run the
Solution: "sp_updatestats"
1) did you update ALL statistics with a FULL SCAN?
2) I would simply use profiler to capture rpc batch completed and tsql batch completed events and find the big hitters. Tune these. I would also consider running a trace to disk and using Qure from DBSophic to do aggregate trace analysis. The thing(s) you need to tune most are not necessarily the ones that run the longest or use the most resources individually...
3) it is possible that someone altered the schema in any way - i.e. dropped some indexes?
4) I also see this routinely at clients these days: did you perchance upgrade to a much better IO subsystem? Getting data into the CPUs faster can cause them to actually start earning their keep.
2) I would simply use profiler to capture rpc batch completed and tsql batch completed events and find the big hitters. Tune these. I would also consider running a trace to disk and using Qure from DBSophic to do aggregate trace analysis. The thing(s) you need to tune most are not necessarily the ones that run the longest or use the most resources individually...
3) it is possible that someone altered the schema in any way - i.e. dropped some indexes?
4) I also see this routinely at clients these days: did you perchance upgrade to a much better IO subsystem? Getting data into the CPUs faster can cause them to actually start earning their keep.
