Issues:-
- Long Running Query / Procedure
- Slow Response Table / View / Database
Solutions:-
- Find the
Query - Activity
Monitor /
- Analysis
the Query
- Consider the correct Indexs
- Ensure Index Statistics
- Consider Partitioned Table
/Views
- Consider to
create Full Text Index
- Analyse the
query by using Live Query statistics
- Consider the
Performance Tuning Tools
- PERFMON
- 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
- Profiler trace file (Run the SQL Profiler and
store the trace file)
- Table to Analyse (We can select in DTA tool to
analyse and get recommendations)
- .sql query
Ref: Microsoft /