SQL Server 2000 and 2005 Performance Tuning Jeremy

SQL Server 2000 and 2005 Performance Tuning Jeremy Kadlec Edgewood Solutions www. edgewoodsolutions. com jeremyk@edgewoodsolutions. com 410. 591. 4683 CFUNITED – The premier Cold. Fusion conference www. cfunited. com

Session Agenda § § § Introductions The Reality Design and Development Monitor and Diagnose Q & A and thank you June 28 th – July 1 st 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 ü jeremyk@edgewoodsolutions. com ü 410. 591. 4683 § Author of numerous SQL Server resources ü www. edgewoodsolutions. com/resources/articles. asp ü Search. SQLServer. 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/Edgewood. Labs/ June 28 th – July 1 st 2006

Topic 1 – The Reality § People, Process, Technology § Best Practices § Performance Tuning Circle of Life June 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 2006

Performance Tuning Circle of Life Learn Analyze Implement Prioritize Test Develop June 28 th – July 1 st 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 28 th – July 1 st 2006

Idealistic Hardware Configuration 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 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 2006

Index Maintenance § DBCC SHOWCONTIG ü Determine the table, index and data fragmentation ü Rule of thumb 80% fragmentation June 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 2006

Topic 3 – Monitor and Diagnose § Performance Monitor § SQL Server Profiler § Locking and Blocking June 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 2006

SQL Profiler Event Data § Cursors ü Cursor. Open ü Cursor. Execute ü Cursor. Close § Errors and Warnings ü Hash Warning ü Missing Column Statistics § Locks ü Lock: Deadlock ü Lock: Timeout § TSQL ü Unprepare SQL June 28 th – July 1 st 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: Stmt. Completed ü SP: Stmt. Starting

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 28 th – July 1 st 2006

Locking and Blocking Business Processe s Blocking and Blocker Spids Common Tables Associate d Code June 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 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 28 th – July 1 st 2006

Questions and Thank You Jeremy Kadlec Edgewood Solutions www. edgewoodsolutions. com jeremyk@edgewoodsolutions. com 410. 591. 4683 June 28 th – July 1 st 2006