SlideShare a Scribd company logo
1 of 46
Ing. Eduardo Castro, PhD
ecastro@grupoasesor.net

http://ComunidadWindows.org
Session Objectives:
  Get familiar with database performance tuning
  tools, especially what SQL Server 2008 offers
  Understand methodology of database
  performance bottleneck analysis
  Learn features/improvements of SQL Server
  2008 that help improve performance
Target the most challenging and innovative
SQL Server applications

10+ TB DW, 3k/tran/s OLTP, Large 500GB+ Cubes, Competitive migrations,
Complex deployments, Server Consolidation (1000+)


Invest in large scale, referenceable SQL Server projects
across the world
    Provide SQLCAT technical & project experience
    Conduct architecture and design reviews covering performance,
    operation, scalability and availability
    Offer use of HW lab in Redmond with direct access to SQL Server
    development team
Performance Tuning Tools
I/O Bottleneck
CPU Bottleneck
Memory Bottleneck
System Performance Monitor (Perfmon)
SQL Profiler Trace
Dynamic Management Views/Functions
DBCC commands
SQLDiag (Shipped with SQL 2005/2008)
SQLIO, SQLIOSim
KernRate
Debugging tools (Windbg, DebugDiag,…)
More…
Data Collector & Management Data
Warehouse (MDW)
Extended Events
Activity Monitor
New DMVs/DMFs
MDW Host



                                   MDW         Client Workstation
     Target Server                Snapshot
                                    Data




                 dcexec.exe                  Management Studio
   SSIS
   Pkgs
                     Cache
Collection Set                                    MDW
  Metadata            Files
                                                 Reports
Job Metadata
                 SQL Agent
   msdb
Time line




Navigation
 control



Resource
 usage
Highly scalable with low overhead
Future of SQL tracing
Built-in system_health session
select event_session_id, name, startup_state from
sys.server_event_sessions

Session definition example
create event session session_error on server
    ADD EVENT sqlserver.error_reported
             (action (sqlserver.sql_text, sqlserver.tsql_stack,
    sqlserver.client_app_name)
             where error = 50001)
    ADD target package0.ring_buffer
    with (max_dispatch_latency = 1 seconds)
go
alter event session session_error on server state = start;
go
SQL Server Performance Analysis
Memory allocation tracking
   sys.dm_os_memory_brokers
   sys.dm_os_memory_nodes
Reqource Governor
   sys.dm_resource_governor_configuration
   sys.dm_resource_governor_resource_pools
   sys.dm_resource_governor_workload_groups
Query performance (hash value for query and plan)
   sys.dm_exec_query_stats
  Sys.dm_exec_requests
  sys.dm_exec_procedure_stats
  sys.dm_exec_trigger_stats
More…
Bottleneck – A point or component of a
process where throughput is the slowest.
Understanding where time is spent
Elapsed Time = Running + Waiting
Key is the waiting - what type of wait(s)?
Monitor and identify I/O bottleneck
  Resource Monitor & Performance Monitor
  MDW
  DMV & Extended Events
What is driving high I/O load?
  Inefficient query plan
  Too much data to read/write
  Slow I/O subsystem
File level I/O
  throughput
    and disk
response time
Runtime
Performance
 monitoring




  System
  Reports




What counters
 to look at?
Physical Disk Object:
   Avg. Disk Queue Length
   Avg. Disk Sec/Read
   Avg. Disk Sec/Write
   %Disk Time
   Disk Transfers/Sec
   Avg. Disk Bytes/Read
   Avg. Disk Bytes/Write
   Disk Read Bytes/Sec
   Disk Write Bytes/Sec
SQL Server Buffer Manager Object: (memory
pressure could cause I/O pressure)
   Buffer Cache hit ratio
   Page Life Expectancy
   Checkpoint pages/sec
   Lazywrites/sec
Page I/O waits
in Buffer pool




WriteLog and
 LogBuffer
    waits
High I/O Latch    sys.dm_os_wait_stats
  Wait Time       sys.dm_exec_requests




pending/stalled   sys.dm_io_pending_io_requests
     I/O          sys.dm_io_virtual_file_stats




 ERRORLOG                                         This is
                  15 second delay in IO
warnings of I/O   completion                      way too
    delays                                        slow
sqlos package                                              sqlserver package

•   async_io_requested                                    •   physical_page_read
•   async_io_completed                                    •   physical_page_write
•   wait_info                                             •   file_read
•   wait_info_external                                    •   file_write
                                                          •   long_io_detected



        2009-04-13 18:30:10.170 spid5s        SQL Server has encountered 1
        occurrence(s) of I/O requests taking longer than 15 seconds to complete on file
        [T:tempdbtemplog.ldf] in database [tempdb] (2). The OS file handle is
        0x0000000000000670. The offset of the latest long I/O is: 0x000007eecac000
        king longer than 15 seconds to complete on file [e:myslowdb.mdf] in database
        [myslowdb] (1). The OS file handle is
        0x00000438. The offset of the latest long IO is: 0x000000513ce000
Look if there is memory pressure
Further narrow down to what queries are
generating the I/O workload. Tune these
queries.
Explore data compression to reduce I/O
load
Upgrade storage hardware
Compress tables/indexes to reduce
database size
Compression ratio depends on schema &
data distribution (avg 50-60%)
Reduce query runtime I/O (increase cpu)
Identify compression candidates
  sp_estimate_data_compression_savings
  sys.dm_db_index_operational_stats
SQL Server Performance Analysis
Sustained %Processor Time > 80%
  Performance Monitor/Task Manager
  MDW Server Activity Report
  DMV & Extended Events
What is driving high CPU?
  Recompilations
  Cursors
  Inefficient query plans
  More…
Resource          • Monitor it Live
Monitor/Task Manager

Performance Monitor     • Watch Live or Log
      Counters            counters you like

System Data Collector   • Formatted Perfmon
      Reports             counter based report

Windows Performance     • Dig deeper using ETW
   Toolkit (XPerf)
Processor object:
   %Privileged Time
   %Processor Time                New in SQL Server
                                  2008 for Resource
   %User Time                      Governor feature
Process object (SQL Server):
   %Privileged Time
   %Processor Time
   %User Time
SQL Server:Workload Group Stats
   CPU usage %
   Max request cpu time
