Performance Tuning

I did some performance tuning tasks at BFSI domains recently, and summarized a checklist for performance tuning in the below:

Performance Tuning:

Performance tuning is a broad and somewhat complex topic when it comes to databases. Many times the user reports a problem about a slow or poor performing application or long running query. When it comes to, two of the biggest questions faced by DBA is where to start and what to do.

Types of Performance Tuning: 

• Hardware Level Tuning
• OS Level Tuning
• Database Level (SQL Server) Tuning

Hardware Level:

Main aim of Hardware Level testing is to "save a lot of money of corporations using Performance tuning just by modifying a system to handle higher loads and thereby enhance the server performance without spending on new Infrastructure or applications."
You should know all about your hardware configurations and their speed and limitations etc.

Hardware Components:-

CPU: 

The performance of the CPU is affected by the number of cores, clock speed etc.

You can see number of cores & other information in task manager on windows server.

Refer this microsft site for more information on CPU utilization.
This microsoft link have great information on power and performance tuning. Most servers will run to 90% to 98% average, where it is considered fully utilised. If it peaks 100% all the time, then you have an issue with CPU.

RAM: 

Random Access Memory (RAM) is used as a temporary, super-fast data storage for your processor (CPU). 

I got lucky to got chance working with 1TB & 500GB RAM. 

When you launch a program, the hard drive sends the relevant data for that program to the RAM, where the processor can access it much quicker than going directly to the hard drive thus eliminating physical reads.

Ram & Page File: 

Windows uses a page file to store data that can’t be held by your computer’s random-access memory when it fills up. The page file, also known as the swap file, pagefile, or paging file, is a file on your hard drive. It’s located at C:\pagefile.sys by default. Follow this link to know more about the page file.

Ref: Microsoft 

DISK Type:  

Disk performance usually refers to the reading and writing speed of the disk.

There are two types of drives:

HDD (hard disk drive):

A hard drive has moving parts, and during the normal operation like perform copying, or writing, these parts (Disk head) moves or rotates at high speed, such as 7200 rpm, so it has to perform physical seek. If you simultaneously perform another tasks like two tasks of copying or pasting then it will write data whenever there is space thus it casuses fragmentations.


SSD (solid-state drive): 

SSD has no moving parts and thus no physical seek limits, so the IOPs is higher than that of HDD. Disadvantages of HDDs as compared to SSDs is: low speed of reading/writing operations.

So you should know following related to your hard disk:
Is it SSD or HDD ?
The speed of your disk ?
Whats your disk IOPS ratio ?

Disk Relation with SQL:

SQL uses 8kb for per page and one extent is of 64 bit so formatting disk with the 64kb allocation improves performance.

NETWORK SPEED: 

How fast packets or units of data travel from source to destination. Many factors contribute to network latency, including: the distance between two systems (WAN), the number of hops (bridge, router or gateway points) along the way, large packet sizes (video files or encrypted data), jitter (the variance in time delay between packets) or network congestion (too many bits in the pipe).

Ref: Microsoft

OS Windows Level:

The following points to be considered in order to improve performance at Operating System level:

• Operating System Capacity or limitations comparisons.
• Database software capacity or limitations.

• How many instances installed, What features or Programs installed on Windows Server:

• Reporting services: DBA should know all services of sql server with separate services and their impact. For Reporting services it is possible to on separate hardware and only connection with ip address, so it not load your server
• Cluster, Active Directory, IIS etc: It is possible to make the Domain System separate from SQL Server and have controlling from separate server.
• Visual Studio etc: Front end softwares should be installed on separate machine so it not consume resources of your server.
• Antivirus Software: Antivirus software do a lot of scanning in background, for performance it is good to exclude files of database from scanning.

Add these files in antivirus exclude list:

• mdf,
• ldf,
• log files,
• bak,
• trn,
• rpt,
• dtsx, rdl files etc

SQL Server Level Performance Tuning:

Database level performance tuning is further divided into two parts:

• As DBA (Database Level Performance Tuning):
• As Developer (Query Level Performance Tuning:)

As DBA (Database Level Performance Tuning):

• Monitoring: Long running queries due to blocking and find deadlock.
• Wait types: From SQL Server 2016 new dmv is introduced which shows the session wait stats history of particular session.
• LATCHES (PageLatchUP): Related to Tempdb etc.
• CXPACKET and CXCONSUMER: Related to MAXDOP/ CPU
• SOS_SCHEDULER_YIELD: Related to CPU
• IO_COMPLETION: Related to Disk Latency. Include system admin to check disk iops for particular disk, script to check find on internet type of drive with details.
• ASYNC NETWORK IO: Related with network latency(Network Adapter)
• Server Configurations:  From SQL Server 2016 there are Database scoped configurations options  available for MAXDOP and QUery Store etc 
• Degree of parallelism
• Cost threshold for parallelism
• Min server memory
• Max server memory
• Adhoc workload etc
• Unused indexes
• Missing indexes
• Fragmentations
• Rebuild indexes
• Statistics Update(histogram, density vector,etc ): Based on statistics SQL makes query execution plan.
• Data and log file segration
• Table size/database size
• Number of Archived tables

As Developer (Query Level Performance Tuning:)

Query Optimizations using Indexing:

• Developer should know when to use index, which index is good as per the query, limitations of indexes, clustered and nonclustered indexes etc 
• Note: It is possible to create the same index with same columns.

• Columns of table with proper data types and proper values, i.e You are creating a character data type of your column and using it for storing integer values, It affects the performance.  
• Index types, Columnstore index vs rowstore index.
• Architecture of SQL Query Flow.
• Different types of Joins & their impact in execution plan.
• Plan Monitoring, remove specific plan from memory using DBCC, reuse, recompile options.  
• Actual and estimated execution plans, Fat pipes, Many plans in one procedure etc.
• Changing costly query execution plan by using options or removing costly functions:
• With recompile or DBCC flush,
• Using query level hints (join hint, maxdop, recompile, force order,table hints etc)  
• Dealing with Execution Plan Warnings: Conversions(implicit and explicit) 
• Index insert, Index Scan, Index Delete, Sort operator, Key/ RID lookups, Parallel Plan etc 
• Understanding of Sargable and non sargable functions optimizations that causes a table/index scan i.e  IN,OR,NOT IN, NOT EXISTS, NOT LIKE or <>.  The functions ike = is more likely to give good performance becauce the database can look up a single or a limited number of records from an index.
• Parameter sniffing(new execution plan for every time with new parameter so with sniffed parameter)
• sp_ prefix vs schema prefix,
• Tables in procedure like direct name of table vs dbo.database.tablename
• Inserting or selecting all table and join them why not pick required and join them fast
• Understanding of CTE, Table Variable, Global/Local temporaty table, Derived tables  From above which table uses memory/tempdb for sorting purpose. In which condition we should select CTE or Derived Tables etc.
• Use of XML Functions: xml modify replace value of(), exist() , nodes()
• Cursor vs while loop performance.
• Use of triggers.
• SSIS to load data(ETL) vs Using SQL Server queries.
• Analyzing multiple query load using third party Stress Sotware.

From Where to Start Tuning:

Many times I had assigned to analyze the slowness issue at multiple BFSI domains. For finding cause of performance (There's no shortcuts, Search everything from scratch and all parameters of performace mostly involved in my case, so what there's no choice you had to be prepare for everything.

You should know every aspects that is good for performance because:

• System Admin say all good from their side, you had to find and prove it and
• You have to explain to non technical members about root cause of issue.