SQL Test Performance (out of memory exception)

datacentricity

Is there a practical limit to the number of tests supported by the SQL Test UI?

It has always been somewhat slow but as the number of tests across our platform have increased it has reached the point of being unusable. I have experienced three issues:

1) Opening SQL Test in an already open SSMS can take a minute or more (during which SSMS is unusable/not responding). If we leave SQL Test open when closing SSMS, then next time we start SSMS, SQL Test will load at the same time and it can be several minutes before SSMS is usable.

2) Once we reached the point of 1000 or so tests in a single database, I found that running a suite of tests (almost regardless of the number of tests in that suite), SSMS is virtually unusablle not only whilst the tests are but often also once the tests have completed. It seems as if the CPU used by SSMS ramps up whilst the tests are running (understandably) but that heavy CPU utilisation continues after the tests have completed in probably 50% of cases. This means that the only thing we use this tool for now is viewing and editing tests.

3) The entire team (4 developers) have recently been experiencing out of memory exceptions whilst SQL Test is open. The exceptions are experienced typically in an open query window. I do not experience this at all if I don't open SQL Test. Once I have SQL Test open, at some point an existing query window will suddenly start throwing out-of-memory exceptions even on relatively simple queries that might only return a "completed successfully" message. The only way to resolve this is to close SSMS (closing just SQL Test does not work). This now means that SQL: Test is unusable for any purpose.

We are using SQL test v1.5.1.211 within SSMS 2012 (v11.0.6020.0) against a local (dedicated sandbox) instance running SQL 2012 Developer Edition SP2. Typical developer machine hardware will be four core i5 with 8GB RAM and SSD drive. We have a little under 6,000 unit tests across five databases and, given how our problems have grown as the number of tests have increased I have a strong suspision that we have reached a limit on the number of unit tests supported by SQL Test.

Is there a solution to this or do we just have to accept this we cannot use SQL Test any longer?

Many thanks,

Eddie D

Hi

Thank you for your forum post and sorry that you have encountered a problem.

I suspect the biggest limiting factor to the number of tests that can be run is that SSMS 2012 as a 32-bit application is limited on the maximum amount of RAM it can use. Although your developer machines have 8GB RAM, I believe that the maximum memory that can be used by SSMS is 2GB. There again the tests themselves are simply stored procedures that are created upon the database to be tested and when the user selects 'Run Tests', SQL Test runs each test in turn reporting the results.

I have no metrics available to me to confirm the maximum number of tests that can run per database.

Does SQL Test generate an exception or any entries in the Event Viewer logs to help identify a cause of the problem?

Many Thanks
Eddie

datacentricity

I will take a look at the event log but should also note that when ever we see the out of memory exception, the amount of memory being utilised by SSMS is usually no more that 500MB and I have never seen it higher than 840MB

Eddie D

Hi,
Thank you for your reply.

Would it be possible for you to upgrade to V1.5.2.448 using this LINK?

I have created a support ticket for you the call reference is #70143. If upgrading does not help, would for please send an email to support@red-gate.com, subject is call reference #70143 with details of the actual tests that you are running?

Many Thanks
Eddie

datacentricity

Eddie,

Thank you for this. I have downloaded and installed the updated version. I will keep it open in SSMS for the next few days and then report back on whether this solves any of the issues.

Regards