Options
Long-running test crashes SSMS
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.
From that example I created a simple test:
To pass the test, you might naively try something like this:
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:
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:
Session 54 must be the test runner. It is running parallel SELECT INTO processes, while everything else is idle.
Let's kill it!
SSMS crashes again!
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.
SELECT * FROM generate_series(2,4);
generate_series
-----------------
2
3
4
(3 rows)
From that example I created a simple test:
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:
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:
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:
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!
KILL 54;
SSMS crashes again!
Iain Elder, Skyscanner
Comments
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.
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.
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?
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.
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
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:
Sorry for my late reply - been away from development projects for a while.
Thanks for tackling this, David!
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
Do you confirm that it remains stable with a long test runs ?
i received many messages about timeouts in the GUI...and the stop button does not work in test progress...
Can you tell me witch version are you using ??
Best regards,
A.Amine,
Senior Data Base Administrator
https://www.الرقية-الشرعية.com