Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server Monitoring Tools – Server Profiler

By Exforsys | on December 12, 2005 |
SQL Server 2005

SQL Server Monitoring Tools – Server Profiler

In this tutorial you will learn about SQL Server Profiler, To start SQL Server Profiler in SQL Server Management Studio, To start SQL Server Profiler in Database Engine Tuning Advisor, Security, Space Requirements, To change the TEMP environment variable in Windows operating systems, Using SQL Server Profiler and Analysis Services, Replay for Queries, Discovers and commands

SQL Server Profiler

SQL Server Profiler is a graphical tool that helps in the monitoring of an instance of SQL Server Database Engine or Analysis Services. The data about each event can be captured to a file or table for analysis at a later date. SQL Server 2005 incorporates certain new features into the SQL Server Profiler. The significant enhancements are as under:

1. Rollover trace files. SQL Server Profiler can replay one or more collected rollover trace files continuously and in order.

2. New extensibility standard. SQL Server Profiler uses an XML-based definition that allows SQL Server Profiler to more easily capture events from other types of servers and programming interfaces.

3. Profiling of Microsoft SQL Server 2005 Analysis Services (SSAS). SQL Server Profiler now supports capturing and displaying events raised by SSAS.

4. Profiling of Microsoft SQL Server 2005 Integration Services (SSIS). SQL Server Profiler can now display events raised by SSIS.

5. Saving of traced Showplan as XML. Showplan results can be saved in an XML format, which can be later loaded for graphical Showplan display in Query Editor without the need to have an underlying database. SQL Server Profiler will also display a graphical representation of Showplan XML events at that the time they are captured by SQL Server Profiler.

6. Save trace results as XML. Trace results can be saved in an XML format in addition to the standard save formats of ANSI, UNICODE, and OEM. Results saved in this fashion can be edited and used as input for the Replay capability of SQL Server Profiler.

7. Aggregate view. Users can choose an aggregate option and select a key for aggregation. This will enable users to see a view that shows the column on which the aggregation was performed, along with a count for the number of rows that make up the aggregate value.

8. Correlation of Trace Events to Performance Monitor Counters. SQL Server Profiler can correlate Performance Monitor Counters with SQL Server or SSAS events. Administrators can select from a predefined set of Performance Monitor Counters and save them at specified time intervals while also collecting a SQL Server or SSAS trace.

Before examining the process of using SQL Server Profiler, let us look at the terminology associated with the tool.

1. An Event is an action that is generated within an instance of a SQL Server Database Engine. These could be login failures, connection failures or disconnections. It would include events such as T-SQL statements, remote procedure call batch status, the start or end of a stored procedure, the start or end of statements within a stored procedure and so on.. These are displayed in the trace in a single row intersected by data columns with descriptive details.

2. An Event Class is an event that can be traced and contains all of the data that can be reported by the event. SQL: Batch completed for instance is an event class, just as .Audit Login, .Audit Logout etc are event classes.

3. An Event Category defines the methodology used for grouping events within the SQL Server Profiler. For instance lock events will be categorized under Lock event category.

4. A Data Column is an attribute of an event class that is captured in the trace. The event class determines the type of data that can be collected and not all data columns are applicable to all event classes.

5. A Template is the default configuration for a trace. It includes the event classes that are required for monitoring with the SQL Server Profiler.

6. A Trace captures data based on selected event classes, data columns and filters. For instance a trace monitor can be created to capture Exception event class with the Error, State and Severity data columns.

7. Data can be Filtered by specifying criteria of selection during the execution of an event. This feature is used to reduce the size of the Trace.

The SQL Server Profiler tool captures the events and stores them in a trace file for analysis. The trace file enables the replay of the events for the diagnosis of the problems. The SQL Server Profiler is used for stepping through the problem to find the cause or finding and diagnosing slow running queries, or capturing T-SQL statements that lead to a problem or Monitoring the performance of the SQL Server for tuning workloads.

Auditing actions are also supported by the Profiler. Audit traces help in maintaining the security of the server.

