Performance in SQLTest
Martin Lange
Thank you for the excellent tool.
It seems to me there is a performance problem with SQLTest.I tested today on version 1.2.0.28.
I wrote a feedback yesterday from version 1.2.0.27. The problem still exists on 1.2.0.28.
When it runs a workload, it gets slower and slower. This I see on my own use and also on your videos:
1) For a 10 second run, 50% of the iterations are reached after 2 seconds. The other 50% takes the remaining 8 seconds.
2) When I run SQLTest from PC A and B against database server C, A reaches 50% more iterations than B. Only difference between PCs A and B is that A is twice as fast as B.
3) While a long workload (30 seconds) I can see that SQLTest consumes more and more cpu power. In beginning it consumes 2% at the and 15% as seen in taskmanager.
These 3 observations all point to a problem with a datatype inside SQLTest growing bigger (storing iterations?) and thereby slower.
In its current state it actaully benchmarks the client machine more than the database server (as seen in observation 2 above).
Ramesh Meyyappan
I have had similar experience; in my case the “Results” tab was consuming significant CPU resource (SQLTest uses StringBuilder Class to store the results and displays them using RichTextBox) getting slower and slower as more results accumulate.
In SQLTest you can choose to execute the query in 2 ways, using ExecuteNonQuery() or using ExecuteReader() choosing Menu Settings -> Workload Settings. With ExecuteNonQuery() there are no rows to retrieve and hence there is no display or memory overhead. When you use ExecuteReader(), the rows are retrieved using Read() and shown in “Results” tab, this has significant display and memory overhead (in addition, there is the “Auto Scroll Results Tab” possibility choosing Menu Settings -> General Settings to keep the cursor on the last line of the RichTextBox which adds further overhead. The more the results the slower it becomes to manage them in the RichTextBox).
Of course one should be realistic and should not use ExecuteNonQuery() and omit retrieving the results. When using ExecuteReader() one possibility is to disable “Show Results” using Menu Settings -> Workload Settings for that workload. This will still execute Read() and retrieve the results which is realistic, but won’t save them in StringBuilder or display them in RichTextBox. If necessary one can log the results to a file (which is much faster) using Menu Settings -> General Settings. In the next release there is a possibility to save the results in a database, this again will slow down the test overall, so disabling “Show Results” and if necessary saving them to a file for verification is the best option.
Martin Lange
It improves the situation but not totally removed the problem.
Difference between A nd B workstations are now smaller. 70000 iterations on B and 82000 on B.
CPU usage no longer increase enough to conclude anything.
It is still possible to see (by looking at the iterations counter) that speed decreases during the test. But much less.
I tried another tool "Eye On Sql". It is open source. It has the same problem, but I removed locking in the code and that fixed it. PC A and B have same iterations during 60 seconds.
This tool always connect for every iteration (as far as I can see in code). So to compare with your tool I added the option for that in workload settings.
In that case SQLTest reaches 11500 iterations (60 seconds) and Eye On SQL reaches 56000.
I then changed the code for Eye On SQL to reuse connection. It then reaches 118000 iterations, with SQLTest reaching 81000.
So I still see some improvement can be done in SQLTest.
Ramesh Meyyappan
In the connection string did you use Pooling=true to reuse connection?
Martin Lange
It seems the pooling works well. Now both A and B makes same number of iterations (actually the slower on makes more. I think because of better network card). Also the number of iterations/sec is constant from start to finish.
I just do not understand why pooling should make a difference when SQLTest is not connecting before each iteration. It is probably a detail I do knot know about, so do not worry. Also I find it strange that the iterations/sec is falling when using pooling=false.
Also SQLTest now performs equally good or better than Eye On SQL tool.
Thanks for the help.
Do you still want the info from me extracted from SQLTest?
Ramesh Meyyappan
2. Eye On SQL tool uses ExecuteNonQuery (should be faster due to low network usage) and not ExecuteReader, did you also use ExecuteNonQuery for SQLTest (http://www.sqltest.org/Help/WorkloadSettings/ExecuteNonQuery)?
3. You can also disable Provider Statistics to decrease overhead (http://www.sqltest.org/Help/WorkloadSettings/EnableProviderStatistics).
No need to test the above, just some ideas.
