SQL Server contains many Dynamic Management Views (DMV) for diagnosing server activity and health, including one for examining performance counters, sys.dm_os_performance_counters. Some of the counters in “sys.dm_os_performance_counters” with names ending with ‘/sec’ (such as like “Page lookups/sec”, or “Page writes/sec”) are known as “per second” counters. When you first come across this DMV you might not realise that the values for these counters are cumulative and require two measurements over an interval and then calculating the difference in those values based on the time between the two measurements.
Here is a simple TSQL snippet to perform this calculation for all per second counters in sys.dm_os_performance_counters:
-- Get first sample
SELECT *, getdate() as sampletime,
ROW_NUMBER() OVER (order by [object_name], instance_name, [counter_name]) as rowid
INTO #tempCounters1
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
order by [object_name], instance_name, [counter_name];
-- Wait for 10 seconds
WAITFOR DELAY '00:00:10';
SELECT *, getdate() as sampletime,
ROW_NUMBER() OVER (order by [object_name], instance_name, [counter_name]) as rowid
INTO #tempCounters2
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
order by [object_name], instance_name, [counter_name];
-- Calculate per second values
SELECT t1.[object_name], t1.instance_name, t1.[counter_name],
(t2.cntr_value - t1.cntr_value) / datediff(ss,t1.sampletime,t2.sampletime)
FROM #tempCounters1 t1 join #tempCounters2 t2 ON t1.rowid = t2.rowid;
DROP TABLE #tempCounters1
DROP TABLE #tempCounters2
GO
Here is a Table valued function version which accepts an interval parameter, and is easy to filter:
IF OBJECT_ID(N'dbo.PerSecondPerformanceCounters', N'TF') IS NOT NULL
DROP FUNCTION dbo.PerSecondPerformanceCounters;
GO
CREATE FUNCTION PerSecondPerformanceCounters(@intervalseconds int)
RETURNS @retCounterInformation TABLE
(
[object_name] nchar(128) not null,
[counter_name] nchar(128) not null,
[instance_name] nchar(128) not null,
[cntr_value] bigint not null
)
AS
BEGIN
DECLARE @tempTable1 TABLE
(
[object_name] nchar(128) not null,
[counter_name] nchar(128) not null,
[instance_name] nchar(128) not null,
[cntr_value] bigint not null,
[sampletime] datetime not null,
[rowid] int not null
)
DECLARE @tempTable2 TABLE
(
[object_name] nchar(128) not null,
[counter_name] nchar(128) not null,
[instance_name] nchar(128) not null,
[cntr_value] bigint not null,
[sampletime] datetime not null,
[rowid] int not null
)
-- Take first sample
INSERT INTO @tempTable1
SELECT [object_name],
[counter_name],
[instance_name],
[cntr_value],
getdate() as sampletime,
ROW_NUMBER() OVER (order by [object_name], [counter_name], [instance_name]) as rowid
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
ORDER BY [object_name], [counter_name], [instance_name];
-- Wait for specified time
declare @start datetime = getdate()
while (1=1)
BEGIN
if datediff(ss, @start, getdate()) >= @intervalseconds
BREAK;
END
-- Take second sample
INSERT INTO @tempTable2
SELECT [object_name],
[counter_name],
[instance_name],
[cntr_value],
getdate() as sampletime,
ROW_NUMBER() OVER (order by [object_name], [counter_name], [instance_name]) as rowid
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
ORDER BY [object_name], [counter_name], [instance_name];
---- calculate per second values (ignore fractional values)
INSERT @retCounterInformation
SELECT
t1.[object_name],
t1.instance_name,
t1.[counter_name],
(t2.cntr_value - t1.cntr_value) / datediff(ss,t1.sampletime,t2.sampletime) AS cntr_value
FROM @tempTable1 t1
join @tempTable2 t2 ON t1.rowid = t2.rowid;
RETURN;
END
GO
Here is how you would call it:
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
-- Return only per second counters with non-zero values
select * from PerSecondPerformanceCounters(10)
where cntr_value > 0
-- Return non-zero counter values for specified objects only
select * from PerSecondPerformanceCounters(10)
where cntr_value > 0 AND
(object_name = 'SQLServer:Buffer Manager' OR object_name = 'SQLServer:Access Methods')
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
smoServer = new-object Microsoft.SqlServer.Management.Smo.Server “servername”![smoServer gm </span></pre> So having done that we found a property named <a href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.defaultfile.aspx">DefaultFile</a> which looked promising, but it just returned an empty string. After a bit of digging around, it transpires that it only returns a path if the current location is different to where the master DB is located, so here it is in Powershell: <pre class="csharpcode"><span class="kwrd">function</span> Get-SQLServerDefaultDataFilePath( [<span class="kwrd">string</span>]](https://i0.wp.com/mitchwheat.com/wp-content/ql-cache/quicklatex.com-35dd1d61373d8311c1fda836c8af96a6_l3.png?resize=675%2C217&ssl=1 "Rendered by QuickLaTeX.com")
sqlServer = ![(<span class="kwrd">throw</span> <span class="str">'sqlServer is required'</span>)){ [reflection.assembly]::LoadWithPartialName(<span class="str">"Microsoft.SqlServer.Smo"</span>) >](https://i0.wp.com/mitchwheat.com/wp-content/ql-cache/quicklatex.com-7a11983740e13b5419d54ac7264e1d05_l3.png?resize=825%2C56&ssl=1 "Rendered by QuickLaTeX.com")
null
sqlServer
smoServer.DefaultFile
str}