Performance Counters via sysperfinfo 

By Bill Wunder

A couple of weeks ago I set out to pirate a phrase from the life sciences to describe the shortcutting often used to determine when and if a SQL Server is in need of a hardware upgrade or possibly even an application design re-evaluation. In a manner far too similar to that which Social Darwinism in the early 20th century helped lay waste to any prospects of a humane humanity and helped to propagate a world order of oppression and violent wars, so is SQL Darwinism propping up an explosion of big iron solutions to rather more modest data applications. Not that I would want to even pretend that any flaws in current hardware sizing practices carry anything even close to the consequences for life on our planet as the might-is-right syllogism spawned in part from Social Darwinism. Rather, my contention is simply that in both cases there is a large and inexplicable subjective element necessary to get from the underlying science - or probably more appropriately the underlying engineering in the case of SQL Darwinism - to the overt manifestations.

As often as not, the decision on when to upgrade hardware is based on the ubiquitous use-it-or-lose-it budgetary practice over any quantifiable prediction of the need for more throughput. As often as not the the determination of a target upgrade platform is couched in some mystical boogie factor - ever used "estimate the need and multiply by 2" calculus? - and driven more by vendor marketing spiel than even the most fundamental of formulae. In part, these shortfalls can be overcome if the organization is well prepared to be able to quantify the next move.

I don't want to spend to much time here expounding upon the possibility that the business thought process that is SQL Darwinism is flawed similarly to the socio-political thought process that is Social Darwinism. I just wanted to stir that idea up a little bit before I dive into the topic at hand. If you'd like to explore that notion please refer to the discussion and links I've provided in that earlier SQL Darwinism article. Here my intention is only to use that as spring board into what seems to me to be a more reasonable approach: quantifiable analytical trend analysis.

The way to objectively evaluate current behavioral qualities and the performance and throughput trends of a SQL Server is of course to measure them over time. In order to provide fundamental meaning to any time series analysis it is necessary to establish a base or beginning point of measurement. System Monitor (aka Perfmon) is commonly referenced as a valid tool to capture behavior over time on a Microsoft Windows server. While system monitor does a very good job of collecting and graphically depicting behavior over time, it does not lend itself as nicely to ad hoc, a priori or otherwise deductive analysis. Typically, when you do find someone that has gone through an exercise in using Perfmon captured data points in a trend analysis, there will be a time intensive component in the analysis where the analyst has manually moved data points from Perfmon into a spreadsheet or database. One outstanding feature within SQL Server 2000 is that the SQL Server Perfmon metrics are already available. Most in table form in the system table master.dbo.sysperfinfo. A few others from the system function ::fn_virtualfilestats. And even some low level statistics accessible via the System Statistical Functions and  sp_monitor as we have already discovered in the previous article: Benchmarking Techniques Using T-SQL Part 1 - System Statistical Functions. To continue to build upon that article, in this installment we will first give sysperfinfo a good look, then we can give due consideration to the file level metrics available through ::fn_virtualfilestats.

sysperfinfo

sysperfino is a system supplied virtual table much like sysprocesses and syslocks and a few others. Virtual tables are like views with the exception being that the system virtual tables are views into the SQL Server's protected memory space rather than views based on tables stored in a database on disk. It's somewhat amusing to contemplate - and undeniably true - that when the SQL Server process is not running there is no data in sysprocesses or sysperfinfo. Conversely, those 23 rows in pubs.dbo.authors are there whether the SQL Server is running or not. It's perplexing to note that sysperfinfo is not listed on the Books Online "Monitoring Tools" page. Personally I think it's an awesome monitoring tool component and based on some of the comments I've heard over time my suspicion is there are folks on the SQL Server team at Microsoft that might agree.

sysperfinfo contains rows to correspond to counters from these System Monitor objects:

The data in sysperfinfo represents a real time value. With only a little effort you can easily capture periodic snapshots of the data you deem interesting. There are a few different data capture methods necessary in order to assure proper interpretation of the data collected. Some metrics such as the "Data File(s) Size (KB)" or "Log File(s) Used Size (KB)" counters of the database object provide a point in time value. The value represent the actual size of the data files or the actual amount of the log now in use. If you capture these values it's a pretty straight forward query to determine the file growth patterns.

If you refer to the  sysperfinfo scripts you can review the table schema and stored procedures I use to capture sysperfinfo data and a few T-SQL examples of how I use that data. It's as easy to render the results through your favorite charting tool as it is T-SQL and often provides a much more intuitive view for those non-DBA types we all have to deal with from time to time.

