• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

Card Range To Study

through

image

Play button

image

Play button

image

Progress

1/15

Click to flip

Use LEFT and RIGHT arrow keys to navigate between flashcards;

Use UP and DOWN arrow keys to flip the card;

H to show hint;

A reads text to speech;

15 Cards in this Set

  • Front
  • Back
How to tune a database using a workload file or table as inputs.
1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis. For more information, see About Workloads and Considerations for Using Database Engine Tuning Advisor.
2. Create a workload
3. Launch Database Engine Tuning Advisor, and log into an instance of Microsoft SQL Server.
4. On the General tab, type a name in Session name to create a new tuning session.
5. Choose either a Workload File or Table and type either the path to the file, or the name of the table in the adjacent text box.
Format - database_name.schema_name.table_name
Database Engine Tuning Advisor assumes that workload files are rollover files.
When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, move it to the server that Database Engine Tuning Advisor is tuning before using it as your workload.
How to tune a database using a workload file or table as inputs. cont..
6. Select the databases and tables against which you wish to run the workload that you selected in step 5. To select the tables, click the Selected Tables arrow.
7. Check Save tuning log to save a copy of the tuning log. Clear the check box if you do not want to save a copy of the tuning log.
You can view the tuning log after analysis by opening the session and selecting the Progress tab.
8. Click the Tuning Options tab and select from the options listed there. For more information, see Available Tuning Options.
9, Click the Start Analysis button in the toolbar.
If you want to stop the tuning session after it has started, choose one of the following options on the Actions menu:
-Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.
-Stop Analysis stops the tuning session without generating any recommendations.
Pausing Database Engine Tuning Advisor
Pausing Database Engine Tuning Advisor is not supported. If you click the Start Analysis toolbar button after clicking either the Stop Analysis or Stop Analysis (With Recommendations) toolbar buttons, Database Engine Tuning Advisor starts a new tuning session.
Database Tuning adviser - tasks
1. Troubleshoot the performance of a specific problem query
2. Tune a large set of queries across one or more databases
3. perform an exploratory what-if analysis of potential physical desig changes.
4. Manage storage space
Database tuning adviser - benefits
1. Recommend the best mix of indexes for databases by using the query optimizer to analyze queries in a workload.
2. Recommend aligned or non-aligned partitions for dbs referenced in a workload.
3. Recommend indexed views for dbs referenced in a workload.
4. Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.
5. Recommend ways to tune the db for a small set of problem queries.
6. Allow you to customize the recommendation by specifying advanced options such as disk space constraints.
7. Provide reports that summarize the effects of implementing the recommendations for a given workload
DTA - types of query workloads
1. OLTP queries only
2. OLAP queries only
3. mixed OLTP and OLAP queries
4. Query-Heavy workloads (more queries than data modifications)
5. Update-heavy workloads (more data modifications than queries)
DTA - Limits
It cannot add or drop unique indexes or indexes that enforce PK or UK constraints
It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.
It cannot analyze a database that is set to single-user mode.
For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.
DTA - Limits cont..
Database Engine Tuning Advisor might not make recommendations under the following circumstances:
The table being tuned contains less than 10 data pages.
The recommended indexes would not offer enough improvement in query performance over the current physical database design.
The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. The user must be a member of the db_owner database role.
Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. If changes are made to the msdb database you may risk losing tuning session data. To eliminate this risk, implement an appropriate backup strategy for the msdb database.
Reduce the Production Server Tuning Load
Use the query optimizer to analyze workload and make recommendations. This adds to the server load and can hurt server performance. Add test server to reduce impact.
Database Engine Tuning Advisor - Test Server Process
1. Make user that the user who wants to use the test server exists on both servers. Create the user and login on both servers.
2. Tune the workload on the test server
3. Generate tuning recommendation
4. apply the recommendation to the production server
Database Engine Tuning Adviser - Tune the workload on the test server
- must use xml input file w/ dta command-line util. Specify the name of your test server w/ the TestServer sub-element.
DTA imports metadata from Prod DB to test the server shell DB. Metadata includes empty tables, indexes, views, sps, triggers. Now possible for the workload queries to execute on the shell db.
DTA imports stats from the prod server so the query optimizer can work on the test server
DTA imports hardware params specifying the number of processors and available memory from the prod server to generate query plan
DTA - Test Server Setup
user must exist on both
extended sp, xp_msver must be enabled-used to get hardware parameters from Prod, otherwise test servers params will be used or defaults of 1 processor/1024 MBs of memory. Turned on by default
editions of test/prod servers should be the same to get accurate results
DTA - prod/test server behavior
only metadata is copied from prod to test
all session info is strored in msdb on the prod server
DTA - LoginName Data Column
DTA impersonates the user specified in the LoginName Column. If user doesn't have SHOWPLAN permission, DTA will not tune those statements.
DTA - SHOWPLAN permission-to avoid granting SHOWPLAN to every user
1. Tune the trace file or table workload
2. check for statements that were not tuned, due to inadequate permissions
3. create a new workload by deleting the LoginName column from the events that were not traced and then save only the untuned events in a new trace file/table.
4. Resubmit the new workload w/out the LoginName column to the DTA.