A simple LINQPad script I wrote for load testing SQL Server.

Readme.md

This is a simple LINQPad script I wrote one day to load test some large SQL servers. Maybe it's useful to someone. The basic premise is defining your queries once, including which ID patterns to fetch (at the bottom), and load test a mixture. The script defines everything needed in one place, then fires up the command-line linqpad runner to run many queries at once.

Params up top:

const string LinqPadPath = @"C:\Linqpad\lprun.exe";
const bool runSequential = false;
const int defaultThreads = 1;
const int defaultIterations = 2000;
const int toSkip = 0;
int? toRun = 50;

• LinqPadPath: The path to LINQPad's command-line runner (installed by default in the LINQPad directory).
• runSequential: If true, run one query at a time rather than many at once.
• defaultThreads: How many threads per query to run (unless specified on the query). Useful for testing contention.
• defaultIterations: How many iterations of the query to run (unless specified on the query).
• toSkip: How many from the start of the list to skip. e.g. 5 would start at the 6th query.
• toRun: How many in the list to run, starting after the skip count. Set this larger than the list to always run all.

Additionally, on each query in List<Run> runs, there are params available:

• Query: The text of the query
• IdQuery: The text of the parameter population query. I define these at the bottom as constants strictly for re-use but that's not necessary.
• IdParam: Name of the parameter variable (defaults to "Id" for @Id)
• ParamType: Type of the parameter (defaults to SqlDbType.Int)
• Threads: Number of simulatenous workers to run (defaults to defaultThreads above).
• Iterations: Number of iterations to run (defaults to defaultIterations above).

If IdQuery isn't specified, no parameter code is run, the query is simply executed Iterations times.

Notes:

• The path to linqpad to run is up top - if your path is different then change it.
• The script is a launcher of itself
• When maing changes, save the file.

SQLLoadRunner.linq

<Query Kind="Program">
  <Namespace>System.Threading.Tasks</Namespace>
</Query>

SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder
{
	IntegratedSecurity = true,
	MaxPoolSize = 500,
    DataSource = ".",
	InitialCatalog = "Local.StackExchange.Meta"
};

const string LinqPadPath = @"C:\Linqpad\lprun.exe";
const bool runSequential = false;
const int defaultThreads = 1;
const int defaultIterations = 2000;
const int toSkip = 0;
int? toRun = 50;

List<Run> runs = new List<Run>
{
	new Run(@"select /* Models\Post.Answers.cs@51 */
 p.* from Posts p where p.ParentId = @Id", IdQueries.Questions),
	new Run(@"
select  c.Id
      , c.PostId
      , c.[Text]
      , c.CreationDate
      , convert(int, isnull(c.EditCount, 0)) [EditCount]
      , c.Score
      , c.ApplicationId
      , c.UserId [OwnerId]
      , coalesce(u.DisplayName, c.UserDisplayName, 'Anonymous') [OwnerName]
      , convert(nvarchar(20), u.Id) [OwnerUrl]
      , convert(bit, case when u.UserTypeId = 4 then 1 else 0 end) [OwnerIsMod]
      , isnull(u.Reputation, 0) [OwnerReputation]
      , convert(bit, 0) [UserHasVoted]
      , convert(bit, 0) [UserHasFlagged]
from    ( select    row_number() over ( partition by cOrdered.PostId order by isnull(cOrdered.Score, 0) desc, cOrdered.CreationDate ) [Rank]
                  , cOrdered.*
          from      PostComments cOrdered
          where     cOrdered.PostId in (Select p.Id From Posts p Where p.ParentId = @postId Union Select @postId)
                    and cOrdered.DeletionDate is null
                    and isnull(cOrdered.Score, 0) >= 0) [c]
        left outer join Users u on c.UserId = u.Id
where   c.[Rank] <= 5
order by c.CreationDate
option  ( optimize for ( @postId unknown ) )", IdQueries.Questions, "postId") { Iterations = 2000 },
	new Run( @"select p.*, isnull(pm.ViewCount,0) ViewCount from Posts p  /* Models\Post.Selecting.cs@122 */ 
left join PostMetadata pm on pm.PostId = p.Id
where p.Id = @id", IdQueries.Questions),
	new Run(@"Declare @Type tinyint = (2);

select  p.Id /* Models\Post.RelatedQuestions.cs@114 */ 
      , p.Title
      , p.Score
      , CAST(CASE 
            WHEN p.AcceptedAnswerId IS NULL THEN 0
            ELSE 1
        END AS BIT) AS HasAcceptedAnswer
      , pl.Rank
      , pl.CreationDate
from    PostLinks pl
        join Posts p on pl.RelatedPostId = p.Id
where   pl.PostId = @Id
        and pl.LinkTypeId = @Type
        and p.DeletionDate is null", IdQueries.Questions),
	new Run(@"SELECT [t0].[Id], [t0].[UserTypeId], [t0].[Reputation], [t0].[LastAccessDate], [t0].[LastDailySiteAccessDate], [t0].[LastLoginDate], [t0].[LastEmailDate], [t0].[LastLoginIP], [t0].[CreationDate], [t0].[Email], [t0].[DisplayName], [t0].[WebsiteUrl], [t0].[RealName], [t0].[Location], [t0].[Birthday], [t0].[BadgeSummary], [t0].[OptInEmail], [t0].[PreferencesRaw], [t0].[HasReplies], [t0].[TimedPenaltyDate], [t0].[DaysVisitedConsecutive], [t0].[DaysVisitedTotal], [t0].[LastModifiedDate], [t0].[IsVeteran], [t0].[ReputationToday], [t0].[ReputationWeek], [t0].[ReputationMonth], [t0].[ReputationQuarter], [t0].[ReputationYear], [t0].[ReputationSinceLastCheck], [t0].[AcceptRateAsked], [t0].[AcceptRateAccepted], [t0].[AccountId], [t0].[ProfileImageUrl], [t0].[HasAboutMeExcerpt]
FROM [dbo].[Users] AS [t0]
WHERE [t0].[Id] = @p0", IdQueries.Users, "p0"),
	new Run(@"Select top 50
       p.Id 
     , p.Title
     , p.Score
     , CAST(CASE WHEN p.AcceptedAnswerId IS NULL THEN 0 ELSE 1 END AS BIT) AS HasAcceptedAnswer
     , pm.ViewCount
     , p.AnswerCount
     , p.AnswerScore
     , x.LinkTypeId
     , p.CreationDate
     , p.LastActivityDate
From Posts p
     Join PostMetadata pm On p.Id = pm.PostId
     Join (select pl.RelatedPostId Id, pl.LinkTypeId
             from PostLinks pl
            where pl.PostId = @Id 
              and pl.LinkTypeId in (1, 3)
            union
           select pl.PostId, pl.LinkTypeId
             from PostLinks pl
            where pl.RelatedPostId = @Id
              and pl.LinkTypeId in (1, 3)) x On p.Id = x.Id
Where p.DeletionDate is null
  and p.PostTypeId = 1
Order By x.LinkTypeId Desc, ( ( log(isnull(ViewCount, 1) + 1) * 4 ) + ( ( ( isnull(AnswerCount, 1) * Score ) + 1 ) / 5 ) + ( isnull(AnswerScore, 0) ) ) Desc", IdQueries.Questions),
	new Run(@"SELECT [t0].[Id], [t0].[PostTypeId], [t0].[CreationDate], [t0].[Score], [t0].[Body], [t0].[OwnerUserId], [t0].[LastEditorUserId], [t0].[LastEditDate], [t0].[LastActivityDate], [t0].[LastActivityUserId], [t0].[ParentId], [t0].[AcceptedAnswerId], [t0].[Title], [t0].[Tags], [t0].[CommunityOwnedDate], [t0].[HistorySummary], [t0].[AnswerScore], [t0].[AnswerCount], [t0].[CommentCount], [t0].[FavoriteCount], [t0].[DeletionDate], [t0].[ClosedDate], [t0].[LockedDate], [t0].[OwnerDisplayName], [t0].[LastEditorDisplayName], [t0].[BountyAmount], [t0].[BountyCloses], [t0].[BountyClosed], [t0].[LastOwnerEmailDate], [t0].[ProtectedDate], [t0].[MigrationDate], [t0].[IsAcceptedAnswer], [t0].[IsAnswered], [t0].[HasNotices], [t0].[IsFrozen] AS [IsFrozenRaw], [t0].[QualityScore], [t0].[IsClosedAsDuplicate], [t0].[LastActivityTypeId]
FROM [dbo].[Posts] AS [t0]
WHERE [t0].[Id] = @p0", IdQueries.AllPosts, "p0"),
	new Run(@"SELECT /* Controllers\QuestionsController.Show.cs@318 */
 MAX(CreationDate) FROM PostComments WHERE DeletionDate IS NULL AND PostId IN (SELECT Id FROM Posts WHERE Id = @Id OR ParentId = @Id)", IdQueries.Questions),
	new Run(@"SELECT /* Controllers\QuestionsController.Show.cs@330 */
 LastRelatedQuestionRefreshDate from PostMetadata WHERE PostId = @Id", IdQueries.Questions),
	new Run(@"
SELECT /* Controllers\QuestionsController.Show.cs@324 */
 MAX(DeletionDate) FROM Posts WHERE DeletionDate IS NOT NULL AND (Id = @Id OR ParentId = @Id)", IdQueries.Questions),
	new Run(@"
select u.Id,  /* Controllers\QuestionsController.Show.cs@496 */ 
       u.UserTypeId,
       u.DisplayName, 
       u.Reputation, 
       u.BadgeSummary,
       u.Email,
       u.LastLoginIP,
       u.TimedPenaltyDate,
       u.ProfileImageUrl,
       u.HasAboutMeExcerpt, 
	   AcceptRateAsked, 
	   AcceptRateAccepted 
  from Users u 
 where Id in (@Id)", IdQueries.Users),
	new Run(@"
Select PostTypeId,
       Sum(Case When ClosedDate Is Not Null Then 1 Else 0 End) as ClosedPosts,
       Sum(Case When ClosedDate Is Null And DeletionDate Is Null Then 1 Else 0 End) as OpenPosts,
       Sum(Case When DeletionDate Is Not Null Then 1 Else 0 End) as DeletedPosts,
       Sum(IsNull(CommentCount, 0)) Comments
  From Posts
 Where PostTypeId In (1,2)
Group By PostTypeId
Option(MAXDOP 4)"),
};

void Main(string[] args)
{
	try
	{
		if (args != null && args.Length == 1)
		{
			int index;
			if (int.TryParse(args[0], out index) && index < runs.Count)
				DoRun(index);
		}
		else
		{
			var runningCount = Math.Min(toSkip + toRun.GetValueOrDefault(runs.Count), runs.Count) - toSkip;
			$"{runningCount} Quer{(runningCount == 1 ? "y" : "ies")} running...".Dump();
			var timers = new List<Stopwatch>(runs.Count);
			for (var i = toSkip; i < toSkip + runningCount; i++)
			{
				var j = i;
				var run = runs[j];
				var procInfo = new ProcessStartInfo()
				{
					FileName = LinqPadPath,
					Arguments = $@"""{Util.CurrentQueryPath}"" {j}",
					UseShellExecute = false,
					RedirectStandardOutput = true,
					RedirectStandardError = true,
	 			   	CreateNoWindow = true,
   					WindowStyle = ProcessWindowStyle.Hidden
				};
				var pb = new Util.ProgressBar($"Query {j}")
				{
					HideWhenCompleted = true
				};
				var timer = Stopwatch.StartNew();
				timers.Add(timer);
				var proc = Process.Start(procInfo);
				proc.EnableRaisingEvents = true;
				proc.BeginOutputReadLine();
				proc.OutputDataReceived += (s, e) =>
				{
					if (e.Data?.Contains("workers started") == true) timer.Restart();
					if (e.Data?.Contains("Done!") == true)
					{
						timer.Stop();
						pb.Visible = false;
					}
					if (e.Data?.Contains(" to run") == true)
					{
						var val = int.Parse(e.Data.Replace(" to run", ""));
						run.Iterations = val;
						pb.Dump();
                    }
					if (e.Data?.Contains(" runs complete") == true)
					{
						var val = int.Parse(e.Data.Replace(" runs complete", ""));
						if (run.Iterations > 0)
						{
							pb.Fraction = (val / (double)run.Iterations.GetValueOrDefault());
							pb.Caption = $"Query {j}: {val} / {run.Iterations.GetValueOrDefault()}";
						}
					}
				};
				if (runSequential) proc.WaitForExit();
			}
			while (timers.Any(t => t.IsRunning))
			{
				Util.Progress = (timers.Count(t => !t.IsRunning) / timers.Count);
				Thread.Sleep(1000);
			}
			var totalMs = timers.Sum(t => t.ElapsedMilliseconds);
			var results = new List<Result>();
			$"Total Run: {totalMs:n0}ms".Dump();
			for (var i = 0; i < timers.Count; i++)
			{
				results.Add(new Result { Id = i, Duration = timers[i].Elapsed, Run = runs[i] });
			}
			results.Select(r => new { r.Id, r.Run.Iterations, r.Duration.TotalMilliseconds, r.Run.Query }).Dump();
		}
	}
	catch (Exception e)
	{
		$"{e}".Dump();
	}
}

public class Result
{
	public int Id { get; set; }
	public TimeSpan Duration { get; set; }
	public Run Run { get; set; }
}

private SqlConnection GetConnection => new SqlConnection(csb.ConnectionString);
List<List<int>> questionIds = new List<List<int>>();

private void DoRun(int id)
{
	var run = runs[id];
	ThreadPool.SetMinThreads(run.Threads + 2, run.Threads + 2);
	$"Id: {id}".Dump();
	$"Query: {run.Query}".Dump();
	$"Threads: {run.Threads}".Dump();
	for (var i = 0; i < run.Threads; i++)
	{
		questionIds.Add(new List<int>());
	}

	if (!string.IsNullOrEmpty(run.IdQuery))
	{
		using (var conn = GetConnection)
		using (var cmd = conn.CreateCommand())
		{
			conn.Open();
			cmd.CommandText = run.IdQuery;
			cmd.Parameters.Add("runs", SqlDbType.Int).Value = run.Iterations;
			using (var dr = cmd.ExecuteReader())
			{
				int i = 0;
				while (dr.Read() && i < run.Iterations)
				{
					questionIds[i % run.Threads].Add(dr.GetInt32(0));
					i++;
				}
				$"{i} to run".Dump();
			}
		}
		$"{questionIds.Count} queues loaded".Dump();

		for (var i = 0; i < questionIds.Count; i++)
		{
			var j = i;
			$"Queue {j}: {questionIds[j].Count} items".Dump();
		}
	}
	else
	{
		$"{run.Iterations} to run".Dump();
	}

	var workers = new Task[run.Threads];
	for (int i = 0; i < run.Threads; i++)
	{
		var j = i;
		Task t = Task.Factory.StartNew(() => RunQueries(run, questionIds[j]), TaskCreationOptions.LongRunning);
		workers[j] = t;
	}
	$"{workers.Length} workers started".Dump();
	Task.WaitAll(workers);
	$"Done!".Dump();
}

public static int procRuns;

private void RunQueries(Run run, List<int> queue)
{
	var hasIds = !string.IsNullOrEmpty(run.IdQuery);
	using (var conn = GetConnection)
	{
		conn.Open();
		using (var ru = conn.CreateCommand())
		{
			ru.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
			ru.ExecuteNonQuery();
        }
		using (var cmd = conn.CreateCommand())
		{
			cmd.CommandText = run.Query;
			cmd.CommandTimeout = 600;
			if (!string.IsNullOrEmpty(run.IdQuery))
			{
				cmd.Parameters.Add(run.IdParam, run.ParamType);
				foreach (var Id in queue)
				{
					cmd.Parameters[run.IdParam].Value = Id;
					cmd.ExecuteNonQuery();
					var result = Interlocked.Increment(ref procRuns);
					$"{result} runs complete".Dump();
				}
			}
			else
			{
				foreach (var Id in queue) cmd.ExecuteNonQuery();
			}
		}
	}
}

public class Run
{
	public string Query { get; set; }
	public string IdQuery { get; set; }
	public string IdParam { get; set; }
	public SqlDbType ParamType { get; set; }
	public int Threads { get; set; }
	public int? Iterations { get; set; }

	public Run(string query, string idQuery = null, string idParam = "Id", SqlDbType paramType = SqlDbType.Int)
	{
		Query = query;
		IdParam = idParam;
		ParamType = paramType;
		IdQuery = idQuery;
		Threads = defaultThreads;
		Iterations = defaultIterations;
	}
}

public static class IdQueries
{
	public const string Questions = "Select Top (@runs) Id From Posts Where PostTypeId = 1 Order By Id";
	public const string Answers = "Select Top (@runs) Id From Posts Where PostTypeId = 2 Order By Id";
	public const string Users = "Select Top (@runs) Id From Users Order By Id";
	public const string AllPosts = "Select Top (@runs) Id From Posts Order By Id";
	public const string Tags = "Select Top (@runs) Id From Tags Order By Id";
	public const string ReviewTaskTypes = "Select Top (@runs) Cast(Id as int) Id From ReviewTaskTypes Order By Id";
	public const string Accounts = "Select Top (@runs) AccountId From Users Where AccountId Is Not Null Order By Id";
	public const string Badges = "Select Top (@runs) Id From Badges Order By Id";
}