May 25, 2012 at 3:44 am
Dear reader,
There are a number of Performance indicators.
For me it is not totaly clear what the Unit is of the counter.
What is the time duration how they are measured (4,5,6).
And what they exactly represent or measure.
Specificcally at the moment I am looking into:
1. logical reads
2. Physical reads
3. Read-ahead reads
4. Buffer cache hit ratio
5. Buffer cache hit ratio base
6. page life expectancy
Units Guessing:
1,2,3,4,5 Number of pages. (A page being 8 K).
(Or is 2 the actual number of physical reads which can be a segment or a number of consequtive pages/segments ?)
6 Number of seconds.
Timing:
1,2,3 As during the previous call.
4,5,6 guessing: each second, moving average ???, a minute?
Represents:
1. The number of requests for pages.
2. The number of requested pages read from disk (not cache) (or segments or consequtive parts).
3. Number of pages which are requested ahead of time, these can be in cache (no further action), or on disk (resulting in physical accesses). When is this done and counted?
4,5 Number of units found in the cache and the total number of units which are requested. (Unit probably a page?).
6 Number of seconds any page is expected to be removed from cache if it is not accessed. How does this work does each page have a timestamp which shows how long it is not used?
How are the numbers related to the number of seeks on a disk?
(For example immediatly after rebuild index).
I have been searching for this on the internet, but although the concept is explained in many places the units and timing are missing in most explenations.
So anwsers or links to webpages are welcome.
(similary I do not know how to read the units in a query plan, or relate those units with the units above).
Thanks for your time and attention,
Ben Brugman
May 25, 2012 at 4:45 am
The reads measures are based on pages, yes.
As for time, it really depends on where you're getting the measure and what you're looking at. If, for example, you're looking at the output from extended events for query execution time, you're probably seeing microseconds. Same measure within a trace event and you're seeing milliseconds. These are marked in the documentation for each event.
Other than that, maybe I'm misunderstanding the question.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 25, 2012 at 5:23 am
Grant Fritchey (5/25/2012)
The reads measures are based on pages, yes.Other than that, maybe I'm misunderstanding the question.
The hit ratio
SELECT '--' [--], cntr_value AS 'xhitx', CONVERT(varchar(30), getdate(), 126) as TIJD, '>>>'+counter_name+'<<<' as counter_name, object_name
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager' AND counter_name like '%hit%' or counter_name Like '%expectancy%'
both the hit ration and the hit ratio base fluctuate a lot. One instance they are both very high (thousands), next query they are both low (9 for example) indicating a ratio of 100 percent if both are equal. Because of the fast fluctuations I do not think that this hit ratio is since the SQL-server has been started. Is there a query to get the hit ration for a 'last' period or over a certain period. For example I want to measure the hitratio for the next 15 minutes. (I do not want to bring down the server 😉 for this ).
As for the other questions, can the pages read from disk be 'converted' into reads and seeks. Even roughly. Because from a disksubsystem it is often known how many seeks can be done and how much data can be read.
Thanks for you answers.
Ben
May 25, 2012 at 5:31 am
ben.brugman (5/25/2012)
As for the other questions, can the pages read from disk be 'converted' into reads and seeks.
No. A page read from disk is just a page read from disk. There's nothing in that counter that says whether that was part of sequential reads or random reads, whether any seek time was necessary or not.
unless you have no access to the OS, monitor page life expectency and buffer cache hit ratio (if you must, it's a useless counter) with perfmon, not the SQL DMV. It's easier and then you can easily tell what kind of time frames are involved (it's the sample rate you select0
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply