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

Friday, May 6, 2016

SQL Saturday Jacksonville #552 Pre-Con: A Day of DBA Fundamentals- Install- Maintenance plans-and Security

Hello Dear Reader!  I'm here in beautiful Jacksonville FL for the SQL Saturday 552 events!  Today my friend Dan Taylor (@DbaBulldog | Blog) and I are presenting our Pre-con A Day of DBA Fundamentals, Install, Maintenance Plans, and Security.

We are having a lot of fun so far, and I wanted to share.  If you are in the class or curios you can download the Deck Here.

More updates as we go!

Final Update 4:43 pm

I just wanted to say a big Thank You to all the people who attended our pre-con today and the SQL Saturday Jacksonville team!

We covered the Azure Portal, SQL Azure DB, Elastic Pools, Azure VM's, and Premium Storage.

I'm exhausted.  The class is exhausted.  It's been a great start to the event.  See you all tomorrow!

Here's the Class waving goodbye!

Update 5

We just finished up group activities working on backups and restores.  Getting the group interaction was key.  A very adapt pupil found a great way to use azure for a highly transactional system.

Not gonna lie I gave them some difficult ones to figure out.  In the process looks like Dan caught me blogging!


Alright up next Maintenance Plans, Azure, and one last group exercise!


Update 4 2:30 pm

At the beginning of the day we split the class into 5 groups.  I told them not to get comfortable and we just showed why.

In this job communication is key.  We just make all the teams regroup and reintroduce themselves.  Next we started work on discussing the importance of backups and restores.  Dan led the way preaching the importance of the right backup strategy for the right business objective.


 During lunch we had a good discussion about how to communicate with the business.  I recomended two books that have become a good starting foundation for me, and a mandatory read for everyone on my old team (Much love to the #TeamBall).

If your interested you can find them here.

Crucial Conversations Tools for Talking When Stakes Are High, Second Edition

Crucial Accountability: Tools for Resolving Violated Expectations, Broken Commitments, and Bad Behavior, Second Edition

Update 1:30 pm

Great lunch by Panera here at the SQL Saturday Jacksonville Pre-Con!  We are having a nice group exercise.  I like watching the teams interact and discuss security and what permissions should be given per scenario.


Will we use AD or SQL Authentication?


Granular permissions?


Dan never could resist a great debate on security!


Update 2 11:49 am

We've taken a break for lunch!  The class exercises so far have been a lot of fun.  We've got a smart crew here today.  So far we've covered the different versions of SQL Server, how features and licensing go together, and using SSMS to create basic options and monitor.

Great participation so far.  We had a nice side discussion on what services should sit side by side.  Lot of caveats with this and this will be a nice blog for a later day.

Oh and Devin Knight stopped by!

OK Lunch Time!



Update 1

Scott Gleason (@SQLScottGleason) Is helping us kick this off!


Great crowd so far!  I wonder what Scott thinks of my performance?


Tough crowd!