asked
SQL using all server memory
Hi all We have a production server here with 30Gb of memory, SQL server (2008) R2 using all of it. There are only 4 databases on that server, one being used heavily. Is there a query that i can run to see which SQL statements or procs are taking long to run? Also running profiler at the moment using the 'tuning' template and saving the results to a table. It gets saved as microseconds as far as i know, what's the correct way to convert to seconds? Thanks Ruan
Use extended events instead, they use radically less resources than trace. But, in general, you need to have metrics from "before" in order to understand what the deal is with "after" the server changed its behavior.
Extended Events:
http://msdn.microsoft.com/en-us/library/bb630354(v=SQL.105).aspx To understand why a server is running slow look to the wait stats. To get some more specific idea, look at the queues for disk, memory and CPU. Finally, use ex events or a trace to capture query performance metrics.
Why are you guys answering in the comments? Oh, and I'm pretty sure duration in trace is in microseconds only if you're storing output. If you're using Profiler, it displays as milliseconds.
Thanks for your input guys, when looking at task manager i thought the server was under pressure when all the memory was being used. The problem is not there then, it has to be with the database which has regular inserts and updates. Apart from running a trace with profiler to check for long running queries, is there anything else that i can check? According to the developers the database was performing fine a few days ago, now insert selects are taking very long. Thanks
For all the answers that are helpful, please show this by clicking on the thumbs up next to those answers. If any answer solved your question, please indicate that by clicking on the check mark next to that one answer.
SQL Server is designed to take all the memory. I suggest reading this as a starting point.
http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/ 1 millisecond = .001 Seconds. You can do the calculations in the query returning the data from the table you are capturing the profiler trace data.
As above, sql server will eat all the memory by default. You can set memory limits on some areas of sql server (but not all). Crux of the issue is, unless you have something else running on that server then don't worry :) It should release memory its not using when the box is under "memory pressure" from other apps & services.
16 People are following this question.