For example, a query of the collected data can show you when data files grew and allow you to plot a growth trend:

select DbName, cast(max(SampleDate) as varchar(11)) as [Latest Date at this size], 

        DataFileSize_KB,

        LogFileSize_KB

from admin.dbo.DbPerfHistory

where DbName like @DbName + '%'

group by DbName, DataFileSize_KB, LogFileSize_KB

order by case DBName when '_Total' then 1 else 0 end, DbName, max(SampleDate) desc

Other metrics are accumulated values and increase over time such as the "Transactions/sec" counter of the Database performance object or the "Readahead pages/sec" of the Buffer Manager object. These values require that you know the elapsed time between samples in order to produce a meaningful metric from the collected data. In general the algorithm for this type of metric is:

    (current counter value - previous counter value) / DateDiff(second, previous sample datetime, this sample datetime) ;

where second can be replaced with another time division when appropriate. When you are using a metric that is described as value/sec you're probably best to stay with seconds as your time division.

You could pre-calculate such values at insert time such as in this example taken from the admin.dbo.GetPerfHistory procedure included in the sysperfinfo scripts:

update d

set LastTranRateSample = t.TxnPerSec,

    LastSampleDt = @CurDt,     

    CurTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)

                        then (t.TxnPerSec - d.LastTranRateSample) 

                             / DateDiff(s, d.LastSampleDt, @CurDt)

                        else (t.TxnPerSec) / DateDiff(s, d.LastSampleDt, @CurDt) 

                        end, -- handle a counter reset 

    MinTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) 

                              / DateDiff(s,d.LastSampleDt,@CurDt) < d.MinTxnPerSec)  

                       then case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)

                                 then (t.TxnPerSec - d.LastTranRateSample) 

                                      / DateDiff(s,d.LastSampleDt,@CurDt) 

                                 else (t.TxnPerSec) / DateDiff(s,d.LastSampleDt,@CurDt) 

                                 end

                       else d.MinTxnPerSec 

                       end,

    MinTxnDt = case when ((t.TxnPerSec - d.LastTranRateSample) 

                           / DateDiff(s,d.LastSampleDt,@CurDt) < d.MinTxnPerSec) 

                    then @CurDt 

                    else d.MinTxnDt 

                    end,    

    MaxTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) 

                                   / DateDiff(s,d.LastSampleDt,@CurDt) > d.MaxTxnPerSec) 

                    then case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)

                              then (t.TxnPerSec - d.LastTranRateSample) 

                                   / DateDiff(s,d.LastSampleDt,@CurDt) 

                              else (t.TxnPerSec) / DateDiff(s,d.LastSampleDt,@CurDt) 

                              end

                    else d.MaxTxnPerSec 

                    end,

    MaxTxnDt = case when ((t.TxnPerSec - d.LastTranRateSample)

                           /DateDiff(s,d.LastSampleDt,@CurDt) > d.MaxTxnPerSec) 

                    then @CurDt 

                    else d.MaxTxnDt 

                    end,            

    TransActivityDetected = case when (t.TxnPerSec - d.LastTranRateSample) = 0

                                 then TransActivityDetected

                                 else TransActivityDetected  + 1

                                 end

from @t t

inner join admin.dbo.DbPerfHistory d 

on t.DbName = d.DbName

where SampleDate = @today                     -- work on today's record

and (   LastSampleDt < @CurDt)                -- unless inserted this time

And then to query the collected data thusly to show you the relative throughput patterns over time:

select p1.dbName,

       Datepart(yyyy, p1.SampleDate) as year, 

        Datepart(mm, p1.SampleDate) as month,

        cast(avg(p1.MinTxnPerSec) as numeric(12,2)) as [AvgDailyMinTxn/Sec], 

        cast(avg(p1.MaxTxnPerSec) as numeric(12,2)) as [AvgDailyMaxTxn/Sec], 

        avg(cast(cast(p1.TransActivityDetected as numeric(12,2))

            /cast(p2.TransActivityDetected as numeric(12,2)) * 100 as int)) 
              as [Avg%Active/Day]

from admin.dbo.dbperfhistory p1

inner join admin.dbo.dbperfhistory p2

on p1.SampleDate = p2.SampleDate

where Datepart(d, p1.SampleDate) = 1

