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
  • 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 Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Update Query - What's wrong with this. 18 20
Service Statictic 11 30
Upgrading Integration Services 3 28
SQL Query Conversion of IIF statement into CASE - Syntax issue 17 29
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

914 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

14 Experts available now in Live!

Get 1:1 Help Now