Level 400 Performance Monitoring and Tuning Webcast

Hello,

Recently SQLWORKSHOPS organized Level 400 Performance Monitoring and Tuning Webcast. Here is the link to download the webcast.

The agenda for the Webcast is:

Webcast 1: Explains Memory allocation issues with sort. Demonstrates ways to identify sort spills to tempdb. Provides query rewrite procedure to avoid sort spills to tempdb. Demonstrates cases where 1 tempdb date file per core might not be ideal for all implementations.

Webcast 2: Explains high CXPACKET waits are NOT a direct result of delays associated with inefficiencies of parallel processing. Provides example to scale queries over many CPU cores without reducing MAXDOP settings.

Webcast 3: Recommends not using stored procedure or other plan caching mechanism like using sp_executesql and Prepared Statement using ADO.NET or OLEDB based executions for memory allocating queries. Common memory allocating queries are that perform Sort and do Hash Match operations like Hash Join or Hash Aggregation or Hash Union.

With examples provides ways to identify queries performing Hash match operations that spill to tempdb. Using SQL Profiler: Hash Warnings (Hash Recursion and Hash Bailout). Using sys.dm_exec_query_memory_grants: Granted Memory, Used Memory and Maximum Used memory. Explains how SLEEP_TASK wait type is associated with Hash Warnings.

Explains with examples how sp_recompile can block and bring an application to a standstill and recommends using DBCC FREEPROCCACHE (plan_handle) instead.

Explains with examples how stored procedure or other plan caching mechanism affects queries that sort (perform order by). Explains how IO_COMPLETION wait type is associated with Sort Warnings.

Demonstrates why rollbacks and database restore could wait on IO_COMPLETION wait type and if needed how this wait can be reduced.

Explains how parallel query performance is significantly affected by a CPU intensive query executing on one of the CPU cores. Explains the reason for the performance issue and how to identify the issue with SLEEP_TASK wait type. With example explains the reason for the observed query execution time when the child thread executes on the CPU core where the CPU intensive query executes and also when the coordinator executes on the CPU core where the CPU intensive query executes. Recommends ways to avoid this parallel sort query performance issue and also demonstrates that only certain queries will be affected by this and not all parallel queries.

Explains sys.dm_os_waits_stats.signal_wait_time_ms does not indicate system wide CPU pressure, just at a CPU core level.

Explains the prefetch mechanism and how it can affect query performance. Provides ways to force prefecth. Demonstrates cases where Avg. Disk Sec / read, the disk latency, PAGEIOLATCH_SH, Avg. disk queue length is very high and the query executes fast.

Explains why Plan Guides (Plan Freezing!) cannot help with prefetch or in few other cases.

The Goal of these webcasts is to help reduce Total Cost of Ownership (TCO), by efficiently utilizing existing investment.

Do let me know if you face any problem downloading the files.

Happy Coding

Sudhir