Monitoring and Optimizing SQL Server 2005 PerformanceAnil DesaiSpeaker InformationAnil DesaiIndependent consultant (Austin, TX)Author of several SQL Server booksInstructor, “Implementing and Managing SQL Server 2005” (Keystone Learning)Info: http:/AnilDesai.net or AnilAnilDesai.net Agenda and OverviewPerformance Monitoring OverviewUsing SQL ProfilerIntegrating with Windows System MonitorGetting Performance InformationDynamic Management Views (DMVs)SQL Server Management Studio ReportsUsing the Database Engine Tuning AdvisorManaging Processes, Locking, and DeadlocksPartitioning and Physical Storage DesignPerformance Monitoring ApproachesPerformance optimization process:1.Establish a baseline2.Identify bottlenecks3.Make one change at a time4.Measure performance5.Repeat (if desired)RecommendationsOptimize for real-world workloads Monitor/review performance regularly Focus on specific issuesOverview of Performance MonitoringSystemWindows System MonitorAlerts (Performance-Based)SQL ServerSQL Profiler / SQL TraceActivity MonitorDynamic Management Views (DMVs)SQL Server Agent AlertsQuery-LevelDatabase Engine Tuning AdvisorQuery Execution Plans Understanding SQL ProfilerPurpose / Features: GUI for managing SQL TraceMonitor important eventsCapture performance data / resource usageReplaying of workloads / transactionsIdentifying performance bottlenecksCorrelation of data with System MonitorWorkloads for Database Tuning AdvisorExamples:Generate a list of the 100 slowest queriesMonitor all failed logins (Security)SQL Server Profiler ArchitectureSQL Profiler TerminologyTrace DefinitionsEventsColumnsFiltersCreating and Managing SQL TracesSQL Profiler (GUI)System Stored Procedures (Transact-SQL)Trace Templates (Built-In)Standard (Default), SP_CountsTSQL, TSQL_Duration, TSQL_Grouped,TSQL_Replay, TSQL_SPsTuningConfiguring Trace EventsGroupings:Event Categories Event ClassesEventsExamples:TSQLStored ProceduresPerformanceErrors and WarningsSecurity auditingConfiguring Trace ColumnsSpecify the details to be recordedColumns can be ordered and groupedValues can be filteredExamples of Columns:StartTime / EndTimeTextDataDurationResource Usage (CPU, Reads, Writes)Information: User, Database, App. NamesTrace Output OptionsInteractiveGood for “live” monitoring of small sets of dataTrace Files (*.trc)Can enable file rollover based on size“Server processes trace data” optionTrace tableWill automatically create the tableCan set maximum number of rowsScheduling of traces (stop time)Creating Profiler TracesLaunching SQL ProfilerConnecting to a database instanceConfiguring output optionsCreate a trace definitionSpecifying events, columns, and filtersOther SQL Profiler OptionsCreating new templatesScripting trace definitionsExtracting SQL Server EventsTransact-SQL EventsShowPlan EventsDeadlock EventsUsing System Monitor with SQL ProfilerPurpose / Goal:Correlate server performance with database performanceProcess:Define and start a counter logDefine and start a SQL Profiler traceImport Performance Data in SQL ProfilerRequired Trace propertiesStartTimeEndTimeWindows Performance MonitorCan monitor local or remote computersPerformance Statistics:ObjectsCountersInstancesModes:System MonitorPerformance Logs and AlertsCounter LogsTrace LogsAlertsUsing Dynamic Management Views (DMVs)Purpose:Monitoring and troubleshootingView server state and performance detailsReturns relational result setsScopes:Server levelDatabase levelDMV ExamplesExamples:Database EngineSys.DM_DB_File_Space_UsageIndexesSys.DM_DB_Index_Operational_StatsSys.DM_DB_Index_Physical_StatsI/O RelatedSys.DM_IO_Pending_IO_RequestsSys.DM_IO_Virtual_File_StatsCommon Language RuntimeDatabase MirroringTransactionsSQL Server Management Studio ReportsOverviews of SQL Server usageCan export to Excel or PDFServer-Level Report Examples:Server DashboardMemory ConsumptionActivity All Block TransactionsActivity Top SessionsPerformance Batch Execution StatisticsPerformance Top Queries by Average CPUObject Execution StatisticsDatabase-Level ReportsExamples:Disk UsageAll TransactionsAll Blocking TransactionsIndex Usage StatisticsTop Transactions by AgeSchema Changes HistoryMonitoring SQL Server LogsWindows Event Logs / Event Viewer Application and System Event LogsSQL Server Management StudioSQL Server LogsCan configure max. # of log filesSQL Server Agent Error logsCan configure logging levels (Errors, Warnings, Information)Using the Log File ViewerCan Export / Load log informationCan search for specific errors/messagesUsing SQLDiagData Collected:System Information (MSINFO)Windows Event LogsSQL Server configurationCommand-Line Utility (SQLDiag.exe)Stores output to filesConfiguration file: SQLDiag.xmlCan run as a service (/R)Can run in continuous modeDatabase Engine Tuning AdvisorCan make performance-related recommendationsReplaces the “Index Tuning Wizard”Evaluates Physical Design Structures (PDS)Indexes (clustered, non-clustered)Indexed ViewsPartitionsNumerous analysis optionsOutputGenerates modification scriptsGenerates Reports for later analysisWorkloadsFilesTransact-SQL FilesXML FilesShould represent commonly-used queriesSQL Profiler Trace Files / TablesUse Tuning built-in trace templateEvents: Transact-SQL BatchRemote Procedure Call (RPC)Columns: Event Class and Text DataDTA OptionsLimit tuning time Tuning OptionsAllowed Physical Design Structures (PDS)Keep all/specific existing objectsMaximum storage spaceOnline or offline recommendationsPartitioningDTA ReportsCan export to XML filesExamples:Workload analysisColumn accessTable accessView-Table RelationsStatement costEvent frequencyIndex Usage (current / recommended)Using the Database Engine Tuning AdvisorProcess:Generate a workload (file or table)Select tuning optionsRun the analysisView reportsSave and/or apply recommendationsRunning the DTA:Database Engine Tuning Advisor Application (GUI)Dta.exe command-line utilityUnderstanding ProcessesProcessesInteractive users SQL Server Management StudioApplications (Connection Pooling)SQL ProfilerDatabase Engine Tuning AdvisorReplicationService BrokerProcess IDs 50 are system-relatedMonitoring ProcessesSQL Server Activity MonitorProcesses (connected users)Locks (by Process / by Object)Filtering optionsAuto-refresh optionSystem Stored Procedures / ViewsSys.DM_Exec_Sessions Sys.DM_Exec_RequestsSys.SysProcessessp_who / sp_who2Managing ProcessesProcess InformationCurrent Process ID: SPIDSession Options: DBCC USEROPTIONSKilling ProcessesKILL ProcessID WITH STATUSONLYViewing Last ActivityDBCC INPUTBUFFER(ProcessID)DBCC OUTPUTBUFFER(ProcessID)Understanding LockingCoordinates multiple accesses to the same dataEnsures ACID Properties for transactions (Atomic, Consistent, Independent, Durable)Contention can reduce performanceLocking granularity: Row-Level, Page-Level, Table-Level, etc.Lock Modes: Shared, Exclusive, etc.Lock escalationUnderstanding BlockingBlockingWhen transaction(s) must wait for a lock on a resourceLOCK_TIMEOUT setting (default = wait forever)Locking Models:PessimisticOptimisticTransaction Isolation LevelsBalance of concurrency (performance) vs. consistencyAffects SELECT queriesSET TRANSACTION ISOLATION LEVELTransaction Isolation LevelsREAD UNCOMMITTEDREAD COMMITTED (default)REPEATABLE READSERIALIZABLERow-Versioning:ALLOW_SNAPSHOT_ISOLATION READ_COMMITTED_SNAPSHOTMonitoring Locking ActivityActivity MonitorSQL ProfilerLocks Event CategorySystem Monitor: SQL Server Locks ObjectSystem ViewsSys.DM_Tran_LocksSys.DM_Exec_RequestsSystem Stored Proceduressp_LockUnderstanding the Deadlock ProcessDeadlocks:Two or more tasks permanently block each other based on resource locksDefault resolution is within 5 secondsDeadlock victim Transaction is rolled-backProcess receives a 1205 errorExample:Process 1 locks the Customers table and requires access to the Orders TableProcess 2 locks the Orders table and requires access to the Customers TableAvoiding DeadlocksMinimize transaction timesCommit / Rollback transactions as quickly as possiblyAvoid user-related time within a transactionAccess objects in a consistent orderChange the transaction isolation levelUse a lower level isolation level, if appropriateUse snapshot-based isolation levelsDeadlock VictimsDeadlock priorities:SET DEADLOCK_PRIORITY (LOW, NORMAL, HIGH, integer)Deadlock resolution:Lower priority is killed firstIf equal priorities, least expensive transaction becomes the victimApplication or user should attempt to re-run the transactionMonitoring DeadlocksSQL Server Error LogSQL Profiler Locks Event CategoryLock:Deadlock ChainLock:DeadlockDeadlock GraphEvents Extraction Trace PropertyExport deadlock XML (.xdl) fileViewing Deadlock FilesSQL Server Management Studio (File Open SQL Deadlock Files (*.xdl)Deadlock GraphUnderstanding PartitionsPhysical distribution of dataPartitions can be on the same or different filegroupsPartitioning MethodsHorizontal PartitioningVertical PartitioningExample:Partition 1: Current Sales Data (Current Year)Partition 2: Archived Sales DataBenefits of PartitioningNo special requirements for users or applicationsCan partition tables and indexesFast transfer of data between partitions Supports “Sliding” windowsCan improve query performanceSimplifies management of large tablesIncreases maintenance performance (indexes)Designing PartitionsReasons to implement partitioningLarge tablesPerformance problemsData usage (read-only historical data)Partial backupsSupported in Enterprise and Developer EditionsCan have up to 1,000 partitionsImplementing PartitionsProcess:Create a partition functionCreate a partition schemeCreate a table using the schemeOther options:Managing partitioned dataCreating CHECK constraintsCreating a Partition FunctionDefines sets of data based on ranges within the partitioning columnExamples: Dates, Row IDs, alpha valuesRANGE LEFT (default) / RIGHTComputed columns must be persistedExample:CREATE PARTITION FUNCTION fn_part1 (int) AS RANGE LEFT FOR VALUES (1000, 2000, 3000)$PARTITION Function shows on which partition data would resideCreating a Partition SchemeDefines storage options for each partitionBased on a single partition functionCan specify files or filegroups for storageA filegroup can be used for multiple partitionsExample:CREATE PARTITION SCHEME Partition1AS PARTITION PartitionFunctionTO (FileGroup1, FileGroup2, FileGroup3)Creating Partitioned TablesSpecify the partition scheme when creating a tableCREATE TABLE ON PartitionSchemeData storage will be based on the partition schemeCreating Partitioned Indexes“Aligned” partitioningIndex partitions are based on table partitionsManaged automatically by SQL ServerOptimizes switching performanceManual partition managementCan use CREATE INDEX ON clausePartition column is automatically included in clustered and non-clustered indexesManaging Partitioned DataPhysical location of data may not be moved (only pointers are updated)CHECK constraints can be used to manage data“Sliding Windows”Partition functions can be modifiedSplitting: Adding a new partitionMerging: Combining two partitionsPartition switchingALTER TABLE SWITCHMoves data between partitionsWorking with Partitioned DataSQL Server Management Studio Database Reports Disk Space Used by Partitions ObjectNameSystem Views:Sys.Tables / Sys.IndexesSys.PartitionsSys.Partition_SchemesSys.Partition_ParametersSys.Partition_Range_ValuesFor More InformationResources from Anil DesaiWeb Site (http:/AnilDesai.net)E-Mail: AnilAnilDesai.net Keystone Learning Course: “Microsoft SQL Server 2005: Implementation and Maintenance (Exam 70-431)”The Rational Guide to Managing Microsoft Virtual Server 2005The Rational Guide to Scripting Microsoft Virtual Server 2005