SQL Profiler

SQL Profiler captures SQL Server events from a server. The events are saved in a trace file that can be used to analyze and diagnose problem.

The different purposes of using SQL Profiler are:
*It is used to find the cause of the problem by stepping through problem queries.
*It is very useful to analyze the cause of slow running queries.
*It can be used to tune workload of the SQL server.
*It also stores security-related actions that can be reviewed by a security administrator.
*SQL Profiler also supports auditing the actions performed on instances of SQL Server.
*Saved traces can be used by the SQL Server Index Tuning.
*It is also possible to sort and filter the information captured.

*How can you use the SQL Profiler to ensure database security?
*SQL Profiler can audit events like Security, User Activity and DBA Activity.
*Security events include granting, revoking or denying permissions of a user or role.
*End user activity like logins and logouts can be audited.Enabling of roles for an application can also be done.

When do you use SQL Profiler?
*When some queries are performing slowly
*To trace and monitor events of the SQL server
*When indexes need to be fine tuned
*When security is a concern, it can be used to audit and review security activities
*It can be used when troubleshooting to find root cause of an issue

What are the tasks performed by SQL Profiler?
*The SQL Profiler can save traces to a file or a table. These saved traces can be used by the SQL Server Index Tuning.
*To be able to execute a set of actions in the future, the traces can be saved to an SQL script.
*The SQL Profiler can directly replay the traces.
*It is also possible to sort and filter the information captured. This helps in finding performance bottlenecks or deadlocks in the application.

How can you use the SQL Profiler to ensure database security?
SQL Profiler can audit events like Security, User Activity and DBA Activity.
Security events include granting, revoking or denying permissions of a user or role.
End user activity like logins and logouts can be audited.Enabling of roles for an application can also be done.

How can you reduce the amount of data collected by a trace?
There are various options that you need to fill up before creating a new trace. It is always a better practice to use the default options unless you dont want to get into the details of the data provided by the trace.

There are a lot of events that you can audit. If you need to focus on a particular problem, then you can select the events needed and go through to determine the problem.