erikej/sqlquerystress

SQL query stress simulator created by Adam Machanic http://dataeducation.com/sqlquerystress-the-source-code/

SqlQueryStress

SQL query stress simulator created by Adam Machanic

Getting started guide

Introduction to SQL Query Stress

Download

Install from the Microsoft Store

You can download a .zip file with the latest daily build from GitHub releases

Notice that you must have the .NET 6 Desktop Runtime installed.

Release notes

sqlstresscmd

Cross platform command line tool using the same load engine, see dedicated readme

Contributing

Any and all contributions are welcome! Please see the full contributing guide for more details.

High DPI issue

The font may look strange on high DPI screens, use this tip to fix it.

sqlquerystress's Issues

Statistic creation bug

Apparently there are some bugs around the statistics creation options not getting properly turned off when you turn them off.

Elapsed Time doesn't line up with Actual seconds/iteration

I am seeing some inconsistency with the Elapsed Time.

I have a query that I am comparing an old vs a new version. I was expecting the new version to be significantly faster.
SQLQueryStress wasn't showing that when comparing the Elapsed Time.

I dug into it further and I was able to use extended events to verify the AVG execution time for the query is significantly faster (2.5-3.5x). This is shown in the Actual Seconds/Iteration (Avg) but is not reflected in the Elapsed Time. The Elapsed Time shows about the same to slightly slower when compared with the old.

My settings are as follows:

The code is run inside a transaction.

In every respect the new version should have faster elapsed time.
I can't share the query exactly but the skeleton is this:
OLD

begin tran
DECLARE @var BINARY(8);
EXEC dbo.stored_procedure_old @var = @var OUTPUT
rollback tran

NEW

begin tran
DECLARE @var BINARY(8);
EXEC dbo.stored_procedure_old @var = @var OUTPUT
rollback tran

I think the main point here is that the NEW stored procedure runs significantly fast but that isn't reflected in the elapsed time.

If you have an idea of what part of the SQLQueryStress code is causing the problem I can look at the code in context of my stored procedure and see if I can't identify anything specific since I can't share the stored procedure code.

---

Edit:

Some more context -
The new stored procedure basically was made to be parallel run where the old stored procedure was completely serial. The old stored procedure had to wait for any other instance of it to finish before it could run. The new one is parallel up to a partitioned amount.

Publish via Store?

http://ift.tt/2wD8NjL

Cancel button does not work

after clicking cancel button ,it 's not working and it always put UI in readonly mode.

Log unatttended results to a file!

Setting more than 2 threads does not work and no error message

Running the SQLQueryStress.exe

I set my query to SELECT @@Version; and set the threads to 2. It works fine. I then update the threads to 5 as shown and the program fails to execute but gives no error message or indication that it failed.

Good:

Bad:

Add "Active Threads" to the UI

While running a load that involves a lot of threads, it's interesting to know how many threads are "active" at a given time. I'll usually run sp_WhoIsActive to see how many active connections there are, but this causes me to switch back and forth a lot between apps.

Knowing the number of active threads would also be useful from a user experience perspective when canceling a load. It would essentially act as a "progress bar," watching the count go down to 0.

To that end, I'd like to see the number of "Active Threads" (I think this would be _threads - _finishedThreads during a given load) available in the UI. One option would be the bottom right corner, simply because there's an empty space there now. Somewhat lazy mockup here 😃

Ability to connect to contained databases

The tool is unusable for contained databases (for example, Azure databases) if the account does not have server privileges. This is caused by the UI behavior prompting the database list refresh and moving focus to the server field if the database selection dropdown has no items.

Profiler Events on Azure?

Not sure if this is an issue or other.
When running on an AZURE SQL DB, see no events at all for the query runs in the SQL Server profiler (Azure Data Studio)
Of course, when running on local SQL DB, events are seen as expected in its Profiler

App issue?

Exception: Value is not valid for 'Value'. 'Value' should be between 'minimum' and 'maximum'.

I receive this kind of uncatching exception when I run many iterations

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.ArgumentOutOfRangeException: Value of '142' is not valid for 'Value'. 'Value' should be between 'minimum' and 'maximum'.
Parameter name: Value
at System.Windows.Forms.ProgressBar.set_Value(Int32 value)
at SQLQueryStress.Form1.UpdateUi() in D:_Samples\SQLServer\SqlQueryStress-master\src\SQLQueryStress\Form1.cs:line 414
at SQLQueryStress.Form1.mainUITimer_Tick(Object sender, EventArgs e) in D:_Samples\SQLServer\SqlQueryStress-master\src\SQLQueryStress\Form1.cs:line 283
at System.Windows.Forms.Timer.OnTick(EventArgs e)
at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