SQL Server:Resource Pool Stats
   CPU usage %
   CPU usage target %
SQL Server:SQL Statistics
  Batch requests/sec
  SQL Compilations/sec
  SQL Re-Compilations/sec
SQL Server:Cursor Manager by Type
SQL Server:Cursor Manager Total

                                 Concurrent
                            processing of multiple
                            cursors could be cpu
                                  intensive
Total CPU
  waits




CPU cycles
that did the
 real work



  Wait in
 runnable
  queue




  Thread
voluntarily
  yields
sys.dm_exec_query_stats
Sys.dm_exec_sessions   sys.dm_exec_requests   sys.dm_exec_procedure_stats   Sys.sysprocesses
                                                sys.dm_exec_trigger_stats




      cpu_time               cpu_time            total_worker_time
                                                                                   cpu




 total_scheduled_tim
          e
                         total_elapsed_time     total_elapsed_time



                                                                               Backward
                                                                            compatibility view
                                                Sql_handle/query_h
  total_elapsed_time        query_hash
                                                       ash
sqlos package                                                 sqlserver package

•scheduler_monitor_non_yielding_ring_buffer_recorded            •sql_statement_completed
•spinlock_backoff                                               •sp_statement_completed
•Wait_info                                                      •cursor_manager_cursor_end
•wait_info_external                                             •checkpoint_end




              Extended Event can track individual statement level events like Profiler does
              with minimal impact -
              ADD EVENT sqlserver.sql_statement_completed
                                        (action (sqlserver.sql_text)
                                        WHERE duration > 0),
                            ADD EVENT sqlserver.sp_statement_completed
                                        (action (sqlserver.sql_text)
                                        WHERE duration > 0)

              select top 10
              CONVERT(xml, event_data).value('(/event/data/value)[4]','int') as 'cpu'
High cpu doesn’t necessarily mean it’s a
problem.
Drill down to specific workload and queries
that drive cpu usage. Tune them.
Use Resource Governor to protect high
priority workloads
Upgrade hardware
Resource Governor
  allocate cpu/memory to prioritized workloads
  based on connection string parameters
  Mixed workloads: OLTP, Reporting,
  Maintenance
Soft Numa
  Node configuration (soft numa node and cpu
  affinity)
  Network Configuration (soft numa node and tcp
  port)
SQL Server Performance Analysis
Monitor and identify memory pressure
  Performance Monitor/Task Manager
  MDW Server Activity Report
  DMV & Extended Events
What is eating up my memory?
  External or internal memory pressure
  Inefficient query plan (hashing, sorting,…)
AWE
   32-bit uses AWE to cache data/index pages
   64-bit uses AWE to implement “Locked pages
   in memory”


 Environment                 Virtual Address   Max physical
                             Space             memory
 32-bit on 32-bit OS         2 GB              64 GB
 32-bit on 64-bit OS (Wow)   4 GB              64 GB
 64-bit on 64-bit OS         8 TB              2 TB
Perfmon is golden tool to identify memory
pressure
Memory object:
   Available K/M bytes
   Commit Limit
   Paging File %Usage (Peak)
   Page reads/sec
Process object:
   Private bytes
   Virtual bytes
   Working set
SQL Server Memory Manager object:
    Connection Memory
    Lock Memory                         Somebody is waiting on
    Optimizer Memory                          memory
    Memory Grants Pending
    …
SQL Server Buffer Manager object:
    Buffer cache hit ratio
    Checkpoint pages/sec
    Free list stalls/sec
    Free pages
    Lazy writes/sec
    Page life expectancy                 How long will the page
    Stolen pages                                 live?
    Target pages
    Total pages
SQL Server Plan Cache object
SQL Server Resource Pool Stats object
Query execution waits on
  memory for sorting,
       hashing,…
DBCC MEMORYSTATUS
sys.dm_os_sys_memory
  system_memory_state_desc a good
  indicator of available physical memory
sys.dm_os_process_memory
  process_physical_memory_low = 1
  indicates low physical memory.
  memory_utilization_percentage value below
  100% indicates external memory pressure.
sys.dm_os_memory_clerks
sys.dm_os_ring_buffers
  RING_BUFFER_SCHEDULER_MONITOR
  RING_BUFFER_RESOURCE_MONITOR
  RING_BUFFER_OOM
  RING_BUFFER_MEMORY_BROKER
  RING_BUFFER_BUFFER_POOL
sqlos package                          sqlserver package

• page_heap_memory_allocated             • sort_memory_grant_adjustment
• page_heap_memory_freed                 • plan_cache_cache_hit
• memory_node_oom_ring_buffer_recorded   • plan_cache_cache_attempt
                                         • cursor_manager_cursor_memory_usage




          2009-04-14 09:27:15.43 spid62 Failed allocate
          pages: FAIL_PAGE_ALLOCATION 1
Check memory configuration – sp_configure
External memory pressure – Identify what
other processes are competing
Internal memory pressure – Identify which
SQL component is main consumer
Workload/query tuning (sorting, hash join…)
Resource Governor
Hardware upgrade
SQL Server Performance Analysis
Focus on methodology – no guess
Correlate data points from multiple tools
Performance tuning is iterative process –
bottleneck could move
Resource contention impacts each other
  Lack of memory -> high cpu & I/O
  Slow I/O -> tempdb contention
  …
Ing. Eduardo Castro, PhD
ecastro@grupoasesor.net
“Troubleshooting Performance Problems in SQL Server
2008” http://msdn.microsoft.com/en-
us/library/dd672789.aspx

More Related Content

What's hot

Oracle: Binding versus caging
Oracle: Binding versus cagingOracle: Binding versus caging
Oracle: Binding versus cagingBertrandDrouvot
 
2013 Collaborate - OAUG - Presentation
2013 Collaborate - OAUG - Presentation2013 Collaborate - OAUG - Presentation
2013 Collaborate - OAUG - PresentationBiju Thomas
 
PuppetDB: Sneaking Clojure into Operations
PuppetDB: Sneaking Clojure into OperationsPuppetDB: Sneaking Clojure into Operations
PuppetDB: Sneaking Clojure into Operationsgrim_radical
 
Sql Server 2005 Memory Internals
Sql Server 2005 Memory InternalsSql Server 2005 Memory Internals
Sql Server 2005 Memory InternalsDmitry Geyzersky
 
