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

Category: Performance Item: Implicit Conversion What is SQL Server implicit conversion? It occurs when SQL Server needs to convert the value of variables or columns to another data type for comparison. Usually, when you mismatch data types in a WHERE clause or JOIN condition of the query. Why should you care about them? Your indexes will not

What is Deadlock in SQL Server? A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. When this happens on SQL Server, it decides to abort one of the processes that cause a “victim”, allowing another process to continue its transaction

Why should you care about them? You may be wasting your precious resources on unused services. It would be best if you prioritize your more critical SQL Instance to use more resources and do not allow less important ones to take all of them. How can I list all the SQL Server instances and services?

Category: Performance Item: Trigger usage What are SQL Server triggers? They are a particular type of stored procedure that automatically runs when an event occurs in the database. Why should you care about them? The delete, update, and insert operations against theses objects incur extra costs, which affects performance. Massive bulks and recursive triggers can cause severe

What are orphaned datafiles? Orphaned database files are files not associated with any attached database (live database). Sometimes when you drop a database from a SQL Server instance, the underlying files are not removed. If you manage a lot of development and test environments, this can certainly occur. Usually, when you take a database offline

Category: Performance Item: Windows task scheduler What is the windows task scheduler? It is a Windows component that can schedule programs and programs to run at pre-defined times or after specified time intervals. Why should you care about it? Sometimes, there some non-SQL related tasks scheduled that can start during SQL peak times without you knowing