What is SQL Server Stress Testing?
Implementing a new database consists of various aspects like creating indexes, tuning stored procedures or installing new RAM etc. But among all of these items, performance stress testing plays a prominent role.
Numerous organizations rely on the basic testing of store procedure performance, which includes implementing threshold settings or concurrent executions. This leads to identifying performance issues which might emerge after the release of an application. It becomes crucial to overcome such obstacles and with that view, a solution is given below to mitigate the requirement.
Query Stress tools?
A performance stress tool plays a significant role for information system developer or QA department’s employee in order to make sure the quality testing of a product, which is under development, to ensure that it can work with a large number of simultaneous connections and concurring queries and transactions. This utility can also be useful for the database administrators to evaluate the current performance of a query and to make plans about improving performance.
There are many tools available for stress testing but they can be expensive and complicated. To work around this, I have come up with a rudimentary solution which will help to do more thorough job of stressing the database application than having half a dozen developers hammering their keyboards for an hour or so.
OStress
OStress is a Microsoft tool comes with RML utilities package and it uses to stress SQL Server. This is especially useful when you want to troubleshoot SQL Server while SQL Server is under heavy load.
It is a free tool for SQL Server developers and DBAs. It is designed to assist with performance stress testing of T-SQL queries and routines. The tool automatically collects metrics to help you determine whether your queries will perform under load, and what kind of resource strain they put on a server. In short, it also allows putting a serious load on your database.
Use of OStress utilities
Stress: Most applications have application response time targets. Developers, DBAs, and architects need to be confident that once a platform is commissioned and servicing live users, the database platform will be capable of handling the volume of requests generated by the application.
To be more specific, OStress’s primary goal is to make you aware of query or store procedures capabilities for testing your applications in development.
It is also helpful to identify waiting and blocking in a subsequent call of procedure which is very crucial when in tuning store procedure.
Replay: Replay trace data against SQL Server in term of generating traffic and testing the database.
Assuming a trace representative of the live environment and containing the problem can be captured, RML Utilities can be used to replay the workload and simulate the problem in the
test environment. This can be a useful test in and of itself to determine whether the problem is resident within the database code (T-SQL) or environment specific.
- ReadTrace
- Reporter
- OStress
- OStress Replay Control Agent (ORCA)
- SSIS Web Service Tasks - May 14, 2019
- Spatial SQL data types in SQL Server - July 11, 2018
- The HashBytes function in T-SQL - May 16, 2018
How to operate and use
OStress is a command line tool similar to SQLCMD, iSQL and the other SQL command line utilities.
What is more, OStress also shares the same basic set of options that already are used by those tools. In short, one will not face any difficulty in learning the additional options that are specific to OStress.
Step1:
You can download OStress in the RML Utilities package available here: Description of the Replay Markup Language (RML) Utilities for SQL Server. There are 32bit and 64bit versions. Be sure to download the right one for your server.
The RML Utilities are a set of tools originally developed by the SQL Server support organization within Microsoft to help engineers effectively resolve customer support incidents.
The RML Utilities for SQL Server contains the below four utilities:
Here I have just drawn a line at description of OStress utility and its sample usage
Step2:
To install, just tick term and condition check box and press next to install (easy to install).
For checking, you should just search in start “rml cmd prompt”
And open the command prompt, in which just write OStress, which give you a list of options that you can manage with OStress
Step3
As we have already discussed, the core benefit of OStress is that it allows you to connect to an instance of SQL Server and execute any number of SQL scripts a configurable number of times by a configurable number of user (threads).
For a demo of load testing, I have created a SQL store procedure script. In which data should be insert into temporary table with help of loop that is real-time milieu: bulkInset_into_temptable.sql
OStress for generating stress
Firstly, execute script from “.sql” file
Note: I have put the store procedure execution statement in specific d:\demo\ directory
Ostress.exe -Usa –Pdemo –i D:\demo\bulkInset_into_temptable.sql –n25 –r50 –od:\demo
In above statement, I have tried to execute with user- SA, include password: demo, and Database is: Northwind. In addition, input file has been taken from D:\demo\bulkInset_into_temptable.sql, and its output merely put on d:\demo, I have simulated 25 user connections having concurrent execution of 50 iterations.
Secondly, directly fire an ad-hoc query
In below code, I have just put bit variation, use window authentication –E for login.
ostress -E -D”Demo_Ostress” -d NORTHWND -Q”select OrderID, ProductID, UnitPrice, Quantity, Discount from [Order Details] ” –n25 –r50 –od:\demo
Also, an additional switch is, ‘-D’ is for ODBC connection, and ‘ –d’ is for database although, for the demo purpose I have given default database name as “master” in ODBC connection, due to that I have to provide a specific database name. ‘–Q ‘ is for standalone SQL queries.
At last, execute of whole folder’s all ‘.sql file’
Here I have not put user name and password as, it will ask me at time of execution. You can put’ –u’ and ‘–p’ if you have to give at time of execution.
Ostress.exe –iD:\demo\*.sql –n25 –r50 –oD:\demo
For instance, this command line will execute every SQL script which is located in the D:\ demo\ for 50 times with 25 different threads (users).
Finally, the question arises how can we check waiting, and blocking with concurrent execution?
Now, I want to highlight the most informative part of this execution. How one can easily catch plenty of problems that might not be normally be considered without stress testing. Generally, performance issues might not be caught before the release. Especially, when we talking about issues of locking and blocking, waiting resources and many others. After getting profound knowledge and better understating the use case of OStress tools and DMV’s, one can believe the ability of the powerful tool.
Simply, you just need to use below DMV query while executing OStress commands.
Below are the queries, Checking_waiting_blocking.sql
And for the purpose of this demonstration, I have put the result in an image below:
Here it is clearly visible 25 rows, that is concurrent execution of 25 threads, on the top of that, a DBA can smoothly catch a waiting type [PAGELATCH_UP] and blocking text, session id and resources, which is enough for finding a root cause while tuning store procedure.
OStress for replaying workload
As mentioned earlier, The Replay Markup Language (RML) is an XML-like language that can be acclimated replay workload against a SQL Server. Using SQL Server Profiler, we can capture a trace containing the statements that executed against a server, what is next, ReadTrace is used to convert the .TRC (profiler trace output) to .RML files, which can then be replayed using OStress. It can be difficult to simulate production workload in a test environment, and RML Utilities addresses this challenge.
When replaying RML fi les, ORCA (The OStress Replay Control Agent provides session tracking, sequencing, and distributed transaction control for OStress replay.) is used to ensure correct sequencing of activities. Every RML file contains details of the sequence number, including the next sequence number. The default sequencing mode used by OStress suppresses periods of inactivity while preserving sequencing to ensure that query execution occurs serially, with any delays between statements suppressed.
When using OStress replay functionality, the -m should be indicated to “replay,” which will result in ORCA being used to control the replay. Also use the -i which refers an RML or SQL file to be executed.
Since ReadTrace creates one RML file for each SPID, when replaying a complete workload, it is necessary to mention (*) which indicates that all RML files should be replayed, for example:
Ostress.exe -id:\Demo\*.RML –oc:\demo\output -mreplay
Conclusion
OStress can be used in two modes, either to replay a workload as it was captured from the server or to generate a workload based on a set of SQL scripts allowing you to simulate many user sessions and activity. In essence, OStress is probably not the best tool for stress testing your applications and databases, but it is a great place to start and certainly the best free option.
In his downtime, he enjoys spending time with his family, especially with his wife. On the top of that, he loves to do wondering and exploring different places.
Say hi and catch him on LinkedIn