Scribd
Upload
27 views3 pages

SQL Server Performance Troubleshooting Guide

The document provides guidance on troubleshooting various SQL Server performance and availability issues including IO, CPU, memory, query tuning, indexing, latch contention, tempdb contention, deadlocks, MAXDOP, AlwaysOn latency, and backup/restore issues. Links are given to Microsoft documentation and blogs on analyzing and resolving each type of issue.

Uploaded by

Prasad Redd
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
27 views3 pages

SQL Server Performance Troubleshooting Guide

The document provides guidance on troubleshooting various SQL Server performance and availability issues including IO, CPU, memory, query tuning, indexing, latch contention, tempdb contention, deadlocks, MAXDOP, AlwaysOn latency, and backup/restore issues. Links are given to Microsoft documentation and blogs on analyzing and resolving each type of issue.

Uploaded by

Prasad Redd
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

How to analyze troubleshoot IO issues ?

Refer : [Link]
longer-than-15-seconds-to-complete-on-file/

How to analyze tackle Writelog issue ?

Refer : [Link]
waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server/

How to analyze CPU issue ?

Refer : [Link]
troubleshooting-checklist/

How to analyze memory issue ?

Refer : [Link]
[Link]

SQL Server useful performance counters ?

Attached a file.

Query tuning and indexing tips ?

Refer : [Link]

Latch contention .

Refer : [Link]
increasing-clustered-index-keys-can-cause-latch-contention/

How to analyze Tempdb contention .

Refer: [Link]
troubleshooting-tempdb-contention/

Refer: [Link]
allocation-contention-in-sql-server-tempdb-d

A detailed look into deadlock ?


Refer : [Link]
part-1/

MAXDOP best practices .

Refer : [Link]
guidelines-for-the-max-degree-of-parallelism-confi

In-depth memory architecture .

Refer : [Link]
sql-server-memorypart-1/

Index internals .

Refer: [Link]

SQL server query timeouts. Attached file .

Back up restore troubleshooting . File attached .

Server performance and monitoring .

Refer : [Link]
databases/performance/server-performance-and-activity-monitoring?view=sql-server-
2017

ReadTrace or RML Utilities: [Link]


FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en (needed to install
SQLNexus, also can be used to analyze profiler traces)

SQLDiag Utility (general purpose diagnostics collection utility with profiler


traces) - [Link]

Performance Dashboard Reports (Precursor to SQL Server 2008 Activity Monitor) -


[Link]
e419218c1efc&displaylang=en

Performance Dashboard Reports for SQL 2012 (also for 2008/2008 R2) -
[Link]

Database Engine Tuning Advisor - [Link]


us/library/ms173494(SQL.90).aspx

AlwaysOn latency issue .


Using perfmon 1 : [Link]
server-2012-alwayson-part-11-performance-aspects-and-performance-monitoring-i/

Using perfmon 2 : [Link]


server-2012-alwayson-part-12-performance-aspects-and-performance-monitoring-ii/

Using extended events :


[Link]
latency-between-synchronous-commit-always-on-availability-groups/

Redo queue issue :


[Link]
build-up-data-latency-issues-on-alwayson-readable-secondary-replicas-using-the-
wait_info-extended-event/

Latency reports : [Link]


always-on-availability-group-latency-reports/

Troubleshooting SQL server alwayson configuration issue � file attached

You might also like