Hello Friends,
SQLWORKSHOP is organizing a free Level 300-400 Microsoft SQL Server 2008 Performance Monitoring and Tuning Seminar on Sept 9th.
Click here to register for the Seminar.
Event Overview
Registration as of 08:30h, Start of Seminar 09:00h, End 16:15h.
Goal of the Seminar: To provide the database developers and administrators with knowledge on how to monitor and tune performance of Microsoft SQL Server 2008 and SQL Server 2005. The seminar addresses CPU, Memory, Disk, Query and Lock monitoring and tuning techniques.
Target Group
Developers, Database Administrators and Database Architects. This seminar is equally designed for application developers and database administrators.
Prerequisite
Some experience administering or developing applications with SQL Server.
Instructor
Ramesh Meyyappan (www.sqlworkshops.com) with more than 15 years of SQL Server expertise including working in the past as a Program Manager in the SQL Server Development Team at Microsoft Corporation. He is specialized in SQL Server Performance Monitoring, Tuning & Troubleshooting and SQL Server Administration and Tuning for SAP installations.
Agenda
08.30 – 09.00 Registration
09.00 – 10.15 Monitoring and Tuning Queries and Configuration to better utilize CPU, Memory and Disk and to reduce Total Cost of Ownership
10.15 – 10.45 Break
10.45 – 12.00 Monitoring and Tuning Queries and Configuration to better utilize CPU, Memory and Disk and to reduce Total Cost of Ownership
12.00 – 13.00 Lunch
13.00 – 14.15 Monitoring and Tuning Query Execution and Locking issues
14.15 - 14.45 Break
14.45 – 16.00 Monitoring and Tuning Query Execution and Locking issues
16.00 – 16:15 Q and A
Happy Coding
Sudhir
Thursday, September 2, 2010
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
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
Subscribe to:
Posts (Atom)
