CPU vs Memory

Question

When 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.

Answer 1

Actually, 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?

Answer 2

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..

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

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 DESC

Some 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/

---

Anup | Database Consultant

Blog: www.sqlsailor.com Twitter: Follow me !

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

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.

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 5

I would actually counter to say that CPU and Memory alone are not the sole performance considerations for stored procedures.

What about IO? Databases pages must be read from disk at some point in order to be placed into memory.

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

Answer 6

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.