2008 Collaborate IOUG Presentation
2008 Collaborate IOUG Presentation2008 Collaborate IOUG Presentation
2008 Collaborate IOUG PresentationBiju Thomas
 
Oracle Open World Thursday 230 ashmasters
Oracle Open World Thursday 230 ashmastersOracle Open World Thursday 230 ashmasters
Oracle Open World Thursday 230 ashmastersKyle Hailey
 
2011 Collaborate IOUG Presentation
2011 Collaborate IOUG Presentation2011 Collaborate IOUG Presentation
2011 Collaborate IOUG PresentationBiju Thomas
 
Ash masters : advanced ash analytics on Oracle
Ash masters : advanced ash analytics on Oracle Ash masters : advanced ash analytics on Oracle
Ash masters : advanced ash analytics on Oracle Kyle Hailey
 
Pandora FMS: PostgreSQL Plugin
Pandora FMS: PostgreSQL PluginPandora FMS: PostgreSQL Plugin
Pandora FMS: PostgreSQL PluginPandora FMS
 
MySQL Enterprise Backup: PITR Partial Online Recovery
MySQL Enterprise Backup: PITR Partial Online RecoveryMySQL Enterprise Backup: PITR Partial Online Recovery
MySQL Enterprise Backup: PITR Partial Online RecoveryKeith Hollman
 
2009 Collaborate IOUG Presentation
2009 Collaborate IOUG Presentation2009 Collaborate IOUG Presentation
2009 Collaborate IOUG PresentationBiju Thomas
 
New features in Performance Schema 5.7 in action
New features in Performance Schema 5.7 in actionNew features in Performance Schema 5.7 in action
New features in Performance Schema 5.7 in actionSveta Smirnova
 
Performance Schema for MySQL troubleshooting
Performance Schema for MySQL troubleshootingPerformance Schema for MySQL troubleshooting
Performance Schema for MySQL troubleshootingSveta Smirnova
 
Data Guard Deep Dive UKOUG 2012
Data Guard Deep Dive UKOUG 2012Data Guard Deep Dive UKOUG 2012
Data Guard Deep Dive UKOUG 2012Emre Baransel
 
Troubleshooting MySQL Performance
Troubleshooting MySQL PerformanceTroubleshooting MySQL Performance
Troubleshooting MySQL PerformanceSveta Smirnova
 
2013 london advanced-replication
2013 london advanced-replication2013 london advanced-replication
2013 london advanced-replicationMarc Schwering
 
Out of the box replication in postgres 9.4
Out of the box replication in postgres 9.4Out of the box replication in postgres 9.4
Out of the box replication in postgres 9.4Denish Patel
 

What's hot (20)

Oracle: Binding versus caging
Oracle: Binding versus cagingOracle: Binding versus caging
Oracle: Binding versus caging
 
2013 Collaborate - OAUG - Presentation
2013 Collaborate - OAUG - Presentation2013 Collaborate - OAUG - Presentation
2013 Collaborate - OAUG - Presentation
 
PuppetDB: Sneaking Clojure into Operations
PuppetDB: Sneaking Clojure into OperationsPuppetDB: Sneaking Clojure into Operations
PuppetDB: Sneaking Clojure into Operations
 
Sql Server 2005 Memory Internals
Sql Server 2005 Memory InternalsSql Server 2005 Memory Internals
Sql Server 2005 Memory Internals
 
2008 Collaborate IOUG Presentation
2008 Collaborate IOUG Presentation2008 Collaborate IOUG Presentation
2008 Collaborate IOUG Presentation
 
Oracle Open World Thursday 230 ashmasters
Oracle Open World Thursday 230 ashmastersOracle Open World Thursday 230 ashmasters
Oracle Open World Thursday 230 ashmasters
 
2011 Collaborate IOUG Presentation
2011 Collaborate IOUG Presentation2011 Collaborate IOUG Presentation
2011 Collaborate IOUG Presentation
 
Ash masters : advanced ash analytics on Oracle
Ash masters : advanced ash analytics on Oracle Ash masters : advanced ash analytics on Oracle
Ash masters : advanced ash analytics on Oracle
 
Pandora FMS: PostgreSQL Plugin
Pandora FMS: PostgreSQL PluginPandora FMS: PostgreSQL Plugin
Pandora FMS: PostgreSQL Plugin
 
MySQL Enterprise Backup: PITR Partial Online Recovery
MySQL Enterprise Backup: PITR Partial Online RecoveryMySQL Enterprise Backup: PITR Partial Online Recovery
MySQL Enterprise Backup: PITR Partial Online Recovery
 
2009 Collaborate IOUG Presentation
2009 Collaborate IOUG Presentation2009 Collaborate IOUG Presentation
2009 Collaborate IOUG Presentation
 
Terraform Cosmos DB
Terraform Cosmos DBTerraform Cosmos DB
Terraform Cosmos DB
 
Oracle GoldenGate
Oracle GoldenGateOracle GoldenGate
Oracle GoldenGate
 
New features in Performance Schema 5.7 in action
New features in Performance Schema 5.7 in actionNew features in Performance Schema 5.7 in action
New features in Performance Schema 5.7 in action
 
Performance Schema for MySQL troubleshooting
Performance Schema for MySQL troubleshootingPerformance Schema for MySQL troubleshooting
Performance Schema for MySQL troubleshooting
 
Data Guard Deep Dive UKOUG 2012
Data Guard Deep Dive UKOUG 2012Data Guard Deep Dive UKOUG 2012
Data Guard Deep Dive UKOUG 2012
 
Troubleshooting MySQL Performance
Troubleshooting MySQL PerformanceTroubleshooting MySQL Performance
Troubleshooting MySQL Performance
 
2013 london advanced-replication
2013 london advanced-replication2013 london advanced-replication
2013 london advanced-replication
 
Out of the box replication in postgres 9.4
Out of the box replication in postgres 9.4Out of the box replication in postgres 9.4
Out of the box replication in postgres 9.4
 
Linux watchdog timer
Linux watchdog timerLinux watchdog timer
Linux watchdog timer
 

Similar to SQL Server Performance Analysis

Sql server performance tuning
Sql server performance tuningSql server performance tuning
Sql server performance tuningJugal Shah
 
