Introduction to SQL Server Profiler

Arya MirΑποθήκευση

6 Οκτ 2011 (πριν από 5 χρόνια και 10 μήνες)

1.244 εμφανίσεις

Jack D. Corbett

Introduction to SQL Server
Profiler

Jack D. Corbett

About the Speaker



SQL Server Developer
\
DBA
Consultant


Worked with SQL Server since 1999


Regular SQLSaturday speaker


One of the organizers of PASS SQLRally
2011



What is Profiler?



Client application on top of SQL Trace


SQL Trace is:


Method of monitoring events in SQL Server


Based on a set a of stored procedures for defining
events to monitor, columns to return, and filters.

Why use SQL Server Profiler?


It’s FREE with SQL Server!


It’s easy to use.


To analyze and determine application
behaviors


To troubleshoot applications


To performance tune


To audit your SQL Server

How Does Profiler Work?


Event Providers produce events


Trace Controller handles traces and marks
events to be traced


Marked events are filtered and queued in
buffers.


There are 2 trace providers


Rowset Provider


used by Profiler


File Provider


Trace Architecture (from BOL)


Security Considerations


Alter Trace permissions required (new to
SQL Server 2005)


Hides passwords in Create/Alter Login


Encrypted code (SP’s, UDF’s, etc…)
hidden.


Accessing Profiler


Start

> Programs

> Microsoft SQL Server
2008

> Performance Tools


In SSMS under the Tools menu.

Demo


Opening Profiler


Connecting to a server


Selecting a Template


Saving a trace while executing


Add/remove events


Add/remove columns


Add/remove filters

Demo


Executing a trace


Saving a custom template


Saving a completed trace


Re
-
opening a saved trace


Saving a trace definition

Integrating with Perfmon Data


Need a PerfMon log


Need a saved trace with start and end time
columns

Demo

Integrating trace with
PerfMon Data

Performance Considerations


Using Profiler is more impactful than a
server
-
side trace


More events and columns = more impact


Multiple small traces have less impact than
one large trace


Server
-
side tracing to a file has minimal
impact on performance


see


http://tinyurl.com/LinchiShea

Resources


Microsoft SQL Server 2005 Unleashed



Inside Microsoft® SQL Server(TM) 2005: Query
Tuning and Optimization


SQL Server 2005 Books On Line


http://tinyurl.com/SimpleTalkDeadlocks


http://tinyurl.com/DbJProfilerPart1


http://tinyurl.com/DbJProfilerPart2


http://
tinyurl.com/PASSProfiler


Contact Information


Blog:
www.wisemanorwiseguy.com


Twitter:
http://twitter.com/unclebiguns


LinkedIn:
http://www.linkedin.com/in/jackcorbett


Email:
corbett.jack@gmail.com



Thanks for Coming!


Thanks for coming and I hope you learned
something useful.


Let me know about any comments or
suggestions you may have
.