...
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Temporal Table IO Reads not being reflected

LoadEngine.zip

Hi There,

I've been using this tool for some time and I noticed that when a #temp table is created within a SP or SQL Query the IO reads generated by these objects are not being counted by the application due to the fact that the regex it has to collect that kind of information only works for normal tables but not for #temp table. Steps to reproduce:

Create a SP or SQL query which contains a #Temp table and you'll notice that the read generated are not counted by the app.

I used the http://www.statisticsparser.com/ to compare against. I have a fix for this but not sure what's the process to check in the code, minor changes. Please take a look at the changes at https://github.com/jthalliens/src/blob/master/SQLQueryStress/LoadEngine.cs line 385 & 334

Unable to build the project.

I'm using Visual Studio 2015 Enterprise edition...

Process to Recreate:

1. Forked the repo
2. Did a git clone to my workstation
3. Opened the solution in Visual Studio 2015
4. Right-Clicked the project "SQLQueryStress" and chose "Build"

This error occurs in the DEBUG configuration (not the RELEASE configuration)

Errors Received:
Severity Code Description Project File Line
Error CS7036 There is no argument given that corresponds to the required formal parameter 'value' of 'Dictionary<string, int>.Add(string, int)' SQLQueryStress C:_SourceControl\NHGIT\SqlQueryStress\src\SQLQueryStress\Form1.cs 197
Error Unable to find manifest signing certificate in the certificate store. SQLQueryStress

Screen shot:

Also received 1 warning:
Severity Code Description Project File Line
Warning Unable to find code signing certificate in the current user’s Windows certificate store. To correct this, either disable signing of the ClickOnce manifest or install the certificate into the certificate store. SQLQueryStress

Screen shot:

Building the solution doesn't work either.

Add check for Updates feature

Improved query editor

Implement a better query editor. The control I used is bare bones to the max and does no one any favors.

http://stackoverflow.com/questions/4255398/wpf-avalonedit-sql-xhsd-request

No repository license

Hello,

Would it be possible to add an open source license for this repository? Without one specified, we aren't legally permitted to modify or use your code.

Add ability to speficy additional connection parameters

No textbox to type a query

Hi, I just donwload and compile, the main screen don´t show the left textbox do type a T-SQL.
and if I try run whitout nothing a exception appear: "ExecuteNonQuery: CommandText property not initialized"

AppVeyor latest build not working

The home page of the project has this link as the latest build from AppVeyor:

https://ci.appveyor.com/api/projects/ErikEJ/SqlQueryStress/artifacts/SqlQueryStress.zip?branch=master

But it's 404'ing.

Move Documentation from Word to github

Hi,

I was just looking for some documentation for the tool and thought Brent Ozars blog might be the best place. However, there is actually Documentation hidden in the src folder https://github.com/ErikEJ/SqlQueryStress/blob/master/src/SQLQueryStress/SQLQueryStress_Documentation.doc .

I would suggest to remove the file and move the content to the github wiki. For an example see https://github.com/d3/d3/wiki. However, I do not know what permission one needs to edit the SqlQueryStress wiki.

regards
Felix

Download marked as "Virus detected"

When downloading the release I receive "Virus detected" message from Chrome in windows.

Crash when settings file has a database that doesn't exist as the default

If you save your settings to a file, and then drop the database you were testing in, SQS will crash when you try to change the database in the connection dropdown. You can edit the settings file to work around.

Multiple Iterations/Threads causes "Connection was open" Exceptions

I see that conn.Close(); was removed from LoadEngine.cs. That causes exceptions due to open connections.

This can be fixed by adding conn.Close(); at ln445 of LoadEngine.cs (I think). I'd prefer for smarter minds than mine to verify and implement the fix.

repeatedly hitting go cancel go cancel can cause worker threads to deadlock

If you continually press go and cancel and go and cancel, randomly waiting a length of time between go and cancel eventually one or more of the worker threads will deadlock, typically in an exception block as they're throwing as part of canceling. I believe calling abort on the threads causes a race condition - if they are in a specific state, instead of terminating they deadlock. When this happens the background worker can never exit because theThread.Join hangs waiting for the deadlock threads and the Go and Cancel buttons never enable. The fix #85 works so that you can now exit cleanly.

File-save doesn't include the delay

Howdy sir! Small bug - set up a load test with a delay between queries specified (say 100ms), then click File, Save Settings. Close SQLQueryStress, and load the settings file - the delay is back to 0.

New feature request: time delay between queries

Sometimes I like to display the effect of concurrency on queries - if people are jumping in and out getting their own locks and leaving, then they're fine. But as more simultaneous queries pile up, blocking problems can pop up.