PASS VC: SQL Server Performance Monitoring and Baselining
PASS VC: SQL Server Performance Monitoring and BaseliningPASS VC: SQL Server Performance Monitoring and Baselining
PASS VC: SQL Server Performance Monitoring and BaseliningPARIKSHIT SAVJANI
 
It Depends - Database admin for developers - Rev 20151205
It Depends - Database admin for developers - Rev 20151205It Depends - Database admin for developers - Rev 20151205
It Depends - Database admin for developers - Rev 20151205Maggie Pint
 
Investigate SQL Server Memory Like Sherlock Holmes
Investigate SQL Server Memory Like Sherlock HolmesInvestigate SQL Server Memory Like Sherlock Holmes
Investigate SQL Server Memory Like Sherlock HolmesRichard Douglas
 
Ms sql server architecture
Ms sql server architectureMs sql server architecture
Ms sql server architectureAjeet Singh
 
Sql server performance tuning and optimization
Sql server performance tuning and optimizationSql server performance tuning and optimization
Sql server performance tuning and optimizationManish Rawat
 
SQL Server - High availability
SQL Server - High availabilitySQL Server - High availability
SQL Server - High availabilityPeter Gfader
 
Back 2 basics - SSMS Tips (IDf)
Back 2 basics - SSMS Tips (IDf)Back 2 basics - SSMS Tips (IDf)
Back 2 basics - SSMS Tips (IDf)sqlserver.co.il
 
How should I monitor my idaa
How should I monitor my idaaHow should I monitor my idaa
How should I monitor my idaaCuneyt Goksu
 
Ebs dba con4696_pdf_4696_0001
Ebs dba con4696_pdf_4696_0001Ebs dba con4696_pdf_4696_0001
Ebs dba con4696_pdf_4696_0001jucaab
 
Developing on SQL Azure
Developing on SQL AzureDeveloping on SQL Azure
Developing on SQL AzureIke Ellis
 
SQL Server Tuning to Improve Database Performance
SQL Server Tuning to Improve Database PerformanceSQL Server Tuning to Improve Database Performance
SQL Server Tuning to Improve Database PerformanceMark Ginnebaugh
 
6 tips for improving ruby performance
6 tips for improving ruby performance6 tips for improving ruby performance
6 tips for improving ruby performanceEngine Yard
 
Oracle Performance Tuning Fundamentals
Oracle Performance Tuning FundamentalsOracle Performance Tuning Fundamentals
Oracle Performance Tuning FundamentalsCarlos Sierra
 
OpenStack DRaaS - Freezer - 101
OpenStack DRaaS - Freezer - 101OpenStack DRaaS - Freezer - 101
OpenStack DRaaS - Freezer - 101Trinath Somanchi
 

Similar to SQL Server Performance Analysis (20)

Sql server performance tuning
Sql server performance tuningSql server performance tuning
Sql server performance tuning
 
It Depends
It DependsIt Depends
It Depends
 
PASS VC: SQL Server Performance Monitoring and Baselining
PASS VC: SQL Server Performance Monitoring and BaseliningPASS VC: SQL Server Performance Monitoring and Baselining
PASS VC: SQL Server Performance Monitoring and Baselining
 
It Depends - Database admin for developers - Rev 20151205
It Depends - Database admin for developers - Rev 20151205It Depends - Database admin for developers - Rev 20151205
It Depends - Database admin for developers - Rev 20151205
 
Investigate SQL Server Memory Like Sherlock Holmes
Investigate SQL Server Memory Like Sherlock HolmesInvestigate SQL Server Memory Like Sherlock Holmes
Investigate SQL Server Memory Like Sherlock Holmes
 
Ms sql server architecture
Ms sql server architectureMs sql server architecture
Ms sql server architecture
 
Sql server performance tuning and optimization
Sql server performance tuning and optimizationSql server performance tuning and optimization
Sql server performance tuning and optimization
 
Sql Server
Sql ServerSql Server
Sql Server
 
SQL Server - High availability
SQL Server - High availabilitySQL Server - High availability
SQL Server - High availability
 
Back2 Basic Tools
Back2 Basic ToolsBack2 Basic Tools
Back2 Basic Tools
 
Back 2 basics - SSMS Tips (IDf)
Back 2 basics - SSMS Tips (IDf)Back 2 basics - SSMS Tips (IDf)
Back 2 basics - SSMS Tips (IDf)
 
How should I monitor my idaa
How should I monitor my idaaHow should I monitor my idaa
How should I monitor my idaa
 
Ebs dba con4696_pdf_4696_0001
Ebs dba con4696_pdf_4696_0001Ebs dba con4696_pdf_4696_0001
Ebs dba con4696_pdf_4696_0001
 
Developing on SQL Azure
Developing on SQL AzureDeveloping on SQL Azure
Developing on SQL Azure
 
SQL Server Tuning to Improve Database Performance
SQL Server Tuning to Improve Database PerformanceSQL Server Tuning to Improve Database Performance
SQL Server Tuning to Improve Database Performance
 
6 tips for improving ruby performance
6 tips for improving ruby performance6 tips for improving ruby performance
6 tips for improving ruby performance
 
Oracle Performance Tuning Fundamentals
Oracle Performance Tuning FundamentalsOracle Performance Tuning Fundamentals
Oracle Performance Tuning Fundamentals
 
Performance Tuning
Performance TuningPerformance Tuning
Performance Tuning
 
Sherlock holmes for dba’s
Sherlock holmes for dba’sSherlock holmes for dba’s
Sherlock holmes for dba’s
 
OpenStack DRaaS - Freezer - 101
OpenStack DRaaS - Freezer - 101OpenStack DRaaS - Freezer - 101
OpenStack DRaaS - Freezer - 101
 

More from Eduardo Castro

Introducción a polybase en SQL Server
Introducción a polybase en SQL ServerIntroducción a polybase en SQL Server
Introducción a polybase en SQL ServerEduardo Castro
 
Creando tu primer ambiente de AI en Azure ML y SQL Server
Creando tu primer ambiente de AI en Azure ML y SQL ServerCreando tu primer ambiente de AI en Azure ML y SQL Server
Creando tu primer ambiente de AI en Azure ML y SQL ServerEduardo Castro
 
