Options
SQL Monitoring creating deadlocks
jonwilk
Posts: 32
Hi
I've started to get deadlocks on one of our servers, and after further investigation it seems redgate sql monitor is causing them. It looks like it's because of temp tables. Here's the output from SQL monitor for the deadlock
SQL query fragment:
USE [tempdb] ;
SET NOCOUNT ON ;
DECLARE @M INT ;
DECLARE @N INT ;
SET @M = 50 ;
SET @N = 15 ;
IF OBJECT_ID('tempdb..[redgate_sqlmonitor_topqueries_b_xxxxxxxxxxx.co.uk.(local)_sqlmonitor]', 'U') IS NULL
BEGIN
CREATE TABLE [redgate_sqlmonitor_topqueries_b_xxxxxxxxx.co.uk.(local)_sqlmonitor] (
sql_handle varbinary(64) NOT NULL ,
plan_handle varbinary(64) NOT NULL ,
statement_start_offset bigint NOT NULL,
statement_end_offset bigint NOT NULL,
creation_time datetime NOT NULL,
last_execution_time datetime NOT NULL,
execution_count bigint NOT NULL,
total_worker_time_ms bigint NOT NULL,
total_logical_writes bigint NOT NULL,
total_logical_reads bigint NOT NULL,
total_physical_reads bigint NOT NULL,
total_elapsed_time_ms bigint NOT NULL
)
CREATE CLUSTERED INDEX [redgate_sqlmonitor_topqueries_b_index_xxxxxxx.co.uk.(local)_sql
I've started to get deadlocks on one of our servers, and after further investigation it seems redgate sql monitor is causing them. It looks like it's because of temp tables. Here's the output from SQL monitor for the deadlock
SQL query fragment:
USE [tempdb] ;
SET NOCOUNT ON ;
DECLARE @M INT ;
DECLARE @N INT ;
SET @M = 50 ;
SET @N = 15 ;
IF OBJECT_ID('tempdb..[redgate_sqlmonitor_topqueries_b_xxxxxxxxxxx.co.uk.(local)_sqlmonitor]', 'U') IS NULL
BEGIN
CREATE TABLE [redgate_sqlmonitor_topqueries_b_xxxxxxxxx.co.uk.(local)_sqlmonitor] (
sql_handle varbinary(64) NOT NULL ,
plan_handle varbinary(64) NOT NULL ,
statement_start_offset bigint NOT NULL,
statement_end_offset bigint NOT NULL,
creation_time datetime NOT NULL,
last_execution_time datetime NOT NULL,
execution_count bigint NOT NULL,
total_worker_time_ms bigint NOT NULL,
total_logical_writes bigint NOT NULL,
total_logical_reads bigint NOT NULL,
total_physical_reads bigint NOT NULL,
total_elapsed_time_ms bigint NOT NULL
)
CREATE CLUSTERED INDEX [redgate_sqlmonitor_topqueries_b_index_xxxxxxx.co.uk.(local)_sql
Comments
Due to the huge amounts of data that SQL Monitor can process, especially for elements like the top queries, there is always the risk of deadlocks at busy times. During development the development team realised that to prevent deadlocks would cost performance in the tool. So they instead implemented some functionality that makes sure that the deadlocks are as short as possible and that there is no loss of collected data. They have also been able to ensure that the UI should show no ill effects when a deadlock is encountered.
The deadlock that you have quotes looks to be one of these 'normal' deadlocks that can occur at busy times. If they are occurring very frequently then this can be an indication of a performance problem. But in themselves they shouldn't be anything to worry about.
I hope that this makes sense. If you have any questions then please let me know.
It's a monitoring tool. It should be as free of side-effects as possible, but instead, it is generating deadlocks in what is effectively a shared resource (tempdb). Those deadlocks are bleeding over into non-SQLMonitor databases because of the tempdb contention. On our production instances, the deadlocks caused by SQL Monitor managed to cripple our servers and we had to disable monitoring of those servers. Our customers noticed. We got a lot of egg on our faces.
Frankly, it's a monitoring tool. Me having to wait a few extra seconds (or even minutes) for us to know what our top queries are isn't going to cause any issues. Not getting a graph updated in precise realtime isn't going to cause any issues.
You guys have prioritized the wrong features. I don't care nearly as much about your UI or your product being 100% realtime as I care about your product not interfering with my databases and my applications and my servers. Your product is by far the least important database on my server as far as I am concerned. It doesn't make me any money and isn't directly related to supporting my customers as long as everything is running smoothly. It should know its place and avoid impacting anything else on the server at all costs, even at the cost of the speed and responsiveness of your tool.
I have currently had to uninstall the monitoring from our production instances because it flat out broke our servers. Even on heavily loaded servers, that should never be necessary.
At the very least, allow us to disable the "high overhead" things on production instances. I only care about those things as a forensic tool anyway. For production, I just want to be made aware of space issues, deadlocks, and critical items and that's it - I only look at top queries on a periodic basis, and frankly don't consider them to be things that SHOULD be alerted on.
Ours is a heavy server load, but i should'nt be seeing redgate errors on their.
Is there way to turn this top query aspect off as it's something we don't use anyway?
Also the deadlocks fill my sql log so simply ignoring them is very difficult to do.
We could do with a quick fix for this please?
thanks
Can you therefore, both of you, email support@red-gate.com to raise a support ticket with the following information:
- The SQL Monitor error logs. Navigate to Configuration > About and select Retrieve all logs.
- an example deadlock alert. I am specifically interested in the Deadlock Graph as it will be interesting to see the processes involved and the resources.
- confirmation about whether these are new issues, or you saw the with version 3 too.
The fact that you are DEFENDING your product having deadlocks has made me reconsider the product entirely. You've lost a customer today.
The SQL Monitor team are currently investigating this issue, and unfortunately we are not able to reproduce any of the reported blocks or deadlocks. The one reported case we have available is one of SQL Monitor deadlocking itself, and there is no data on SQL Monitor beginning to affect other Databases on the system, the way rcinator mentioned. If anybody else experiences these issues, any information you could provide on the following questions could be very helpful.
Have you noticed any instances of SQL Monitor deadlocking other, non-SQL Monitor queries on the server?
Do you also observe other deadlocks unrelated to SQL Monitor on the server? If other types of deadlocks abound on the SQL Server instance, it would point to a more general issue with the instance.
How frequent are these deadlocks? You've mentioned you are seeing multiple of them.
While working with a Demo installation of the latest version. I am tracking transactions in all of our databases. I noted an increase in the TempDB transactions after a couple days of running, totaling 5 - 6 million a day. When I researched the default trace I noticed the product was very busy in TempDB. I paused the monitoring and the TempDB transactions went back to our nominal value of ~100 transactions per second.
So, I am not sure I can recommend the purchase of this product with the impact it has on the monitored databases. I realize all monitoring has some impact, but this product appears to be thrashing TempDB fairly hard. Is there something I did incorrectly or is this by design?
It did graph the impact of the product nicely.
Thanks,
John
SQL Monitor does use a fair number of transactions. We have found that a larger number of smaller transactions has a lower impact on the monitored systems, so that's the side we tend to err on when developing our monitoring routines. However, 5-6M transactions per day on the monitored system sounds extreme!! I believe something unusual was going on there. It might be worth opening a support ticket if you wish to drill into this.
Obviously, it's not possible to gauge load by any one metric (were it that simple!), but if you're particularly concerned with your transaction rate on TempDB, support can also help you to reduce the number of transactions SQL Monitor performs.
Hope that helps
Daniel Rothig
Product Manager for SQL Monitor