Problem Summary One of the biggest challenges in migrating to Azure Managed Instance is determining the resource limits. We successfully used Microsoft SQL Server Distributed Replay feature to assess a SQL Server migration and choose the right service tier. Which allowed us to size it properly and not overspend on the instance. Test Server 1 – Current Production (or Source ..read more

Improvement after tuning 6x DURATION 4x CPU 3x DISK SQL Server tuning is crucial – it reduces CPU time, disk reads, and speeds up operations, making everything more efficient. Problem The slow stored procedure significantly hampered SQL Server performance. Pre-tuning Metrics 1,881 Duration (ms) 1,025 CPU (ms) 9,849 Disk (number of reads) Solution Here is what we’ve done to make ..read more

Improvement after tuning, Stored Procedure #1 2x DURATION 1x CPU 6x DISK Improvement after tuning, Stored Procedure #2 5x DURATION 6x CPU 5x DISK Improvement after tuning, Stored Procedure #3 2x DURATION 2x CPU Overall improvements for the other stored procedures in the SQL Server 2x DURATION (MS) 3x CPU (MS) 2x DISK (NUMBER OF READS) SQL Server tuning is ..read more

Improvement after tuning 3x DURATION 3x CPU 11x DISK Reading from disk is the slowest operation SQL Server does. Therefore, tuning for less disk “reads” is often the primary goal. Problem The frequently executed stored procedure select_scInvoicesCredits is performing slower than expected. Pre-tuning Metrics 2,464 Duration (ms) 2,469 CPU (ms) 118,566 Disk (number of reads) Solution To fix the issue ..read more

Duration improvement after tuning 41x QUERY #1 2x QUERY #2 8x QUERY #3 Tuning SQL Server to cut down on CPU time and disk read operations makes everything run smoother and faster, boosting both performance and reliability. Problem Three slow stored procedures significantly affected the SQL Server’s performance. Solution Here is what we did to make these queries run much ..read more

Improvement after tuning 8x DURATION 5x CPU 17x DISK Reading from disk is the slowest operation SQL Server does. Therefore, tuning for less disk “reads” is often the primary goal. Problem The slow stored procedure significantly hampered SQL Server performance. Pre-tuning Metrics 180 Duration (ms) 117 CPU (ms) 21,737 Disk (number of reads) Solution So here’s what we did to ..read more

Improvement after tuning 4x DURATION 5x CPU 48x DISK Reading from disk is the slowest operation SQL Server does. Therefore, tuning for less disk “reads” is often the target. Problem The stored procedure showed up in the top resource-consuming report. It was putting too much stress on the CPU. Pre-tuning Metrics 142 Duration (ms) 157 CPU (ms) 12,805 Disk (number ..read more

Improvement after tuning 1,152x DISK Disk (number of reads) 884,014 Before tuning 767 After tuning Reading from disk is the slowest operation SQL Server does. Therefore, tuning for less disk “reads” is often the target. Problem The stored procedure was too slow and performed too many disk reads. Solution Here is what was done to get this improvement: Before vs ..read more

We talked about availability and security best practices on Amazon RDS. Completing the series of posts, today we will learn about Migration on Amazon RDS using SQL Server. In this article, best practices will be defined for the Native Backup/Restore and AWS data migration service. But let’s first learn what Amazon RDS is. Why do you even need to migrate to Amazon RDS? And what benefits does it contain for you? What is Amazon RDS? Amazon Relational Database Service (Amazon RDS) is an Amazon Web Services managed SQL database service. Amazon RDS assists with database management chores like migrat ..read more

If you, like other software professionals, think that RDBMS settings can be left as default for improved SQL query performance tuning, then you are wrong.  Default RDBMS settings are far from being the optimal ones and not optimizing can result in performance issues. The good news, however, is that you can improve SQL query performance with little to no technical knowledge.  Despite being indispensable, SQL performance tuning is a rare skill for software teams to have. So, today I will let you know some essential techniques that never change to make your queries run faster in the Mic ..read more