Seguridad en SQL Azure
Seguridad en SQL AzureSeguridad en SQL Azure
Seguridad en SQL AzureEduardo Castro
 
Azure Synapse Analytics MLflow
Azure Synapse Analytics MLflowAzure Synapse Analytics MLflow
Azure Synapse Analytics MLflowEduardo Castro
 
SQL Server 2019 con Windows Server 2022
SQL Server 2019 con Windows Server 2022SQL Server 2019 con Windows Server 2022
SQL Server 2019 con Windows Server 2022Eduardo Castro
 
Novedades en SQL Server 2022
Novedades en SQL Server 2022Novedades en SQL Server 2022
Novedades en SQL Server 2022Eduardo Castro
 
Introduccion a SQL Server 2022
Introduccion a SQL Server 2022Introduccion a SQL Server 2022
Introduccion a SQL Server 2022Eduardo Castro
 
Machine Learning con Azure Managed Instance
Machine Learning con Azure Managed InstanceMachine Learning con Azure Managed Instance
Machine Learning con Azure Managed InstanceEduardo Castro
 
Novedades en sql server 2022
Novedades en sql server 2022Novedades en sql server 2022
Novedades en sql server 2022Eduardo Castro
 
Sql server 2019 con windows server 2022
Sql server 2019 con windows server 2022Sql server 2019 con windows server 2022
Sql server 2019 con windows server 2022Eduardo Castro
 
Introduccion a databricks
Introduccion a databricksIntroduccion a databricks
Introduccion a databricksEduardo Castro
 
Pronosticos con sql server
Pronosticos con sql serverPronosticos con sql server
Pronosticos con sql serverEduardo Castro
 
Data warehouse con azure synapse analytics
Data warehouse con azure synapse analyticsData warehouse con azure synapse analytics
Data warehouse con azure synapse analyticsEduardo Castro
 
Que hay de nuevo en el Azure Data Lake Storage Gen2
Que hay de nuevo en el Azure Data Lake Storage Gen2Que hay de nuevo en el Azure Data Lake Storage Gen2
Que hay de nuevo en el Azure Data Lake Storage Gen2Eduardo Castro
 
Introduccion a Azure Synapse Analytics
Introduccion a Azure Synapse AnalyticsIntroduccion a Azure Synapse Analytics
Introduccion a Azure Synapse AnalyticsEduardo Castro
 
Seguridad de SQL Database en Azure
Seguridad de SQL Database en AzureSeguridad de SQL Database en Azure
Seguridad de SQL Database en AzureEduardo Castro
 
Python dentro de SQL Server
Python dentro de SQL ServerPython dentro de SQL Server
Python dentro de SQL ServerEduardo Castro
 
Servicios Cognitivos de de Microsoft
Servicios Cognitivos de de Microsoft Servicios Cognitivos de de Microsoft
Servicios Cognitivos de de Microsoft Eduardo Castro
 
Script de paso a paso de configuración de Secure Enclaves
Script de paso a paso de configuración de Secure EnclavesScript de paso a paso de configuración de Secure Enclaves
Script de paso a paso de configuración de Secure EnclavesEduardo Castro
 
Introducción a conceptos de SQL Server Secure Enclaves
Introducción a conceptos de SQL Server Secure EnclavesIntroducción a conceptos de SQL Server Secure Enclaves
Introducción a conceptos de SQL Server Secure EnclavesEduardo Castro
 

More from Eduardo Castro (20)

Introducción a polybase en SQL Server
Introducción a polybase en SQL ServerIntroducción a polybase en SQL Server
Introducción a polybase en SQL Server
 
Creando tu primer ambiente de AI en Azure ML y SQL Server
Creando tu primer ambiente de AI en Azure ML y SQL ServerCreando tu primer ambiente de AI en Azure ML y SQL Server
Creando tu primer ambiente de AI en Azure ML y SQL Server
 
Seguridad en SQL Azure
Seguridad en SQL AzureSeguridad en SQL Azure
Seguridad en SQL Azure
 
Azure Synapse Analytics MLflow
Azure Synapse Analytics MLflowAzure Synapse Analytics MLflow
Azure Synapse Analytics MLflow
 
SQL Server 2019 con Windows Server 2022
SQL Server 2019 con Windows Server 2022SQL Server 2019 con Windows Server 2022
SQL Server 2019 con Windows Server 2022
 
Novedades en SQL Server 2022
Novedades en SQL Server 2022Novedades en SQL Server 2022
Novedades en SQL Server 2022
 
Introduccion a SQL Server 2022
Introduccion a SQL Server 2022Introduccion a SQL Server 2022
Introduccion a SQL Server 2022
 
Machine Learning con Azure Managed Instance
Machine Learning con Azure Managed InstanceMachine Learning con Azure Managed Instance
Machine Learning con Azure Managed Instance
 
Novedades en sql server 2022
Novedades en sql server 2022Novedades en sql server 2022
Novedades en sql server 2022
 
Sql server 2019 con windows server 2022
Sql server 2019 con windows server 2022Sql server 2019 con windows server 2022
Sql server 2019 con windows server 2022
 
Introduccion a databricks
Introduccion a databricksIntroduccion a databricks
Introduccion a databricks
 
Pronosticos con sql server
Pronosticos con sql serverPronosticos con sql server
Pronosticos con sql server
 
Data warehouse con azure synapse analytics
Data warehouse con azure synapse analyticsData warehouse con azure synapse analytics
Data warehouse con azure synapse analytics
 
Que hay de nuevo en el Azure Data Lake Storage Gen2
Que hay de nuevo en el Azure Data Lake Storage Gen2Que hay de nuevo en el Azure Data Lake Storage Gen2
Que hay de nuevo en el Azure Data Lake Storage Gen2
 
Introduccion a Azure Synapse Analytics
Introduccion a Azure Synapse AnalyticsIntroduccion a Azure Synapse Analytics
Introduccion a Azure Synapse Analytics
 
Seguridad de SQL Database en Azure
Seguridad de SQL Database en AzureSeguridad de SQL Database en Azure
Seguridad de SQL Database en Azure
 
Python dentro de SQL Server
Python dentro de SQL ServerPython dentro de SQL Server
Python dentro de SQL Server
 
