Transcript Document

SQL Server
2000 and 2005
Performance Tuning
Jeremy Kadlec
Edgewood Solutions
www.edgewoodsolutions.com
[email protected]
410.591.4683
CFUNITED – The premier ColdFusion conference
www.cfunited.com
Session Agenda





Introductions
The Reality
Design and Development
Monitor and Diagnose
Q & A and thank you
June 28th – July 1st 2006
Jeremy Kadlec
 Edgewood Solutions (www.edgewoodsolutions.com)
 Customer focused SQL Server solutions
 Planning, Audits, Integration, Training, Products
 Performance Tuning, Administration, Development, Upgrades,
High Availability, Disaster Recovery, Database Auditing
 Principal Database Engineer
 [email protected]
 410.591.4683
 Author of numerous SQL Server resources
 www.edgewoodsolutions.com/resources/articles.asp
 SearchSQLServer.com – Ask the Experts
 The Rational Guide to IT Project Management
 NOVA SQL Co-Leader – www.novasql.com
 SQL Server 2005 Adoption Rate Report
 www.edgewoodsolutions.com/EdgewoodLabs/
June 28th – July 1st 2006
Topic 1 – The Reality
 People, Process, Technology
 Best Practices
 Performance Tuning Circle of Life
June 28th – July 1st 2006
People, Process, Technology
Talented team with
architecture, hardware,
development and
database expertise
People
Process
Documented and
practiced processes
to analyze, prioritize,
develop, test,
implement and learn
Technology
Intuitive tools with the ability to make informed
decisions and accurate corrections efficiently
June 28th – July 1st 2006
Performance Tuning Best Practices
 Focus on performance needs from the project scope
to maintenance
 Design and develop for high performance
• Hardware, Windows, SQL Server and application
 System baseline with ongoing comparisons
 Monitor, analyze, alert and report
 Solidified change management process
 Properly designate permissions based on duties
 Work towards continuous improvements
June 28th – July 1st 2006
Performance Tuning Circle of Life
Learn
Analyze
Implement
Prioritize
Test
Develop
June 28th – July 1st 2006
 Art and a science
 People, Processes and
Technology
 Few simple problems,
typically complex based
on application
 Holistic view needed
 Goal – Accurate and
efficient correction
without any biases
Topic 2 – Design and Development





Idealistic Hardware Configuration
Paint Like Leonardo
The Index Impact
T-SQL Coding
Graphical Query Plans
June 28th – July 1st 2006
Idealistic Hardware Configuration
Disk
Disks
Size
Controller
0
2
72 GB
1–1
1
72
C:\
Windows, SQL Server
Program Files and System
Databases (Master,
Model, MSDB) (Random)
1
2
72 GB
1–1
1
72
D:\
Tempdb (Random)
2
2
72 GB
1–2
1
72
E:\
Database Transaction
Logs (Sequential)
3
5
72 GB
2–1
5
288
F:\
Databases (Random)
4
3
72 GB
2–2
5
144
G:\
Backups and Batch
Processing (Sequential)
14
June 28th – July 1st 2006
RAID Size
648
Volume
Purpose
Be Like Leonardo…
 Leverage the
inherent data
modeling and data
dictionary
capabilities in
Enterprise Manager
 Share among IT
and business team
to improve
communications
 Demo
June 28th – July 1st 2006
The Index Impact
 Identify valuable indexes per table
 Coffee break – Indexes needed
 Page splitting – Too compact fill factor
 More indexes then columns – Too many indexes
 Balance SELECT vs. INSERT, UPDATE and
DELETE statements
 Validate performance expectations
 Schedule predefined index maintenance
 Don’t forget UPDATE STATISTICS
 Good candidates for indexes
 PK, FK, JOIN, WHERE, ORDER BY, GROUP BY
June 28th – July 1st 2006
Database Engine Tuning Advisor
 Start | All Programs |
Microsoft SQL Server 2005
| Performance Tools
 Capture data via Profiler
and save to a table
 Index and partition
analysis
 Analyze results and
provide data with the
percentage improvement
June 28th – July 1st 2006
Index Maintenance
 DBCC
SHOWCONTIG
 Determine the table,
index and data
fragmentation
 Rule of thumb 80%
fragmentation
June 28th – July 1st 2006
 Index rebuild
options
 SQL Server
Maintenance Wizard
 DBCC DBREINDEX
 DROP INDEX and
CREATE INDEX
 DBCC
INDEXDEFRAG
T-SQL Coding
 Rely on set based logic, no cursors or
looping when unnecessary
 Leverage stored procedures
 Avoid dynamic SQL use compiled code
 Review query plans for all code and
optimize each query
 Reference the materials from Selene
Bainum for specific examples
June 28th – July 1st 2006
Query Analyzer – Query Plan
 Ability to graphically
review the query
execution plan
 Insight into SQL Server’s
