Page Life Expectancy Using Idera SQL Diagnostic Manager Data
This process uses Idera SQL Diagnostic Manager (Idera SQLDM) to generate persisted information about sessions running whenever the Page Life Expectancy (PLE) counter falls below a threshold set in SQLDM. I built this process as I needed to know what was running on a given server whenever the PLE fell below a set value. I wanted the active transactions as well as the execution plan and other statistics.
SQLDM is used to fire a SQL Agent job on the management server whenever the PLE threshold drops below a set value. This job will call the target server, gather information, and dump it into a target table on my management server.
CreateTable_PageLifeExpectancy
The first thing to do is to make sure you have a management database created and a schema called Idera within it. I use DBAManagement as my utility database. Within this database, you will need a target table to store your results.
CREATE TABLE [Idera].[PageLifeExpectancy]
(
[session_id] [SMALLINT] NOT NULL ,
[sql_text] [NVARCHAR](MAX) NULL ,
[login_name] [NVARCHAR](128) NOT NULL ,
[wait_info] [NVARCHAR](4000) NULL ,
[CPU] [INT] NULL ,
[tempdb_allocations] [BIGINT] NULL ,
[tempdb_current] [BIGINT] NULL ,
[blocking_session_id] [SMALLINT] NULL ,
[reads] [BIGINT] NULL ,
[writes] [BIGINT] NULL ,
[physical_reads] [BIGINT] NULL ,
[used_memory] [BIGINT] NOT NULL ,
[status] [VARCHAR](30) NOT NULL ,
[open_tran_count] [SMALLINT] NULL ,
[percent_complete] [REAL] NULL ,
[host_name] [NVARCHAR](128) NULL ,
[database_name] [NVARCHAR](128) NULL ,
[program_name] [NVARCHAR](128) NULL ,
[start_time] [DATETIME] NOT NULL ,
[login_time] [DATETIME] NULL ,
[request_id] [INT] NULL ,
[collection_time] [DATETIME] NOT NULL ,
[query_plan] [VARCHAR](MAX) NULL
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CreateAgentJob_TruncatePageLifeExpectancy
Although not required, I have a SQL Agent job that truncates data from the table after it is three months old. You could keep data forever, but I’ve found that I rarely need data that is over three months old.
DELETE
FROM [DBAManagement].[Idera].[PageLifeExpectancy]
WHERE CONVERT(DATE,start_time) >= DATEADD(m, 3, GETDATE())
CreateAgentJob_PageLifeExpectancy
This is the job that SQLDM will call when the internal alert fires in response to a low PLE condition. You will need to configure the alert in SQLDM to call this job. You will also need to allow for the SQLDM service account to call this job.
Notice in the code that you will need to replace “xxxxx” with the name of your target server. A linked server is used to reach over to the target server, so that will need to be created as well. If you want to run this process for more than one server, I suggest you name the job “PageLifeExpectancy_”, of something similar.
The job does three things. It calls the stored procedure to capture the activity, inserts the data into our target table, and calls the stored procedure to drop the temporary table on the target server.
EXEC xxxxx.DBAManagement.dbo.sp_InsertPageLifeExpectancy
GO
INSERT INTO DBAManagement.Idera.PageLifeExpectancy
( [session_id] ,
[sql_text] ,
[login_name] ,
[wait_info] ,
[CPU] ,
[tempdb_allocations] ,
[tempdb_current] ,
[blocking_session_id] ,
[reads] ,
[writes] ,
[physical_reads] ,
[used_memory] ,
[status] ,
[open_tran_count] ,
[percent_complete] ,
[host_name] ,
[database_name] ,
[program_name] ,
[start_time] ,
[login_time] ,
[request_id] ,
[collection_time] ,
[query_plan]
)
SELECT [session_id] ,
[sql_text] ,
[login_name] ,
[wait_info] ,
[CPU] ,
[tempdb_allocations] ,
[tempdb_current] ,
[blocking_session_id] ,
[reads] ,
[writes] ,
[physical_reads] ,
[used_memory] ,
[status] ,
[open_tran_count] ,
[percent_complete] ,
[host_name] ,
[database_name] ,
[program_name] ,
[start_time] ,
[login_time] ,
[request_id] ,
[collection_time] ,
[query_plan]
FROM xxxxx.tempdb.dbo.tmpPageLifeExpectancy
GO
EXEC xxxxx.DBAManagement.dbo.sp_DropPageLifeExpectancy
GO
sp_InsertPageLifeExpectancy and sp_DropPageLifeExpectancy
These two stored procedures are what the job above call on the target server. The sp_WhoIsActive tool is used to actually capture the information. This is a free tool from Adam Machanic. A link to the tool is available in the comments of the code. You will need this tool installed on your target server.
The code is straightforward. sp_WhoIsActive is used to capture the information to a temporary table. The query_plan column type is changed to VARCHAR(MAX). After the information is written to the management server target table, the temporary table is deleted.
CREATE PROCEDURE [dbo].[sp_InsertPageLifeExpectancy]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @s VARCHAR(MAX)
EXEC sp_WhoIsActive @format_output = 0, @return_schema = 1, @get_plans = 1,
@schema = @s OUTPUT
SET @s = REPLACE(@s, '<table_name>',
'tempdb.dbo.tmpPageLifeExpectancy')
EXEC(@s)
EXEC sp_WhoIsActive @format_output = 0, @get_plans = 1,
@destination_table = 'tempdb.dbo.tmpPageLifeExpectancy'
ALTER TABLE tempdb.dbo.tmpPageLifeExpectancy ALTER COLUMN query_plan VARCHAR(MAX)
END
GO
CREATE PROCEDURE [dbo].[sp_DropPageLifeExpectancy]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tempdb.dbo.tmpPageLifeExpectancy
END
GO
The T-SQL code is available here.