Useful Trace Flags

Trace Flag 1117 (Auto Grow All Files in a Filegroup): This trace flag ensures that all files in a filegroup grow together when one file needs to auto-grow. This helps prevent one file from growing significantly larger than others, which can lead to performance issues.

Trace Flag 1222 (Deadlock Information): When enabled, this provides detailed deadlock information in the SQL Server error log, which can be invaluable for diagnosing and resolving deadlock issues.

Trace Flag 2371 (Improved Cardinality Estimation): This trace flag, introduced in SQL Server 2014 and later, enables improvements in cardinality estimation for certain query patterns. It can be useful for addressing performance issues related to cardinality estimation.

Trace Flag 1118 (Full Extents Only): This trace flag can be used to force uniform extent allocations in tempdb, potentially reducing contention on allocation pages.

Trace Flag 4199 (Query Optimizer Fixes): Enabling this trace flag activates various query optimizer fixes that have been introduced in cumulative updates and service packs. It can help address certain query performance issues and improve optimizer behavior.

Trace Flag 1224 (Parallelism): This trace flag limits the degree of parallelism used by queries. It can be useful in scenarios where excessive parallelism may negatively impact performance.

Trace Flag 834 (Lock Pages in Memory): Enabling this trace flag allows SQL Server to lock its memory into physical RAM, preventing the operating system from paging SQL Server memory to disk. This can help maintain consistent performance.

Trace Flag 3605 (Output to SQL Server Error Log): When used with DBCC commands, this trace flag directs the output to the SQL Server error log, which can be helpful for capturing diagnostic information.

Trace Flag 3023 (Backup Compression): This trace flag allows you to use backup compression with the BACKUP command, which can reduce backup file sizes and improve backup and restore performance.

Trace Flag 610 (Lock Timeout): This trace flag changes the lock timeout period for specific operations, which can be useful when dealing with lock contention issues.

Complete SQL Server DBA Tutorial for Beginner

📌 All topics discussed

🌐 https://ourtechideas.com/complete-sql-server-dba-tutorials/

📌 SQL Server Database Administration

🌐 https://ourtechideas.com/sql-server-database-administration/

📌 SQL Server Database Architecture

🌐 https://ourtechideas.com/sql-server-architecture/

📌 SQL Server Storage Architecture

🌐 https://ourtechideas.com/sql-server-storage-architecture/

📌 SQL Server Database Architecture

🌐 https://ourtechideas.com/sql-server-database-architecture/

📌 SQL Server Transaction Log Architecture

🌐 https://ourtechideas.com/sql-server-transaction-log-architecture/

📌 SQL Server installation

🌐 https://ourtechideas.com/sql-server-installation/

📌 SQL Server Databases

🌐 https://ourtechideas.com/sql-server-databases/

📌 SQL Server BACKUPS

🌐 https://ourtechideas.com/8-sql-server-backups/

📌 SQL Server Restoration Tutorial

🌐 https://ourtechideas.com/9-sql-server-restoration-tutorial/

📌 SQL Server Security

🌐 https://ourtechideas.com/10-sql-server-security/

📌 SQL Server AUTOMATION Tutorial

🌐 https://ourtechideas.com/11-sql-server-automation-tutorial/

📌 SQL Server MIGRATION

🌐 https://ourtechideas.com/sql-server-migration/

📌 SQL Server Upgradation

🌐 https://ourtechideas.com/sql-server-upgradation-blog/

📌 SQL Server Log shipping

🌐 https://ourtechideas.com/log-shipping/

Most Common Wait Types

ASYNC_NETWORK_IO -- slow consuming by application, not SQL server issue, try to increase the Packet size with network team help

BACKUP_IO -- Backups are running slow; it might be due to storage issue.

CXPACKET --When you pass in a query, SQL Server will

consider parallelizing it across more cores – up to your Maximum Degree of

Parallelism

LCK* -- It means one query is holding locks on an

object, and another query is waiting to get locks on that same

object. Maybe one query is trying to update all the rows or inserting in

the table, and another query is trying to read them.

PAGEIOLATCH_* -- SQL Server is waiting to read data

pages from storage

SOS_SCHEDULER_YIELD -- The SQL Operating System (SOS,

get it?) is waiting for a CPU scheduler to yield more time, it means that

an individual task in a query needs more CPU time.

WRITELOG -- Users are doing delete/update/insert

(DUI) operations, your transaction log storage is having a tough time keeping

up (You can increase auto growth settings to fix write log wait type)

RESOURCE_SEMAPHORE -- can cause due to order by ,

hash joins and so on

Resolution:

create the indexes as required. sort operator may be

an issue. create an index on it to track index creation status use -- SET

STATISTICS PROFILE ON and run the below query

Summary: -

PAGEIOLATCH,IO_COMPLETION,`ASYNC_IO_COMPLETION,WRITELOG

Description: -

"Wait types that indicate I/O (Input/Output)

pressure in SQL Server typically involve situations where the server is

experiencing delays due to slow or busy I/O operations. These wait types often

suggest that SQL Server is waiting for I/O operations to complete before

proceeding with further processing. Some of the common wait types that indicate

I/O pressure include:

1. `PAGEIOLATCH_XXX`: These wait types indicate

latching on data pages while waiting for I/O operations to read or write data

from/to disk. Examples include:

 - `PAGEIOLATCH_SH`: Shared latch for reading data pages from disk.

 - `PAGEIOLATCH_EX`: Exclusive latch for writing data pages to disk.

 - `PAGEIOLATCH_UP`: Update latch for updating 

data pages on disk.

2. `IO_COMPLETION`: This wait type indicates that an

I/O operation has completed, and SQL Server is waiting for the completion

notification.

3. `ASYNC_IO_COMPLETION`: Similar to `IO_COMPLETION`,

this waits type indicates the completion of an asynchronous I/O operation.

4. `WRITELOG`: This wait type indicates that SQL

Server is waiting for log write operations to complete before proceeding.

5. `BACKUPIO`: This wait type occurs during backup

operations when SQL Server is waiting for I/O to read data for the backup.

6. `BACKUPBUFFER`: This wait type indicates that SQL

Server is waiting to allocate or write backup buffers to disk.

7. `FCB_REPLICA_WRITE`: This wait type occurs during

Always on Availability Group replication when SQL Server is waiting to write

data to the replica's disk.