Long-running test crashes SSMS

Easy unit testing for databases in SQL Server Management Studio

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Michelle Taylor, David Priddle

Long-running test crashes SSMS

Postby isme » Sun Dec 15, 2013 1:19 am

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 all
SELECT * FROM generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
(3 rows)


From that example I created a simple test:

Code: Select all
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: Select all
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.

Image

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

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.

Image

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 all
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: Select all
KILL 54;


SSMS crashes again!
Iain Elder, Skyscanner
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby Brian Donahue » Mon Dec 16, 2013 4:46 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby isme » Mon Dec 16, 2013 6:34 pm

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
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby isme » Mon Dec 16, 2013 7:14 pm

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
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby Brian Donahue » Fri Jan 17, 2014 10:56 am

I have logged development bug SQT-372 to have someone look into the cancellation problem.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby isme » Fri Jan 17, 2014 1:37 pm

Thanks, Brian!
Iain Elder, Skyscanner
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby David Priddle » Thu Feb 13, 2014 4:03 pm

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
David Priddle
 
Posts: 36
Joined: Tue Jan 28, 2014 10:22 am
Location: Red Gate Software, Cambridge

Postby isme » Wed Apr 02, 2014 4:53 pm

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 all
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
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby David Priddle » Thu Apr 03, 2014 11:18 am

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
David Priddle
 
Posts: 36
Joined: Tue Jan 28, 2014 10:22 am
Location: Red Gate Software, Cambridge


Return to SQL Test

Who is online

Users browsing this forum: No registered users and 0 guests