--Script:16 SQL Server 2019 and Above. Find DB and Object name having Page_latch Wait type.
--You can use the following script as an input to powershell script and create report for all your sql instances.
SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
Declare @dbname varchar(max)
DECLARE @command nvarchar(4000);
DECLARE db_names CURSOR FOR SELECT name FROM sys.databases;
open db_names;
fetch next from db_names into @dbname;
while @@FETCH_STATUS = 0
Begin
fetch next from db_names into @dbname
Set @command = N'Use [' + @dbname +'];
declare @sqlinstancename varchar(max)
declare @sqldbname varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max),
sqldbname varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('''+'MachineName'+'''))
select @sqldbname='''+@dbname+'''
insert into @servername (sqlinstancename,sqldbname)
select @sqlinstancename, @sqldbname;
select SQLServerVersion.sqlinstancename as [SQLInstanceName], SQLServerVersion.sqldbname as [SQLDatabaseName],
object_name(page_info.object_id) AS [OBJECT_NAME], page_info.*
from sys.dm_exec_requests as d
cross apply sys.fn_PageResCracker(d.page_resource) as r
cross apply sys.dm_db_page_info(r.db_id, r.file_id, r.page_id, '''+'DETAILED'+''') as page_info
cross apply @servername as SQLServerVersion';
execute (@command);
--print N'Executed: ' + @command
End
close db_names;
deallocate db_names;
Go
Comentários