High Performance SQL Server DBA Tuning & Optimization Secrets Splash "SQL Server 2005" Robin Schumacher Retail Price $27.95 /  £17.95   Key Features About the Author Table of Contents Index Reader Comments Errata         Only $19.95 (30% off) ISBN 0-9761573-6-5 ISBN 13 978-0976157366 Library of Congress Number 2005901897 304 pages Perfect bind - 9x7 PD 905 Shelving: Database/SQL Server IT in-Focus: Series # 1 If you're a SQL Server DBA who wants to get proactive and organized with performance monitoring and tuning, then you've come to the right place. Written by one the world's most widely-read DBAs and SQL Server internals experts, Robin Schumacher offers real-world advice, an easy-to-follow performance strategy, and lots of SQL diagnostics scripts in a superb book that shows how to quickly diagnose and optimize SQL Server performance problems. As a Vice President at Embarcadero Technologies, Robin Schumacher has written the internals for some of the world's most powerful SQL Server performance software, and now he shows you how to use the most recent advancements in SQL Server 2005, as well as SQL Server 7 and 2000, to make your database servers run as fast as possible.   * Provides a winning performance methodology for use in analyzing any SQL Server database * Demonstrates how to uncover serious bottlenecks that exist in your SQL Server * Offers insight into how to determine the overall workload of a SQL Server * Supplies methods for determining how and when to reorganize databases and objects * Presents new techniques for monitoring and optimizing memory usage * Shows how to quickly pinpoint and resolve I/O hotspots at the database session, and object level Details strategies on how to successfully use capacity planning for performance analysis * Provides new tips and techniques for locating and fixing problem SQL and procedure code * Presents improved methods for uncovering session-related bottlenecks * Provides new tips and techniques for locating and fixing problem SQL * Comes with online code depot with dozens of new performance analysis and monitoring scripts     About the Author:   Robin Schumacher   Robin Schumacher serves as the Worldwide Director of Product Management for MySQL, the world's leading open-source database management system.  Robin has over fourteen years experience in database administration, development, monitoring, and tuning with Oracle, DB2, Teradata, Sybase, and Microsoft SQL Server.  He has authored countless performance-related articles for many database-centric magazines as well as serving as a database software reviewer and feature writer for the likes of Intelligent Enterprise, eWeek, DM Review, and others.    Table of Contents:   I. Performance Lifecycle Management   Database performance isn’t something to be managed haphazardly, but instead should be approached in a methodical and organized manner. This is precisely what Performance Lifecycle Management or PLM is designed to provide. This chapter provides an introduction to PLM and covers the basics areas involved in a successful PLM implementation.   II. How to Accurately Measure Database Performance   Determining the overall health of a SQL Server system can be confusing task, but it doesn’t have to be. Accurately measuring database performance can easily be accomplished once you understand the basic components of performance and how they relate to one another. This chapter takes a brief look at performance modeling and how it can be applied to any SQL Server installation.   III. PLM Step 1 - Proactive Actions that Ensure Optimized Performance   The foundation of SQL Server performance isn’t found in SQL tuning or other such tasks. Instead, it’s established during the initial physical design and performance testing phases of the PLM cycle. This chapter shows how to create a winning SQL Server database design and discusses how to perform one of the most neglected activities of performance management, which is proactive testing.   IV. PLM Step 2 - Establishing a Smart Monitoring Plan   What are the overall goals of a smart SQL Server monitoring plan? Do you know how to accurately use the most important performance analysis methods for SQL Server? This chapter provides an overview of the building blocks of a solid SQL Server monitoring plan and introduces the key performance analysis methods that every DBA needs to understand and practice.   V. Bottleneck Analysis Part 1 – Diagnosing storage and system problems   Bottleneck analysis is the primary performance analysis method DBAs should use in diagnosing and tuning SQL Server response time problems. This chapter focuses on how to recognize and correct bottlenecks that occur in the storage and overall SQL Server system layers.       VI. Bottleneck Analysis Part 2 – Diagnosing session and O/S issues   If DBAs cannot locate any obvious SQL Server issues at the storage or system layers, the next step is to drill down into session and operating system metrics to determine if any bottlenecks exist in these layers. This chapter contains information on how to identify and correct bottlenecks with SQL Server processes and the Windows server.   VII. Workload Analysis - Unlocking the Who, What, and Why of Performance Problems   After bottleneck analysis, the next most important performance methodology is workload analysis, which focuses on the overall workload generated by system, session, and SQL activity. This chapter contains details on how to understand the overall workload on a SQL Server and recognize where improvements can be made.   VIII. Ratio Analysis - Techniques for quickly getting a bird's eye view of performance   Key performance ratios can quickly help a SQL Server DBA understand how well their overall system is performing. This chapter provides details on what ratios a DBA needs to monitor, along with recommendations for what to do when key performance metrics are out of line.   IX. PLM Steps 3 and 4 - Using History to Prepare for the Future   Many SQL Server DBAs work in a reactive mode, which means they do little to plan for the future needs of the databases they oversee. This chapter discusses the importance of historical trend analysis, what key metrics should be tracked, and how to use historical data to forecast future needs.   X. PLM Step 5 - Tuning that makes a difference   Many SQL Server DBAs spend time tuning things that make little or no impact in overall performance. This chapter focuses on what to pay attention to and how to form an overall PLM tuning plan that can be effectively used in large SQL Server installations to dramatically increase performance.

 Index Topics:
 

