I suggest you ...

Create a Framework for driving realistic load against SQL Server

I have a framework for driving unit tests directly against SQL server (similar to the original SQL Load Testing project by Rob Jarrett) but with many enhanced features. The tool is already being used for engagement within the Testing Services and Labs, but the tool needs a full team effort to take it to the next level. Some high level info is below and some more in the two attached documents.

SqlUT Framework Overview:
* The testing engine sits inside of a Visual Studio Unit Test and takes advantage of the ability to feed data sources, test contexts, use timers and be driven in load tests.
* The engine reads and parses a T-SQL file, turning it into a collection of SQL queries, each with the ability to have pre-defined or customized extraction/validation rules and/or plugins, as well as full statistics.
* The T-SQL code is marked up by adding specific tokens embedded in SQL comments. This allows the engine to act on the code, but still allows any text/SQL editor to see the file as syntactically correct SQL.
* The initial T-SQL code can be created in any fashion, but the most common way is to use a SQL Profiler trace file that captures the traffic between a thick client and the SQL server while the client is performing work.

SqlUT Editor:
* The editor created for this project has a bunch of very nice features, but is also very crude in the actual code editing (for instance it does NOT support syntax highlighting). I envision a future version that sits inside Visual Studio and uses the built in T-SQL editor and simply exposes the extra functionality through extensions.
* The editor either currently has, or will have soon the following abilities:
* (CURRENT) Locate INSERT Statements that have corresponding SCOPE_IDENTITY calls and retrieve the column name for the identity column so you can add extraction rule for that column.
* (FUTURE) Automatically add the extraction rule when such INSERTS are found
* (CURRENT) List out the args for a sproc and show the arg type
* (CURRENT) Pull down the code for a sproc and put it in the clipboard for viewing (useful when you are editing the SQL and need to see what functionality is in a sproc that is being called.
* (CURRENT) Get statistics about the T-SQL file, showing things like how many queries are SELECT, or INSERT, etc.
* (CURRENT) Ability to identify queries that have DateTime info that meets a given regex expression. This is useful for marking queries that you may need to edit and make the DateTime dynamic.
* (CURRENT) Ability to see the queries grouped based on user steps. This feature assumes the use of a utility called "AddSqlComment" that you use to inject comments into the initial T-SQL profiler trace while recording the SQL script. This list of steps becomes a testing "use-case" and shows what SQL is happening behind the scenes for each user step that a person performs on the client app.
* (FUTURE) Ability to capture the SQL trace via Netmon and have it parsed through there. SPECIFICALLY to allow the ability to get both sides of the conversation in a single trace. Currently you capture a SQL Profiler trace and then replay the trace through SQLCMD with "Echo Responses" turned on to get the info, but that requires a restore of the DB between the recording and playback. A NetMon trace would give it all in a single step. A NetMon trace would also allow timing info -to be captured for the injection of think time.

60 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Geoff GrayGeoff Gray shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Visual Studio TeamAdminVisual Studio Team (Product Team, Microsoft Visual Studio) commented  ·   ·  Flag as inappropriate

        (Chuck Typing)
        Looks like this was originally a Ranger suggestion...but is now dropped into "uncategorized".
        If this was for the load test team i would still decline -but with a different response:

        Excellent Suggestion and have passed this to the SQL Team for their consideration.
        Visual Studio Load Testing is focused on doing Load and Performance Testing within the application development process and while we understand underlying services (such as databases) may need to be load tested directly have focused on making the platform extensible to solve these cases.

      Feedback and Knowledge Base