Performance improves after reboot

Question

Post reply

Performance improves after reboot

Viewing 10 posts - 106 through 114 (of 114 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24363 More actions · Answer 1

RVO - Wednesday, March 1, 2017 1:12 PM

Van Heghe Eddy - Wednesday, March 1, 2017 5:18 AM

Here ya go:

SELECT --HAGS.group_id ,         HAGS.primary_replica ,        HAGS.primary_recovery_health ,         HAGS.primary_recovery_health_desc ,        HAGS.secondary_recovery_health ,        HAGS.secondary_recovery_health_desc ,        HAGS.synchronization_health ,         HAGS.synchronization_health_desc ,       -- AGS.group_id ,         AGS.name ,        AGS.resource_id ,        AGS.resource_group_id ,         AGS.failure_condition_level ,         AGS.health_check_timeout ,         AGS.automated_backup_preference ,         AGS.automated_backup_preference_desc FROM    sys.dm_hadr_availability_group_states HAGS         INNER JOIN sys.availability_groups AGS ON HAGS.group_id = AGS.group_id

SELECT AGS.name AS AGGroupName ,
        AR.replica_server_name AS InstanceName ,
        HARS.role_desc ,
        DB_NAME(DRS.database_id) AS DBName ,
        DRS.database_id ,
        AR.availability_mode_desc AS SyncMode ,
        DRS.synchronization_state_desc AS SyncState ,
        DRS.last_hardened_lsn ,
        DRS.end_of_log_lsn ,
        DRS.last_redone_lsn ,
        DRS.last_hardened_time ,
        DRS.last_redone_time ,
        DRS.log_send_queue_size ,
        DRS.redo_queue_size
FROM    sys.dm_hadr_database_replica_states DRS
        LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
        LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id
        LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
                                                              AND AR.replica_id = HARS.replica_id
ORDER BY AGS.name ,
        AR.replica_server_name ,
        DB_NAME(DRS.database_id)

Wkr,
Eddy

Thank you very much, Van Heghe Eddy !
Your scripts did not render nicely
but if I'm correct there are two scripts.
First one didn't work for me
I probably don't have permissions to access
sys.availability_groups

But the second script worked.
Results below
Can you please teach me how make sense out of it?
What does it tell me?
How can I tell from it if it's getting behind. how badly behind . . .

What error did you get from the first query? The second one pulls from the same tables as the first one plus a few extras. If the first failed due to permissions, I am fairly certain the second one would too.
As for that second query, it shows you what is sync-ing and the state at which it has finished syncing. But an AG expert will likley give you a better answer; we don't use AG's here.

What is your connection speed between the primary and secondary?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

RVO SSCertifiable Points: 5484 More actions · Answer 2

RVO

SSCertifiable

Points: 5484

March 2, 2017 at 10:37 am

#1931268

No errors.
It's just when I query
sys.availability_groups
it returns records.

When I query sys.dm_hadr_availability_group_states
I get no records returned.
So I thought it's a permission thing.

Mr. Brian Gale SSC-Insane Points: 24363 More actions · Answer 3

RVO - Thursday, March 2, 2017 10:37 AM

No errors.
It's just when I query
sys.availability_groups
it returns records.

When I query sys.dm_hadr_availability_group_states
I get no records returned.
So I thought it's a permission thing.

It could be row level permission but I doubt anybody would set up row level security on a system table.

But I am confused then. When you run that first query you get no results but the second one gives you results? Is that what you are saying?
Both queries are getting data from sys.availability_groups and sys.dm_hadr_availability_group_states. They are just joined differently. One is left joined while the other is inner joined.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

RVO SSCertifiable Points: 5484 More actions · Answer 4

SELECT * FROM sys.availability_groups
--------------------------------------------------------------------------------------
0 rows

SELECT * FROM sys.availability_groups
-----------------------------------------------------------------------------
1 row

That is why
. . . . FROM sys.dm_hadr_availability_group_states HAGS
INNER JOIN sys.availability_groups AGS ON HAGS.group_id = AGS.group_id
returns 0 rows.

RVO SSCertifiable Points: 5484 More actions · Answer 5

RVO

SSCertifiable

Points: 5484

March 2, 2017 at 11:27 am

#1931293

If I get NULL
in secondary_recovery_health,
does it mean it's not healthy?

RVO SSCertifiable Points: 5484 More actions · Answer 6

RVO

SSCertifiable

Points: 5484

March 2, 2017 at 11:37 am

#1931304

Question guys.

Why FIG 1 results (look above for query)
returns me duplicate sets of databases?
I see 2 records for each database_id.

RVO SSCertifiable Points: 5484 More actions · Answer 7

I figured it out.
Apparently we have two SECONDARY servers (things get more interesting...)
Let's call it server 52 and server 57

So it looks like we use AlwaysOn High Avalabillity not really for Disaster Recovery solution
but more for data replication for various reporting subsystems.
Is it a good idea?

I thought with AlwaysOn High Availability solution,
your SECONDARY server is in a "sleep" mode, waiting to "get up and run" if PRIMARY fails.
Am I wrong?

I need to read more about this AlwaysOn technology and how to use it properly.

CC-597066 SSCommitted Points: 1626 More actions · Answer 8

This might give you better information because it breaks it down by database:

SELECT ar.replica_server_name, adc.database_name, ag.name AS ag_name, drs.is_local, CASE WHEN hags.primary_replica = ar.replica_server_name THEN 1 ELSE 0 END AS is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant, drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id INNER JOIN sys.dm_hadr_availability_group_states AS hags ON hags.group_id = ag.group_id ORDER BY ag.name, ar.replica_server_name, adc.database_name;

You can also just right click on each Availability Group in SSMS, select 'Show Dashboard' then choose the columns you want to display.

cphite SSCarpal Tunnel Points: 4231 More actions · Answer 9

RVO - Thursday, March 2, 2017 12:07 PM

I figured it out.
Apparently we have two SECONDARY servers (things get more interesting...)
Let's call it server 52 and server 57

So it looks like we use AlwaysOn High Avalabillity not really for Disaster Recovery solution
but more for data replication for various reporting subsystems.
Is it a good idea?

I thought with AlwaysOn High Availability solution,
your SECONDARY server is in a "sleep" mode, waiting to "get up and run" if PRIMARY fails.
Am I wrong?

I need to read more about this AlwaysOn technology and how to use it properly.

You can setup your secondaries to allow read-only access, and also to allow backups against them.

If you have a lot of users running reports you can take some stress away from your primary by having them run against the secondary.

I would not expect AOHA to cause performance issues on the primary, however if all of your servers are on the same hosts, same disks, for example it's possible that you're just thrashing the heck out of your storage system. You have two secondaries so basically all of the changes you make to the primary are being replicated - separately - to the two secondaries. Depending on your workload that could be a lot of data.

RVO SSCertifiable Points: 5484 More actions · Answer 10

That's what the plan was - to reduce stress on PRIMARY.
PRIMARY is server 51.
Users run reports against SECONDARY server 52 and server 57.
SECONDARY server 52 is not read-only for sure.

They are all VM's sharing the same CPU,RAM, SAN storage.
On the same Network.

A few days ago we had an issue with SUNGUARD I/O and AlwaysOn Data Movement stopped unexpectedly.
SECONDARY 52 started lagging behind. By the time DBA resumed Data Movement we were 152 GB behind.
SECONDARY server 52 switched to "Recovery" mode. Not accessible. Users could not run reports.
Error: PRIMARY database not accessible.
PRIMARY TLog grew from 162 to 490 GB.
We noticed performance slight impact on PRIMARY server 51.

Looks like AlwaysOn is only fine until your SECONDARY gets behind. Then - it's a lot of problems . .. .
And "AlwaysOn" quickly turns into "FrequentlyOFF" solution.