Just to share major components to find performance bottlenecks. Read this article for quick reference.
P - PerfMon, Major counter to look at.
Buffer Cache Hit Ratio.
Page Life Expectancy.
Memory Available vs Target.
Disk IO read/write.
CPU Utilization.
Total vs Target Memory etc.
E - Enabling Performance Features, MaxDOP, CPU and Memory Configurations.....
A - Analyse High CPU Queries/SPROCs and Analyse Execution plans for Missing Indexes.
N - NDF files/Secondary Files - We can plan to add another Filegroup and move large tables to new Filegroups as For example: DataFile2.ndf, DataFile3.ndf, and DataFile4.ndf, can be created on three disk drives and assigned to the filegroup "SecondaryFileGroup". A table can then be created specifically on this filegroup. Queries for data from the table will be spread across the three disks; it will improve performance.
U - Update Statistics, we can disable Auto Update Stats Setting and run the Update Stats During Off hours.
T - Transaction Logs (In this Category we can reduce the number of virtual Logs).
S - Simple Recovery Model (When we switch to Simple Recovery its minimally logged/Only Page allocations, hence improve performance.
However, there is no point-in-time recovery/No Log Backup can be taken as disadvantage.
Best Regards
Amit Vaid
