A successful performance tuning project
Performance tuning project
I’m coming to the end of what has been a successful performance tuning project for SQL Server. I wanted to share some of the things that made it a success.
Corporate buy in
The company had a goal to improve the page load times of a number of key pages within our application. This was to improve the user experience for our customers. They acknowledged that the database code, both indexes and stored procedures needed optimsation but so too did aspects of the application code. It was good to see this acknowledged as I’ve been in many situations where the database takes all the blame.
The company approved a considerable amount of resource in terms of personnel to work solely on this stability and optimisation project. It included senior developers, testers and a project manager. I was brought in to look at the database performance. Whilst some business as usual (BAU) priorities did come in from time to time, a large core of the team was protected from this and allowed to get on with the work of making the system go faster, thus improving the customer experience.
Daily scrum meetings
We held daily scrum meetings where we covered what we had done since the last meeting, what we were working on and if anything was blocking our progress. These were kept short so as to not get in the way of the development work, but allowed everyone an overview of what the other team members were working on. Often, as a result of these, side conversations spun up and team members helped out others who were looking for a bit of assistance. (Or simply to bounce ideas around)
Collaboration
The team were willing to help each other. When Pull Requests (PRs) were submitted, these were swiftly approved where there were no objections, or challenged in a positive way which helped get the best overall result. When the API calls were showing as slow, but nothing was obvious on the SQL server, heads were put together to use the tools at our disposal to get to the root cause. This often included Azure App Insights which I had not previously used, and this helped us get the end to end transaction details. We could pull out the SQL for any areas which were slow and work on making it perform better.
Measuring improvements
The Azure Instance class for the SQL Server had previously been doubled so there was no appetite to scale it up again. The hope was that we may eventually be able to scale back down after a period of stability.
The system previously had issues with blocking, high CPU and slow durations so I wanted to reduce page reads, CPU and duration for all of the SQL calls I was working on. I wouldn’t consider a PR if at least 2 of these metrics were not improved. My main focus was on reducing duration of calls but I didn’t want to improve one thing, and make others worse as a consequence. In my own tickets, I always made sure to document the before and after metrics from my standalone testing so as to give confidence that they would be included in upcoming releases.
We also used Apdex which is a standardised way of measuring application performance. It ranks page views on whether the user is satisfied, tolerating or frustrated. The more we move users out of the frustrated and tolerating groups, and in to satisfied, the higher the Apdex score will be. As our project moved through release cycles, we were able to see steady increases in our Apdex scores. Apdex also allowed us to identify what was hurting us most and create tickets based on this information.
Top Quality Load Test Environment
We had a top quality load test environment which used production masked backups for the databases. I set up the availability groups to match production, the servers were all sized the same as production and had the same internal settings such as tempdb size, sp_configure settings and trace flags etc. We were able to replay the same tests over and over again using Gatling, and our testers made really useful reports available to help us analyse the performance of each hotfix. If it was a proven fix, it was promoted to a release branch, if it wasn’t, it was binned.
End Game
This intensity was kept up for the almost 2 months and it was ultimately transformative for the business. Whilst there are still many further improvements that can be made, the specialised squad is being disbanded and team members are being reallocated to other squads. Performance should be a way of life now, rather than an afterthought or another performance tuning project.
We can be happy that we improved the Apdex scores, sped up a huge number of regularly used SQL transactions, and removed the large CPU peaks that dominated our core business hours.
If you enjoyed this, you may also enjoy some of these other posts.
How DBATools can help with performance tuning
How DBATools can help with performance tuning.
DBATools is well known in the SQL Server community for the ease at which it allows you to automate certain tasks but did you know that DBATools can help with performance tuning your SQL Server. As my job recently changed to have more of a performance tilt, I wanted to investigate which commands would be useful in helping me with performance tuning. It turned out there are quite a few.
Compression
Page and row compression is a hidden gem when it comes to performance tuning SQL Server. Simply compressing some large indexes can sometimes be enough on it’s own to get you over the line in terms of performance improvement. With compression, it shouldn’t be a guessing game. SQL Server has built in stored procedures to let you know whether a table or index would benefit from page,row or no compression.
EXEC sp_estimate_data_compression_savings 'dbo', 'DB1', NULL, NULL, 'ROW' ; EXEC sp_estimate_data_compression_savings 'dbo', 'DB1', NULL, NULL, 'PAGE' ;
But if you want to test a whole DB or instance at a time, DBA tools makes this a lot easier.
Test-DbaDbCompression -SqlInstance localhost -Database WideWorldImporters | Select-Object TableName, IndexName, IndexId, PercentScan, PercentUpdate, RowEstimatePercentOriginal, PageEstimatePercentOriginal, CompressionTypeRecommendation, SizeCurrent, SizeRequested, PercentCompression | Out-GridView
Piping to Out-Gridview is optional but I love the way the output is tabulated and easy to read. I use this output to create a list of objects to be compressed. I then do some load testing, before and after the changes and compare the results. This gives me the confidence to roll out the changes to production.
Hint: Having a way of replaying a consistent load and measuring results before and after is essential when performance tuning by making wholesale changes. If you don’t have a way of doing this, I recommend looking into WorkloadTools.
Find unwanted code hints
When I took over a new environment, there was A LOT of blocking. Part of this was down to the code which was in need of optimsation, but it was also due to a lot of hints peppered throughout the stored procedures that made blocking inevitable. I found several examples of REPEATABLE READ and SERIALIZABLE throughout the code base. Asking developers about why we needed this level of isolation, no one thought we did and said this was done by developers who had moved on.
So, what is the easiest way to find every example of REPEATABLE READ in your code base, DBATools of course.
Find-DbaStoredProcedure -SqlInstance SQL123 -Database WideWorldImporters -Pattern 'REPEATABLE READ' | Select-Object * | out-gridview
Just like before, if you can get a replay running that simulates production, then you can measure whether your blocking and response times go down.
Check disk speeds
Knowing that your disks are performing well gives you the confidence to go out and work on your code to seek performance improvements. But if you have slow disks, this can also be a bottleneck. Knowing you have a problem and rectifying it means you can see performance improvements before you have even looked at your indexes or stored procedures.
A common bottleneck can be tempdb, it’s always good to know you have tempdb on high performing disks. (If using Azure VMs, you can utilise the D drive for tempdb)
Test-DBADiskSpeed -SQlInstance SQL123 -Database tempdb | Out-Gridview
Community tools
Easily install some of the best free community tools around which will help you evaluate and monitor your SQL Server.
# Install the First responder kit from Brent Ozar Unlimited. Includes sp_blitz, sp_blitzcache and sp_blitzIndex which all help with performance tuning. See https://www.brentozar.com/ for more info. Install-DbaFirstResponderKit -SqlInstance SQL123 -Database dba # Installs sp_whoisactive by Adam Machanic which is a priceless tool in terms of evaluating what is running on your instance right now. See http://whoisactive.com/ for more info. Install-DbaWhoIsActive -SqlInstance SQL123 -Database dba # Install the free community monitoring tool SQLWatch. See https://sqlwatch.io/ for more info. Install-DbaSqlWatch -SqlInstance SQL123
External Links
A full list of commands can be reviewed on https://dbatools.io/commands/.
When should I stripe SQL Server database backups
What is a striped backup?
First of all a definition of what we mean when we say stripe SQL Server database backups. When we talk about striping a backup, it simply means the overall backup is distributed across several files, often on different drives. Microsoft’s description.
What are the benefits?
- Time
- Writing to multiple files can save time. This is because the files can be written to the file system in parallel. This is useful if your backups are taking too long and you would like/need them to complete faster.
- Storage
- If a backup drive is getting full, you can split your backup across multiple drives thus saving space on each drive. The total space used will be similar to keeping the backup on a single file but you may see a bit more overall space taken up.
Striped vs Single File
It’s a balancing act. My advice is stick with the defaults until you have an issue or would like something to perform better. Then it’s a good time to experiment and see how you get on. As a rule, the bigger your backups become, the more useful you may find striping your database backups.
How to stripe my backups?
We will discuss T-SQL and Powershell. For Powershell, I’m using the DBATools PowerShell module. DBATools covers far more scenarios than Microsoft’s own default module for SQL Server and is being used and tested all day every day. If you wish to run the powershell commands below, you will need to have dbatools installed. For the T-SQL commands, these all run natively in SSMS.
T-SQL
-- Split file across two drives as not enough space on E: BACKUP DATABASE StackOverFlow2010 TO DISK = N'E:\Backup\StackOverFlow2010\StackOverFlow2010_1.bak', DISK = N'F:\Backup\StackOverFlow2010\StackOverFlow2010_2.bak' WITH COMPRESSION;
T-SQL (Using Ola Hallengren’s DatabaseBackup procedure)
EXECUTE dbo.DatabaseBackup @Databases = 'StackOverFlow2010', @Directory = 'E:\Backup\StackOverFlow2010\,F:\Backup\StackOverFlow2010\', @BackupType = 'FULL', @Compress = 'Y', @NumberOfFiles = 2
Powershell (DBATools)
Backup-DbaDatabase -SqlInstance localhost -Database StackOverflow2010 -Path E:\Backup\StackOverflow2010\,F:\Backup\StackOverflow2010\ -CompressBackup
Restoring striped backups
It is therefore worth testing your backups and restore scripts every so often. A backup that cannot be restored is not really a backup.
T-SQL
RESTORE DATABASE StackOverFlow2010 FROM DISK = N'E:\Backup\StackOverFlow2010\StackOverFlow2010_1.bak', DISK = N'F:\Backup\StackOverFlow2010\StackOverFlow2010_2.bak'
Powershell (DBATools)
Restore-DbaDatabase -SqlInstance localhost -DatabaseName StackOverflow2010 -Path E:\Backup\StackOverflow2010\,F:\Backup\StackOverflow2010\ -WithReplace
Data Glasgow User Group
I spoke at the relaunch of the Glasgow Data User Group (formerly Glasgow SQL Server User Group).
It was a great event, hosted by Craig, Louise and Robert. My presentation was on some useful free community tools, which can make your life as a DBA much easier. It shows that DBAs with zero budget to spend on expensive software still have a wide range of free software to make our lives easier. Moreover, even with a large budget, some of these free tools are still the best in their class so are worth reviewing.
Slides for tonight’s talk on SQL Server Community Tools are available on Github.
- 1
- 2
- 3
- …
- 11
- Next Page »