@@CPU_BUSY @@IDLE @@TIMETICKS A access efficiency access path effectiveness active monitoring ad hoc cache administration console analytic monitoring ansi_defaults ansi_nulls ansi_padding ansi_warnings array controller ASYNC_IO_COMPLETION auto create statistics auto update statistics AUTO UPDATE STATISTICS autogrowth auto-parameterization Average Page Density avgfragmentation avgfragmentsize avgpagefullness B Best Practices Analyzer bitmap index blocking lock blocking locks bottleneck Bottleneck Analysis buffer cache buffer cache hit ratio C cache hit ratio capacity planning Cartesian joins change control Change Management CHECKTABLE clustered indexes CMTHREAD cntr_value collection intervals COMMIT concat_null_yeilds_null Configuration connectivity CURSOR cursory monitoring CXPACKET D Data Definition Data Definition Language Data Lifecycle Management Data Management Service Layer Data Manipulation Language data modeling Data models data_spaces DataBase Consistency Check database design Database Management Service Layer database server readiness Database Tuning Advisor Database Tuning Component Database Tuning Wizard Database Workload database_name db_can_grow DBCC DBREINDEX DDL deadlock victim Deadlocks deadly embraces delta measurements delta statistics denormalizing DLM DML Documentation Drill Down drop_existing dynamic memory management E enterprise database monitor Enterprise Manager Enterprise Resource Planning error log analysis ETL EXCHANGE EXPLAIN eXtensible Markup Language extent count extent fragmentation Extent Scan Fragmentation extent switches External Fragmentation Extract-Transform-Load F filegroups filegrowth fill factor fillfactor fillfactors fn_virtualfilestats forecasting foreign keys forwarded records forwarded_fetches forwarding pointer fragments freespace scans full object partitioning fundamental analysis

G Gartner group Global Allocation Map Global Server Workload H hash joins HIPAA horizontal partitioning I I/O contention ILM Index Allocation Map index fragmentation Index Tuning Wizard INDEXDEFRAG INDEXREFRAG Information Lifecycle Management information_schema INPUTBUFFER INPUTBUFFER() Internal Fragmentation IO_COMPLETION iops iostall IoStallReadMS IoStallWriteMS ITWIZ J JBOD just a bunch of disks K keep_existing_indexes L LATCH_ LCK_ lck_m_s Lock Activity lock contention locks Log buffer waits log_can_grow Logical Data Management logical fragmentation Logical Scan Fragmentation login_id LOGMGR M Management Studio master..sysperfinfo master..sysprocesses master.dbo.syscacheobjects master.dbo.syslockinfo master.dbo.sysperfinfo master.dbo.sysprocesses max server memory max_columns_in_index maxsize MEMUSAGE(NAMES) Metadata Management Metric Rollups Microsoft Task Manager min memory per query min server memory minimum_improvement modify file N network packet size NETWORKIO New Technology File System normalization NTFS number_of_queries O object fragmentation object resource availability OLEDB OLTP online transaction processing open objects owner_name P page density Page Free Space Page Life Expectancy Page Reads page split PAGEIOLATCH_ PAGELATCH_ pages PAGESUPP passive monitoring password Performance Lifecycle Management Performance Management Service Layer Performance model performance monitor performance monitoring Performance Monitoring Plan performance testing Physical Data Management Physical Database Design physical design Physical Design Physical Design Lifecycle PINTABLE PIPELINE_ plan cache PLM point-in-time recovery primary key indexes priority boost proactive actions Proactive Performance Testing procedure cache PSS_CHILD