and p2.DbName = '_Total'

and p1.dbName like @DbName + '%' -- allows query to be for all or for a specified db

group by p1.dbName,Datepart(yyyy, p1.SampleDate),Datepart(mm, p1.SampleDate)

order by case p1.DBName when '_Total' then 1 else 0 end, 

         p1.dbname, 

         Datepart(yyyy, p1.SampleDate) desc,Datepart(mm, p1.SampleDate) desc

Or , if it makes more sense to the way you want to use the data, you could do the calculation within the trend analysis query such as this query to compute the batch request/second trend from a once a day sample table:

select p1.SampleDate

        , (p1.BatchRequestPerSec - p2.BatchRequestPerSec)

           /(24*60*60) [BatchRequest/Sec]-- seconds in a day

from admin.dbo.SrvPerfHistory p1

inner join admin.dbo.SrvPerfHistory p2

on p1.SampleDate = p2.SampleDate + 1

order by p1.SampleDate Desc

Lastly there is the data that includes a column for the the current value and a base value such as the "Average Wait Time(ms)" counter of the Locks object or "Cache Hit Ratio" of the Cache Manager object. For example to insert a meaningful Buffer Cache Hit Ratio into a trend capture table you would need to compute the ratio of the 'Buffer Cache Hit Ratio' rows data value and the 'Buffer Cache Hit Ratio Base' rows data value in sysperfinfo:

insert admin.dbo.srvperfhistory (BufferCacheHitRatio)

select cast(p1.cntr_value as numeric(12,2))
            / cast(p2.cntr_value as numeric(12,2))

from master.dbo.sysperfinfo p1

inner join master.dbo.sysperfinfo p2

on p1.object_name = p2.object_name

where p1.object_name = 'SQLServer:Buffer Manager'

and p1.counter_name = 'Buffer cache hit ratio'

and p2.counter_name = 'Buffer cache hit ratio base'  

and p1.instance_name = p2.instance_name

Alternately you could simply record the counter value and the base value in the data capture table and compute the ratio at query time.

With that assortment of techniques you can easily define a regularly scheduled task that at it's first execution will provide you with a baseline for your SQL Server and at each subsequent execution will refine the behavioral trend documentation for your SQL Server and customize the reporting queries to meet the needs of your organization.

I'm not trying to spew dogma here as a SQL Darwinist might, I'm just trying to give you adequate engineering tools to meet the needs of your organization. The technique I use is to execute the the admin.dbo.GetPerfHistory procedure you can download from the scripts provided in the swwug.org Script Library to complement the sysperfinfo discussion in this article every half hour from a SQL Agent job. This gives me a new record each night at midnight and then throughout the day I can update such things as the min and max transactions per second so I can make a determination as to when during the day a server is working the hardest (and when it's not working so hard which is useful information when you are trying to establish a maintenance window for those ugly delete archive processes or to reorg your indexes.

 ::fn_virtualfilestats

For all practical purposes you can get performance and throughput details at the database file level through the System Statistical Function fn_virtualfilestats using the very same techniques I have outlined above for the system virtual table sysperfinfo. It's actually a rather limited set of metrics and because you need to provide the function with a database id and a file id some special tactics are necessary when collection data.

I certainly don't want to give the idea that fn_virtulafilestats - or sysperfinfo for that matter - are only useful for occasional data collection. These data sources can provide powerful troubleshooting information when your are in the thick of a problem. I strongly encourage you to become familiar with fn_virtualfilestats and add it to your tool belt. It has some great information. 

For baseline and trend analysis I suggest a stored procedure that uses sp_msforeachdb to iterate through the databases and then captures a row for each file in each database once a day. You may want to start with the stored procedure I have posted. You can schedule it once a day in the SQL Agent or you could consider adding it as a job step in the job suggested above to capture sysperfinfo data. There are only 5 data points for each file (Reads, Writes, Read Bytes, Write Bytes and Stalls) so you end up with a fairly narrow but meaty row.

With these T-SQL based resources for establishing baselines and identifying performance and throughput trends, there really is little reason for anyone to have to resort to 20th century approaches like SQL Darwinism to assure proper scale and hardware class for a SQL Server database in 2004. Deciding what to include in your benchmarking and trend capture collection is again another science. Each application will likely require a different set of data to properly do the job. For more details on selecting counters and how they can be used see the "Monitoring with System Monitor" chapter of SQL Server Books Online, Ken Henderson's awesome The Guru's Guide to SQL Server Architecture and Internals, Brian Kelly's very practical e-book Start to finish Guide to SQL Server Performance Tuning and the performance monitor tips at sql-server-performance.com (look toward the bottom of the home page for the tips index), and don't overlook the incredible group of unbelievable smart, savvy and generous folks on the SQL Server Worldwide User Groups list server at SQL Server 2000 discussions as you define the correct baseline and trend data points for your organization.

There are two scripts referenced above that you may want to investigate for additional details for this article:

admin db - baseline and trends using sysperfinfo

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetPerfHistory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetPerfHistory]
GO

