Long running query... cluprit SQL Response!

• 
  Hiya
  
  Do you know which are the long running queries?
  
  Out of interest how much job history do you have?
  
  Regards
  David

  david connell April 10, 2009 07:52

  - 0 +
• 

  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 April 10, 2009 09:11

  - 0 +
• 
  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 April 10, 2009 09:20

  - 0 +
• 
  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.

  dylanfinney April 10, 2009 09:34

  - 0 +
• 
  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

  david connell April 10, 2009 10:52

  - 0 +
• 

  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.

  dylanfinney April 10, 2009 11:35

  - 0 +
• 
  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

  david connell April 10, 2009 12:00

  - 0 +
• 

  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.

  dylanfinney April 10, 2009 12:42

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

  david connell April 10, 2009 12:53

  - 0 +
• 
  Any thoughts on this?

  dylanfinney April 14, 2009 12:17

  - 0 +
• 
  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

  dlkj April 15, 2009 06:24

  - 0 +
• 

  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 April 15, 2009 11:17

  - 0 +
• 
  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
  

  dylanfinney April 16, 2009 09:57

  - 0 +
• 
  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.

  dlkj April 16, 2009 10:06

  - 0 +
• 
  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.

  dylanfinney April 16, 2009 13:20

  - 0 +
• 
  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

  dlkj April 17, 2009 06:24

  - 0 +

Add comment

Please sign in to leave a comment.