-
Notifications
You must be signed in to change notification settings - Fork 93
Duplicate information collected in Perfstats Snapshot and MiscPssdiagInfo scripts #162
Description
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