Create proc dbo.GetPerfHistory
as
/*******************************************************************************************************
*	admin.dbo.GetPerfHistory 
*	Creator: Bill W	
*	Outline: query the system table sysperfinfo daily to capture: 
*			DB file size
*			Log File size
*			Log File size used
* 			Table Scans
*			Data Cache Hit Ratio
*			Procedure Cache Hit Ratio	
*		query sysperfinfo throughout the day to capture a daily distribution: 
*			transaction/sec 	
*			
*	How it Works:	The procedure is run from the SQL Agent scheduler on a regular interval.   
*			
*	usage:
		EXECUTE admin.dbo.GetPerfHistory 

*	Notes:		Query this table to determine the avg, max and min transactions per second by Date
*			use daily deltas to determine change trends
*	Modifications   
*
********************************************************************************************************/
declare @today datetime, 
	@CurDt datetime

declare @t table (DbName varchar(30) primary key,
		  TxnPerSec numeric(16,6))	

set nocount on

-- get a timestamp for now and midnight
set @CurDt = getDate()
set @today = cast(@CurDt as varchar(11))

-- don't do anything if wrong database
if db_name() <> 'admin'
	begin
		raiserror ('This utility may only be installed in the admin database!',16,1)
		return -1
	end

-- init the tables if first time on this server
if (object_id('dbo.DbPerfHistory','U') is null)
	create table dbo.DbPerfHistory (DbName varchar(30) NOT NULL, 
				SampleDate datetime NOT NULL,
				DataFileSize_KB int NULL,
				LogFileSize_KB int NULL,
				LogFileSizeUsed_KB int NULL,
				FirstTranRateSample int NULL,
				LastTranRateSample int NULL, 
				CurTxnPerSec numeric(16,6) NULL,
				MinTxnPerSec numeric(16,6) NULL,  
				MinTxnDt datetime NULL,  
				MaxTxnPerSec numeric(16,6) NULL, 
				MaxTxnDt datetime NULL, 
				TransActivityDetected int NULL,
				FirstSampleDt datetime NULL,	
				LastSampleDt datetime NULL,
				constraint pk_DbPerfHistory__SampleDate__DbName primary key
				(SampleDate, DbName))
			
		
if (object_id('dbo.SrvPerfHistory','U') is null)
	create table dbo.SrvPerfHistory (SampleDate datetime NOT NULL,
				TableScans numeric(16,6) NULL,
                                BatchRequestPerSec numeric(16,6) NULL,
				DataCacheHitRatio numeric(12,2) NULL,
				DataCacheHitRatioBase numeric(12,2) NULL,
				ProcCacheHitRatio numeric(12,2) NULL,
				ProcCacheHitRatioBase numeric(12,2) NULL,
				constraint pk_SrvPerfHistory__SampleDate primary key
				(SampleDate))

-- can add other values that require intraday range tracking here
insert @t (DbName, TxnPerSec)
select 	substring(instance_name,1,30), 
		cast(cntr_value as numeric(16,6))
	from master.dbo.sysperfinfo
	where counter_name= 'Transactions/sec'


-- if no rows for today, init a row
insert admin.dbo.DbPerfHistory (DbName, 
			FirstTranRateSample,
			FirstSampleDt,
			LastTranRateSample,
			LastSampleDt,
			CurTxnPerSec,
			MinTxnPerSec,
			MinTxnDt,
			MaxTxnPerSec,
			MaxTxnDt,
			TransActivityDetected,
			SampleDate)
select DbName,
	t.txnPerSec,
	@CurDt,
	t.txnPerSec,
	@CurDt,
	999999999.999999,
	999999999.999999,
	@CurDt,
	0,
	@CurDt,
	0,
	@today
