Forem: DEAC European Data Center Operator

SQL Server Query Optimizer. And how it can hang your business.

Jānis Veinbergs — Wed, 03 Aug 2022 09:55:00 +0000

Dynamics AX works well until it comes to a grinding halt. Without any software or hardware configuration change. How come?

Enter SQL Server Query Optimizer. And how to tame it.

So occasionally some part of functionality looses performance (i.e you can never finish your operation, it just hangs). Sometimes it impacts so many areas that various real life operations are severely impacted. Having experience administering this particular system meant I should look at SQL Server that causes most of performance issues.

Finding the issue

The first thing is to look at queries which take most of the time in particular timeframe. So someone complains something is going bad - I look at last 2 hours of data collected with SQL Sentry (Now SentryOne)*
A-ha, some very suspicious query - average execution time is ~25s. Moreover it does many physical reads (reads from disk, data not cached into RAM). This was very easy to spot, but sometimes you get bad queries with 100ms execution time.
```
(@P1 nvarchar(5),@P2 nvarchar(21),@P3 datetime)SELECT 
<list_of_224_columns> FROM CUSTINVOICEJOUR A WHERE 
((DATAAREAID=@P1) AND ((LEDGERVOUCHER=@P2) AND (INVOICEDATE=@P3)))
```
A super simple query if you ask me, albeit pretty wide one.
Lets look at the query plan. On Query History page I can right click and press Open Plan. At first I see a Clustered Index Seek and nothing else - looks perfect.

Until I put my mouse over that index to see what columns it seeks over. I look at Seek Predicates (columns which utilize index) and Predicate (columns which could utilize index, but does not). And we see only 1 column DATAAREAID at Seek Predicates, which is a very rough data categorization column with only a few distinct values in there.

So it is more like data scanning most of the table rows each time this query executes. At previous screenshot we see it did that 180 times over period of ~2hrs.
What we ideally want to see there is many Seek Predicate columns and possibly few Predicate ones (but it depends on the query whether it is possible). So we are dealing with Queries that have parameter sensitive plan (PSP) problems.

To get a sense of how indexes are used, what does clustered index mean, what is key lookup (or bookmarked lookup) look at Clustered Index article.
So culprit is identified (diagnostics is usually most of the work). Isn't there a better index for this query to use? Let's check. Execute sp_helpindex 'dbo.CUSTINVOICEJOUR' - or better yet sp_sqlskills_helpindex 'dbo.CUSTINVOICEJOUR' (sp_sqlskills_helpindex)

From query plan we saw it uses INVOICENUMIDX which is clustered index and it can only use first column for seek. However there is much better index: LEDGERVOUCHERIDX which has all 3 key columns that could be used for seeking: DATAAREAID, LEDGERVOUCHER and INVOICEDATE.

Why are we facing this issue?

Why did SQL Server choose obviously much more worse index? My theory is as follows: The Query plan estimated (out of STATISTICS) that there will be only 1 row. Why? If you look at query plan XML, you can see on what query parameters (Actual values for @P1, @P2, @P3) were present when SQL decided to generate & cache this particular query plan:

                <ParameterList>
                  <ColumnReference Column="@P3" ParameterCompiledValue="'2022-07-26 00:00:00.000'" />
                  <ColumnReference Column="@P2" ParameterCompiledValue="N'<something>'" />
                  <ColumnReference Column="@P1" ParameterCompiledValue="N'DMT'" />
                </ParameterList>

@P1 corresponds to DATAAREAID. Lets look at statistics - how much rows does SQL server expect to find:

DBCC SHOW_STATISTICS ('dbo.CUSTINVOICEJOUR', 'DATAAREAID')
Could not locate statistics 'DATAAREAID' in the system catalogs.

Alright, no statistics for particular column - we have to look at index statistics then:

DBCC SHOW_STATISTICS ('dbo.CUSTINVOICEJOUR', 'I_062LEDGERVOUCHERIDX')

