DBA > Articles

SQL SERVER – Collect and Analyze SQL Server Data Efficiently

By: Pinal Dave
To read more DBA articles, visit http://dba.fyicenter.com/article/

dbForge Event Profiler is one of the most useful SQL Server “build-in” tools. The Profiler records data associated with various SQL Server events for further analysis. This data is stored in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose SQL Server relates problems. The tool allows you to view communications between a client and SQL Server, and gives you an insight into its internal performance. To take full advantage of its potential, download dbForge Event Profiler for SQL Server for free now.

The tool offers a large spectrum of features that can assist you in:

analyzing trace results by grouping or aggregating them;
auditing user activity;
creating your own custom traces and save them for future use;
debugging T-SQL code and stored procedures;
executing quality assurance check;
identifying performance-related problems with front-end applications, queries, T-SQL, transactions, and so forth;

performing stress testing;
performing query analysis of execution plans;
viewing SQL Server performance when interacting with a client.

Essentially, the Event Profiler is designed to quickly and efficiently track down and fix many SQL Server related problems, such as poorly-performing queries, locking and blocking, excessive table/index scanning, and a lot more. For example, you can monitor the execution of a stored procedure to see whether it hampers SQL Server performance.

Using the Profiler, you can monitor the events that you are interested in. For example, you may want to capture events from a specific user or a given database. Smart filters allow you to collect only the events that you want, filtering out those of no interest. This reduces the amount of data that is stored in your trace.

dbForge Event Profiler provides a rich graphical user interface that can be used to create, analyze, and replay trace results. As the trace data is being collected, you can stop or pause the trace at a certain point and store the trace results to a physical file on a local hard disc. The saved SQL Server Profiler document has the “.*ssp” extension. This file may then be viewed to analyze data captured, share it with others, or compare the trace results to traces performed later.

Powerful Tool in Action
Below is an example of how you create a new trace using dbForge Event Profiler.
To create a new trace, you follow these steps:
On the Start page, click Profile Server Events. The Profile Server Events wizard appears.
Specify the connection.
Optionally, select a profiling template, modify the trace file settings and the data storage settings. Click Next.

On the Events to Capture page, select the events you want to capture. Click Next.
Optionally, on the Actions page, select the actions you want to capture in the current events session. Click Next.
Optionally, on the Event Filters page, specify filter options to limit the tracing data.
Click Execute. The Trace will start and the Server Event Profiler Document opens.

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/