from @t t
where not exists(select 1 from admin.dbo.DbPerfHistory
			where dbName = t.DbName
			and SampleDate = @today)

if not exists (select 1 from admin.dbo.SrvPerfHistory
			where SampleDate = @today)
	begin
		-- if buffer cache hit ratio getting smaller and scans increasing, new queries not using indexes
		insert admin.dbo.SrvPerfHistory (SampleDate, TableScans) 
		select @today, p.cntr_value 
		from master.dbo.sysperfinfo p
		where p.object_name = 'SQLServer:Access Methods'
		and p.counter_name = 'Full Scans/sec'

		-- as falls away from 100% more memory can help
		--select cast(cast(p2.cntr_value as numeric(14,4))/cast(p1.cntr_value as numeric(14,4)) as numeric(6,4)) * 100
		-- calc daily delta to plot over time (elim days where counter rolls over or restarts)
		update h
			set DataCacheHitRatio = p1.cntr_value,
		 	    DataCacheHitRatioBase = p2.cntr_value
		from master.dbo.sysperfinfo p1
		inner join master.dbo.sysperfinfo p2
		on p1.object_name = p2.object_name
		and p1.instance_name = p2.instance_name
		cross join admin.dbo.SrvPerfHistory h
		where p1.object_name = 'SQLServer:Buffer Manager'
		and p1.counter_name = 'Buffer cache hit ratio'
		and p2.counter_name = 'Buffer cache hit ratio base'  
		and h.SampleDate = @today   	   	-- work on today's record

		-- lower value may mean high dynamic sql use
		--select cast(cast(p1.cntr_value as numeric(14,4))/cast(p2.cntr_value as numeric(14,4)) as numeric(6,4)) * 100
		-- calc daily delta to plot over time (elim days where counter rolls over or restarts)
		update h
			set ProcCacheHitRatio = p1.cntr_value,
		 	    ProcCacheHitRatioBase = p2.cntr_value
		from master.dbo.sysperfinfo p1
		inner join master.dbo.sysperfinfo p2
		on p1.object_name = p2.object_name
		and p1.instance_name = p2.instance_name
		cross join admin.dbo.SrvPerfHistory h
		where p1.object_name = 'SQLServer:Cache Manager'
		and p1.instance_name = 'Prepared Sql Plans' 
		and p1.counter_name = 'Cache Hit Ratio'
		and p2.counter_name = 'Cache Hit Ratio Base'  
		and h.SampleDate = @today   	   	-- work on today's record

		-- use batch request per second as a general indicater of read/write activity over time 
		-- calc daily delta to plot over time (elim days where counter rolls over or restarts)
		update h 
			set BatchRequestPerSec = p.cntr_value 
		from master.dbo.sysperfinfo p
		cross join admin.dbo.SrvPerfHistory h 
		where p.object_name = 'SQLServer:SQL Statistics'
		and p.counter_name = 'Batch Requests/sec'
		and h.SampleDate > = @today   	   	-- work on today's record
	end

if exists (select 1 from admin.dbo.DbPerfHistory
			where SampleDate = @today
			and FirstSampleDt = @CurDt)
	begin
		update h 
			set DataFileSize_KB = p.cntr_value 
		from master.dbo.sysperfinfo p
		inner join admin.dbo.DbPerfHistory h
		on p.instance_name = h.DbName
		where p.counter_name = 'Data File(s) Size (KB)'
		and h.SampleDate = @today   	   	-- work on today's record
		and h.FirstSampleDt = @CurDt 		-- only if inserted this time

		update h
			set LogFileSize_KB = p.cntr_value 
		from master.dbo.sysperfinfo p
		inner join admin.dbo.DbPerfHistory h
		on p.instance_name = h.DbName
		where p.counter_name = 'Log File(s) Size (KB)'
		and h.SampleDate = @today   	   	-- work on today's record
		and h.FirstSampleDt = @CurDt 		-- only if inserted this time

		update h 
			set LogFileSizeUsed_KB = p.cntr_value 
		from master.dbo.sysperfinfo p
		inner join admin.dbo.DbPerfHistory h
		on p.instance_name = h.DbName
		where p.counter_name = 'Log File(s) Used Size (KB)'
		and h.SampleDate = @today   	   	-- work on today's record
		and h.FirstSampleDt = @CurDt 		-- only if inserted this time

	end
