Les comparto la presentación utilizada en la charla sobre optimización de desempeño de SQL Server.
Saludos,
Eduardo Castro Martinez
http://ecastrom.blogspot.com
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
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
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
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
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
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
…