Ok so I can monitor how SQL Server is performing, and I do it through a couple of options (Run Perfmon, log to SQL Server, analyse log results via analysis server OR Spotlight on SQL Server - No Log Or trend analysis though)We are about to consolidate our existing SQL Servers onto new hardware. This will involve a new costing model for our user departments, and what I've been asked is Can we monitor how much SQL resource a particular database/applciation is using at a given point in time?Now I use this stored procedure to log basic stats on a 5 minute periodic basis, but anyone got any ideas how to do this on a DB/user/process idCREATE PROCEDURE dbo.sp_HowBusy ( @Over INT = 60 ) ASBEGIN /**************************************************************************** Procedure Name : sp_HowBusy Procedure Desc : : Parameters : : Returns : 0 If Execution Completed Without Errors : 1 If Execution Failed Due To Errors Date Created : DD-MMM-2005 Author : <Your Name Here> - BBC Last Amended : DD-MMM-CCYY : : ****************************************************************************/ DECLARE @ProcName varchar(32) DECLARE @UserId varchar(32) DECLARE @s VARCHAR(8) DECLARE @busy INT, @idle INT, @io INT, @start DATETIME Declare @ServerID Integer SET NOCOUNT ON -- Get the current ServerID from the SQLAudit Database Select @ServerID = ServerID from SQLAudit.dbo.Servers Where Name = @@Servername -- Remove Rows Greater than 24 Hours Old DELETE FROM SQLAudit.dbo.tempServersPerformance WHERE StartMeasure < DATEADD(hh , -24 , GETDATE() ) SELECT @ProcName = 'sp_HowBusy' IF @UserId = Null SELECT @UserId = SUSER_SNAME ( SUSER_SID ( ) ) CREATE TABLE #tempwaits ( type varchar ( 40 ) , requests int , waittime numeric ( 19 , 3 ) , signalwaittime numeric ( 19 , 3 ) ) SELECT @busy = @@CPU_BUSY , @idle = @@IDLE , @io = @@IO_BUSY , @start = GETDATE() , @s = CONVERT(VARCHAR(8), DATEADD(SECOND,@Over,0), 8) DBCC SQLPERF (waitstats, clear) WAITFOR DELAY @s INSERT INTO #tempwaits EXEC ('dbcc sqlperf (waitstats)') Insert Into SQLAudit.dbo.tempServersPerformance SELECT @ServerID , @start -- AS StartMeasure , GETDATE() -- AS EndMeasure , 100.0 * (@@CPU_BUSY-@busy) / ((@@CPU_BUSY+@@IDLE)-(@busy+@idle)) -- AS CpuBusyInPercent , 100.0 * (@@IO_BUSY-@io) / ((@@CPU_BUSY+@@IDLE)-(@busy+@idle)) -- AS IoBusyInPercent , 100.0 * (SUM(waittime - signalwaittime)/SUM(waittime)) -- AS "% Resource Waits" , 100.0 * (SUM(signalwaittime)/SUM(waittime)) -- AS "%cpu waits" , SUM(signalwaittime) -- AS "Signal Wait Time" , SUM(waittime - signalwaittime) -- AS "Resource Wait Time" FROM #tempwaits IF @@ERROR = 0 RETURN 0 ELSE RETURN 1 END /******* END OF PROCEDURE sp_HowBusy ***************/-- RegardsTony The DBA
Performance Monitoring
Posted - 2006-12-15 : 07:22:01