else
	-- if already rows for today, do tran rate aggregations
	-- use 0 as LastTranRateSample if counter has been reset
	-- used values returned in perfmon to determine calculations
	update d
	set LastTranRateSample = t.TxnPerSec,
	    LastSampleDt = @CurDt,	
	    CurTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)
				then (t.TxnPerSec - d.LastTranRateSample) / DateDiff(s, d.LastSampleDt, @CurDt)
				else (t.TxnPerSec) / DateDiff(s, d.LastSampleDt, @CurDt) 
			end, -- handle a counter reset	
	    MinTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) < d.MinTxnPerSec)  
				then case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)
						then (t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) 
						else (t.TxnPerSec) / DateDiff(s,d.LastSampleDt,@CurDt) 
				     end
				else d.MinTxnPerSec 
			   end,
	    MinTxnDt = case when ((t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) < d.MinTxnPerSec) 
				then @CurDt 
				else d.MinTxnDt 
			       end,	
	    MaxTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) > d.MaxTxnPerSec) 
				then case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)
						then (t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) 
						else (t.TxnPerSec) / DateDiff(s,d.LastSampleDt,@CurDt) 
				     end
				else d.MaxTxnPerSec 
			   end,
	    MaxTxnDt = case when ((t.TxnPerSec - d.LastTranRateSample)/DateDiff(s,d.LastSampleDt,@CurDt) > d.MaxTxnPerSec) 
				then @CurDt 
				else d.MaxTxnDt 
		       end,		
	    TransActivityDetected = case when (t.TxnPerSec - d.LastTranRateSample) = 0
					then TransActivityDetected
					else TransActivityDetected  + 1
					end
	from @t t
	inner join admin.dbo.DbPerfHistory d 
	on t.DbName = d.DbName
	where SampleDate = @today   		   	-- work on today's record
	and (   LastSampleDt < @CurDt) 		-- unless inserted this time

return


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DbActivityTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DbActivityTrend]
GO


CREATE PROCEDURE [dbo].[DbActivityTrend] 
	@DbName varchar(30) = ''
	  
