SQL Server performance tuning using Indexes

Few technologies in SQL Server cause as much confusion to a developer than indexes. Indexes operate “behind the scenes”: adding and removing indexes rarely result in changes to an application’s code. The proper index can drastically increase the performance of an application. However, there are tradeoffs to almost any feature, and indexes are no exception.

How to troubleshoot a slow running query using wait stats in the MS SQL server?

SQL Server Performance tuning is a vast topic that can be a complicated and struggling task. Fix a slow query and solve performance problems can involve hours of research and tests. Today I will show how you can use the query wait statistics to troubleshoot a slow running query on Microsoft SQL Server. Query wait

MS SQL Server Migration: AWS RDS vs. Azure SQL Database

Amazon AWS and Microsoft Azure are excellent options for deploying MS SQL Server databases. Many companies use both to enjoy each Cloud’s unique feature set. However, it’s essential to compare the features, advantages, and limitations between them before you decide on an Amazon AWS or Azure migration. In this post, we’ll see a high-level of

7 TSQL Development Tips for Faster Queries

Today I will let know some essential techniques that never change to make your queries run faster in the Microsoft SQL server! It is excellent to deploy an optimized product that runs a dozen times faster than the previous version. Performance tuning is a vast topic that can be a complicated and struggling task to

Top 10 SQL Server Performance Tuning Mistakes

It’s not enough that your code is readable: it must perform well too. In this post, I will show a list of common antipatterns in TSQL coding that make code perform badly. They are cumulative. Just don’t make these mistakes, and you’ll be much better off. 1- Not making predicates SARGable What is SARGable? Sargable

Azure SQL: How to choose the right service database

What is Azure SQL? In the recent past, Microsoft introduced Azure SQL, bringing all the SQL Server products in one suite. Microsoft is grouping all the options for SQL running in the Azure platform into one portfolio.  A family of managed products, making it easier to see them and choose the best for your needs.

SQL Performance Tuning Report #16 – 589x faster!

Case study #16 Here is another recent SQL Server performance tuning report. The stored procedure below was one of the most called on the client’s SQL Server. Improvements achieved CPU from 50 to 1 Reads from 2,496 to 8 Duration from 227 to 1 What changes made this query run faster? Dropped the IX_ClientID_OrderDate; Added

SQL Performance Tuning Report #15 – 3,187% gain!

Real sample #15 Here is another recent SQL Server performance tuning report. Improvements achieved CPU from 125 to 18 Reads from 21,737  to 18,411 Duration from 180 to 1,271 Before vs After The green block below shows SQL performance numbers after we made some changes in the database to improve slow SQL calls. What changes

SQL Performance Tuning Report #14 – 13x Faster!

Today I am sharing another MS SQL Performance tuning report from recent tuning. Improvements after Red9 SQL Server tuning  After making a couple of small tweaks to the SQL Server stored procedure we were able to get few improvements Run time duration improved by: 636% CPU improved by: 377% Disk improved by: 312% Overall stored

SQL Server objects created with SET Options

Category: Performance Item: Objects created with SET Options What are the set options? SQL Server backward compatibility SET options allow legacy T-SQL code to run on newer SQL Server versions without changes while supporting ISO SQL standards for new development. Briefly, they avoid applications that expected non-ISO behavior from breaking. Why should you care about

Are you using stored procedures with recompile option?

Category: Performance Item: SPs with RECOMPILE What is the RECOMPILE option? The compilation is the process when a query execution plan of a stored procedure is optimized based on the current database objects state. This query execution plan is often stored in the cache to be quickly accessed. Recompilation is the same process as a

Should you care about page life expectancy in the SQL Server?

Category: Performance Item: Page life expectancy What is the SQL Page Life Expectancy (PLE)? PLE is the expected time (in seconds) that a data file page read into the buffer pool will remain in memory before being pushed out of memory to make room for a different data file page. This metric is not an aggregate or

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.