Category Archives: Performance

QueryStore – Regressed queries based on execution count

Handy TSQL statement to find gergressed queries on a given time based on execution count. I had a scenario to validate if application is executing any statements more frequently on a given time. Following SQL can be used to find … Continue reading

Posted in knowledge, Performance, Script, SQL, SQL Query, Uncategorized | Tagged , , , , , | Leave a comment

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