Red Gate forums :: View topic - Long-running test crashes SSMS
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Test
SQL Test forum

Long-running test crashes SSMS

Search in SQL Test forum
Post new topic   Reply to topic
Jump to:  
Author Message
isme



Joined: 12 Jun 2012
Posts: 78
Location: Edinburgh

PostPosted: Sun Dec 15, 2013 1:19 am    Post subject: Long-running test crashes SSMS Reply with quote

I discovered two related issues with SQL Test today.

1. SQL Test can't stop a test in progress.
2. A runaway test eventually SSMS crashes.


Can you make it safe to stop a a test in progress, and provide a way to do it in the SQL Test interface?

Here's an example to illustrate the problem.

Say you're trying to roll your own version of PostgreSQL's generate_series function.

Code:

SELECT * FROM generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
(3 rows)


From that example I created a simple test:

Code:

CREATE PROCEDURE [PGDocTests].[test can generate simple series]
AS
BEGIN
  --Act
  CREATE TABLE #actual (
    num INT
  );
 
  INSERT INTO #actual
  SELECT *
  FROM generate_series(2, 4);

  --Assert
  SELECT TOP (0) *
  INTO #expected
  FROM #actual;

  INSERT INTO #expected
  VALUES (2), (3), (4);

  EXECUTE tSQLt.AssertEqualsTable
    @Actual = N'#actual',
    @Expected = N'#expected';
END;


To pass the test, you might naively try something like this:

Code:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION generate_series(@start INT, @stop INT)
RETURNS TABLE
AS
RETURN
WITH rowgen (n) AS (
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
  FROM sys.all_columns AS a
  CROSS JOIN sys.all_columns AS b
)
SELECT n
FROM rowgen;


Joining sys.all_columns to itself without a filter generates over five million rows in a new database.

This should fail because the test expects just three output rows.

Before my machine can materialize the output, SQL Test triggers an internal timeout and then crashes.



The Red Gate error reporting tool showed this message just before SSMS crashed:

Quote:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.



Before the crash, I tried to stop the test using the interface, but the test kept going.



SSMS also crashes if you try to stop the test more forcefully.

I let the test run again for a minute then queried the user-level processes to find the test:

Code:

SELECT spid, program_name, cmd
FROM sys.sysprocesses
WHERE spid > 50;

spid   program_name                                   cmd
------ ---------------------------------------------- ----------------
51     Microsoft SQL Server Management Studio         AWAITING COMMAND
52     Microsoft SQL Server Management Studio         AWAITING COMMAND
53     Microsoft SQL Server Management Studio         AWAITING COMMAND
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
54     Microsoft SQL Server Management Studio         SELECT INTO     
55     Microsoft SQL Server Management Studio - Query SELECT         
56     Red Gate Software Ltd SQL Prompt 5.3.4.1       AWAITING COMMAND

(15 row(s) affected)


Session 54 must be the test runner. It is running parallel SELECT INTO processes, while everything else is idle.

Let's kill it!

Code:

KILL 54;


SSMS crashes again!
_________________
Iain Elder, Skyscanner
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6666

PostPosted: Mon Dec 16, 2013 4:46 pm    Post subject: Reply with quote

Hi Iain,
Thanks for pointing this out and I will bring it to someone's attention, but it looks like a situation we may not be able to work around.

The "stop tests" button works by setting a flag telling SQL Test to cancel execution at the next test task. It can't cancel a query that is running whilst in-progress, unfortunately. The query you have given it to run exceeds the maximum execution time, which I believe is 10 minutes and can't be extended.

SQL Test's process, according to the who query, is SPID 56. Cancelling SPID 54 is killing a generic SQL process, so there is no control over what will happen when you try to kill the SPID. It seems to cause an "Internal Binder Error" in the SSMS process.

So unfortunately I don't have any advice to either a. prevent the problem, b. make the stop button cancel a running query execution (without taking out SSMS) or c. prevent SSMS from crashing in either case. Best advice I have is to avoid this type of long-running query.
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 78
Location: Edinburgh

PostPosted: Mon Dec 16, 2013 6:34 pm    Post subject: Re: Reply with quote

