Long running query... cluprit SQL Response!

dylanfinneydylanfinney Posts: 26
edited April 17, 2009 6:24AM in SQL Monitor Previous Versions
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?
· Share on Twitter

Comments

  • david connelldavid connell Posts: 172 Silver 1
    Hiya

    Do you know which are the long running queries?

    Out of interest how much job history do you have?

    Regards
    David
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    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.
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    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
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    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.
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    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
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    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.
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    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
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    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.
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    Hiya
    Me too I would consider that to be reasonable....
    Perhaps DLKJ has a view on this?
    Regards
    David
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    Any thoughts on this?
    · Share on Twitter
  • dlkjdlkj Posts: 151
    edited April 7, 2010 10:58AM
    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
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    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
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    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
    · Share on Twitter
  • dlkjdlkj Posts: 151
    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.
    · Share on Twitter
  • dylanfinneydylanfinney Posts: 26
    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.
    · Share on Twitter
  • dlkjdlkj Posts: 151
    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
    · Share on Twitter
Sign In or Register to comment.