Servicios Cognitivos de de Microsoft
Servicios Cognitivos de de Microsoft Servicios Cognitivos de de Microsoft
Servicios Cognitivos de de Microsoft
 
Script de paso a paso de configuración de Secure Enclaves
Script de paso a paso de configuración de Secure EnclavesScript de paso a paso de configuración de Secure Enclaves
Script de paso a paso de configuración de Secure Enclaves
 
Introducción a conceptos de SQL Server Secure Enclaves
Introducción a conceptos de SQL Server Secure EnclavesIntroducción a conceptos de SQL Server Secure Enclaves
Introducción a conceptos de SQL Server Secure Enclaves
 

Recently uploaded

COMPUTER 10: Lesson 7 - File Storage and Online Collaboration
COMPUTER 10: Lesson 7 - File Storage and Online CollaborationCOMPUTER 10: Lesson 7 - File Storage and Online Collaboration
COMPUTER 10: Lesson 7 - File Storage and Online Collaborationbruanjhuli
 
Machine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfMachine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfAijun Zhang
 
9 Steps For Building Winning Founding Team
9 Steps For Building Winning Founding Team9 Steps For Building Winning Founding Team
9 Steps For Building Winning Founding TeamAdam Moalla
 
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDEADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDELiveplex
 
AI You Can Trust - Ensuring Success with Data Integrity Webinar
AI You Can Trust - Ensuring Success with Data Integrity WebinarAI You Can Trust - Ensuring Success with Data Integrity Webinar
AI You Can Trust - Ensuring Success with Data Integrity WebinarPrecisely
 
Igniting Next Level Productivity with AI-Infused Data Integration Workflows
Igniting Next Level Productivity with AI-Infused Data Integration WorkflowsIgniting Next Level Productivity with AI-Infused Data Integration Workflows
Igniting Next Level Productivity with AI-Infused Data Integration WorkflowsSafe Software
 
Crea il tuo assistente AI con lo Stregatto (open source python framework)
Crea il tuo assistente AI con lo Stregatto (open source python framework)Crea il tuo assistente AI con lo Stregatto (open source python framework)
Crea il tuo assistente AI con lo Stregatto (open source python framework)Commit University
 
NIST Cybersecurity Framework (CSF) 2.0 Workshop
NIST Cybersecurity Framework (CSF) 2.0 WorkshopNIST Cybersecurity Framework (CSF) 2.0 Workshop
NIST Cybersecurity Framework (CSF) 2.0 WorkshopBachir Benyammi
 
COMPUTER 10 Lesson 8 - Building a Website
COMPUTER 10 Lesson 8 - Building a WebsiteCOMPUTER 10 Lesson 8 - Building a Website
COMPUTER 10 Lesson 8 - Building a Websitedgelyza
 
Videogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfVideogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfinfogdgmi
 
Building Your Own AI Instance (TBLC AI )
Building Your Own AI Instance (TBLC AI )Building Your Own AI Instance (TBLC AI )
Building Your Own AI Instance (TBLC AI )Brian Pichman
 
Comparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioComparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioChristian Posta
 
UiPath Solutions Management Preview - Northern CA Chapter - March 22.pdf
UiPath Solutions Management Preview - Northern CA Chapter - March 22.pdfUiPath Solutions Management Preview - Northern CA Chapter - March 22.pdf
UiPath Solutions Management Preview - Northern CA Chapter - March 22.pdfDianaGray10
 
Empowering Africa's Next Generation: The AI Leadership Blueprint
Empowering Africa's Next Generation: The AI Leadership BlueprintEmpowering Africa's Next Generation: The AI Leadership Blueprint
Empowering Africa's Next Generation: The AI Leadership BlueprintMahmoud Rabie
 
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfJamie (Taka) Wang
 
UWB Technology for Enhanced Indoor and Outdoor Positioning in Physiological M...
UWB Technology for Enhanced Indoor and Outdoor Positioning in Physiological M...UWB Technology for Enhanced Indoor and Outdoor Positioning in Physiological M...
UWB Technology for Enhanced Indoor and Outdoor Positioning in Physiological M...UbiTrack UK
 
OpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureOpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureEric D. Schabell
 
Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024SkyPlanner
 
UiPath Community: AI for UiPath Automation Developers
UiPath Community: AI for UiPath Automation DevelopersUiPath Community: AI for UiPath Automation Developers
UiPath Community: AI for UiPath Automation DevelopersUiPathCommunity
 
KubeConEU24-Monitoring Kubernetes and Cloud Spend with OpenCost
KubeConEU24-Monitoring Kubernetes and Cloud Spend with OpenCostKubeConEU24-Monitoring Kubernetes and Cloud Spend with OpenCost
KubeConEU24-Monitoring Kubernetes and Cloud Spend with OpenCostMatt Ray
 

Recently uploaded (20)

COMPUTER 10: Lesson 7 - File Storage and Online Collaboration
COMPUTER 10: Lesson 7 - File Storage and Online CollaborationCOMPUTER 10: Lesson 7 - File Storage and Online Collaboration
COMPUTER 10: Lesson 7 - File Storage and Online Collaboration
 
Machine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfMachine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdf
 
9 Steps For Building Winning Founding Team
9 Steps For Building Winning Founding Team9 Steps For Building Winning Founding Team
9 Steps For Building Winning Founding Team
 
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDEADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
 
AI You Can Trust - Ensuring Success with Data Integrity Webinar
AI You Can Trust - Ensuring Success with Data Integrity WebinarAI You Can Trust - Ensuring Success with Data Integrity Webinar
AI You Can Trust - Ensuring Success with Data Integrity Webinar
 
Igniting Next Level Productivity with AI-Infused Data Integration Workflows
Igniting Next Level Productivity with AI-Infused Data Integration WorkflowsIgniting Next Level Productivity with AI-Infused Data Integration Workflows
Igniting Next Level Productivity with AI-Infused Data Integration Workflows
 
Crea il tuo assistente AI con lo Stregatto (open source python framework)
Crea il tuo assistente AI con lo Stregatto (open source python framework)Crea il tuo assistente AI con lo Stregatto (open source python framework)
Crea il tuo assistente AI con lo Stregatto (open source python framework)
 