Q quality_of_recommendation Query Analyzer query plans R RAD RAID RAID RAID+ RAID RAID+ RAID RAID- range_scans Rapid Application Development ratio analysis Ratio Analysis ratio-based analysis ratio-based performance counters read ahead manager Read Ahead manager recommendation_quality recompiles reorganization requests resource_queue S Sarbanes Oxley scan density Scan Density script_file_name Scripts access_ratios active_ratio block_ratio blockcount.sql data_centric db_activity.sql dbactivity_overview dbproblem.sql dbproblem.sql dbusage_overview.sql deadlock.sql deadlock.sql fn_trace_gettable global_sql last_index_stats lock_analysis.sql lock_analysis.sql lock_timeouts.sql lock_timeouts.sql log_expand.sql mempressure.sql object_io object_io.sql page_life page_splits pagesplit.sql plan_objects.sql proc_linear_regression proc_linear_regression_forecast server_IO.sql server_object_access.sql sess_activity sess_count sesswait.sql sql_stats_ sql_stats_.sql test_time up_bn_error_log_count up_bn_locks up_bn_log_daily_volume up_bn_storage_bnecks up_bn_storage_filegroups up_bn_storage_files up_bn_storage_indexdiag up_bn_storage_indexdiag up_bn_storage_map up_bn_storage_overview up_bn_storage_tablediag up_bn_storage_tablediag up_bn_waits up_index_reorg_ up_memory_status up_ratios_memory up_weekly_workload up_wl_file_io up_wl_file_stats up_wl_session_hogs up_wl_trace_session up_wl_trace_sql waitsum_.sql server_name Session Workload

SET DEADLOCK_PRIORITY SET LOCK_TIMEOUT share locks SHOW CLIENT STATISTICS SHOW SERVER TRACE SHOW_STATISTICS SHOWCONTIG showplan_all on SHRINKDATABASE SHRINKFILE sleep sp_add_data_file_recover_suspect_db sp_add_log_file_recover_suspect_db sp_dboption sp_executesql sp_foreachdb sp_minibench sp_monitor sp_MSforeachdb sp_msforeachtable sp_perf_space_db sp_recompile sp_resetstatus sp_spaceused sp_updatestats space resource availability SQL Agent SQL Code SQL Profiler SQL Server Health and History Tool SQL Workload SQLH SQLPERF(WAITSTATS) Standards Control Statistical Aggregation stats_date storage_bound sys.dm_db_index_operational_stats sys.dm_db_index_physical_stats sys.dm_exec_query_stats sys.dm_os_performance_counters sys.dm_os_waiting_tasks sys.dm_tran_locks sysindexes syslockinfo system waste T table_list_file table_name technical analysis tempdb TEMPOBJ Timeframes trace flags TRACEON transaction log tree depth trend analysis TRUNCATE T-SQL tuning_feature_set tuning_feature_setting U UMS_THREAD UPDATE STATISTICS UPDATEUSAGE V vertical partitioning W wait event interface wait time wait type wait types waitfor WAITFOR Waits Windows Task Manager workload analysis Workload Analysis workload_file workload_table_name workspace memory WRITELOG X XML

Reviews:

Succinct and immediately applicable, August 16, 2005   Reviewer: MattBrody - See all my reviews I was 1/2 sold when I read in the introduction that the book wasn't to be a 700-page doorstop. After reading the whole book, now I'm totally sold. Clear, concise, and easy to apply. Acts like a reference manual on steroids for my day-to-day needs. Gave me some good best practices advice, too. Art work is a little strange, but the content is spot on.

Succinct and immediately applicable, August 17, 2005   Reviewer: MattBrody - See all my reviews I was 1/2 sold when I read in the introduction that the book wasn't to be a 700-page doorstop. After reading the whole book, now I'm totally sold. Clear, concise, and easy to apply. Acts like a reference manual on steroids for my day-to-day needs. Gave me some good best practices advice, too.

Another solid book from this author, August 16, 2005   Reviewer: William Curry "William" (Atlanta, GA) I've read Schumacher's Oracle book as well as his articles, and now he's taken that same style over to SQL Server. I've only had this book for a short time, but it's already paid for itself (more than once). His writing style is easy to follow and the code snippets are excellent.

Errata:
Page 114 -

·         avg_fragmentation_in_percent – the logical fragmentation percentage, which takes into account multiple files.  Microsoft’s rule of thumb is that any index with a score over 30 is a candidate for a rebuild/reorganization.   

·         fragment_count – the number of physically consecutive leaf pages in the index. 

·         page_count – number of data pages.

·         avg_page_space_usec_in_percent – describes how full the pages in the table/index are.

·         avg_fragment_size_in_pages – the average number of pages in one fragment of the index.  Larger numbers are better here. 

Page 116 - (7,NULL,NULL,NULL,’DETAILED’)

Page 116 -  Figure 5.7