Over next few posts I am going to talk about "a" methodology to do performance assessment / tuning of SQL Server based applications. Main focus will be on SQL Server engine but wherever necessary references to changes needed in application code (non T-SQL, non schema based, non configuration options etc.) will be made. I would love to hear your thoughts and comments on this.

SQL Server performance issues show up in following categories –

1. Increased utilization of resources – This condition happens when SQL Server starts consuming more resources – CPU, IO, memory, network bandwidth etc. even when the workload has not changed much.
2. Slow response time – Application which was running within user expected time start running slowly even when the workload has not changed much.

Basic essential information

Before starting performance investigation it is necessary to collect basic information about the machine.

• Hardware – CPU, memory, Disk layout – physical / logical, network bandwidth
• Other applications running on the system
• SQL Server configuration – sp_configure
• Database, transaction log, TempDB files layout.

Resource bottleneck

Typical resources which can become bottleneck in SQL Server set up are –

• CPU
• Memory
• Disk I/O
• Network
• TempDB

We will look at these various resources to understand why it has become bottleneck and what can be done to reduce its impact.