Scribd
Upload
47 views10 pages

Best Practices for SQL Profiler Usage

The document provides best practices for using SQL Profiler and server-side traces to troubleshoot performance issues, including how to create, start, and query trace files. It also discusses using performance monitor counters and dynamic management views to identify bottlenecks like CPU, memory, I/O, and wait stats. Resources are provided for diagnosing issues with indexes, top SQL Server performance problems, and normalizing trace data.

Uploaded by

Moises Fabro
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
47 views10 pages

Best Practices for SQL Profiler Usage

The document provides best practices for using SQL Profiler and server-side traces to troubleshoot performance issues, including how to create, start, and query trace files. It also discusses using performance monitor counters and dynamic management views to identify bottlenecks like CPU, memory, I/O, and wait stats. Resources are provided for diagnosing issues with indexes, top SQL Server performance problems, and normalizing trace data.

Uploaded by

Moises Fabro
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

Tara Kizer

Database Administrator at Qualcomm, Inc.


SQL Server MVP
 Best Practices
 Never run SQL Profiler directly on the database server
 Never trace directly to a table, instead trace to a file
 Filter the results, such as Duration > 500
 Include only the events that you need, such as RPC:Completed
 Instead of SQL Profiler, run a server-side trace (least expensive way to trace)
 Profiler shows Duration in milliseconds; trace file/table is in microseconds
 Quick trace demo
 Query the trace file via ::fn_trace_gettable function
 Script Trace Definition
 Server-side trace
 Schedule SQL Agent job
 sp_trace_create – creates the definition
 sp_trace_setevent – adds the events
 sp_trace_setfilter – sets the filters
 sp_trace_setstatus – start it (1), stop it (0), delete it (2)
 sys.trace_events – list of events (id,name)
 fn_trace_getinfo – shows what server-side traces are running
on the server
 fn_trace_gettable – query the trace file
 [Link]
 Similar to Read80Trace but with a GUI – normalizes the data
(removes data values so you can group queries together)
 Demo it
 Tables  Jobs
 Numbers (1-10)  Trace Cleanup
 TraceData  Trace Purge
 TraceQueue  Trace Save
 Stored Procedures  Trace Start
 TraceCleanup  Trace Stop
 TracePurge  Trace Stop All
 TraceSave  Trace Stop/Start on Agent
 TraceStart Restart
 TraceStop
 TraceStopAll
 Baseline your system
 Just like SQL Profiler, do not run on the database server
 CPU
 Processor\% Processor Time - _Total
 System\Processor Queue Length
 IO
 Logical Disk\Avg. Disk sec/Read – Data/Log files
 Logical Disk\Avg. Disk sec/Write – Data/Log files
 Memory
 Memory\Available Mbytes
 Memory\Pages/sec
 SQL Server
 MSSQL$InstanceName:Buffer Manager\Buffer cache hit ratio
 MSSQL$InstanceName:Buffer Manager\Page life expectancy
 MSSQL$InstanceName:Memory Manager\Target Server Memory(KB)
 MSSQL$InstanceName:Memory Manager\Total Server Memory(KB)
 MSSQL$InstanceName:SQL Statistics\Batch Requests/sec
 Indexes: [Link]
 Find Missing Indexes
 Find Indexes Not In Use
 Where is the bottleneck?
 sys.dm_os_wait_stats:
[Link]
 sys.dm_os_performance_counters:
[Link]
 sys.dm_exec_requests, sys.dm_exec_sessions:
[Link]
 Top SQL Server Performance Issues for OLTP Applications:
[Link]
Contact - [Link]
SQL Server Blog - [Link]
MVP Profile - [Link]

Watch for a new blog post early next week about this presentation.

You might also like