Long running query... cluprit SQL Response!

dylanfinney

Has anyone else noticed a lot of long running query alerts being thrown because of the SQL Response queries? Anyone have a solution to stop this behavior, or less preferable filter it?

david connell

Hiya

Do you know which are the long running queries?

Out of interest how much job history do you have?

Regards
David

dylanfinney

david connell wrote:

Hiya

Do you know which are the long running queries?

Out of interest how much job history do you have?

Regards
David

xp_readerrorlog (thought that may indicate more of a SQL Server bottleneck issue than application) is the typical one that I see when the query fragment is available, however there are lots of other warnings being thrown without that fragment data so I'm not sure. I could turn on tracing if that will help.

I've been using SQL Response for a little over a month now, it's been a consistent problem.

dylanfinney

Here's another one. Latest fragment.

DBCC TRACEON WITH NO_INFOMSGS
CREATE TABLE #dbccstat
(
	TraceFlag INT,
	Status INT,
)
INSERT INTO  #dbccstat EXEC('DBCC TRACESTATUS(1204) WITH NO_INFOMSGS')
DECLARE @status INT
SELECT @status = Status FROM #dbccstat
IF @status = 0 
BEGIN
	DBCC

dylanfinney

Oops, found the problem (at least on xp_readerrorlog). I've got an application that's log in/out happy. My current SQL Server log is almost 500k records in length.

david connell

Regarding the error log I would use sp_cycle_errorlog to reduce it back down again.
Checkout http://msdn.microsoft.com/en-us/library/ms182512.aspx for more information on that.
Regarding the trace flags, not too sure, need to investigate further. I'll ask DKJ to look into it.
Regards
David

dylanfinney

david connell wrote:

Regarding the error log I would use sp_cycle_errorlog to reduce it back down again.
Checkout http://msdn.microsoft.com/en-us/library/ms182512.aspx for more information on that.
Regarding the trace flags, not too sure, need to investigate further. I'll ask DKJ to look into it.
Regards
David

Thanks for that, I did cycle the log file after I found that application. Helped considerably with the xp_readerrorlog problem. The other still remains however. Let me know if there is any additional information that I can provide you.

david connell

Hiya,
Out of interest, do you have a lot of SQL job history too? This may also be worth trimming down to a more managable size.
Regards David

dylanfinney

david connell wrote:

Hiya,
Out of interest, do you have a lot of SQL job history too? This may also be worth trimming down to a more managable size.
Regards David

Across the servers that I was seeing the behavior the job history is what I would consider reasonable. We've kept the job history log to less than 1000 rows and no more than 100 rows per job.

david connell

Hiya
Me too I would consider that to be reasonable....
Perhaps DLKJ has a view on this?
Regards
David

dylanfinney

Any thoughts on this?

dlkj

Hi Dylan,

I take it from the forum thread that you are regularly seeing two types of long running queries that are coming from SQL Response

One relating to xp_readerrorlog - this has now been resolved by truncating the error log One relating to DBCC TRACESTATUS - which is still occuring

I've had a look at the 2nd query and don't see anything that should be taking a long time to execute.

Could you run the following SQL Queries on the problem server and let me know how log each one takes to execute

DBCC TRACESTATUS(1204) WITH NO_INFOMSGS

CREATE TABLE #dbccstat
(
	TraceFlag INT,
	Status INT,
)
INSERT INTO  #dbccstat EXEC('DBCC TRACESTATUS(1204) WITH NO_INFOMSGS')
DECLARE @status INT
SELECT @status = Status FROM #dbccstat
IF @status = 0 
BEGIN
	DBCC TRACEON( 1204, -1 ) WITH NO_INFOMSGS;
END
DROP TABLE #dbccstat

Cheers

dylanfinney

dlkj wrote:

Hi Dylan,

I take it from the forum thread that you are regularly seeing two types of long running queries that are coming from SQL Response

One relating to xp_readerrorlog - this has now been resolved by truncating the error log One relating to DBCC TRACESTATUS - which is still occuring

The error log reading was due to extremely large log files. I had been logging all connections (successful and failed) and one of my 3rd party applications is log in/out happy so the log files grew to be very large. I cycled the logs and that problem has ceased.

I ran the queries that you provided, had to change it to execute (code below, missing columns in temp table). It was under a second on each of the servers that I see the problem.

I have several more instances of long running query alerts coming from SQL Response, however I can't get a query fragment from any of the alerts!

I will keep looking at them today and post another query fragment once I've found an alert with the data.

CREATE TABLE #dbccstat
(
   TraceFlag INT,
   Status INT,
   GLOBAL INT,
   Session INT
)
INSERT INTO  #dbccstat EXEC('DBCC TRACESTATUS(1204) WITH NO_INFOMSGS')
DECLARE @status INT
SELECT @status = Status FROM #dbccstat
IF @status = 0
BEGIN
   DBCC TRACEON( 1204, -1 ) WITH NO_INFOMSGS;
END
DROP TABLE #dbccstat

dylanfinney

Sorry it took almost an entire day to get one to come through with a code snippit. The query that this fragment belongs to took over 15 seconds to run.

(@table int,@index int)select GETDATE(); DBCC SHOWCONTIG ( @table, @index ) WITH TABLERESULTS, FAST

dlkj

Hi Dylan,

that would be SQL Response checking the index fragmentation of your databases. I would expect that this might take more than 15 seconds to execute.

dylanfinney

Found something interesting.

Started a trace on one of the servers that encounters the long running queries from SQL Response. The trace captured a statement and some errors being returned.

Here's the code:

exec sp_reset_connection

DBCC TRACEON WITH NO_INFOMSGS

CREATE TABLE #dbccstat
(
TraceFlag INT,
Status INT,
)
INSERT INTO  #dbccstat EXEC('DBCC TRACESTATUS(1204) WITH NO_INFOMSGS')
DECLARE @status INT
SELECT @status = Status FROM #dbccstat
IF @status = 0
BEGIN
DBCC TRACEON( 1204, -1 ) WITH NO_INFOMSGS;
END

DROP TABLE #dbccstat

The first error is barking that it can't find sp_reset_connection. The second error appears to be the same but I would expect an error on the insert because the table definition does not match the output of the DBCC command.

dlkj

Hi Dylan,

The query should execute without errors, but it should only be executed on a SQL 2000 instance not a sql server 2005 or 2008 instance.

exec sp_reset_connection is an internal system call used by the .net sql server connection library to manage the sql connection pool.

Cheers,
--
Daniel