AS
/*******************************************************************************************************
*	admin.dbo.DbActivityTrend 
*	Creator:	Bill Wunder
*	Outline:	Query the stored samples from master..sysperfinfo
*			to display the transaction rate history for a database
*			
*			
*	usage:
		EXECUTE admin.dbo.DbActivityTrend customer
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- set session
SET NOCOUNT ON


-- create temp tables


-- body of stored procedure
select p1.dbName,
	cast((cast(p1.LastTranRateSample as numeric(12,2)) - cast(p1.FirstTranRateSample as numeric(12,2)))/cast(datediff(s, p1.FirstSampleDt, p1.LastSampleDt) as numeric(12,2)) as numeric(12,2)) as avgTxnPerSec, 
	cast(p1.MinTxnPerSec as numeric(12,2)) as MinTxnPerSec, 
	cast(p1.MaxTxnPerSec as numeric(12,2)) as MaxTxnPerSec, 
	cast(cast(p1.TransActivityDetected as numeric(12,2))/cast(p2.TransActivityDetected as numeric(12,2)) * 100 as int) as PctActive,
	convert(varchar(21),p1.SampleDate,102) as [Date],
	datepart(dw, p1.SampleDate) as [Day of Week]
from admin.dbo.dbperfhistory p1
inner join admin.dbo.dbperfhistory p2
on p1.SampleDate = p2.SampleDate
where p1.DbName like @dbName + '%' 
and p2.DbName = '_Total'
order by p1.dbname, p1.sampledate



RETURN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


use admin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DbLogSizeTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DbLogSizeTrend]
GO


CREATE PROCEDURE [dbo].[DbLogSizeTrend] 
	@DbName sysname = null
	  
AS
/*******************************************************************************************************
*	admin.dbo.dbLogSizeTrend 
*	Creator:	Bill Wunder
*	Outline:	show log space over time

*	usage:
		EXECUTE admin.dbo.dbLogSizeTrend 
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- create temp tables

-- set session
SET NOCOUNT ON

-- body of stored procedure

if @DbName is null -- all
	select DbName, cast(max(SampleDate) as varchar(11)) as [Latest Date at this size], LogFileSize_KB, LogFileSizeUsed_KB, datepart(dw, SampleDate) as [weekday]
	from admin.dbo.DbPerfHistory
	group by DbName, LogFileSize_KB, LogFileSizeUsed_KB, datepart(dw, SampleDate)
	order by DbName, max(SampleDate)
else -- only one db
	select Dbname, cast(max(SampleDate) as varchar(11)) as [Latest date at this size], LogFileSize_KB, LogFileSizeUsed_KB,  datepart(dw, SampleDate) as [weekday]
	from admin.dbo.DbPerfHistory
	where DbName = @DbName
	group by DbName, LogFileSize_KB , LogFileSizeUsed_KB, datepart(dw, SampleDate)
	order by max(SampleDate)

RETURN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DbSizeTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DbSizeTrend]
GO


CREATE PROCEDURE [dbo].[DbSizeTrend] 
	@DbName sysname = null
	  
AS
/*******************************************************************************************************
*	admin.dbo.dbSizeTrend 
*	Creator:	Bill Wunder
*	Outline:	show data space over time
*			does not include log space
*			
*			
*	usage:
		EXECUTE admin.dbo.dbSizeTrend 
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- create temp tables

-- set session
SET NOCOUNT ON

-- body of stored procedure

if @DbName is null -- all
	select DbName, max(SampleDate) as [Latest Date at this size], DataFileSize_KB
	from admin.dbo.DbPerfHistory
	group by DbName, DataFileSize_KB 
	order by DbName, max(SampleDate)
else -- only one db
	select Dbname, max(SampleDate) as [Latest date at this size], DataFileSize_KB
	from admin.dbo.DbPerfHistory
	where DbName = @DbName
	group by DbName, DataFileSize_KB 
	order by max(SampleDate)

RETURN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 


use admin
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSrvActivityTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSrvActivityTrend]
GO


CREATE PROCEDURE [dbo].[GetSrvActivityTrend] 
	@DbName sysname = null
	  
AS
/*******************************************************************************************************
*	admin.dbo.GetSrvActivityTrend 
*	Creator:	Bill Wunder
*	Outline:	show server activity over time

*	usage:
		EXECUTE admin.dbo.GetSrvActivityTrend 
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- create temp tables

-- set session
SET NOCOUNT ON

-- body of stored procedure

select p1.SampleDate
	, (p1.TableScans - p2.TableScans)/(24*60*60) [TableScans/Sec]-- seconds in a day	
	, (p1.BatchRequestPerSec - p2.BatchRequestPerSec)/(24*60*60) [BatchRequest/Sec]-- seconds in a day
	, p1.DataCacheHitRatio/p1.DataCacheHitRatioBase [Data Cache Hit Ratio]
	, p1.ProcCacheHitRatio/p1.ProcCacheHitRatioBase [Procedure Cache Hit Ratio]
from admin.dbo.SrvPerfHistory p1
inner join admin.dbo.SrvPerfHistory p2
on p1.SampleDate = p2.SampleDate + 1
order by p1.SampleDate Desc

RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

admin db - db file performance baseline and trends using ::fn_virtualfilestats

use admin

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetFileStats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetFileStats]

GO

create procedure dbo.GetFileStats
/*******************************************************************************************************
*	dbo.GetFileStats.PRC
*	Creator:       Bill Wunder
*
*	Description:   create a trend history of ::fn_virtualfilestats output
*	Notes:         TimeStamp - Time at which the data was taken 
*                NumberReads - Number of reads issued on the file 
*                NumberWrites - Number of writes made on the file 
*                BytesRead - Number of bytes read issued on the file 
*                BytesWritten - Number of bytes written made on the file 
*                IoStallMS - Total amount of time, in milliseconds, that users 
*                            waited for the I/Os to complete on the file 
*
*
*	Usage:
	     EXECUTE admin.dbo.GetFileStats
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/
as

set nocount on

create table #file
  ( Name varchar(128) not null
  , FileId smallint not null
  , FileName nvarchar(260) 
  , FileGroup varchar(128)
  , Size nvarchar(18)
  , MaxSize nvarchar(18)  
  , growth nvarchar(18)
  , usage varchar(9))

if object_id('admin.dbo.FileStat','U') is null
  create table admin.dbo.FileStat
    (DbName varchar(128) not null
    , FileName varchar(128) not null
    , RecCreatedDt datetime not null
      constraint dft_FileStats__RecCreatedDt
      default getdate()
    , PathName nvarchar(260)
    , FileGroup varchar(128)
    , Size nvarchar(18)
    , usage varchar(9)
    , TimeStamp int
    , NumberReads bigint 
    , NumberWrites bigint 
    , BytesRead bigint 
    , BytesWritten bigint 
    , IoStallMS bigint
    , constraint pkc_FileStats__DbName__FileName__RecCreatedDt
      Primary key (DbName, FileName, RecCreatedDt))
  
exec sp_msforeachdb 'use ?
declare @dbid smallint
  , @fileid smallint
set @dbid = db_id()

truncate table #file

insert #file
  (name
  , fileid
  , filename
  , filegroup
  , size
  , maxsize
  , growth
  , usage)
exec sp_helpfile

select @fileid = min(FileId) 
from #file

while @Fileid is not null
  begin
 
   insert admin.dbo.FileStat
      (DbName
      , FileName
      , PathName
      , FileGroup
      , Size
      , usage
      , TimeStamp
      , NumberReads
      , NumberWrites
      , BytesRead
      , BytesWritten
      , IoStallMS)
    SELECT db_name(@dbid)
        , file_name(@fileid)
        , filename
        , filegroup
        , size
        , usage
        , TimeStamp
        , NumberReads
        , NumberWrites
        , BytesRead
        , BytesWritten
        , IoStallMS
    FROM :: fn_virtualfilestats(@dbid,@fileid) fn
    join #file f
    on fn.fileid = f.fileid
 
    select @fileid = min(FileId) 
    from #file
    where fileid > @fileid
  end'



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO

use [admin]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FileStatDailyTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FileStatDailyTrend]
GO


CREATE PROCEDURE [dbo].[FileStatDailyTrend] 
	@DbName varchar(128) = null   -- all
  , @FileGroup varchar(128) = null -- all
	, @BeginDate datetime = 0        -- all
  , @EndDate datetime = null       -- thru today
AS
/*******************************************************************************************************
*	dbo.FileStatDailyTrend.PRC 
*	Creator:       Bill Wunder
*
*	Description:   Produce report of captured files statistics
*	Notes:         can specify by database and/or daterange
*
*	Usage:
	     EXECUTE admin.dbo.FileStatDailyTrend 'admin'
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/
SET NOCOUNT ON

create table #filestat
  (DbName varchar(128)
  , FileName varchar(128)
  , RecCreatedDt datetime
  , FileGroup varchar(128)
  , usage varchar(9)
  , NumberReads bigint
  , NumberWrites bigint
  , BytesRead bigint
  , BytesWritten bigint
  , IoStallMS bigint
  , constraint pkc_#filestat__DbName__FileName__RecCreatedDt
    primary key (DbName, Filename, RecCreatedDt))

insert #filestat
  (DbName
  , FileName
  , RecCreatedDt
  , FileGroup
  , usage
  , NumberReads
  , NumberWrites
  , BytesRead
  , BytesWritten
  , IoStallMS)
select 
  DbName
  , FileName
  , convert(varchar(10),RecCreatedDt,102)
  , FileGroup
  , usage
  , min(NumberReads)
  , min(NumberWrites)
  , min(BytesRead)
  , min(BytesWritten)
  , min(IoStallMS)
from admin.dbo.FileStat
where BytesRead > BytesRead -- skip bad datapoint due to restart
and DbName like isnull(@DbName,'%')
and FileGroup like isnull(@FileGroup,'%')
and (RecCreatedDt >= @BeginDate) or @BeginDate is null
and (RecCreatedDt <= @EndDate) or @EndDate is null
group by DbName
  , FileName
  , convert(varchar(10),RecCreatedDt,102)
  , FileGroup
  , usage
order by DbName
  , FileName
  , convert(varchar(10),RecCreatedDt,102)
  , FileGroup
  , usage

select f2.DbName
  , f2.FileName
  , f2.FileGroup
  , f2.usage  
  , (f2.NumberReads - f1.NumberReads)/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [Reads/Min] 
  , (f2.NumberWrites - f1.NumberWrites)/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [Writes/Min]  
  , (f2.BytesRead - f1.BytesRead)/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [Bytes Read/Min] 
  , (f2.BytesWritten - f1.BytesWritten)/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [Bytes Written/Min]  
  , (f2.IoStallMS - f1.IoStallMS) * 1000 * 60/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [% Wait IO] 
from #filestat f1
join #filestat f2
on f1.DbName = f2.DbName
and f1.FileName = F2.FileName
and convert(varchar(10),f1.RecCreatedDt,102) = convert(varchar(10),f2.RecCreatedDt - 1,102)

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO


Bill