Solved

How much memory usage in sql query ?

Posted on 2011-02-20
1
252 Views
Last Modified: 2012-05-11
If we doing an insert,delete , select or updates, how do we know how much memory sql being use for the operation ? any idea how to check this ?
0
Comment
Question by:motioneye
1 Comment
 
LVL 9

Accepted Solution

by:
s_chilkury earned 500 total points
ID: 34937962
One way is to use PERFMON (SYSMON) to get the values for the above counters alongwith others for further assessment.

To investigate potential memory bottleneck, you can use this query:

SELECT  cntr_value/1024 as 'MBs used'from master.dbo.sysperfinfowhere object_name = 'SQLServer:Memory Manager' and   counter_name = 'Total Server Memory (KB)'

For other counters

SELECT  'ProcedureCache Allocated',     CONVERT(int,((CONVERT(numeric(10,2),cntr_value)* 8192)/1024)/1024)as 'MBs'from master.dbo.sysperfinfowhere object_name = 'SQLServer:Buffer Manager' and   counter_name = 'Procedure cache pages'UNIONSELECT  'Buffer Cache database pages',     CONVERT(int,((CONVERT(numeric(10,2),cntr_value)* 8192)/1024)/1024)as 'MBs'from master.dbo.sysperfinfowhere object_name = 'SQLServer:Buffer Manager' and   counter_name = 'Database pages'UNIONSELECT  'Free pages',     CONVERT(int,((CONVERT(numeric(10,2), cntr_value)* 8192)/1024)/1024)as 'MBs'from master.dbo.sysperfinfowhere object_name = 'SQLServer:Buffer Manager' and   counter_name = 'Free pages'  


Also ... Check the following links:

http://blog.colinmackay.net/archive/2008/07/20/2996.aspx
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/b860a9c4-27da-4b24-b5bf-097dd99f2629
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now