NIST Cybersecurity Framework (CSF) 2.0 Workshop
NIST Cybersecurity Framework (CSF) 2.0 WorkshopNIST Cybersecurity Framework (CSF) 2.0 Workshop
NIST Cybersecurity Framework (CSF) 2.0 Workshop
 
COMPUTER 10 Lesson 8 - Building a Website
COMPUTER 10 Lesson 8 - Building a WebsiteCOMPUTER 10 Lesson 8 - Building a Website
COMPUTER 10 Lesson 8 - Building a Website
 
Videogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfVideogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdf
 
Building Your Own AI Instance (TBLC AI )
Building Your Own AI Instance (TBLC AI )Building Your Own AI Instance (TBLC AI )
Building Your Own AI Instance (TBLC AI )
 
Comparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioComparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and Istio
 
UiPath Solutions Management Preview - Northern CA Chapter - March 22.pdf
UiPath Solutions Management Preview - Northern CA Chapter - March 22.pdfUiPath Solutions Management Preview - Northern CA Chapter - March 22.pdf
UiPath Solutions Management Preview - Northern CA Chapter - March 22.pdf
 
Empowering Africa's Next Generation: The AI Leadership Blueprint
Empowering Africa's Next Generation: The AI Leadership BlueprintEmpowering Africa's Next Generation: The AI Leadership Blueprint
Empowering Africa's Next Generation: The AI Leadership Blueprint
 
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
 
UWB Technology for Enhanced Indoor and Outdoor Positioning in Physiological M...
UWB Technology for Enhanced Indoor and Outdoor Positioning in Physiological M...UWB Technology for Enhanced Indoor and Outdoor Positioning in Physiological M...
UWB Technology for Enhanced Indoor and Outdoor Positioning in Physiological M...
 
OpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureOpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability Adventure
 
Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024
 
UiPath Community: AI for UiPath Automation Developers
UiPath Community: AI for UiPath Automation DevelopersUiPath Community: AI for UiPath Automation Developers
UiPath Community: AI for UiPath Automation Developers
 
KubeConEU24-Monitoring Kubernetes and Cloud Spend with OpenCost
KubeConEU24-Monitoring Kubernetes and Cloud Spend with OpenCostKubeConEU24-Monitoring Kubernetes and Cloud Spend with OpenCost
KubeConEU24-Monitoring Kubernetes and Cloud Spend with OpenCost
 

