Tuesday, March 13, 2012 9:15 PMWhen people talk about stored procedures or sql statements that are resource intensive, they seem to be either talking about CPU intensive or Memory intensive. What makes something take up CPU or Memory? I realize this is a very basic question.
Tuesday, March 13, 2012 9:32 PMActually, let me expand on this a bit more. I understand that Memory is for storing data (so there is less physical IO to disk) and CPU is for processing data. What kind of operations in SQL would be CPU intensive?
Tuesday, March 13, 2012 9:33 PM
A simple select cause...let you have a table with few million records with no index at all.. you issue a very basic select statement SELECT * FROM YOURTABLE..
CPU time to read the data from table
Memory pressure to keep all the data in mem
What makes something take up CPU or Memory?
1. Poor query
2. Lack of index
3. Server resourcess
for much accurate answer pls post your question..
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
Tuesday, March 13, 2012 10:29 PM
Find top 10 CPU intensive queries using
SELECT TOP 10 SUBSTRING(b.text, (a.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(b.text) ELSE a.statement_end_offset END - a.statement_start_offset)/2) + 1) AS statement_text, c.query_plan, total_worker_time as CPU_time FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) AS b CROSS APPLY sys.dm_exec_query_plan (a.plan_handle) AS c ORDER BY total_worker_time DESCSome of the CPU intensive operations are compilation and recompilation. You can monitor them using the SQL Statistics object counters. Also you should monitor the number of batches received. If the ratio of SQL Recompilations/sec to Batch Requests/sec is high then it potentially indicates a problem.
Setup and monitor these counters:
SQL Server: SQL Statistics: SQL Compilations/sec
SQL Server: SQL Statistics: SQL Recompilations/sec
SQL Server: SQL Statistics: Batch Requests/sec
Glenn has a good post on detection part,here is that http://sqlserverperformance.wordpress.com/2006/08/16/some-ways-to-detect-cpu-pressure-in-sql-server/
Wednesday, March 14, 2012 8:10 AM
This article should be able to answer your questions :- http://technet.microsoft.com/en-us/library/cc966540.aspx . It lists the steps for troubleshooting high CPU scenarios.
- Edited by Harsh ChawlaMicrosoft Employee Wednesday, March 14, 2012 8:10 AM
Wednesday, March 14, 2012 8:47 AM
Wednesday, March 14, 2012 3:22 PMModerator
You need to look at the entire system, not just CPU and RAM.
SQL Server is rarely CPU dependent, it is almost always hard drive I/O bound. Although the CPU can spike for short periods to 100%, high CPU is almost always a symptom of another issue, high page swapping or I/O issues.
Most of the RAM usage in SQL Server is buffer cache. The size of the buffer cache can offset some I/O issues. More RAM almost always equates to better performance.