SQL Server Performance Monitor

Posted on 2013-11-25
Last Modified: 2013-11-27
    we have SQL 2008 run on win2008, we need to monitor the SQL Performance, which counters i should use for monitor Memory, CPU,Hard disk and network. the key is, is there any base line after i get the result.
    In the resource monitor, the disk I/O is 103 MB/S, the image is SQL.exe, how to know why the SQL use 103MB disk I/O /S ?
    the Memory of Sql.exe is 23G used, how to know which database use it, we have 80 database in this SQL instance.
Question by:beardog1113
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
  • 4
  • 3

Author Comment

ID: 39673943
the attachment is resource monitor result
LVL 10

Assisted Solution

PadawanDBA earned 500 total points
ID: 39674877
Quest's poster on Perfmon Counters is arguably the most exhaustive one you'll find.  With regards to getting the details of what databases are using what, the only way you are going to find that is to use the dmv's.  Most of your metrics will be compared against the batch requests/sec - a busy SQL Server is a busy SQL Server and will utilize resources accordingly.

This may be for sharepoint performance tuning, but the underlying mechanics of utilizing the DMVs is the same for general SQL Server performance tuning/monitoring:

For perfmon counters:

Author Comment

ID: 39676605
Hi i run the DMVs and get the result as attachment, what does mean for this result? how to know how many memory used for each database? i did not find any script in
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.

LVL 10

Assisted Solution

PadawanDBA earned 500 total points
ID: 39677932
That is a tricky question that is deceivingly complicated.  There is the plan cache and the data cache within the buffer pool.  There are a few DMVs that will give you different information.  There is the sys.dm_os_buffer_descriptors view, which will give you information about all of the data pages that are in the buffer pool.  There is also the sys.dm_exec_cached_plans view, which will give you insight into the query plan cache memory allocations.  Specifically to get insight into what you're looking for you'll likely find use of these two queries:

Data Cache:
	case when database_id = 32767 then N'Resource Database' else db_name( database_id ) end as dbname,
	count( 1 ) as page_count,
	( count( 1 ) * 8 ) / 1024 as mb_data_cache_used
group by

Open in new window

and the Plan Cache:

	case when QP.dbid = 32767 then N'Resource Database' else db_name( QP.dbid ) end as dbname,
	sum( size_in_bytes ) / 1048576 as mb_plan_cache_used
	sys.dm_exec_cached_plans as CP
		cross apply sys.dm_exec_query_plan( CP.plan_handle ) as QP
group by

Open in new window


Author Comment

ID: 39679690
thank you, both is working, one more quesiton, what is different with plan cache and data cache?
LVL 10

Accepted Solution

PadawanDBA earned 500 total points
ID: 39679860
Sure thing!  

As a query heads through it's lifecycle, it will eventually hit the query optimizer, which is where SQL Server puts together a list of different ways to execute the query and calculates plan costs associated with executing them (the costs metric is discrete and only useful in comparing the plans to one another).  The details can get rather tedious (and I'll be happy to go into them if you would like - SQL Server Internals is one of my favorite topics...), but essentially the optimizer looks for a plan that is "good enough" and will then execute that plan (since SQL is declarative, you don't specify how to get the data, just what data you want).  So this can get pretty costly, which means that SQL Server wants to make execution plans as widely reusable as possible.  SQL Server winds up implementing parameterization to replace as many values with a token, so that similar queries can utilize the same plan.  These plans (except for trivial plans, which are essentially simple queries) are stored in the plan cache portion of the buffer pool (which is the largest consumer of memory in SQL Server).  The maximum sizes for the plan cache are (src:

75% of server memory from 0-4GB +
10% of server memory from 4GB-64GB +
5% of server memory > 64GB

The data cache is where SQL Server stores pages.  SQL Server never directly modifies data (beyond transact logs) on disk.  It will pull the pages it needs to execute your query into memory and read/update in memory.  In the event of a read, the page will remain what is called a clean page (unmodified), however in the event of an update or insert, the page will become a dirty page (modified), at which point its changes have not been persisted to the data files on disk, but do have (for ACID compliance) corresponding entries in the transaction log.  Dirty pages get flushed to disk whenever there is a system checkpoint (which is generally every 1 minute to maintain the default 1 minute recovery time of a database).  Pages will stay in memory for a time determined by the least recently used policy.  The header of each page contains information pertaining to the last two times it was accessed.  There is a periodic scan of the pages in the cache and a counter is maintained that is decremented if it has been a while since the page was accessed to free up cache space for other operations, it ensures that the pages that are accessed the least (lowest counters) are flushed first.

That was a really long way to answer the question, but I figure you asked because you were curious, so why not give the long answer!  Short answer:
plan cache - stores query plans from queries that execute to encourage plan reuse and decrease overhead from the query optimizer
data cache - stores pages of information from the databases in the instance

Author Comment

ID: 39680071
thank you  PadawanDBA

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Server running out of memory - Something is consuming all the available memory 17 92
date diff with Fiscal Calendar 4 77
tempdb log keep growing 7 45
Stored Proc - Rewrite 42 73
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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