SQL Server Performance Analysis

  • 1. Ing. Eduardo Castro, PhD ecastro@grupoasesor.net http://ComunidadWindows.org
  • 2. Session Objectives: Get familiar with database performance tuning tools, especially what SQL Server 2008 offers Understand methodology of database performance bottleneck analysis Learn features/improvements of SQL Server 2008 that help improve performance
  • 3. Target the most challenging and innovative SQL Server applications 10+ TB DW, 3k/tran/s OLTP, Large 500GB+ Cubes, Competitive migrations, Complex deployments, Server Consolidation (1000+) Invest in large scale, referenceable SQL Server projects across the world Provide SQLCAT technical & project experience Conduct architecture and design reviews covering performance, operation, scalability and availability Offer use of HW lab in Redmond with direct access to SQL Server development team
  • 4. Performance Tuning Tools I/O Bottleneck CPU Bottleneck Memory Bottleneck
  • 5. System Performance Monitor (Perfmon) SQL Profiler Trace Dynamic Management Views/Functions DBCC commands SQLDiag (Shipped with SQL 2005/2008) SQLIO, SQLIOSim KernRate Debugging tools (Windbg, DebugDiag,…) More…
  • 6. Data Collector & Management Data Warehouse (MDW) Extended Events Activity Monitor New DMVs/DMFs
  • 7. MDW Host MDW Client Workstation Target Server Snapshot Data dcexec.exe Management Studio SSIS Pkgs Cache Collection Set MDW Metadata Files Reports Job Metadata SQL Agent msdb
  • 9. Highly scalable with low overhead Future of SQL tracing
  • 10. Built-in system_health session select event_session_id, name, startup_state from sys.server_event_sessions Session definition example create event session session_error on server ADD EVENT sqlserver.error_reported (action (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name) where error = 50001) ADD target package0.ring_buffer with (max_dispatch_latency = 1 seconds) go alter event session session_error on server state = start; go
  • 12. Memory allocation tracking sys.dm_os_memory_brokers sys.dm_os_memory_nodes Reqource Governor sys.dm_resource_governor_configuration sys.dm_resource_governor_resource_pools sys.dm_resource_governor_workload_groups Query performance (hash value for query and plan) sys.dm_exec_query_stats Sys.dm_exec_requests sys.dm_exec_procedure_stats sys.dm_exec_trigger_stats More…
  • 13. Bottleneck – A point or component of a process where throughput is the slowest. Understanding where time is spent Elapsed Time = Running + Waiting Key is the waiting - what type of wait(s)?
  • 14. Monitor and identify I/O bottleneck Resource Monitor & Performance Monitor MDW DMV & Extended Events What is driving high I/O load? Inefficient query plan Too much data to read/write Slow I/O subsystem
  • 15. File level I/O throughput and disk response time
  • 16. Runtime Performance monitoring System Reports What counters to look at?
  • 17. Physical Disk Object: Avg. Disk Queue Length Avg. Disk Sec/Read Avg. Disk Sec/Write %Disk Time Disk Transfers/Sec Avg. Disk Bytes/Read Avg. Disk Bytes/Write Disk Read Bytes/Sec Disk Write Bytes/Sec SQL Server Buffer Manager Object: (memory pressure could cause I/O pressure) Buffer Cache hit ratio Page Life Expectancy Checkpoint pages/sec Lazywrites/sec
  • 18. Page I/O waits in Buffer pool WriteLog and LogBuffer waits
  • 19. High I/O Latch sys.dm_os_wait_stats Wait Time sys.dm_exec_requests pending/stalled sys.dm_io_pending_io_requests I/O sys.dm_io_virtual_file_stats ERRORLOG This is 15 second delay in IO warnings of I/O completion way too delays slow
  • 20. sqlos package sqlserver package • async_io_requested • physical_page_read • async_io_completed • physical_page_write • wait_info • file_read • wait_info_external • file_write • long_io_detected 2009-04-13 18:30:10.170 spid5s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:tempdbtemplog.ldf] in database [tempdb] (2). The OS file handle is 0x0000000000000670. The offset of the latest long I/O is: 0x000007eecac000 king longer than 15 seconds to complete on file [e:myslowdb.mdf] in database [myslowdb] (1). The OS file handle is 0x00000438. The offset of the latest long IO is: 0x000000513ce000
  • 21. Look if there is memory pressure Further narrow down to what queries are generating the I/O workload. Tune these queries. Explore data compression to reduce I/O load Upgrade storage hardware
  • 22. Compress tables/indexes to reduce database size Compression ratio depends on schema & data distribution (avg 50-60%) Reduce query runtime I/O (increase cpu) Identify compression candidates sp_estimate_data_compression_savings sys.dm_db_index_operational_stats
  • 24. Sustained %Processor Time > 80% Performance Monitor/Task Manager MDW Server Activity Report DMV & Extended Events What is driving high CPU? Recompilations Cursors Inefficient query plans More…
  • 25. Resource • Monitor it Live Monitor/Task Manager Performance Monitor • Watch Live or Log Counters counters you like System Data Collector • Formatted Perfmon Reports counter based report Windows Performance • Dig deeper using ETW Toolkit (XPerf)
  • 26. Processor object: %Privileged Time %Processor Time New in SQL Server 2008 for Resource %User Time Governor feature Process object (SQL Server): %Privileged Time %Processor Time %User Time SQL Server:Workload Group Stats CPU usage % Max request cpu time SQL Server:Resource Pool Stats CPU usage % CPU usage target %
  • 27. SQL Server:SQL Statistics Batch requests/sec SQL Compilations/sec SQL Re-Compilations/sec SQL Server:Cursor Manager by Type SQL Server:Cursor Manager Total Concurrent processing of multiple cursors could be cpu intensive
  • 28. Total CPU waits CPU cycles that did the real work Wait in runnable queue Thread voluntarily yields
  • 29. sys.dm_exec_query_stats Sys.dm_exec_sessions sys.dm_exec_requests sys.dm_exec_procedure_stats Sys.sysprocesses sys.dm_exec_trigger_stats cpu_time cpu_time total_worker_time cpu total_scheduled_tim e total_elapsed_time total_elapsed_time Backward compatibility view Sql_handle/query_h total_elapsed_time query_hash ash
  • 30. sqlos package sqlserver package •scheduler_monitor_non_yielding_ring_buffer_recorded •sql_statement_completed •spinlock_backoff •sp_statement_completed •Wait_info •cursor_manager_cursor_end •wait_info_external •checkpoint_end Extended Event can track individual statement level events like Profiler does with minimal impact - ADD EVENT sqlserver.sql_statement_completed (action (sqlserver.sql_text) WHERE duration > 0), ADD EVENT sqlserver.sp_statement_completed (action (sqlserver.sql_text) WHERE duration > 0) select top 10 CONVERT(xml, event_data).value('(/event/data/value)[4]','int') as 'cpu'
  • 31. High cpu doesn’t necessarily mean it’s a problem. Drill down to specific workload and queries that drive cpu usage. Tune them. Use Resource Governor to protect high priority workloads Upgrade hardware
  • 32. Resource Governor allocate cpu/memory to prioritized workloads based on connection string parameters Mixed workloads: OLTP, Reporting, Maintenance Soft Numa Node configuration (soft numa node and cpu affinity) Network Configuration (soft numa node and tcp port)
  • 34. Monitor and identify memory pressure Performance Monitor/Task Manager MDW Server Activity Report DMV & Extended Events What is eating up my memory? External or internal memory pressure Inefficient query plan (hashing, sorting,…)
  • 35. AWE 32-bit uses AWE to cache data/index pages 64-bit uses AWE to implement “Locked pages in memory” Environment Virtual Address Max physical Space memory 32-bit on 32-bit OS 2 GB 64 GB 32-bit on 64-bit OS (Wow) 4 GB 64 GB 64-bit on 64-bit OS 8 TB 2 TB
  • 36. Perfmon is golden tool to identify memory pressure Memory object: Available K/M bytes Commit Limit Paging File %Usage (Peak) Page reads/sec Process object: Private bytes Virtual bytes Working set
  • 37. SQL Server Memory Manager object: Connection Memory Lock Memory Somebody is waiting on Optimizer Memory memory Memory Grants Pending … SQL Server Buffer Manager object: Buffer cache hit ratio Checkpoint pages/sec Free list stalls/sec Free pages Lazy writes/sec Page life expectancy How long will the page Stolen pages live? Target pages Total pages SQL Server Plan Cache object SQL Server Resource Pool Stats object
  • 38. Query execution waits on memory for sorting, hashing,…
  • 39. DBCC MEMORYSTATUS sys.dm_os_sys_memory system_memory_state_desc a good indicator of available physical memory sys.dm_os_process_memory process_physical_memory_low = 1 indicates low physical memory. memory_utilization_percentage value below 100% indicates external memory pressure. sys.dm_os_memory_clerks
  • 40. sys.dm_os_ring_buffers RING_BUFFER_SCHEDULER_MONITOR RING_BUFFER_RESOURCE_MONITOR RING_BUFFER_OOM RING_BUFFER_MEMORY_BROKER RING_BUFFER_BUFFER_POOL
  • 41. sqlos package sqlserver package • page_heap_memory_allocated • sort_memory_grant_adjustment • page_heap_memory_freed • plan_cache_cache_hit • memory_node_oom_ring_buffer_recorded • plan_cache_cache_attempt • cursor_manager_cursor_memory_usage 2009-04-14 09:27:15.43 spid62 Failed allocate pages: FAIL_PAGE_ALLOCATION 1
  • 42. Check memory configuration – sp_configure External memory pressure – Identify what other processes are competing Internal memory pressure – Identify which SQL component is main consumer Workload/query tuning (sorting, hash join…) Resource Governor Hardware upgrade
  • 44. Focus on methodology – no guess Correlate data points from multiple tools Performance tuning is iterative process – bottleneck could move Resource contention impacts each other Lack of memory -> high cpu & I/O Slow I/O -> tempdb contention …
  • 45. Ing. Eduardo Castro, PhD ecastro@grupoasesor.net
  • 46. “Troubleshooting Performance Problems in SQL Server 2008” http://msdn.microsoft.com/en- us/library/dd672789.aspx