Thanks, Brian.

Are you referring to the output I posted?

Sounds like you might be referring to a different set of spids. The exact numbers will vary from time to time.

Brian Donahue wrote:
SQL Test's process, according to the who query, is SPID 56. Cancelling SPID 54 is killing a generic SQL process, so there is no control over what will happen when you try to kill the SPID. It seems to cause an "Internal Binder Error" in the SSMS process.


Not sure what you mean by "generic SQL process".

In my example, spid 54 must be the SQL Test process. Everything else is idle ("AWAITING COMMAND") while the test is running.

The program_name column contains nothing like "SQL Test".

SQL Test apparently uses the SSMS default program_name ("Microsoft SQL Server Management Studio") instead of identifying itself clearly.

The program_name of SPID 56 is SQL Prompt, not SQL Test.

I don't think SQL Prompt is causing any issue here. I should have disabled it to make the output clearer.

SSMS is generally quite robust, but it is not protected from crashes caused by add-ins.

To clarify, it looks like SQL Test is crashing and taking SSMS with it.

SQL Prompt is more robust. It doesn't crash SSMS if its spid is killed. It just stops working until you turn it off and on again.

Could you make SQL Test behave the same way?
_________________
Iain Elder, Skyscanner
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 78
Location: Edinburgh

PostPosted: Mon Dec 16, 2013 7:14 pm    Post subject: Reply with quote

Brian Donohue wrote:

It looks like a situation we may not be able to work around. [...] [SQL Test] can't cancel a query that is running whilst in-progress, unfortunately.


SQL Server does not impose that behavior. You can work around it by changing how SQL Test manages its sessions.

SSMS allows you to cancel a query that is in progress.

You can press the red stop button, or close the window. If you close the window, you have to answer "Yes" when it asks "Do you want to cancel the query?"

It's not a major issue, but I would be happier knowing that my careless coding can't crash my client.
_________________
Iain Elder, Skyscanner
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6666

PostPosted: Fri Jan 17, 2014 10:56 am    Post subject: Reply with quote

I have logged development bug SQT-372 to have someone look into the cancellation problem.
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 78
Location: Edinburgh

PostPosted: Fri Jan 17, 2014 1:37 pm    Post subject: Reply with quote

Thanks, Brian!
_________________
Iain Elder, Skyscanner
Back to top
View user's profile Send private message
David Priddle



Joined: 28 Jan 2014
Posts: 36
Location: Red Gate Software, Cambridge

PostPosted: Thu Feb 13, 2014 4:03 pm    Post subject: Reply with quote

Hi Iain,

I'm pleased to announce that we have a beta of SQL Test out that should fix all of the issues listed in your post:

http://www.red-gate.com/MessageBoard/viewtopic.php?p=67784

Please let me know if you find any issues with this build.

Best regards,

David
Back to top
View user's profile Send private message Send e-mail
isme



Joined: 12 Jun 2012
Posts: 78
Location: Edinburgh

PostPosted: Wed Apr 02, 2014 4:53 pm    Post subject: Reply with quote

SQL Test is a lot more robust with this update.

It remains stable when the long test runs.

There are no messages about timeouts in the GUI.

The stop button does indeed stop a test in progress.

The time to stop the test seems to depend on how long the test has been running. That's just normal rollback behavior, so that's okay.

Because the program name is "Red Gate Software Ltd SQL Test 1.5.0.79", it's much easier to debug now.

To find all the SQL Test sessions, you can use a query like this:

Code:

SELECT spid, program_name, cmd
FROM sys.sysprocesses
WHERE program_name LIKE '%SQL Test%';


Sorry for my late reply - been away from development projects for a while.

Thanks for tackling this, David!
_________________
Iain Elder, Skyscanner
Back to top
View user's profile Send private message
David Priddle



Joined: 28 Jan 2014
Posts: 36
Location: Red Gate Software, Cambridge

PostPosted: Thu Apr 03, 2014 11:18 am    Post subject: Reply with quote

Hi Iain,

Thanks for responding! It's great to get feedback for our work, glad to hear it's all working ok!

Please keep us informed!

Best regards,

David
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group