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 2005 – Tuning a Database

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

SQL Server 2005 – Tuning a Database

In this tutorial you will learn about Database Tuning Advisor overview, Database Engine Tuning Advisor Features, Improved Workload Parsing, Enhanced Scalability, Integrated Tuning, Multiple Database Tuning, Offload of Tuning Overhead to Test Server, Drop-Only Tuningm, XML Support, User-specified Configuration and "What-if" Analysis Support, Analysis Reports, Tuning Sessions, Database Engine Tuning Advisor Tuning Capabilities and Tuning SQL Server 2000 and 2005 databases with DTA.

Database Tuning Advisor overview

In SQL Server 2005 the Database Tuning Advisor has been introduced in place of the Index tuning wizards of the earlier SQL Server versions. It contains a graphical user interface and a command line utility called the dta.exe. The Database Tuning Advisor provides better quality recommendations, enhanced the type of events evaluated and the types of physical design structures considered.

Database Engine Tuning Advisor Features

Improved Workload Parsing

The new Database Tuning Advisor handles batches that reference transient tables. It does not terminate tuning due to an inability to parse an event. Instead it logs the event into the tuning log and proceeds with the task on hand. It parses and tunes queries that reference user defined functions and handles all the USE statement in a trace when multiple databases are being tuned. Events are correctly tuned using the LoginName column in the workload and also tunes statements in triggers.

Enhanced Scalability

The Database Tuning Advisor uses workload compression to reduce the amount of time spent in tuning and uses an algorithm to avoid redundant statistics creation and reduces the number of I/Os incurred during tuning.

Integrated Tuning

Database Tuning Advisor has the capability of harnessing the integrated configuration of multiple design structures to provide high quality recommendations. This reduces the cost of the execution for any given query.

Multiple Database Tuning

The Database Tuning Advisor can tune multiple databases simultaneously. The set of databases to be tuned can be specified and the DTA makes recommendations for all the databases.

Offload of Tuning Overhead to Test Server

When large workloads are large significant overheads on the server being tuned are created. The DTA then needs to make several calls to the query optimizer during a tuning process. It is advisable to use a test server in addition to the production server to eliminate this problem. The production server environment is duplicated in the test server to the extent that metadata, statistics and hardware parameters are copied from the production server for simulating the required environment. The recommendation then received from the test server can be implemented in the production server.

Drop-Only Tuning

DBA’s need to drop structures which accumulate in databases over a period of time. The drop only tuning option helps him identify existing physical design structures that are derelict. This drop only option can be used with the-fx argument in the dta.exe utility. In the GUI utility the Evaluate utilization of existing PDS only option can be selected from the Tuning options tab. New structures are never proposed by the DTA and the Evaluate utilization of Existing PDS only cannot be used with options that add indexes, indexed views or partitions.

XML Support

DTA operations can be defined in an xml schema input file format defined in the installation directory of Microsoft SQL Server 2005. This feature enables the DBA to have more flexibility.

User-specified Configuration and "What-if" Analysis Support

It is possible to provide hypothetical configuration of physical design structures as a tuning input to the Database Engine Tuning Advisor. These databases can then be implemented as if they were real inputs. In other words, a what if analysis can be done for a given physical design structure even before it is actually designed and implemented. This feature is fully supported by the XML schema that comes with the DTA.

Analysis Reports

Once the tuning process is complete, DTA generates XML reports and recommendations. The reports detail the cost of queries in a workload, or relationships between queries and the indexes they reference. The GUI can be used to view the reports or the xml editor can be used.

Tuning Sessions

Each tuning session can be given a unique identity in the DTA. The tuning session information is saved once the process is complete and it is associated with the identifier. The identifier, then, can be used to reload the tuning session as and when required. These sessions make comparison of reports of different sessions possible using the Session Monitor.

Database Engine Tuning Advisor Tuning Capabilities

The DTA recommends the best mix of indexes for databases by using the query optimizer and analyzing queries in a workload. It can recommend aligned or non aligned partitions for databases references in the workload. It recommends indexed views for databases in the workload and analyzes the effects of the proposed changes including index usage, query distribution among tables and query performance in a workload. It recommends ways to tune the database for small set of problem queries or permits customization of the recommendation by specification of advanced options such as disk space constraints. It provides the DBA with report summaries of the impact of implementing the recommendation of a given workload.

Tuning SQL Server 2000 and 2005 databases with DTA.

The SQL Server 2005 Database Engine Advisor (DTA) can be used to tune SQL Server 2000 and SQL Server 2005 databases.

SQL Server 2000 databases can be tuned using the following features of the SQL Server 2005 Database Tuning Wizard.

1. Time bound tuning which controls the amount of time taken by the Database Engine Tuning Advisor to analyze a workload. The quality of recommendations is directly in proportion to the amount of time taken. The more the time taken the better the quality of the recommendation made.

2. Workloads spread over multiple databases can be tuned. The DTA recommends indexes, indexed views or partitioning of databases in the workload.

3. The DTA helps tune broader classes of events and triggers. Workloads can include User-defined functions, batches that reference temporary tables, statements in triggers etc.

4. A tuning log is maintained by the DTA for all the events that cannot be tuned and the reason why the tuning could not be performed. This log can be viewed during the tuning session.

5. The DTA supports What-if analysis. The configuration of existing and hypothetical physical designs can be specified in an XML input file. The DTA then evaluates the impact of the physical design structures without expensive overheads relating to implementing the configuration before the tuning is performed.

6. The DTA allows the DBA to specify a greater variety of tuning options such as adding nonclustered indexes or keeping existing clustered indexes etc.

7. Finally DTA provides XML support. It accepts xml files as input and generates the recommendations in XML.

SQL Server 2005 databases can be tuned using the following features of the DTA in addition to the ones already mentioned in the context of SQL Server 2000 databases:

1. Partitioning is recommended by the DTA when performance can be improved on large tables.

2. A test server is supported when the production server tuning load has to be reduced. Most of the load is offloaded into the test server by using the production server hardware configuration information, but does not copy the data from the production server to the test server. However the metadata is copied along with the required statistics.

3. The database tuning advisor can be used only by members of the sysadmin fixed server role or db_owners fixed database role and hence security can be maintained.

« « SQL Server 2005 – Defining Indexes
SQL Server 2005 – Using the Database Tuning Advisor » »

Author Description

Avatar

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

Ads

Free Training

RSSSubscribe 416 Followers
Ads
  • Popular
  • Recent
  • SQL Server 2005 – Using DDL Triggers

    December 12, 2005 - 0 Comment
  • 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
  • SQL Server 2005 – Using Event Notifications

    December 13, 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 1

    December 15, 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
© 2021. 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.Accept Reject Read More
Privacy & Cookies Policy
Necessary Always Enabled