question

ramya5 avatar image
ramya5 asked

Alwayson - blocking

I have a heavily used(updated) table on AlwaysOn Primary. My read queries on the same table are blocking on Secondary and are very slow. The reason I have found so far is with update stats. The stats are updated frequently on the table and causing blocking. Is there anyway to overcome this issue ? NOLOCK and isolation levels have no effect on secondary so that's not an option for me.
alwayson
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you elaborate a little more on what might be blocking and what might be getting blocked? I'm not clear on if your read queries are causing blocking on the secondary or if they are being blocked. If the read queries are blocking, what is it that they are blocking? With Read Committed Snapshot isolation on the secondary, they should only be able to block schema changes since they only take schema stability locks. If the read queries are being blocked, can you confirm what is blocking them? Check out the wait_resource, last_wait_type and blocking_session_id columns in sys.dm_exec_requests to confirm what is blocking and what is being blocked - https://msdn.microsoft.com/en-us/library/ms177648.aspx
1 Like 1 ·
Statistics, like the read queries, should only take schema stability locks, plus a separate lock on the statistics object associated with the table. Nothing that should block a read query or allow the stats update to be blocked by a read query. Can you confirm that the statistics are directly causing the blocking of the read queries or vice versa? See the sys.dm_exec_requests note above. If your replicas are synchronous, do you see any of this blocking bleed over into the primary node in the form of HADR_SYN_COMMIT waits? If so, are your I/O related waits in the operating system of the secondary replica? Check out Avg Disk Sec/Read and Avg Disk Sec/Write in performance monitor.
1 Like 1 ·
Thanks for replying. There is a table that gets 400 updates a second on AlwaysOn primary. Because it is always busy, the records are updated on secondary(asynchronus commit is my alwayson setting) and also the stats. There is a SPID (82 in my case) which is for AlwaysOn for that database. If I try to use the table in a query on Secondary - I see my session is blocking 82. But 82 takes precedence and causes blockings and deadlocks. In case of deadlock, my select query gets killed. All I see in the deadlock info is - UPDATEQPSTATS. But I can clearly see that 82 is trying to update stats and my session is also trying to update stats on the table at the sametime. I can route the queries to primary to resolve the issue but report queries are supposed to go to Secondary otherwise alwayson is not necessary. please advise.
1 Like 1 ·
that's good information, thanks. Do you have auto update statistics enabled? If you do, you might try also enabling Auto Update Statistics Asynchronously. Both options are documented here - https://msdn.microsoft.com/en-us/library/bb522682.aspx I'm guessing this behavior is intermittent? It sounds like the update statistics transaction that is being replayed from the primary node could be colliding with a temporary auto update statistics process on the asynchronous replica. Changing the auto stats update to be asynchronous will allow the auto update of the temporary statistics to happen independently of your select query so the query should no longer be affected by deadlocks in the statistics update routines.
1 Like 1 ·
I would be curious to know what SQL build you are running. Can you post the results from SELECT @@Version Also, you can list the temporary statistics in the read only replica with a query like this to compare with the deadlock XML (the deadlock XML will show the object_id and stats_id so you can confirm if you have an equivalent temporary statistic in the replica): select case when s.name like '%readonly_database_statistics' then 0 else 1 end as is_statistic_on_primary , s.name as statistics_name , SCHEMA_NAME(o.schema_id) as [schema_name] , o.name as table_name , c.name as column_name , * from sys.stats as s inner join sys.objects as o on s.object_id = o.object_id inner join sys.stats_columns as sc on s.object_id = sc.object_id and s.stats_id = sc.stats_id inner join sys.columns as c on sc.object_id = c.object_id and sc.column_id = c.column_id where s.is_temporary = 1 order by schema_name , table_name , c.column_id
1 Like 1 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.