Step 3: Set Up the Monitoring Database
When you set up PerfMon as described in the previous steps, a set of base tables will be created automagically. If you wish to use these tables in queries, and you will, you need to create indexes on these tables.
Scripts 3.1 will adjust your database design on your monitoring database server so queries run more efficiently.
–Script 3.1
CREATE CLUSTERED INDEX ix_1 ON dbo.CounterData(CounterID) ON [PRIMARY]
CREATE UNIQUE INDEX ix_2 ON dbo.CounterData(RecordIndex, CounterID) ON [PRIMARY]
CREATE INDEX ix_1 ON dbo.CounterDetails(CounterName) ON [PRIMARY]
CREATE INDEX ix_2 ON dbo.CounterDetails(ObjectName) ON [PRIMARY]
Script 3.2 creates views for each counter you want to monitor. These scripts will be used in our next topic covering visualization. Script 3.2 will collect the 100 most recent values for user connections.
–Script 3.2
CREATE VIEW VW_User_Connections
AS
SELECT TOP 100 * FROM
(
SELECT TOP 100 data.CounterDateTime
, data.CounterValue AS [User connections]
FROM CounterData data WITH (NOLOCK)
INNER JOIN CounterDetails details WITH (NOLOCK)
ON data.CounterID = details.CounterID
WHERE details.CounterName = ‘User connections’
ORDER BY 1 desc
) AS t
ORDER BY t.CounterDateTime
Script 3.3 will collect the 100 most recent values for total CPU usage.
–Script 3.3
CREATE VIEW dbo.VW_CPU_total_user_time
AS
SELECT TOP 100 * FROM
(
SELECT TOP 100 data.CounterDateTime
, data.CounterValue AS [CPU Total User Time]
FROM dbo.CounterData data WITH (NOLOCK)
INNER JOIN dbo.CounterDetails details WITH (NOLOCK)
ON data.CounterID = details.CounterID
WHERE details.ObjectName = ‘Processor’
AND details.CounterName = ‘% User Time’
AND details.InstanceName = ‘_Total’
ORDER BY 1 DESC
) AS t
ORDER BY t.CounterDateTime
Later, these two views will be joined in one graph.
Step 4: Profiler
The difficulty with collecting data from Profiler is in finding a process to filter useful information from the textdata column. For example: you want to investigate the average duration of a stored procedure but you can’t perform a group by unless you get rid of all parameters that are also stored in textdata. Script 4.1 can be a first step in your quest how to filter and consolidate SQL Profiler data so it becomes useful to you. This function relies on the use of ADO recordSet.updateBatch methods (notice sp_executesql) but every application can construct its SQL commands in a different way so customization of this script to your current situation is still required.
–Script 4.1
CREATE FUNCTION fn_getSPfromTextdata (@textdata VARCHAR(4000))
RETURNS VARCHAR (4000)
AS
BEGIN
DECLARE @ret VARCHAR (4000)
SET @ret = ”
IF SUBSTRING(@textdata, 1, 18) = ‘exec sp_executesql’ AND SUBSTRING (@textdata, 22, 6) = ‘INSERT’
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX (‘% (%’, SUBSTRING(@textdata, 6, LEN(@textdata) – 5))-17)
END
ELSE IF SUBSTRING(@textdata, 1, 18) = ‘exec sp_executesql’ AND SUBSTRING(@textdata, 22, 6) = ‘UPDATE’
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX (‘% SET %’, SUBSTRING(@textdata, 6, LEN(@textdata) – 5))-17)
END
ELSE IF SUBSTRING(@textdata, 1, 18) = ‘exec sp_executesql’ AND SUBSTRING(@textdata, 22, 6) = ‘DELETE’
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX (‘% WHERE %’, substring (@textdata, 6, LEN(@textdata) – 5))-17)
END
ELSE IF substring(@textdata, 1, 4) = ‘exec’
BEGIN
SELECT @ret = SUBSTRING(@textdata, 6, PATINDEX (‘% %’, SUBSTRING(@textdata, 6, LEN(@textdata) – 5)))
END
RETURN @ret
END
Still, you can store SQL Profiler data using the settings discussed in my previous article and map the endtime values with events shown in PerfMon. Remember, this is the magic part of SQL Server 2000 PerfMon/Profiler integration.
I promise I’ll write an article dedicated to SQL profiler in SQL Server 2005.