Friday, May 20, 2016

SQL Server Random Workload Generator

Hello Dear Reader!  Before I start this blog I want to give credit to Jonathan Kehayias (@SQLPoolBoy | Blog).  He’s an Microsoft Data Platform MVP, MCM,  part of the amazing crew over at SQLSkills, and a really nice guy.  



I heard Jonathan talk about this at SQL Saturday Tampa years ago.  He joked at the time that, “in an effort to show that he did not have a life.  He make a workload out of all the current queries on BOL for SQL Server 2008 R2”.  

When I present and show mine, I tell the audience about that and joke that, “In an effort to show I have less of a life than Jonathan, here’s what I’ve done”.  The feedback I receive is often, "That shouldn't be a goal".  Point taken Dear Reader, point taken.  

Needless to say, I loved the script.  I grabbed it, tweaked it, and have used it for years. 

Today I wanted to make my version, which piggy backs heavily on Jonathan’s.  Here are the main differences:

  1.  tweaked the queries removing the compute by clause (deprecated in SQL Server 2008 R2) and replaced it with Cube or Rollup 
  2. I turned all of the queries into stored procedures, and adjusted the workload to call stored procedures
  3.   IF the table had a result set that allowed for it, I used the Randomize Function and passed in parameters to the stored procedures in order to get different data sets
  4. This gives a healthy dose of adhoc SQL as well as stored procedure use
  5.   I call Jonathan’s PowerShell from a batch script that allows me to spin up or down a larger set of concurrent connections.  (This makes the process visual, which is very handy when you are presenting to a crowd)


“So Balls”, you say, “That’s great.  Show me the money.”

Ah Dear Reader, as ever you are insightful.  We’ll cover this in 3 steps:  Download, Setup, and Run.


DOWNLOAD

You can Click Here or you can visit my Resources Page.  


At the top of the page you will find the download for the Random SQL Workload Generator.  

Download the ZIP and extract it to your C:\ Drive (or anywhere else you would like). 

 I’m going to use the C:\ root for simplicity in our next step Setup up.






SETUP

Now that you have the folder sitting in your C Drive let’s do the following steps:
  1.  Run the Create Stored Procedures for Random SQL Workload Generator.sql
  2. Change the SQL Server Instance Name in the RandomSQLWorkloadGenerator.ps1 to your instance name
  3. IF you changed the path from the C:\ folder, update ClientConnections.cmd and RandomSQLWorkloadGenerator.ps1 to change the paths to the proper file location.




The “Create Stored Procedures for Random SQL Workload Generator.sql” script references the AdventureWorks2014 database.  You can run this against 2012 as well, I have not yet added In-MemoryOLTP test queries, but I will eventually.  If you restored AdventureWorks2014 with a different name you will need to change that in the RandomSQLWorkloadGenerator.ps1, the  “Create Stored Procedures for Random SQL Workload Generator.sql”, and the Random_SQL_Workload _AdventureWorks 2014.sql scripts.



RUN



After all of the setup, you should be good to double click on Create5Connections.cmd.  It will spin up 5 command prompt windows.



Just to validate we are up and running, you can run a quick query against sys.dm_exec_requests, exec_sessions, queryplan, and sqltext to show our activity. 

select
     der.session_id
     ,der.status
     ,der.command
     ,des.program_name
     ,der.last_wait_type
     ,der.wait_type
     ,st.text
     ,qp.query_plan
from
     sys.dm_exec_requests der
     left join sys.dm_exec_sessions des
     on des.session_id=der.session_id
     cross apply sys.dm_exec_sql_text(der.sql_handle) st
     cross apply sys.dm_exec_query_plan(der.plan_handle) qp
where
     des.is_user_process=1
     and
     des.session_id<>@@spid

I can see that I have 4 connections and begin playing around. 



WRAP IT UP

Alright Dear Reader, a few final notes.  I don’t have a lot of write operations in this current workload.  You could easily add them yourself to the workload script, and I do plan to add some eventually.  Yes, some of these queries are bad.  No I’m not going to tune them, that’s part of the fun! 

If you find some cool stuff to do with this script, please let me know.  This is a pretty simple framework with a lot of cool potential.

Next up for this script, Running it against Azure SQL Database.  It’s not that hard actually just a couple configuration changes in PowerShell.  Plus, I’ve already done it.  More on that Next Week!

As always Dear Reader, Thanks for stopping by.

Thanks,

Brad

