{"id":507,"date":"2011-11-18T23:38:00","date_gmt":"2011-11-18T23:38:00","guid":{"rendered":"\/blogs\/jonathan\/post\/The-AdventureWorks2008R2-Books-Online-Random-Workload-Generator.aspx"},"modified":"2017-04-13T12:55:24","modified_gmt":"2017-04-13T16:55:24","slug":"the-adventureworks2008r2-books-online-random-workload-generator","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/","title":{"rendered":"The AdventureWorks2008R2 Books Online Random Workload Generator"},"content":{"rendered":"

\nOver time, I’ve had a number of reasons to need to run a random workload against SQL Server to be able to demonstrate troubleshooting, how SQLOS works, and most recently how to capture a Replay Trace for my series on the Distributed Replay Utility in SQL Server 2012.  For a while I’ve maintained a large workload script that I would run using multiple sqlcmd command line windows to fire off the workload, but one of the problems with this has been that it was incredibly predictable, and it didn’t scale the way I really wanted it to.\n<\/p>\n

\nWhen I was working with Distributed Replay, this became somewhat problematic with generating a randomized workload to capture a Replay Trace off of, so I took a few hours and went back to the drawing board with my idea.  What I came up with was a large script file that contains all of the SELECT statement examples from the SQL Server Books Online (http:\/\/msdn.microsoft.com\/en-us\/library\/ms187731.aspx<\/a>).  This script is divided into separate sections using a delimiter, and then I wrote a PowerShell script that reads the file and breaks it down into individual scripts that are randomly executed against the configured SQL Server using SMO.\n<\/p>\n

\nThe two files required to make use of this are attached to this blog post and can be used with minimal modifications against any SQL Server 2008+ system that has the AdventureWorks2008R2 database attached to it.  To make use of the PowerShell script, you will either have to sign it, or if you work like I do in my VMs, allow unsigned script execution with Set-ExecutionPolicy Unrestricted.\n<\/p>\n

\nThe PowerShell script is incredibly simple code wise.  It loads the SMO assembly, splits the file contents on the delimiter, then inside a infinite loop, it picks a random query and executes it against the SQL Server.\n<\/p>\n

\n

\n\t# Load the SMO assembly
\n\t[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");\n\t<\/p>\n

\n\t# Set the server to run the workload against
\n\t$ServerName = "SQL2012-DB1";\n\t<\/p>\n

\n\t# Split the input on the delimeter
\n\t$Queries = Get-Content -Delimiter "——" -Path "AdventureWorks BOL Workload.sql"\n\t<\/p>\n

\n\tWHILE(1 -eq 1)
\n\t{
\n\t    # Pick a Random Query from the input object
\n\t    $Query = Get-Random -InputObject $Queries;\n\t<\/p>\n

\n\t    #Get a server object which corresponds to the default instance
\n\t    $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName\n\t<\/p>\n

\n\t    # Use the AdventureWorks2008R2 database
\n\t    $srv.ConnectionContext.set_DatabaseName("AdventureWorks2008R2")\n\t<\/p>\n

\n\t    # Execute the query with ExecuteNonQuery
\n\t    $srv.ConnectionContext.ExecuteNonQuery($Query);\n\t<\/p>\n

\n\t    # Disconnect from the server
\n\t    $srv.ConnectionContext.Disconnect();
\n\t   
\n\t    # Sleep for 100 miliseconds between loops
\n\t    Start-Sleep -Milliseconds 100
\n\t}\n\t<\/p>\n<\/blockquote>\n

\nTo generate random workloads, I generally fire up 3-5 copies of this script on a client concurrently and leave it running in the background to generate the load. \n<\/p>\n

\nAdventureWorks BOL Workload.zip (6.35 kb)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Over time, I’ve had a number of reasons to need to run a random workload against SQL Server to be able to demonstrate troubleshooting, how SQLOS works, and most recently how to capture a Replay Trace for my series on the Distributed Replay Utility in SQL Server 2012.  For a while I’ve maintained a large […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,21,32,39,40],"tags":[],"class_list":["post-507","post","type-post","status-publish","format-standard","hentry","category-building-a-test-environment","category-distributed-replay","category-powershell","category-sql-server-2012","category-sql-server-denali"],"yoast_head":"\nThe AdventureWorks2008R2 Books Online Random Workload Generator - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The AdventureWorks2008R2 Books Online Random Workload Generator - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Over time, I’ve had a number of reasons to need to run a random workload against SQL Server to be able to demonstrate troubleshooting, how SQLOS works, and most recently how to capture a Replay Trace for my series on the Distributed Replay Utility in SQL Server 2012.  For a while I’ve maintained a large […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2011-11-18T23:38:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:55:24+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"The AdventureWorks2008R2 Books Online Random Workload Generator\",\"datePublished\":\"2011-11-18T23:38:00+00:00\",\"dateModified\":\"2017-04-13T16:55:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/\"},\"wordCount\":516,\"commentCount\":3,\"articleSection\":[\"Building a Test Environment\",\"Distributed Replay\",\"Powershell\",\"SQL Server 2012\",\"SQL Server Denali\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/\",\"name\":\"The AdventureWorks2008R2 Books Online Random Workload Generator - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2011-11-18T23:38:00+00:00\",\"dateModified\":\"2017-04-13T16:55:24+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-adventureworks2008r2-books-online-random-workload-generator\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Building a Test Environment\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/building-a-test-environment\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"The AdventureWorks2008R2 Books Online Random Workload Generator\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The AdventureWorks2008R2 Books Online Random Workload Generator - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/","og_locale":"en_US","og_type":"article","og_title":"The AdventureWorks2008R2 Books Online Random Workload Generator - Jonathan Kehayias","og_description":"Over time, I’ve had a number of reasons to need to run a random workload against SQL Server to be able to demonstrate troubleshooting, how SQLOS works, and most recently how to capture a Replay Trace for my series on the Distributed Replay Utility in SQL Server 2012.  For a while I’ve maintained a large […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/","og_site_name":"Jonathan Kehayias","article_published_time":"2011-11-18T23:38:00+00:00","article_modified_time":"2017-04-13T16:55:24+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"The AdventureWorks2008R2 Books Online Random Workload Generator","datePublished":"2011-11-18T23:38:00+00:00","dateModified":"2017-04-13T16:55:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/"},"wordCount":516,"commentCount":3,"articleSection":["Building a Test Environment","Distributed Replay","Powershell","SQL Server 2012","SQL Server Denali"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/","name":"The AdventureWorks2008R2 Books Online Random Workload Generator - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2011-11-18T23:38:00+00:00","dateModified":"2017-04-13T16:55:24+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Building a Test Environment","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/building-a-test-environment\/"},{"@type":"ListItem","position":3,"name":"The AdventureWorks2008R2 Books Online Random Workload Generator"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/507","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=507"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/507\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=507"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=507"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=507"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}