This lecture provide details about following topics:

1. How SQL executes query.

2. Query life cycle.

This lecture provide details about following topics:

1. Understanding waits in SQL.
2. Details on wait types.
3. CX packet waits.
4. async_network_io_wait

This lecture provide details about following topics:

1. Pageiolatch_ex wait details and tips to reduce this wait.
2. sos_scheduler_yield wait details.
3. Theadpool

This demo section will provide details on how to get wait details on SQL server.

1. Check blocking session and wait details.
2. Query to check if server is under CPU pressure.
3. Query to check waits details on server.
4. IO wait details query.

This lecture provides information about SQL server performance counters and description of some important counters related to performance tuning.

This lecture covers following topics:

1. Index structure.
2. Index types
3. difference between clustered vs non-clustered indexes.

This demo provides information on how to create clustered and non-clustered indexes in sql server and which system tables to refer to get information about indexes in database.

This lecture covers following topic:

1. Column order
2. missing index information in sql
3. un-used index information

This demo will provide information on affect of multiple indexes on query. It also demonstrate the affect of column order in index and in SQL query. Finally we will look, how we can retrieve missing index and un-used index information in SQL server.

This lecture provides information about following topics:

1. Fragmentation in SQL server
2. Caused of fragmentation and page split
3. Index maintenance plans
4. Index rebuild vs Index reorganize options.

This demo list the scripts for the index maintenance we usually perform on sql server databases.

This lecture provide information on:

1. Tempdb content.
2. Tempdb user objects
3. Tempdb size.

This demo list the scripts which help us see content of tempdb.

This lecture provide details on best practices for the tempdb files.

This lecture provides information on following topics:

1. Space Allocation in SQL
2. Latch contention
3. How to avoid latch contention

Demo to check tempdb speed and temdb file usuage.

This lecture covers following topics:

1. Statistics IO
2. Statistics Time

This demo provide details on how to check statistics and time details on any query in sql database.

This lecture provides information on SQL profiler and activity monitor.

Hands on with SQL activity monitor.

This lecture provides basics of query tuning and which queries to tune.

This lecture provides information on Optimizer hints and general sql optimization techniques.

This lecture provides basics of parameter sniffing and details on how to resolve it.

Scripts in this demo demonstrates parameter sniffing scenarios and resolution techniques.

This lecture explains what happens when we change some server level parameters like :

Cost threshold for parallelism

Max degree of parallelism

This lecture provides information on what is partition and how it is useful for easy maintenance of tables and in performance tuning.