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.

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

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

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

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

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.

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

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

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

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

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

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