Solved

SQL Server Performance Monitor

Posted on 2013-11-25
7
328 Views
Last Modified: 2013-11-27
Hi
    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.
0
Comment
Question by:beardog1113
  • 4
  • 3
7 Comments
 

Author Comment

by:beardog1113
ID: 39673943
the attachment is resource monitor result
Resource-Monitor.jpg
0
 
LVL 10

Assisted Solution

by:PadawanDBA
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: http://technet.microsoft.com/en-us/library/bb838723(v=office.12).aspx

For perfmon counters: http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf
0
 

Author Comment

by:beardog1113
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 http://technet.microsoft.com/en-us/library/bb838723(v=office.12).aspx
memory.jpg
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 10

Assisted Solution

by:PadawanDBA
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:
select
	database_id,
	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
from
	sys.dm_os_buffer_descriptors
group by
	database_id;

Open in new window


and the Plan Cache:

select 
	QP.dbid,
	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
from 
	sys.dm_exec_cached_plans as CP
		cross apply sys.dm_exec_query_plan( CP.plan_handle ) as QP
group by
	QP.dbid;

Open in new window

0
 

Author Comment

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

Accepted Solution

by:
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: http://www.wrox.com/WileyCDA/WroxTitle/Professional-SQL-Server-2012-Internals-and-Troubleshooting.productCd-1118177657.html):

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
0
 

Author Comment

by:beardog1113
ID: 39680071
thank you  PadawanDBA
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

22 Experts available now in Live!

Get 1:1 Help Now