Sorry I'm not showing you actual values, however none of the rows say DMT. Basically, when SQL server updates statistics (it looks at each 5th row or whatever percentage of rows you ask SQL to look at) - and it didn't see even one where there would be DATAAREAID = DMT. Thus SQL Server estimates there is 1 row (it never estimates 0). However SQL thinks: well, if I'll get 1 row from index LEDGERVOUCHERIDX using only first key column DATAAREAID, I'll have to lookup all those selected columns within clustered index INVOICENUMIDX - why not just directly get that row from INVOICENUMIDX which also begins with DATAAREAID and spare a key lookup (as all the data will already be within the page)? (Understanding Key Lookups)

For these particular parameters SQL Server has really found the best way to return the data, no questions about that.

However, the problem (or a feature) is that SQL Server caches this query plan. Suppose DATAAREAID now would be something that statistics shows us we have 352 million rows - and SQL will now get each of this row, filter out linearly INVOCIEDATE and LEDGERVOUCHER (because chosen index doesn't have data arranged in a way that index can be utilized to not read that data in the first place).

Solutions?

How about preventing SQL server cache this particular query plan at all? It is possible to do with plan guides, but it is a bad idea: if query is set to execute multiple hundreds of times per second, then query plan compilation adds >10ms of time to execute. Ideally this query would execute in ~1ms time (working few years on the same system kind of tells you how long that simple query should take), so we don't want at LEAST 10x overhead to query execution. Bad idea in this case. But could work for queries that get executed few times OR queries that by itself takes 50ms or more to run.
Previously mentioned Queries that have parameter sensitive plan (PSP) lists other hints we could use, but I'll go over and use my favorite, most stable option.
Tell SQL Server to use particular index using plan guides. That's what we are going to do.

What are plan guides? As documentation puts it:

Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server. Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected.

Perfect:

I cannot directly change the query
I can influence query optimization
Subset of queries from third-party vendor query is not performing well.

Creating plan guide

EXEC sp_create_plan_guide
    @name = N'[QNW-319-445612-custinvoicejour-perf]',
    @stmt = 'SELECT <list_of_224_columns> FROM CUSTINVOICEJOUR A WHERE ((DATAAREAID=@P1) AND ((LEDGERVOUCHER=@P2) AND (INVOICEDATE=@P3)))', 
    @type = N'SQL',
    @params = N'@P1 nvarchar(5),@P2 nvarchar(21),@P3 datetime',
    @hints = N'OPTION(TABLE HINT(A, INDEX(I_062LEDGERVOUCHERIDX)))'
GO

When plan guide is created, it immediately invalidates the existing plan and when such query comes in, generates new query plan taking into account plan guide hints.

Now we must validate:

Whether new query plan uses this plan guide (i.e we provided correct @stmt and @params vars for sp_create_plan_guide)
What is the performance of the query - better or worse?

To validate the first point, we must get query from plan cache and find PlanGuideName attribute within XML. Here is a query that, among some statistical stuff, has uses_plan_guide column and actually provides a way to open query plane and validate that plan guide is in use:

select TOP 100
    execution_count,
    sql_handle, qs.query_hash, plan_handle, qs.query_plan_hash, qt.text,
    [query_plan] = CAST (qp.query_plan AS XML),
    creation_time,
    last_execution_time,
     qs.total_logical_reads,
     qs.last_logical_reads,
     qs.min_logical_reads,
     qs.max_logical_reads,
     qs.last_logical_writes,
     qs.min_logical_writes,
     qs.max_logical_writes,
     [avg_logical_reads] = qs.total_logical_reads / 1.0 / qs.execution_count,
     [total_elapsed_time_ms] = qs.total_elapsed_time / 1000.0,
     [last_elapsed_time_ms] = qs.last_elapsed_time / 1000.0,
     [min_elapsed_time_ms] = qs.min_elapsed_time / 1000.0,
     [max_elapsed_time_ms] = qs.max_elapsed_time / 1000.0,
     [avg_elapsed_time_ms] = qs.total_elapsed_time / qs.execution_count / 1000.0,
     [min_worker_time_ms] = qs.min_worker_time / 1000.0,
     [max_worker_time_ms] = qs.max_worker_time / 1000.0,
     [avg_worker_time_ms] = qs.total_worker_time / qs.execution_count / 1000.0,
     [force_index] = CASE CHARINDEX(N' INDEX(', qt.text) WHEN 0 THEN 0 ELSE 1 END,
     [uses_plan_guide] = CASE CHARINDEX(N' PlanGuideName=', qp.query_plan) WHEN 0 THEN 0 ELSE 1 END,
     [create_plan_guide] = 'sp_create_plan_guide_from_handle @name = N''[provide_name]'', @plan_handle = ' + CONVERT(NVARCHAR(MAX), qs.plan_handle, 1) + ', @statement_start_offset = ' + CAST(qs.statement_start_offset as NVARCHAR(100)) -- you can use this statement to force this query use currently cached query plan
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
   CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE
    qt.text LIKE N'%<list_of_224_columns> FROM CUSTINVOICEJOUR A WHERE ((DATAAREAID=@P1) AND ((LEDGERVOUCHER=@P2) AND (INVOICEDATE=@P3)))'
ORDER BY qs.total_worker_time DESC

And this query actually gives answer to the second question too - avg_elapsed_time_ms is of intereset. For me it reads 0,32ms. I can't ask for more! Moreover, within 41 seconds it has execution_count of 515 times. Compare that to 249 executions in 186 minutes...

In fact, the job completed so fast, it is no more to be seen on SQL Sentry query stats which has configured 5 seconds of total duration for it to be included. So unless it executes 15k+ times within a minute, I'm not seeing it there.

By the way, if you ever delete/rename index, that doesn't mean SQL Server will error out executing that query - it will disregard the plan guide when compiling query plan. There are performance counter to monitor to know when you have stale plan guides: Misguided Plan Executions/sec

Microsoft is on it

This is certainly a flaw of SQL Server being dumb. However since SQL Server 2017 and onwards, Microsoft is tackling performance issues with some intelligent query processing which includes various feedback mechanisms that SQL Server keeps an eye on whether the particular query plan strategy at hand is good enough and adjusts accordingly. These look promising:

If only the application supported latest-and-greatest SQL Server... so I haven't been able to test out those features.

Anyways, I'm happy that the results are delivered and the system can continue working until the next query is to be found misbehaving...

* Note that for newer SQL Servers there are tools that may assist you with finding bad performing queries, like Query Store. For more advanced DB Users Data Collection could be used or just querying plan cache and sorting by qs.total_elapsed_time if the query plan is still there. System uses SQL Server 2008R2 that doesn't have Query Store, so using SQL Sentry is just doing the job faster.

Bringing down ASP.NET deployment interruptions from minutes to blip of a time with Azure DevOps

Jānis Veinbergs — Fri, 01 Apr 2022 08:07:32 +0000

This is going to be about improving deployment process from classic file copy to something more effective, automated and low friction for developers and bringing no interruptions to users. Maximizing Developer Effectiveness is actually competitive advantage after all.

Pain points

Our customer, a national school management platform was experiencing inconvenience when deploying a fairly big classic .NET Framework application to 22 Windows IIS servers:

Deployments would be taken at night
If a hotfix had to be issued at day with high load - it could take up to 30 minutes of complete system halt as IIS server farm compiles resources while pile of requests waits in a queue.
The deployment is not atomic - that is, while files are being copied between v1 and v2, we would get v1.314 - while some files are updated, the others not.
If we ever wanted a rollback - occasionally files would be locked, exacerbating already bad situation.
The website itself consists of ~220MB of data across ~2500 number of files. And small file copy is SLOW.

The root cause of this is that .NET resources (.aspx pages, razor views) get compiled to assemblies on first request whenever they change. When IIS is constantly bombarded with requests to all possible resources, it all must be compiled at the same time on every IIS server. On the other hand, when deployed at night, delay was minor as not many requests came in, and those that did, compiling was handled fairly quickly.

ASP.NET Requests Queued is a good performance counter to monitor to identify when user browsers are just waiting for response and how quickly situation resolves after an update. A healthy value is 0 or something close to that. Here you see cumulative request queue for all IIS servers in evening, when load actually is not high. 4 minutes of interruptions:

We thought about using robocopy to copy changed files based on timestamp, but that wouldn't work for us - as there could be multiple persons that would deploy the code. When Visual Studio Publishes the project, files between machines have different timestamps. Rsync would probably help greatly, but then again - some challenge to get that on developers windows machines and windows share server. And if different hosts build files, little differences in build tools may end up with different checksums. So we didn't go down this path.

The webroot (and IIS Shared Configuration) is actually served from a single Windows share, which at least provided a low effort way to conveniently copy files to a single location and keep them, along with the IIS configuration, in sync.

That's how we lived for years and it was clear that something had to change.

Deployment strategy

We came to a decision we would like to stop depending on a file share. We already started using Azure DevOps for some other stuff. So we got an idea 💡

Keep files on IIS local disk (Duh)
Azure DevOps Agent would be the one compiling code.
We would precompile views in advance. Locking wouldn't be an issue as we will copy within a new folder. 💎
Orchestrate deployment to all IIS servers via Azure DevOps Release Pipelines
Run all .dll files through ngen. That would eliminate additional delay when assembly is first loaded. As C# code compiled is actually MSIL - an intermediate language that CPU doesn't understand any of that. JIT compiler is the one that translates that to bytecode for the processor architecture at hand. And it does so usually Just-In-Time or On-The-Fly. Or, in our case, ngen (Native Image Generator)
A deployment task would have to copy files within a new folder and then we would change IIS Physical path from where to serve site files. 💎

So there are some gems there that are enough to solve our pain points. Now let's see some practical ways on achieving all of this!

Implementing build

Build is the process of generating files we must copy onto IIS servers.

Before we talk about build & deployment, let's get this straight: We are using Azure DevOps online offering. However this could as well be On-Premises installation or any other CI/CD platform of your choice. We are, however, using self-hosted agents that perform the build tasks.

We are talking about solution, which consists of multiple .NET projects. Our main interest is deploying ....Web.Application and ..Web.Application.Payments. The others are mostly dependencies which must also be built. So, the developers used to build app within Visual Studio. When deploying web application, you had to right click on particular project and choose publish. From there, some settings could be configured like whether we want precompilation or not, whether we deploy straight to IIS or filesystem. In our case, it was deployed to filesystem and then copied to production. More about Publishing an ASP.NET web app.

When writing Azure DevOps pipeline, I have to figure out what kind of MSBuild properties I have to use to invoke the "Publish" process. So, after searching the web, reading MSBuild diagnostic output logs, reading MSBuild .target files, I'v come up with properties I need.

By the way, here is a quick tip on how to find relevant .target files by some keyword:

> gci -Path "${env:ProgramFiles(x86)}\Microsoft Visual Studio\" -Recurse -Filter "*.targets" | sls "MvcBuildViews" -SimpleMatch -List
C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\Microsoft\VisualStudio\v15.0\Web\Microsoft.Web.Publishing.targets:849:  <Target Name="CleanupForBuildMvcViews" Condition=" '$(_EnableCleanOnBuildForMvcViews)'=='true' and '$(MVCBuildViews)'=='true' " BeforeTargets="MvcBuildViews">
C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\MSBuild\Microsoft\VisualStudio\v16.0\Web\Microsoft.Web.Publishing.targets:847:  <Target Name="CleanupForBuildMvcViews" Condition=" '$(_EnableCleanOnBuildForMvcViews)'=='true' and '$(MVCBuildViews)'=='true' " BeforeTargets="MvcBuildViews">

Or better yet, use Project System Tools extension with MSBuild Binary and Structured Log Viewer to sneak peek into what MSBuild is doing - you won't regret it.

So, the relevant "Publish" command within Azure Devops build pipeline:

- task: VSBuild@1
  displayName: Publish Web.Application  
  inputs:
    solution: 'Web.Application'
    msbuildArgs: >
      /t:Build,GatherAllFilesToPublish
      /p:PublishProfileName="$(publishProfileName)"
      /p:WebPublishMethod=FileSystem
      /p:DeleteExistingFiles=true
      /p:DeployOnBuild=true
      /p:MvcBuildViews="${{ parameters.Precompile }}"
      /p:PrecompileBeforePublish="${{ parameters.Precompile }}"
      /p:WDPMergeOption="MergeAllOutputsToASingleAssembly"
      /p:SingleAssemblyName="Web.Application.Precompiled"
      /p:UseMerge="true"
      /p:DebugSymbols="True"
      /p:EnableUpdateable="False"
      /p:PublishUrl="$(Build.BinariesDirectory)\my"
      /p:WPPAllFilesInSingleFolder="$(Build.BinariesDirectory)\my"
      /p:RunNpmScripts="$(runNpmScripts)"
      /p:AutoParameterizationWebConfigConnectionStrings="false"
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
    msbuildArchitecture: x64
    logFileVerbosity: detailed

Note that specifying publishProfileName, I use the actual publish profile used by Visual Studio Publish process and then override some properties by passing /p msbuild arguments.
The WebPublishMethod ensures build files are put within PublishUrl folder.
RunNpmScripts is our own custom build property used within .pubxml to run some npm build process.
MvcBuildViews, PrecompileBeforePublish, SingleAssemblyName, UseMerge, EnableUpdateable all relate to precompilation. Before build, I can choose to disable precompilation if I want the build to happen much faster.
AutoParameterizationWebConfigConnectionStrings - required for copy/paste if publish profile contains connection string replacements. Without this, there will be a placeholder value that must be replaced afterwards. More on StackOverflow.

The full build can be seen here:

# ASP.NET
# Build and test ASP.NET projects.
# Add steps that publish symbols, save build artifacts, deploy, and more:
# https://docs.microsoft.com/azure/devops/pipelines/apps/aspnet/build-aspnet-4
trigger: none
name: $(Build.SourceBranchName) $(Date:yyyyMMdd)$(Rev:.r)

pool:
  name: win-dev-pool

parameters:
  - name: Precompile
    default: false
    type: boolean
    displayName: Precompile

variables:
  webApplicationProject: 'Web.Application'
  solution: 'solution.sln'
  buildPlatform: 'x64'
  buildConfiguration: 'Release'
  releaseArchiveFilename: 'webapp.7z'
  releasePaymentsArchiveFilename: 'payments.7z'
  # which .pubxml file to use. Don't append .pubxml
  publishProfileName: STAGING
  runNpmScripts: true
  artifactShareName: \\MY-APP-AGENT2\agentpublishedfiles

steps:
- task: NuGetToolInstaller@1
- task: NuGetCommand@2
  inputs:
    command: 'restore'
    restoreSolution: '$(solution)'
    feedsToUse: 'config'
    nugetConfigPath: 'NuGet.Config'

# npm script prerequisites
- task: NodeTool@0
  inputs:
    versionSpec: '14.x'

# https://docs.microsoft.com/en-us/azure/devops/pipelines/release/caching?view=azure-devops#nodejsnpm
- task: Cache@2
  displayName: Cache npm
  inputs:
    key: 'v2 | npm | "$(Agent.OS)" | $(webApplicationProject)/package.json'
    path: '$(webApplicationProject)/node_modules'
    restoreKeys: 'v2 | npm | "$(Agent.OS)"'
    cacheHitVar: NPM_CACHE_RESTORED
  condition: and(succeeded(), variables.runNpmScripts)

- task: npmAuthenticate@0
  inputs:
    workingFile: '$(webApplicationProject)/.npmrc'
- task: Npm@1
  displayName: 'npm install'
  inputs:
    command: 'install'
    workingDir: '$(webApplicationProject)/'
  condition: and(succeeded(), variables.runNpmScripts, ne(variables.NPM_CACHE_RESTORED, 'true'))

# Workaround for AspNetPrecompile to skip scanning node_modules directory and finding .c,.cpp,.h files... https://stackoverflow.com/a/20963170/50173
- task: CmdLine@2
  displayName: Hide node_modules.
  inputs:
    script: 'attrib +H $(webApplicationProject)/node_modules'

# Specificually pass PublishProfileName as empty. Because otherwise in consequent runs, this task will run npm run-script that is specified as BeforeBuild target within publish profile.
# We already run publish actions further.
- task: VSBuild@1
  displayName: Build $(solution)
  inputs:
    solution: '$(solution)'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
    msbuildArchitecture: x64
    logFileVerbosity: detailed
    msbuildArgs: >
      /p:PublishProfileName=""

- task: VSTest@2
  displayName: Test $(solution)
  inputs:
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
  condition: ne(variables.NoTests, true)

# Target also Build, otherwise BeforeBuild target won't execute. BeforeBuild specifies npm run-script commands. So, this project gets built twice, but project build by itself is fast.
# PublishUrl actually unused as we won't use msdeploy to deploy stuff, just simple copy.
# Precompilation decision matrix: https://docs.microsoft.com/en-us/previous-versions/aspnet/bb398860(v=vs.100)

- task: VSBuild@1
  displayName: Publish Web.Application  
  inputs:
    solution: 'Web.Application'
    msbuildArgs: >
      /t:Build,GatherAllFilesToPublish
      /p:PublishProfileName="$(publishProfileName)"
      /p:WebPublishMethod=FileSystem
      /p:DeleteExistingFiles=true
      /p:DeployOnBuild=true
      /p:MvcBuildViews="${{ parameters.Precompile }}"
      /p:PrecompileBeforePublish="${{ parameters.Precompile }}"
      /p:WDPMergeOption="MergeAllOutputsToASingleAssembly"
      /p:SingleAssemblyName="Web.Application.Precompiled"
      /p:UseMerge="true"
      /p:DebugSymbols="True"
      /p:EnableUpdateable="False"
      /p:PublishUrl="$(Build.BinariesDirectory)\my"
      /p:WPPAllFilesInSingleFolder="$(Build.BinariesDirectory)\my"
      /p:RunNpmScripts="$(runNpmScripts)"
      /p:AutoParameterizationWebConfigConnectionStrings="false"
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
    msbuildArchitecture: x64
    logFileVerbosity: detailed


- task: VSBuild@1
  displayName: Publish Web.Application.Payments  
  inputs:
    solution: 'Web.Application.Payments'
    msbuildArgs: >
      /t:Build,GatherAllFilesToPublish
      /p:PublishProfileName="$(publishProfileName)"
      /p:WebPublishMethod=FileSystem
      /p:DeleteExistingFiles=true
      /p:DeployOnBuild=true
      /p:MvcBuildViews="${{ parameters.Precompile }}"
      /p:PrecompileBeforePublish="${{ parameters.Precompile }}"
      /p:WDPMergeOption="MergeAllOutputsToASingleAssembly"
      /p:SingleAssemblyName="Web.Application.Payments.Precompiled"
      /p:UseMerge="true"
      /p:DebugSymbols="True"
      /p:EnableUpdateable="False"
      /p:PublishUrl="$(Build.BinariesDirectory)\payments"
      /p:WPPAllFilesInSingleFolder="$(Build.BinariesDirectory)\payments"
      /p:AutoParameterizationWebConfigConnectionStrings="false"
    platform: 'AnyCPU'
    configuration: '$(buildConfiguration)'
    msbuildArchitecture: x64
    logFileVerbosity: detailed

# Artifacts
- task: ArchiveFiles@2
  displayName: Archive $(releaseArchiveFilename)
  inputs:
    rootFolderOrFile: '$(Build.BinariesDirectory)\my'
    includeRootFolder: false
    archiveType: '7z'
    sevenZipCompression: 'fastest'
    archiveFile: '$(Build.ArtifactStagingDirectory)/$(releaseArchiveFilename)'
    replaceExistingArchive: true
    verbose: true
- task: ArchiveFiles@2
  displayName: Archive $(releasePaymentsArchiveFilename)
  inputs:
    rootFolderOrFile: '$(Build.BinariesDirectory)\payments'
    includeRootFolder: false
    archiveType: '7z'
    sevenZipCompression: 'fastest'
    archiveFile: '$(Build.ArtifactStagingDirectory)/$(releasePaymentsArchiveFilename)'
    replaceExistingArchive: true
    verbose: true

- task: PublishBuildArtifacts@1
  displayName: Publish website deployment Artifacts
  inputs:
    PathtoPublish: '$(Build.ArtifactStagingDirectory)'
    ArtifactName: 'drop'
    publishLocation: 'FilePath'
    TargetPath: '$(artifactShareName)\my'

What this does, is builds solution (dependency .dlls)
Builds JavaScript SPA (Single Page App). Commands are buried within MSBuild project.
Publishes 2 applications (Just generates files on disk)
7zips those files
Publish artifact (Azure Devops thingie - makes them available for release pipeline). In this case, they are copied to a share.

Implementing deployment

Once we have the artifacts ready (archive of website files), we are ready to implement Release. The release must copy given files to some directory and instruct IIS to change base path from where it will server files. We had 2 options to choose from:

Make Build agent connect to IIS servers and perform the deployment on each IIS server. In this case, we have to write some script that will copy files onto each server and issue some commands to IIS. Moreover, we must control/see whether deployment is successful or not. And the build agent could actually be in another domain, with no direct access to production.
Install Deployment agent on all target IIS servers. Every host then receives deployment job and does whatever it is instructed to do. We don't have to bother about any other remote communication channel other than Deployment Agent with Azure DevOps server over 443/TCP. Plus we get nice UI of seeing whether deployment succeeded, partially succeeded (if partially, which hosts failed) and on which step it failed. Neat.

We went for the second option. Deployment agent is actually almost same as Build agent, just carries on deployment tasks.

When creating release pipeline, you get to play with the UI which is nice.

As you see, I'v split some operations into multiple steps.

Root App Pool - ensures appropriate application pool is created on IIS. It is actually a one-time step and may have been created beforehand.
Copy my files - PowerShell script to extract 7z files:

Expand-7Zip -ArchiveFileName "$(System.DefaultWorkingDirectory)\_Web.Application\drop\webapp.7z" -TargetPath "$(DeployTargetFolder)"

Ngen my - runs ngen.exe on all .dll files found within deployment folder. Also using custom condition so this step which may take a little more than a minute, could be turned off: and(succeeded(), eq(variables.Ngen, 'true'))

Set-Alias ngen -Value (Get-ChildItem -Recurse $env:windir\Microsoft.NET\Framework64\ -Filter ngen.exe | ? Length -gt 0 | select -first 1).Fullname

Get-ChildItem $(DeployTargetFolder) -Recurse -Filter *.dll | % { ngen install $_.Fullname /nologo /verbose }

Deploy my - Switchers virtual directory, issues some IIS configuration commands. When this stage is completed, IIS servers new code.

The great thing is that if any of steps fail for ANY IIS server before Deploy step, deploy won't run for ANY IIS server and they will all be still consistent.

In case of a rollback, we can open appropriate release and for "Deploy my" stage, press Redeploy. IIS will immediately switch back to appropriate folder.

Drawbacks

Using cloud hosted solution, if issues do happen, we won't be able to run our deployment pipeline. And they do happen. However, this may impact us on the rare case of rushing some kind of hotfix out. There are 2 steps we can take in this case:

Change IIS path manually to previous deployment folder (rollback)
Build via developer Visual Studio and copy appropriate DLL manually. Hotfix usually doesn't involve much code changes and is probably contained within a single or few files.

Otherwise we just wait for while DevOps issue gets resolved.

End result

Everything is actually configured that, when production branch gets new code, build is run automatically. After build, deployment is run automatically but stops for an approval. With Azure Pipelines Slack app we just get a message within channel where upon Approve button press, code goes into production.

This doesn't include database updates, which still must be performed manually, but eventually DACPAC deployment can be incorporated within a pipeline too. But luckily, database updates are more rare than backend code/frontend updates.

Judging from the Request Queue size, guess where did deployment happen?

Yeah, that little spike just before 09:00. Except, vertical axis shows max 30 instead of 3k and horizontally, just a blip of a time.

For a fair picture I should share another deployment request queue graphic:

Queue size spiked to almost 300. However it happens on some IIS servers, in this case 3 out of 10. After 60-90 seconds, queue size dropped, so fairly short period of time compared to what we experienced before. But it's not like those IIS completely stopped processing GET/POST requests - actually only minority of requests queued up. Currently I don't know the cause. Maybe if you have any thoughts on what may cause it, leave down in the comments.

In the end, the results leave everyone happy!

On an upcoming post, I'd like to share how these IIS server/Windows OS settings can be installed, managed and kept in sync with PowerShell DSC. And you don't have to keep a separate documentation file somewhere that may drift and be outdated in time. Stay tuned! 👋