The rich interface of the Profiler helps the administrator create and manage traces and analyze and replay trace outputs. In the production environment the DBA will have to create a focused and well organized series of traces. He may want to do the following actions:

1. Monitor the performance of an instance of the SQL Server Database Engine, Analysis Server or Integration Services.
2. Debug Transact SQL statements and stored procedures
3. Identify slowly executing procedures and queries.
4. Perform stress testing and quality assurance by replaying traces
5. Replay traces of one or more users
6. Perform query analysis.
7. Test T-SQL statements and stored procedures in the development phase
8. Troubleshoot problems
9. Audit and review activity
10. Provide standardized hierarchical structure to trace results by saving them to the XML file.
11. Aggregate trace results
12. Correlate performance counters
13. Configure trace problems.
14. Allow non administrators create traces.

The SQL Server Profiler can be accessed in several ways. It can be invoked from the Start Menu, from the tools menu in the SQL Server Management Studio and from the Tools menu in the Database Engine Tuning Advisor.

To start SQL Server Profiler from the Start menu
On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Performance Tools, and then click SQL Server Profiler.

To start SQL Server Profiler in SQL Server Management Studio

On the SQL Server Management Studio Tools menu, click SQL Server Profiler.


To start SQL Server Profiler in Database Engine Tuning Advisor

On the Database Engine Tuning Advisor Tools menu, click SQL Server Profiler.



Security

When the Windows Authentication mode is used, the user account accessing SQL Profiler must have permission to connect to the instance of SQL Server. If they need to perform tracing they must have the ALTER TRACE permission.

Space Requirements

The Temp directory is the repository for the Trace files created by the SQL Server Profiler. At least 10 megabytes of space is required by the Profiler. When the available space drops below the required minimum the profiler activity stops. Therefore it is advisable to ensure that the space does not fall below the required minimum. Often DBA’s will move the Temp directory by changing the value of the TEMP environment variable, to another drive with large space to avoid this problem.

To change the TEMP environment variable in Windows operating systems

1. On the Start menu, choose Control Panel, and then click System.


2. In the System Properties dialog box, click the Advanced tab, and then click Environment Variables.

3. Scroll down the list of System Variables, select the row that corresponds to the TEMP variable, and click Edit.


4. In the Edit System Variable dialog box, enter the path and name of the drive and directory where you want the temp directory to be located.

5. Click OK to save the change.

Using SQL Server Profiler and Analysis Services

The use of the SQL Server Profiler requires that the user must have an administrative role or have a server role. If the user has an Analysis Services Server role he can start the Profiler from Microsoft SQL Server program on the Start Menu.

Trace definitions are stored with the Analysis Services database using the CREATE statement. Multiple traces can run simultaneously and multiple connections can receive events from the same trace. The trace can continue even when Analysis services is stopped and restarted.

Replay for Queries, Discovers and commands

Queries, discovers and commands submitted by users to SQL Server 2005 Analysis Services can be replayed from the SQL Server Profiler trace. A single event class can be used to record the events required and the trace file will contain sufficient information ot support replaying server transactions in a distributed environment.

Queries can be replayed if SQL Profiler captures the Audit Login event class, the Query Begin event class, the Query end event class with all the required data columns. Discovers can be replayed if the Audit login event class, Discover Begin event class and Discover Eng event class with all the data columns are captured. The replay for commands will be possible if the Command Begin and Command End event classes are captured.

« « SQL Server 2005 – Testing Troubleshooting
SQL Server 2005 – Using DDL Triggers » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • Using XML in SQL Server 2005

    October 26, 2005 - 0 Comment
  • SQL Server 2005 – Using the Database Tuning Advisor

    December 24, 2005 - 0 Comment
  • SQL server 2005 Editions

    November 23, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 1

    December 15, 2005 - 0 Comment
  • XML Data Types in SQL Server 2005

    October 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment
  • SQL Server 2005 Installation

    November 23, 2005 - 0 Comment
  • Getting started with SQL Server 2005

    August 9, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 2

    December 15, 2005 - 0 Comment
  • Native HTTP Support in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT