I guess, it is no secret that testing plays a critical part in development of any software product. The more rigorous testing is, the better the final product will be. In this blog post we will be Testing Database Performance with tSQLt and SQLQueryStress.
There is a common practice whenthe software code testing is carried out quite meticulously, while the database testing is either skipped because of the lack of time, or carried out on leftovers. To be quite honest, things are even worse in real life: database is recalled only when there are actual problems with it already. Eventually, the work with database may become a real bottleneck in performance of your application.
To get rid of such problems, I suggest looking into various aspects of database testing, including load testing and performance testing of SQL Server in general by means of unit tests.
Let’s consider an abstract task. For instance, let’s imagine we are developing an engine for online shops. Our customers may have different volume of sales, product types… but to make it more straightforward, we will make the database structure maximally simple.
Database Structure
Let’s also suggest that our sophisticated client will work with the database with help of the preliminary written stored procedures. They all are pretty simple. The first procedure is for insertion of a new user or for getting ID of the existing one:
The second procedure is for placing a new order:
Suppose, we need to ensure the minimal call during execution of queries. There will be hardly any performance issues with an empty database. Therefore, we need at least some data to test performance of our database. For this, let’s use the following script to generate test data for the Customers table:
The script was created for the purpose of generation of random test data for unstructured tables. As a result of this approach, we succeed in terms of versatility, but failed in terms of reality:
CustomerID FullName Email Phone
———– ———————————— —————- —————
1 uN9UiFZ9i0pALwQXIfC628Ecw35VX9L i6D0FNBuKo9I ZStNRH8t1As2S
2 Jdi6M0BqxhE-7NEvC1 a12 UTjK28OSpTHx 7DW2HEv0WtGN
3 0UjI9pIHoyeeCEGHHT6qa2 2hUpYxc vN mqLlO 7c R5 U3ha
4 RMH-8DKAmewi2WdrvvHLh w-FIa wrb uH
5 h76Zs-cAtdIpw0eewYoWcY2toIo g5pDTiTP1Tx qBzJw8Wqn
6 jGLexkEY28Qd-OmBoP8gn5OTc FESwE l CkgomDyhKXG
7 09X6HTDYzl6ydcdrYonCAn6qyumq9 EpCkxI01tMHcp eOh7IFh
8 LGdGeF5YuTcn2XkqXT-92 cxzqJ4Y cFZ8yfEkr
9 7 Ri5J30ZtyWBOiUaxf7MbEKqWSWEvym7 0C-A7 R74Yc KDRJXX hw
10 D DzeE1AxUHAX1Bv3eglY QsZdCzPN0 RU-0zVGmU
Of course, nobody stops us from wiring a script for generating more realistic data for the same Customers table:
Data has become more realistic:
FullName Email Phone
————— ————————– ——————-
Boris_the_Blade boris_the_blade1@gmail.com +38 (146) 296-33-10
John john130@mail.com +38 (882) 688-98-59
Phil phil155@gmail.com +38 (125) 451-73-71
Mike mike188@gmail.com +38 (111) 169-59-14
Sarah sarah144@gmail.com +38 (723) 124-50-60
Andrey andrey100@gmail.com +38 (193) 160-91-48
Stephanie stephanie188@gmail.com +38 (590) 128-86-02
John john723@gmail.com +38 (194) 101-06-65
Phil phil695@gmail.com +38 (164) 180-57-37
Mike mike200@gmail.com +38 (110) 131-89-45
However, we should not forget that we have foreign keys between tables, and generation of consistent data for the rest of instances is a way harder task. To avoid inventing a solution for this problem, I suggest using dbForge Data Generator for SQL Server that allows to generate meaningful test data for database tables.
CustomerID FullName Email Phone
———– ————– ———————————– —————–
18319 Noe Pridgen Doyle@example.com (682) 219-7793
8797 Ligia Gaddy CrandallR9@nowhere.com (623) 144-6165
14712 Marry Almond Cloutier39@nowhere.com (601) 807-2247
8280 NULL Lawrence_Z_Mortensen85@nowhere.com (710) 442-3219
8012 Noah Tyler RickieHoman867@example.com (944) 032-0834
15355 Fonda Heard AlfonsoGarcia@example.com (416) 311-5605
10715 Colby Boyd Iola_Daily@example.com (718) 164-1227
14937 Carmen Benson Dennison471@nowhere.com (870) 106-6468
13059 Tracy Cornett DaniloBills@example.com (771) 946-5249
7092 Jon Conaway Joey.Redman844@example.com (623) 140-7543
Test data is ready. Now, let’s proceed to testing productivity of our stored procedures.
We have the GetCustomerID procedure that returns a client ID. If the ID does not exist, the procedure creates the corresponding record in the Customers table. Let’s try to execute it with the preliminary enabled view of the actual execution plan:
The plan shows that the full scan of the clustered index takes place:
To achieve this, SQL Server has to make 200 logical reads from the table and it takes approximately 20 milliseconds:
Table ‘Customers’. Scan count 1, logical reads 200, physical reads 0, …
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 20 ms.
And here we are talking about execution of one single query. What if our stored procedure will be executed very actively? The constant index scanning will reduce the server productivity.
Let’s try to execute the stress testing of our stored procedure with one interesting open-source tool, SQLQueryStress (link to GitHub).
We can see that the 2 thousand calls of the GetCustomerID procedure in two streams took a little less than 4 seconds. Now, let’s see what will happen if we add an index to the field that is involved in our search:
The execution plan shows that Index Scan has been replaced with Index Seek:
The logical reads and total execution time have been reduced:
Table ‘Customers’. Scan count 1, logical reads 2, …
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.
If we repeat our stress test in SQLQueryStress, we will see that our procedure loads server less and is executed faster during the multiple call:
Now, let’s try to emulate the mass order placement with SQLQueryStress:
100 executions of the procedure in two streams simultaneously took 2.5 seconds. Let’s clear the waits statistics:
We can see that the first place is occupied by WRITELOG, the wait time of which approximately corresponds to the total execution time of our stress test. What does this wait mean? Since each insert command is atomic, the physical fixation of changes in log takes place after its execution. When there are lots of short transactions, the queue appears, because log operations take place simultaneously, unlike data files.
In SQL Server 2014, the ability to setup writeback to the Delayed Durability log was introduced, which can be enabled on the database level:
Next, we need to just alter the stored procedure:
Let’s clear statistics and run stress test for the second time:
The total execution time has been reduced in two times, and the WRITELOG waits have become minimal:
wait_type wait_time
————————– ———-
PREEMPTIVE_OS_WRITEFILE 0.027000
PAGEIOLATCH_EX 0.024000
PAGELATCH_EX 0.020000
WRITELOG 0.014000
Let’s take a look at another situation, when periodic check of the execution productivity of one or another query is required. SQLQueryStress is not that convenient tool for this goal, since we have to open the app, copy the query and wait for its execution.
Can we automate this somehow?
In 2014, I tried out tSQLt for the first time, which turned out to be quite a nice free framework for unit testing. Let’s try to install tSQLt and create an autotest for checking the productivity of our stored procedure.
Let’s download the latest version of tSQLt and set up an SQL Server instance for the work with CLR:
After this, let’s execute the tSQLt.class.sql script from the archive against our database. The script creates its own tSQLt schema, CLR assembly and lots of script objects. A part of the procedures which are supposed for internal framework usage will contain the Private_ prefix.
If everything has been installed successfully, we’ll see the following message in the Output:
+—————————————–+
| |
| Thank you for using tSQLt. |
| |
| tSQLt Version: 1.0.5873.27393 |
| |
+—————————————–+
Now, let’s create a schema in which we will create autotests:
Note, that Extended Property defines the relation of one or another object to the tSQLt functionality.
Let’s create a test in the Performance schema and specify the test prefix in its name:
Let’s try to execute the created autotest. For this, we can either execute
…or specify schema explicitly:
, or a certain test:
If it is required to tun the lately executed test, we can call Run without parameters:
After execution of one of the above commands, we will get the following information:
[Performance].[test ProcTimeExecution] failed: (Failure) TODO: Implement this test.
+———————-+
|Test Execution Summary|
+———————-+
|No|Test Case Name |Dur(ms)|Result |
+–+————————————–+——-+——-+
|1 |[Performance].[test ProcTimeExecution]| 0|Failure|
Let’s try to modify the contents of the autotest to something useful. For instance, let’s take the GetUnprocessedOrders procedure that returns the list of unprocessed orders:
…and create an autotest that will execute the procedure for a certain number of times and will end with error, if the average execution time is more than the specified threshold value.
Let’s execute the autotest:
We receive the following message:
[Performance].[test ProcTimeExecution] failed: (Error) High execution time: 161 ms
+———————-+
|Test Execution Summary|
+———————-+
|No|Test Case Name |D ur(ms)|Result|
+–+————————————–+——-+——+
|1 |[Performance].[test ProcTimeExecution]| 1620|Error |
Let’s try to optimize the query to make the test pass. First, let’s take a look at the execution plan:
As we see, the problem is in the frequent call to the clustered index of the Products table. A large number or logical reads also confirms this statement:
Table ‘Customers’. Scan count 1, logical reads 200, …
Table ‘Orders’. Scan count 1, logical reads 3886, …
Table ‘Products’. Scan count 0, logical reads 73607, …
Table ‘OrderDetails’. Scan count 1, logical reads 235, …
How can we fix the situation? Well, we can either add a non-clusted index, include the Price field to it, make a pre-estimate of values in a separate table. Alternatively, we can create an aggregate index view:
It’s better to specify the NOEXPAND hint to make the optimizer execute the index from our view. Besides, we can create a new filtered index to minimize the quantity of logical reads from Orders:
Now, a more simple plan is used during execution of our stored procedure:
The number of logical reads has decreased as well:
Table ‘Customers’. Scan count 1, logical reads 200, …
Table ‘Orders’. Scan count 1, logical reads 21, …
Table ‘vwOrderSum’. Scan count 1, logical reads 44, …
Execution of the stored procedure has been also minimized, and our test will be executed successfully:
|No|Test Case Name |Dur(ms)|Result |
+–+————————————–+——-+——-+
|1 |[Performance].[test ProcTimeExecution]| 860|Success|
We can hug ourselves. We have optimized all bottlenecks and made a really cool product. But let’s be sincere with ourselves. Data tend to mount up, and SQL Server generates execution plan on the basis of the expected number of lines. We have performed testing with perspective for the future, but there’ s no guarantee that somebody won’t delete the required index and so on. That’s why, it is highly important to run similar autotests on a regular basis in order to influence problems timely.
Now, let’s see what else we can do with unit tests.
For instance, we can check all execution plans for the MissingIndexGroup section. And if the section exists, SQL Server considers that a certain query lacks index:
Also, we can automate search of the unused indexes. It’s quite simple – you just need to know statics of usage of one or another index in dm_db_index_usage_stats:
When developing large and complicated systems, it is a frequent case when a table can be created, filled with data and then forgotten for good.
So, how can such table be determined? For example, there are no references to such tables, and selection from these tables has not taken place since the start of the server, given that the server is working for more than a week. The conditions are relative, and should be adopted for each specific case.
I can create yet more tests similar to the above one)
To sum up, I can recommend trying out tSQLt and SQLQueryStress without any scruples: these products are completely free and have proved to be really useful during heavy load testing of SQL Server and optimization of server productivity.
Speaking of the unit testing software, I would also like to mention dbForge Unit Test – a convenient add-in from Devart for automated unit testing in SQL Server Management Studio. The tool delivers high-quality unit testing and cuts down time spent on the process. Besides, Devart offers a free fully functional trial for the product for 30 days.
Reference: Pinal Dave (http://blog.sqlauthority.com)
First appeared on SQL SERVER – Testing Database Performance with tSQLt and SQLQueryStress