Solved

How much memory usage in sql query ?

Posted on 2011-02-20
1
257 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 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

733 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