optimizer
 Ability to change code
and review the
optimizer’s impact
immediately
 SET SHOWPLAN_ALL
 SET SHOWPLAN_TEXT
June 28th – July 1st 2006
Topic 3 – Monitor and Diagnose
 Performance Monitor
 SQL Server Profiler
 Locking and Blocking
June 28th – July 1st 2006
Performance Monitor
Overview – Capture a macro
view of the servers with the
ability to configure counters with
specific sample rates save to a
log file or real time monitor
Counters
 Memory, Processors
 SQL Server
 Network Activity, Disk Drives
 System Statistics (Threads,
Context Switching, Queuing,
etc.)
Samples – Secs to mins
June 28th – July 1st 2006
Performance Monitor Counters

Memory





Page Reads/sec
Page Writes/sec
Page Input/sec
Page Output/sec
Network Interface






Bytes Received/sec
Bytes Sent/sec
Bytes Total/sec
Current Bandwidth
Output Queue Length

Objects
 All


Paging File
 All
June 28th – July 1st 2006

Physical Disk
 % Disk Read Time
 % Disk Write Time
 % Idle Time
 Avg Disk Bytes/Read
 Avg Disk Bytes/Transfer
 Avg Disk Bytes/Write
 Avg Disk Queue Length
 Current Disk Queue Length
Process
 % Privileged Time
 % Processor Time
 % User Time
Processor
 % Privileged Time
 % Processor Time
 % User Time
Performance Monitor Counters
 Server Work Queues





Active Threads
Available Threads
Queue Length
Total Bytes/sec
Total Operations/sec
 SQLServer:Access Methods
 Full Scans
 Page Splits/sec
 Table Lock Escalations/sec
 SQLServer:Cache Manager
 Cache Hit Ratio - _Total
 Cache Pages - _Total
June 28th – July 1st 2006
 SQLServer:Databases
 Transactions/sec
 SQLServer:General
Statistics
 Logins/sec
 Logouts/sec
 User Connections
 SQLServer:Locks
 Number of Deadlocks/sec
SQL Server 2005 Profiler
Overview – Micro view
of all SQL Server
transactions saved to a
log file or database table
Filters – Ability to
capture a subset of the
transactions based on
the transaction type,
user, application, etc.
Concerns – High
system overhead
June 28th – July 1st 2006
SQL Profiler Event Data
 Cursors
 CursorOpen
 CursorExecute
 CursorClose
 Errors and Warnings
 Hash Warning
 Missing Column
Statistics
 Locks
 Lock:Deadlock
 Lock:Timeout
 TSQL
 Unprepare SQL
June 28th – July 1st 2006
 Parallelism
 Degree of Parallelism –
All Counters
 Execution Plan
 Show Plan All
 Show Plan Statistics
 Show Plan Text
 Stored Procedure
 SP:Starting
 SP:Completed
 SP:Recompile
 SP:StmtCompleted
 SP:StmtStarting
SQL Server 2005 - Enhancements
 Use SQL 2005 Profiler to capture data
on a SQL Server 2000 server
 Profiler for SQL 2005 Analysis Services
 Ability to review Profiler and Perfmon
data in a single interface
June 28th – July 1st 2006
Locking and Blocking
Business
Processes
Blocking
and Blocker
Spids
Common
Tables
Associated
Code
June 28th – July 1st 2006
 Which processes
(spids) are blocking?
 What code (stored
procedures, functions,
ad-hoc, etc.)
corresponds to the
spids?
 Which tables does the
code share?
 Which business
processes map back to
the code?
SQL 2005 - Activity Monitor
 Management Studio | Management folder
| Activity Monitor icon
 Snapshot of SQL Server processing
June 28th – July 1st 2006
Dynamic Management Views
 Operate in near real time from internal structures at a
Server and Component level










dm_exec_* = Execution of user code and associated connections
dm_os_* = Memory, locking and scheduling
dm_tran_* = Transactions and isolation
dm_io_* = I/O on network and disks
dm_db_* = Databases and database objects
dm_repl_* = Replication
dm_broker_* = SQL Service Broker
dm_fts_* = Full Text Search
dm_qn_* = Query Notifications
dm_clr_* = Common Language Runtime
June 28th – July 1st 2006
How DMVs Improve Management
 Index-related DMVs
sys.dm_db_index_physical_stats
 Size and fragmentation information for tables and
indexes
sys.dm_db_index_operational_stats
 Internals information for table and index activities
sys.dm_db_index_usage_stats
 Index statistics and usage counts information for
individual indexes
sys.dm_db_index_partition_stats
 Page and row-count information for every partition
June 28th – July 1st 2006
Questions and Thank You
Jeremy Kadlec
Edgewood Solutions
www.edgewoodsolutions.com
[email protected]
410.591.4683
June 28th – July 1st 2006