Skip to content

Instantly share code, notes, and snippets.

@carlosf

carlosf/Readme.md

Forked from NickCraver/Readme.md
Created April 10, 2016 23:42
Show Gist options
  • Save carlosf/a96abdf8857c3f8697d426925589ac57 to your computer and use it in GitHub Desktop.
Save carlosf/a96abdf8857c3f8697d426925589ac57 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @NickCraver NickCraver created this gist Apr 10, 2016.
    36 changes: 36 additions & 0 deletions Readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,36 @@
    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:
    ```c#
    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**.
    350 changes: 350 additions & 0 deletions SQLLoadRunner.linq
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,350 @@
    <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";
    }