Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How much memory usage in sql query ?

Posted on 2011-02-20
1
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 9

Accepted Solution

by:
s_chilkury earned 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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