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