It'd be sweet to have a "Delay Between Queries, MS" edit box that lets you specify a delay in milliseconds between running queries on each thread. (I say milliseconds because I can see using this with ranges between 0ms and 5,000ms.)

When running, "Delay between queries" isn't grayed out

Small bug - to repro, run SQLQueryStress, and note that the delay between queries box looks like it's still editable during runs:

Thread.Abort is not supported in .NET Core

Since the upgrade to .NET Core, the application occasionally closes without warning when I click the "Cancel" button while many threads are running. No error message is displayed, so I pulled the source and reproduced locally while running under the Visual Studio debugger, and got this PlatformNotSupportedException:

Thread abort is not supported on this platform.

At this call stack:

   at System.Threading.Thread.Abort()
   at SQLQueryStress.LoadEngine.ThreadKiller.KillEm() in C:\Code\SqlQueryStress\src\SQLQueryStress\LoadEngine.cs:line 610
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

The problem is at this line of code, and the reason is documented here:

This method is obsolete. On .NET 5.0 and later versions, calling this method produces a compile-time warning. This method throws a PlatformNotSupportedException at run time on .NET 5.0 and later and .NET Core.

I'd be interested in contributing a fix, but wanted to open the issue before starting, to discuss possible solutions (don't want to overstep my bounds!).

The docs on How to Stop a Thread indicate that CancellationTokens should be used to cooperatively cancel threads.

However, reading through the source code, I wonder if the Thread.Abort could just be removed altogether. It looks like you have cooperative cancellation built in, and this Thread.Abort is kind of a "last resort" if threads aren't ending quickly enough.

Removing Thread.Abort fixes the problem in my case - all of the threads eventually successfully cancel, and control returns to the UI

Like I said, I'm glad to contribute a fix - but wanted to make sure there's not some context I'm missing, as I'm very new to the codebase 🙂

---

My repro scenario involve spinning up 200 threads of a query that will all be blocked indefinitely. Once they have all successfully started (I can see all 200 connections on the SQL Server side), I click cancel. If you'd all like the details, let me know!

---

By the way, thanks for maintaining this excellent tool - it has been a huge help to me as I learn things about SQL Server.

Fix crash

Fix bug where SQLQueryStress tends to crash after the Stop button is hit. Some variable, I guess, is not being properly reset.

Configure AppVeyor CI build

AppVeyor link down

Howdy sir! Looks like on the home page of the repo:

https://github.com/ErikEJ/SqlQueryStress

The AppVeyor link is down:

https://ci.appveyor.com/api/projects/ErikEJ/SqlQueryStress/artifacts/SqlQueryStress.zip?branch=master

Gives an error saying: "The specified blob does not exist. RequestId:93a2de2f-e01e-0086-666f-fbc1df000000 Time:2020-03-16T08:46:03.9279219Z"

Several Instances of SqlQueryStress should have different window titles

Hi,

I have several instances of SqlQueryStress opened. They are hard to find again as they all have the same window title ...

When opening from a file it woul be great to show the file name in the window title, otherwise numbering should be helpfull. e.g. Session1 - SqlQueryStress. Compare Word and Excel for example.

Felix

link to sqlblog broken?

at the home page of the repo in the top description and in the main text it says:

SQL query stress simulator created by Adam Machanic http://sqlblog.com/blogs/adam_machanic/archive/2016/01/04/sqlquerystress-the-source-code.aspx

but this link seems to be now broken

Clear warnings during build

Currently there are 6 warnings during project build. It would be nice if they all got cleared :)

What about print, raiserror statements?

Is it possible to output info of these statements as SSMS does it?

Error after stopping and starting

If you stop a run of SQLQueryStress by hitting 'Cancel', then restart it with 'Go', let the process finish, and then press 'Go' again, when the second run finishes you get the error

Value of '101' is not valid for 'Value'. 'Value' should be between 'minimum' and 'maximum'.
Parameter name: Value

Steps to reproduce

1. Type 'exec sp_configure' in the query window (any query will do really)
2. Set number of iterations = 100
3. Set number of threads = 10 (essentially set these 2 high enough so that the full run will take a few seconds and give you the chance to stop it part way through)
4. Click 'Go'
5. Cick 'Cancel'
6. Click 'Go' to allow the first run to complete
7. Click 'Go' to do a second run
8. Error
9. Click 'Continue' leaves the app in hung state and I have to kill from Task Manager

Full text of the error (with loaded assemblies removed for clarity):

Value of '101' is not valid for 'Value'. 'Value' should be between 'minimum' and 'maximum'.
Parameter name: Value
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.ArgumentOutOfRangeException: Value of '101' is not valid for 'Value'. 'Value' should be between 'minimum' and 'maximum'.
Parameter name: Value
at System.Windows.Forms.ProgressBar.set_Value(Int32 value)
at SQLQueryStress.Form1.UpdateUi()
at SQLQueryStress.Form1.backgroundWorker1_RunWorkerCompleted(Object sender, RunWorkerCompletedEventArgs e)
at System.ComponentModel.BackgroundWorker.OnRunWorkerCompleted(RunWorkerCompletedEventArgs e)