42 comments:

  1. Any plans to update this for the World Wide Importers database next?

    ReplyDelete
  2. Thanks for another great article. Where anyone written such a perfect way of that kind of information might be? I have a presentation next week, and I'm on the search for such information.




    Managed Network Services Dallas

    ReplyDelete
  3. nice blog too informative. looking and reading your points its so impressive. doing more blog like this. i really appreciated doing like this.
    PHP Training in Chennai

    ReplyDelete
  4. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

    Web Designing Training in Chennai Guindy

    ReplyDelete
  5. first error i got was about not being able to run scripts, can be solved with Set-ExecutionPolicy -ExecutionPolicy RemoteSigned. (run powershell as admin). Running the scripts you'll get the error CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : NullReferenceException

    how to solve this?

    ReplyDelete
  6. Nice topic you have discussed. Keep sharing such a useful post.


    SEO Course in Vadapalani

    ReplyDelete

  7. Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information.
    Regards,'
    Performance Tuning Training in Chennai | Oracle PL/SQL Training in Chennai

    ReplyDelete
  8. Great Job, Thanks for sharing this...

    ReplyDelete
  9. Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    iOS Training in Chennai

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Very Nice Blog I like the way you explained these things. I’ve been looking for ways to improve my website and overall rankings.I hope your future article will help me further.Take Digital Marketing Training to mould yourself.

    ReplyDelete
  12. Excellent blog about SEO technique !!! Keep updating your information to gain more knowledge in SEO technology
    SEO Training in Chennai | SEOTraining
    SEO Training in Chennai | SEO Course in Chennai

    ReplyDelete
  13. Good job. I believe that these SQL queries will help me to have something good for SQL workload generation. PhD dissertation writing services

    ReplyDelete
  14. Thank you for your post. This was really an appreciating one. You done a good job. Keep on blogging like this unique information with us.
    Power System Project Center in Chennai | Power System Project Center in Velachery

    ReplyDelete
  15. Thanks a lot very much for the high your blog post quality and results-oriented help. I won’t think twice to endorse to anybody who wants and needs support about this area.
    software testing training in chennai

    ReplyDelete
  16. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…No.1 IOS Training Institute in Velachery | Best Android Training Institute in Velachery | Core Java Training Institute in Chennai

    ReplyDelete
  17. I really enjoyed while reading your article, the information you have delivered in this post was damn good. Keep sharing your post with efficient news.
    ME/M.Tech Project Center in Chennai | ME/M.Tech Project Center in Velachery

    ReplyDelete
  18. Excellent post.I have read your blog it's very interesting and informative..Summer Course in Thiruvanmiyur | Summer Course in Chennai

    ReplyDelete
  19. As opposed to connecting machines to a convenient generator straightforwardly or by means of an additional line, a client may wish to utilize an electric exchange switch (which ought to be introduced by an authorized circuit repairman or some person acquainted with construction regulations in the administrator's zone).https://www.topgeneratorreview.com/quiet-generator-reviews/

    ReplyDelete
  20. Nice Blog, Thanks for sharing this valuable one. This very useful for me and gain more information.

    Regards,
    Selenium Training in Chennai

    ReplyDelete
  21. Very useful information to everyone thanks for sharing, learn the latest updated Technology at Best Training institutions
    Salesforce Lightning is the latest updated technology


    Salesforce Online Training in Bangalore
    Salesforce Training online in India

    ReplyDelete
  22. I enjoy what you guys are usually up too. This sort of clever work and coverage! Keep up the wonderful works guys I’ve added you guys to my blog roll.
    Digital Marketing Training Institute in Chennai | SEO Training in Chennai

    ReplyDelete
  23. Our digital marketing course in Chennai is targeted at those who are desirous of taking advantage of career opportunities in digital marketing. Join the very best Digital Marketing Course in Chennai. Get trained by an expert who will enrich you with the latest digital trends.

    Digital Marketing Course in Chennai
    Digital Marketing Training in Chennai
    Online Digital Marketing Training
    SEO Training in Chennai
    Digital Marketing Course
    Digital Marketing Training
    Digital Marketing Courses

    ReplyDelete
  24. Thanks for this incredible blog. This is amazing and informative for me, your blog always readable and informative. Visit for
    Web Development Company

    ReplyDelete
  25. Look into opportunities where you may be able to pay for leads. Paying for leads is not a bad thing at all. In fact there are many companies out there that can deliver you leads at a surprisingly low cost. Just do your homework before signing up with anyone. There are scams out there.

    To understand additional about Digital Marketing Course in Chennai and online marketing training in chennai, please check out SKARTEC Digital Marketing Academy's website for the best online digital marketing courses

    best online digital marketing courses, digital marketing training in chennai, digital marketing course in chennai, digital marketing in chennai, digital marketing course, digital marketing training courses, digital marketing training institute, SKARTEC Digital Mareketing Academy, digital marketing course in chennai, SEO Training in Chennai, digital marketing course syllabus

    Create engaging content. Lead generation relies a lot on building trust with your product or service. Smart targeted content does a lot to help get you there. Your target audience will be more likely to do business with you if they feel you are providing great service and that you legitimately care.

    ReplyDelete
  26. hi, nice information is given in this blog. Thanks for sharing this type of information, it is so useful for me. nice work keep it up.
    digital marketing company in delhi

    ReplyDelete
  27. nice work keep it up thanks for sharing the knowledge.Thanks for sharing this type of information, it is so useful. Laminated Doors manufacturer in hubli

    ReplyDelete
  28. hi, nice information is given in this blog. Thanks for sharing this type of information, it is so useful for me. nice work keep it up. led lawn lights in delhi

    ReplyDelete
  29. THANKS FOR INFORMATION

    you can search low-cost website with high-quality website functions.
    Today Join Us
    Call: +91 - 8076909847

    website designing company in india

    levantro
    best interior designer in delhi


    livewebindia
    best website designing company in delhi

    seo company delhi

    Best It Service Provider:

    1. Website Designing And Development.
    2. SEO Services.
    3. Software Development.
    4. Mobile App Development.

    ReplyDelete
  30. Thanks for sharing such helpful an informative article. I hope you will keep sharing such an informative articles more and more

    ReplyDelete