Category Archives: Performance

Address High CPU on SQL Server

Following are the guidelines to troubleshoot High CPU scenario in MSSQL Server and fix the problem. 1. Identify running SQL statements taking more CPU 2. Find sql_handle, using SQL handle find average execution time in ms, Number of executions, and … Continue reading

Posted in Execution Plan, Performance, SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

Design considerations for SQL Server performance

While designing SQL Server for high performance following are the options to consider: 1. Block size to 64 KB (Defauly is 4 KB) Ref: * https://community.toadworld.com/platforms/sql-server/b/weblog/archive/2016/09/14/sql-server-storage-reading-block-size * https://blog.purestorage.com/what-is-sql-servers-io-block-size/ 2. Default Auto-Growth: change it to 1 GB 3. Validate MAXDOP Settings … Continue reading

Posted in Performance, SQL, Uncategorized | Tagged , , | Leave a comment

SQL Execution plan deep dive (04-Spool)

Note: This blog is continuation to earlier 3 blogs 1.       SQL Execution plan deep dive (01-Basics I) 2.       SQL Execution plan deep dive (02-Basics II) 3.       SQL Execution plan deep dive (03-Scan, Key Lookup) In simple terms spools are like storing the … Continue reading

Posted in Execution Plan, Performance, Productivity, SQL, Uncategorized | Tagged , , , | Leave a comment

SQL Execution plan deep dive (03-Scan, Key Lookup)

Note: This blog is continuation to earlier 2 blogs 1.       SQL Execution plan deep dive (01-Basics I) 2.       SQL Execution plan deep dive (02-Basics II) In the next few blogs, I will cover following topics which can be analyzed during … Continue reading

Posted in Execution Plan, knowledge, Performance, Productivity, SQL, SQL Server, Uncategorized | Tagged , , , , , | 1 Comment

SQL Execution plan deep dive (01-Basics I)

Majority of DBAs and Developers think about converting Scan into Seek after analyzing execution plan. This is a good start but is not sufficient to address most of the performance problems. I will start with basics in this blog and … Continue reading

Posted in Execution Plan, Performance, Productivity, SQL, SQL Server, Uncategorized | Tagged , , | 2 Comments

Find all Locks and Blocks on a Database or Server – Point in time

On one of our Databases I noticed frequent blocks resulting web page timeouts, had to analyze what is resulting these blocks and which tables are major contributors. SP_WHO2 is the easy way to find the processes which are getting blocked … Continue reading

Posted in Performance, Productivity, Script, SQL | Tagged , , , | Leave a comment

How Statistics impact the performance of SQL Query

In this article I will run through a simple demo which explains how STATISTICS impact the performance. During this Demo, I will use the Number of Logical Reads as performance indicator (More Logical Reads indicates Performance Degrade) Created a new … Continue reading

Posted in Performance, Script, SQL | Tagged , , , , , , | 1 Comment