Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

  

 

  SQL Server Tuning Scripts
Performance Optimization Secrets

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-9916386-7-0
ISBN 13 978-0-9916386-7-3
Library of Congress Number 2014940742
275 pages
Perfect bind - 9.25x7.5
PD 514
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.
 

Key Features

* 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
 
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
 
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
 
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:

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks