SQL Server Performance

SQL Server Performance

✅How to Identify Slowness of SQL Queries :

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC

But Apart from this we have to also think on below Pointers :

✅ Physical Storage
Disks or drives
Location of data files and transaction log files
Location of Temp-DB data and transaction log files
RAID volume type
Number of hard disks
Type of hard disks: HDD, SSD
✅ Physical Resources
Processors
RAM
✅ Network Topology
Bandwidth
Network Library used: TCPIP, Named Pipes
✅ Database Design
OLTP or OLAP
Normalized or Demoralized
Constraints
Triggers
Rules
Page Allocation & De-allocation
✅ Query Design
Join types
Sub Queries
Views
Aggregation
WHERE clauses
SELECT list
✅ Locking
Blocking and Deadlocks
Locking Isolation Levels
Lock Escalation
✅ Indexing
Clustered or Non Clustered
Covering Indexes: Composite or INCLUDE
Index Statistics
Column Statistics
Index Fragmentation
✅ Temp-DB
Temporary Tables
Memory Utilization
Row Versioning
Snapshots