Skip to content
This repository was archived by the owner on Oct 20, 2025. It is now read-only.
This repository was archived by the owner on Oct 20, 2025. It is now read-only.

Duplicate information collected in Perfstats Snapshot and MiscPssdiagInfo scripts #162

@PiJoCoder

Description

@PiJoCoder

Do you want to request a feature or report a bug?
Enhancement

What is the current behavior?
Much of the information in MiscPssdiaginfo.sql below regarding AG is already collected in SQL Server Perf Stats Snapshot.sql. We must remove duplicates and decide which script should collect it. I am leaning towards having MiscPssdiagInfo.sql keeping the AG info, as the other one is supposed to be geared towards Performance (thus Perf stats).

IF @@MICROSOFTVERSION >= 184551476 --11.0.2100
begin
print ''
RAISERROR ('--Hadron Configuration--', 0, 1) WITH NOWAIT
SELECT
ag.name AS ag_name,
ar.replica_server_name ,
ar_state.is_local AS is_ag_replica_local,
ag_replica_role_desc =
CASE
WHEN ar_state.role_desc IS NULL THEN N''
ELSE ar_state.role_desc
END,
ag_replica_operational_state_desc =
CASE
WHEN ar_state.operational_state_desc IS NULL THEN N''
ELSE ar_state.operational_state_desc
END,
ag_replica_connected_state_desc =
CASE
WHEN ar_state.connected_state_desc IS NULL THEN
CASE
WHEN ar_state.is_local = 1 THEN N'CONNECTED'
ELSE N''
END
ELSE ar_state.connected_state_desc
END
--ar.secondary_role_allow_read_desc
FROM
sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states AS ar_state
ON ar.replica_id = ar_state.replica_id;

print ''
RAISERROR ('--sys.availability_groups--', 0, 1) WITH NOWAIT
select * from sys.availability_groups

print ''
RAISERROR ('--sys.dm_hadr_availability_group_states--', 0, 1) WITH NOWAIT
select * from sys.dm_hadr_availability_group_states

print ''
RAISERROR ('--sys.dm_hadr_availability_replica_states--', 0, 1) WITH NOWAIT
select * from sys.dm_hadr_availability_replica_states

print ''
RAISERROR ('--sys.availability_replicas--', 0, 1) WITH NOWAIT
select * from sys.availability_replicas

print ''
RAISERROR ('--sys.dm_hadr_database_replica_cluster_states--', 0, 1) WITH NOWAIT
select * from sys.dm_hadr_database_replica_cluster_states

print ''
RAISERROR ('--sys.availability_group_listeners--', 0, 1) WITH NOWAIT
select * from sys.availability_group_listeners

print ''
RAISERROR ('--sys.dm_hadr_cluster_members--', 0, 1) WITH NOWAIT
select * from sys.dm_hadr_cluster_members
end
go

What is the expected behavior?
Avoid collecting the same information twice. Especially using select * because this may not import well in SQL nexus

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions