SQL Performance Tuning

Issues:-

• Long Running Query / Procedure
• Slow Response Table / View / Database       
  

Solutions:-

1. Find the Query  - Activity Monitor /
   
2. Analysis the Query
3. Consider the correct Indexs
4. Ensure Index Statistics
5. Consider Partitioned Table /Views
6. Consider to create Full Text Index
7. Analyse the query by using Live Query statistics
8. Consider the Performance Tuning Tools
1. PERFMON
2. DTA (Database Engine Tuning Adviser)
   

Activity Monitor (In SSMS Ctrl+alt+A): - Overview Pane / Process Pane / Resource Wait Pane / Date I/O Pane / Recent Expensive Pane

ie > Analysis the  Query -Multiple OR to IN, if possible text to numeric, instead of storing  some repeated values introducing look up table,  implement proper index, some times denationalization
- Correct Developer mistakes like Select * from instead of selecting required columns, some times for different conditions they need different sent of data for that Developer select all the data without where condition - they can disconnect the recordset and they can fetch required records whenever they need. 
- Check execution plan
- Use CTE when dealing with specific set of data / self join.  Avoid Self join, Use Profiler to generate trace file, use DTA tool to get recommendations. Use Partition of Table / View / Index / filegroup / Partition function
---------------------------------------------------------------------------------------------
Analysis the Query: -
- Find out the query by using SYS.DM_EXEC_QUERY_STATS and  SYS.DM_EXEC_SQL_TEXT

 SELECT
    plan_handle, total_worker_time, total_worker_time / execution_count AS AVG_EXEC_TIME,
    TEXT -- actual query
FROM SYS.DM_EXEC_QUERY_STATS
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(plan_handle)
ORDER BY total_worker_time / execution_count DESC

- If multiple OR condition change to "IN"
- Try to use numeric instead of text
- Select the required columns instead of Select *

---------------------------------------------------------------------------------------------

Consider the correct Indexs:-
- Use execution plan & identify missing indexes
- Index based on where condition

----------------------------------------------------------------------------------------------

Ensure Index Statistics
- Use Maintenance Plan to ensure Minimal Index Fragmentation

----------------------------------------------------------------------------------------------
Consider Partitioned Tables / Views -  Partitioned Tables available only in the Enterprise SQL Server Edition,
- SQL Server table partitioning is a great feature that can be used to split large tables into multiple smaller tables, transparently. It allows you to store your data in many filegroups and keep the database files in different disk drives, with the ability to move the data in and out the partitioned tables easily.  Table partitioning improves query performance by excluding the partitions that are not needed in the result set.  Fortunately, SQL Server allows you to design your own partitioning solution without the need to upgrade your current SQL Server instance to Enterprise Edition. This new option is called Partitioned Views. Although this new solution is not as flexible as table partitioning, Partitioned Views will give you a good result if you design it properly.

----------------------------------------------------------------------------------------------

Consider to create Full Text Index

----------------------------------------------------------------------------------------------
Analyse the query by using Live Query statistics

-----------------------------------------------------------------------------------------------
Consider the Performance Tuning Tools

PERFMON
- Select Counters to be considered

Ref:-
- brentozar.com

----------------------------------------------------------------------------------------------
DTA (Database Engine Tuning Adviser)
- This tool is auto installed with ssms installation
- This tool is used to analayse the given query / set of queries  and provide a set of  recommendations using which we can implement: (Indexes / Statistics /Partitions)

- DTA tool can get input as

1. Profiler trace file (Run the SQL Profiler and store the trace file)
2. Table to Analyse (We can select in DTA tool to analyse and get recommendations)
3. .sql query

Ref: Microsoft /