question

ruancra avatar image
ruancra 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
sql-server-2008-r2performancememory-utilization
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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.
1 Like 1 ·
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.
0 Likes 0 ·
My bad habits RE answering in the comments :)
0 Likes 0 ·
Fine. I fixed it.
0 Likes 0 ·
Show more comments
sqlaj 1 avatar image
sqlaj 1 answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave Morrison avatar image
Dave Morrison answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.