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.
About 4 years ago Jonathan created The AdventureWorks2008R2 BOL RandomWorkload Generator (click here to read that blog).
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:
- tweaked the queries removing the compute by clause (deprecated in SQL Server 2008 R2) and replaced it with Cube or Rollup
- I turned all of the queries into stored procedures, and adjusted the workload to call stored procedures
- 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
- This gives a healthy dose of adhoc SQL as well as stored procedure use
- 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:
- Run the Create Stored Procedures for Random SQL Workload Generator.sql
- Change the SQL Server Instance Name in the RandomSQLWorkloadGenerator.ps1 to your instance name
- 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
