Uncategorized

SQL PERFORMANCE MONITOR – PART 10: Snapshots & Fine Tuning

By David Speight / October 12, 2022

Due to the amount of data collected from dozens of servers at half a dozen companies, I typically clean out the data warehouse, keeping only the last 30 days of data. However, I like to take a snapshot of the a single day each month. I have set up a SQL Agent job to run this snapshot procedure on the first day of the month.

Here is a look at my table for this.

This is the code for the stored procedure.

USE [ServerAnalysisDW]
GO
/****** Object:  StoredProcedure [Analysis].[PerfCounterSnapshot]    Script Date: 9/27/2022 8:04:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Create date: 20220902
-- =============================================
CREATE OR ALTER PROCEDURE [Analysis].[PerfCounterSnapshot] 
@DateKey varchar(24) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT @DateKey = (CASE	WHEN LEN(@DateKey) > 5 THEN @DateKey 
			 ELSE REPLACE(EOMONTH(getutcdate(),-1),'-','') END) -- Last Day of previous month


	INSERT INTO [Analysis].[PerfCounterStatsSnapshot]
		([ID], [LocationID], [ClusterID], [MetricID], [PartName], [DateKey], [TimeKey], [iVal], [dVal], [UtcDate])
    SELECT	[ID], [LocationID], [ClusterID], p.[MetricID], [PartName], [DateKey], [TimeKey], [iVal], [dVal], [UtcDate]
	FROM	Analysis.PerfCounterMetric as m WITH(NOLOCK)
	inner join Analysis.PerfCounterStats as p WITH(NOLOCK) on m.MetricID = p.MetricID
	WHERE	m.MetricTsql is not null
	AND p.DateKey = CONVERT(INT,@DateKey)
	AND (m.MetricIntervalMinutes > 1 OR SUBSTRING(p.TimeKey,3,2) = '00')
	ORDER BY [LocationID], [ClusterID], p.[MetricID], [DateKey], [TimeKey];

END

TROUBLE SHOOTING and FINE TUNING

When there is an issue. Typically I check the logs first and then go from there.

1. Start with the log. I learn a lot simply by querying the log files the SSIS packages create.


SELECT TOP (1000) *
  FROM  ServerAnalysis.Analysis.PerfCounterLogActions ORDER BY 1 DESC;

SELECT TOP (1000) *
  FROM ServerAnalysisDwStaging.Analysis.PerfFileExtractionLog ORDER BY 1 DESC

2. STEP through the process manually to find where the breakdown occurs.

Here is a list of common issues and solutions.

ISSUESPARTRESOLUTION
Dashboard shows DOWN when server is UP.9The query is based on Metrics that may only pull every three hours. Rewrite query or check again later.
My CPU is at 90% but dashboard says 5%.9Remember there is a lag time of 5 to 30 minutes, depending on the settings. While this is NEAR real-time, it is by no means REAL TIME.
Dashboard has no data.9,6Verify the data source can connect.
Check that SQL Agent jobs are running.
Check data source connections & passwords
Some instances are missing from the dashboard.9,6Check the entire process for those instances and make sure each step is working from capturing metrics, output of csv file, import of data.
Dashboard runs slow.8Identify and create or update an index on one or more tables.
Delete old records that are bloating the table, or
Come up with a partition solution.
CSV file is not saving locally.6Make sure the SSIS CONFIGURATION on the actual server for the SSIS project parameters is correct.
RDS is not saving the csv to D:\ drive.6When saving a file to a folder on the D: drive of RDS, the folder must first be created. Save a small test file to the path, to create the folder where the CSV file will go
A certain Metric is not showing up.5Check the PerfCounterLog table on the local server and see if data is being captured for it.
Test that the TSQL definition in the PerfCounterMetric table runs by itself on said server, using the SELECT statement only.
CSV file is saving locally but not copying to AWS.4Make sure the correct permissions are set on the S3 bucket, and ACLs enabled.
Make sure port 443 is not blocked, or at least open for AWS CLI.
Check that the Service Account running the CLI is using the right IAM credentials.
Recheck the bucket policy.
Make sure permission is granted on any custom KMS keys that may have changed/updated.
SQL Agent job is failing.Is the PROXY USER active and its CREDENTIAL using a current password?

SAMPLE CODE DOWNLOAD

My untested, still in progress code for this project may be found on GITHUB. Use at your own risk and always test things out first in a development area.

https://github.com/LoveTheSql/SQL-Performance-Monitor-2022

You may also like