Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(This is a continuation of do indexes consume memory)

A fellow database developer believes we are having memory issues. He has seen increased run times in some standard queries - going from under 10 seconds to about two and a half minutes. He looked at Task Manager on the server and found high memory usage and now wants to take some of the memory currently allocated to the OS and free it up for SQL Server.

We are on SQL Server 2008, a 64-bit machine, AWE is not enabled, minimum 4096 MB, max 10240 MB.

I found Brent Ozar's A Sysadmin’s Guide to Microsoft SQL Server Memory which indicates Task Manager is not reliable. I have also found that our Page Life Expectancy is not indicating memory pressure. (Checked via Pinal Dave's query.)

Where else should I look? What else should I check? I'd like to report back to the database developer to either confirm his suspicions or prove them incorrect.

EDIT: modified my actual question. I appreciate, and agree, that these queries are overwhelming more likely slower for reasons other than memory problems. I am in a situation, however, that I need to prove memory is not the culprit across the board. That is, proving that a handful of queries are slower for other reasons won't accomplish my task. I'd like to understand where I can get such information and what metrics I should check.

share|improve this question
3  
For me step 1 would be to see what the wait type is on those queries that went from 10 seconds to 2.5 minutes. That'll tell you what's causing that particular issue. In my pretty narrow experience, there's seldom a memory issue but developers/analysts always think there is. –  JNK Jul 5 '12 at 13:54
    
Agreed, @JNK, and something I am doing in parallel. But I am also seeking an answer for my own knowledge and, perhaps (gasp!) proactive monitoring. :) –  JHFB Jul 5 '12 at 13:56
    
If the transition from slow to fast was very rapid, then you most likely have out of date statistics. If it was a slow degrade, then it could be fragmented indexes. –  Eric Humphrey - lotsahelp Jul 5 '12 at 13:56
    
Agreed, Eric. Working that angle too. –  JHFB Jul 5 '12 at 13:57
    
Task Manager is misleading. SQL Server will allocate and hold memory for the buffer pool, so I wouldn't go by that metric. If you're talking about 1 query that has sky rocketed in execution time, I agree with @JNK and Eric. It doesn't sound like it's a memory problem. –  Thomas Stringer Jul 5 '12 at 14:00
show 2 more comments

2 Answers

up vote 10 down vote accepted

The only way to prove is not a memory issue is by prooving is something else. And this requires you to identify the root cause of your performance problems. I recommend you follow a methodology like Waits and Queues. The SQLCAT team has also published a Troubleshooting Flowchart poster that you can follow.

As a general side comment: if someone is offering you to buy more memory on the server, just say YES, and then go ahead and do the root cause analysis. It doesn't matter that if the root cause of the problems are table scans, SQL Server can always use more memory.

share|improve this answer
3  
"Just say yes" deserves a +1 on it's own :) –  Mark Storey-Smith Jul 6 '12 at 11:51
    
Fantastic resources, particularly the flow chart. Shall save me from asking a dozen more questions! –  JHFB Jul 6 '12 at 13:23
add comment

To address your developers concern only (i.e. demonstrate memory isn't the problem), you could start by proving that the query isn't waiting on memory grants and that page life expectancy is at a comfortable figure.

Memory grants (from Glen Berry):

-- Shows the memory required by both running (non-null grant_time) 
-- and waiting queries (null grant_time)
-- SQL Server 2008 version
SELECT DB_NAME(st.dbid) AS [DatabaseName], mg.requested_memory_kb, mg.ideal_memory_kb,
mg.request_time, mg.grant_time, mg.query_cost, mg.dop, st.[text]
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
ORDER BY mg.requested_memory_kb DESC;

Page Life Expectancy:

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

From there, I'd follow the advice in the comments and identify what the actual problem is, rather than focusing on what the problem is not.

share|improve this answer
    
Great answer - helpful queries and links. Thanks, @Mark! –  JHFB Jul 6 '12 at 13:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.