Save statistics

Implement support for saving of statistics and collected information, and reporting on that data, especially when the same test is run more than once

Fill ApplicationName with something distinguishable

Hi,

when looking at a profilter trace, SqlQueryStress ist hard to filter as it does not overwrite the default .NET ApplicationName: .Net SqlClient Data Provider

See https://social.msdn.microsoft.com/Forums/en-US/89437a55-e237-4a7e-9925-2ab3c795c989/net-sqlclient-data-provider-how-to-change-this-value-to-specific-name-of-application-in?forum=adodotnetdataproviders

I would prefer this to be SqlQueryStress.

regards
Felix

SQLQueryStress just supports english locale

Hi,

I have tested SQLQueryStress with an SQL Server where my default language ist German.
Doing so the "CPU Seconds/Iteration (Avg)" and "Logical Reads/Iteration (Avg)" and "Actual Seconds/Iteration (Avg)" did not show up.

After changing my language to english it worked.

I guess this is caused by

SqlQueryStress/src/SQLQueryStress/LoadEngine.cs

Line 330 in 8e0bf22

private static readonly Regex FindReads = new Regex(@"(?:Table \'\w{1,}\'. Scan count \d{1,}, logical reads )(\d{1,})", RegexOptions.Compiled);

Thanks for the tool!

Felix

Error "The connection was not closed. The connection's current state is open."

In the latest build (Help-About says 0.9.7.35), single queries work - for example, you can run 1 iteration across 1 thread or 10 threads, and they work fine.

However, if you try multiple iterations (like 10 or 100), the Exceptions counter just goes up. If you hit the ... button for errors, they're all, "The connection was not closed. The connection's current state is open."

Cancel queries serie

Run long query *N. Click Cancel - no reaction, cannot cancel the running queries.

Update wiki screenshots

" I noticed that the wiki screen shots didn’t match the coded I downloaded."

create release notes

New Feature Request: Command Line Support

Requesting that the SqlQueryStress tool allow for command line interactions. I would like to be able to test any incoming changes in a CI environment, record the results, and then determine if that change meets minimum performance criteria.

I think this would be a really great feature to draw more people to this project and I can think of several uses along the lines of performance monitoring/tracking.

I think these would need to be exposed to the command line interface:

• Query/SQL File
• Connection information
• Number of iterations/Threads

What I would want back...
An general overview of the run (Elapsed Time, Client Seconds/Iteration, CPU Seconds/Iteration, Actual Seconds/Iteration, # of Exceptions, Logical Reads/Iteration)

Unable to open project in WinForms designer

I've tried this on 2 different computers w/VS2019 16.8.2 and I am not able to use winforms designer to see/edit Form1 (others work)
I believe this is due to use of ElementHost.
Following instructions documented here: https://docs.microsoft.com/en-us/dotnet/desktop/wpf/advanced/walkthrough-hosting-a-wpf-composite-control-in-windows-forms?view=netframeworkdesktop-4.8

I am able to open the form if I move ElementHost init to Form1_Load

Multiple test queries

Implement support for multiple test queries

Compiled version for download

Howdy! I teach training classes with SqlQueryStress, and students have said they'd love to use this newer version (I still use Adam's old one), but they don't have Visual Studio to compile the code. The next time you do a release, can you include the exe? Thanks!

Update help-about

Currently points to Adam's old DataManipulation.net (which is gone), and has the old version numbers.

(I feel bad logging an issue this small, but hey, it's pretty clear, hahaha, and you deserve the credit for the current version.)

Moar threads

Enable more than 200 simultaneous threads. This was just an arbitrary number and can be changed by simply modifying the appropriate control. But perhaps there is a smarter way to come up with a maximum?

Mark load threads as IsBackground = true

Load threads are currently being created as:

var theThread = new Thread(input.StartLoadThread) {Priority = ThreadPriority.BelowNormal};

You might consider setting IsBackground = true as well per the docs:

A thread is either a background thread or a foreground thread. Background threads are identical to foreground threads, except that background threads do not prevent a process from terminating. Once all foreground threads belonging to a process have terminated, the common language runtime ends the process. Any remaining background threads are stopped and do not complete.

This could explain why sometimes the threads keep running even after the application window exits.

Save settings (delay between queries)

Hi,
the Save Settings option doesn't save Delay Between Queries value or Load Settings option doesn't recover it.
This happens in last version as well.

Thank you