Sql server lesson13

 Using the Database Engine Tuning Advisor
 The Database Engine Tuning Advisor (DTA) is designed to evaluate
your queries against the rules in the Query Optimizer to make
suggestions that can improve performance.
 The one thing you need before you can run the DTA is a workload.
 DTA works in conjunction with SQL Trace output.
 Trace is captured that contains the queries that you want DTA to
analyze.
 The trace output is read and evaluated by DTA against a
database.
 The recommendations that DTA can make are:
 Adding indexes.
 Dropping indexes.
 Partitioning tables.
 Storage aligning tables.

 The source of a DTA workload can be :
 A trace file.
 Transact-SQL (T-SQL) script.
 A table that contains T-SQL commands.
 The only events that DTA is concerned about are:
 RPC:Starting
 RPC:Completed
 SQL:Batch Starting
 SQL:Batch Completed

 An analysis is accomplished with four steps:
1. Generate a workload for analysis.
2. Start DTA and connect to a server running SQ Server that
contains a database to analyze the workload against.
3. Select the workload to use.
4. Specify tuning options
 Analysis within DTA is performed in a session.
 Each session must have a name and is saved so that you can review
the results at a later date.
 The most common way of performing an analysis is with a file which either
contains the output of a trace or contains one or more T-SQL commands.

 Creating a DTA analysis session

 DTA is the graphical utility with which you interact
 Analysis within DTA is performed in a session.
 Each session must have a name and is saved so that you can
review the results at a later date
 DTA analyzes the cost of a specified query against each possible
recommendation.
 Query cost is generated by the query optimizer based on
distribution statistics.
 DTA can analyze queries and stored procedures that are stored
in either a file or a table. The most common tuning source for DTA
is a trace output file.

 There are four groups of tuning options:
 Time limitations and online actions
 Existing structures in the database
 Partitioning options
 Whether to retain existing structures in the database

 Working With Resource Governor
 The Resource Governor is a new feature in the Microsoft® SQL Server®
2008 Enterprise.
 Resource Governor is a feature that you can use to manage SQL Server
workload and system resource consumption.
 Resource Governor enables you to limit the amount of CPU and memory that
incoming application requests can use.
 The resources that can be managed by Resource Governor are CPU and
memory.
 Resource Governor works with three components:
 Resource pools.
 Workload groups.
 Classification functions.
 A Resource pool defines the minimum and maximum CPU, memory, or both
allocated to a resource pool.
 By default, there are two resource pools: Internal and Default. You cannot
alter the internal pool in any way, and it has priority over all other pools, But
you can alter the default pool.
 Workload groups are a set of requests you define, so that Resource
Governor can assign the connection to the appropriate Resource pool.

 Working With Resource Governor
 A Classification Function is a function that you create in the master
database. Only ONE classification function can be active for
Resource Governor at a time.
 The classification function cannot have any input parameters and is
required to return a scalar value.
 The value that is returned is the name of the workload group that the
session should be classified into.
 The classification function can contain any code that is valid for a
function, but you should minimize the amount of code in any
classification function.
 Because the classification function executes after authentication but
before a connection handle is returned to the user’s application.
 the classification function is executed for every new connection to
your instance.

 How Does Resource Governor Work?

 Using Dynamic Management Views and Functions
 Dynamic Management Views (DMV) and Dynamic Management
Functions (DMF) provide the instrumentation infrastructure that
allows database administrators to retrieve system information as well
as monitor, diagnose, and fix problems.
 DMV Categories
 DMVs are all stored in the Sysschema
DMV Prefix General Purpose
dm_db_* General database space and index utilization
dm_exec_* Statistics for queries that are executing, as well as
queries that have completed and still have plans in the
query cache
dm_io_* Disk subsystem statistics
dm_os_* Statistics related to the use of hardware resources
Most important DMV categories that are used for performance tuning.

 Database Statistics
The most common DMVs used to gather database statistics are:
 sys.dm_db_index_usage_stats
 sys.dm_db_index_operational_stats
 sys.dm_db_index_physical_stats
 sys.dm_db_missing_index_groups
 sys.dm_db_missing_index_group_stats
 sys.dm_db_missing_index_details
 Indexes are created to improve performance of specific queries. But
Having an insufficient number of indexes can cause performance
problems.
 The sys.dm_db_index_usage_stats view contains the number of times
(and the last time) each index was used to satisfy a seek, scan, or lookup,
as well as the number of times and the last time an update was performed
to each index.

 Database Statistics
 Sys.dm_db_index_operational_stats
(database_id, object_id, index_id, partition_id)
This function returns locking, latching, and access statistics for each index that can
help you determine how heavily an index is being used. This function also helps you
diagnose contention issues due to locking and latching.
 Sys.dm_db_index_physical_stats
(database_id, object_id, index_id, partition_id, and mode)
The function returns size and fragmentation statistics for each index and should be
the primary source for determining when an index needs to be defragmented.

 Query Statistics
 Return information related to connection to the instance, as well as query execution.
 Sys.dm_exec_connections : contains one row for each connection to the
instance.
 Sys.dm_exec_sessions : contains a row for each currently authenticated
session, also returns the accumulated reads, writes, CPU, and query execution
duration for the session.
 Sys.dm_exec_requests : contains one row for each currently executing request
in the instance, also you can retrieve the database and command being
executed.
 sys.dm_exec_query_stats : contains detailed statistics on the performance and
resources consumed for every query in the query cache
 SQL Server stores the query plan and text of each query executed in the
query cache, identified by a unique value called the handle.
 sys.dm_exec_sql_text : returns the text of the SQL statement associated to the
handle that was passed in.
 sys.dm_exec_query_plan : accepts a plan handle and returns the corresponding
XML showplan.

SELECT * FROM
(SELECT user_seeks * avg_total_user_cost * (avg_user_impact
* 0.01) AS index_advantage, migs.* FROM
sys.dm_db_missing_index_group_stats migs) AS
migs_adv INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
)
ORDER BY migs_adv.index_advantage
 Determining which indexes to create
 The query that you can use to calculate a “usefulness factor” is :

 To return the query and XML show plan for all currently executing queries, you could
use the following statement:
SELECT * FROM sys.dm_exec_requests CROSS APPLY
sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 To return the SQL statement and XML show plan for every query that is cached in
the query cache:
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY
sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

 Disk Subsystem Statistics
 sys.dm_io_virtual_file_stats
Returns statistics about the reads and writes for everydatabase file.
 sys.dm_io_pending_requests
Contains a row for each request that is waiting forthe disk
subsystem to complete an I/O request

1. What types of workloads can DTA use for analysis? (Choose all that apply.)
A. A T-SQL script
B. A trace file containing Extensible Markup Language (XML) showplans
C. A trace file containing RPC:Completedevents
D. A trace file containing SP:StmtCompletedevents

2. You are the database administrator at Coho Vineyards. Following the
consolidation of all the wineries’ inventory, customer, and order databases, the
marketing group wants to be able to run ad hoc queries for analysis purposes.
Users are allowed to execute any query that they can construct, regardless of
the impact it might have on the performance of the database. Unfortunately, the
same databases are being used to create and process customer orders.
Management does not want to restrict the queries that marketing can execute,
but it wants you to ensure that customer orders can be created and processed
in a timely fashion. What can be used to limit the impact of marketing queries to
ensure customer orders are processed?
A. Configure the max degree of parallelism option.
B. Implement Resource Governor.
C. Configure the query governor cost threshold.
D. Limit the memory utilization for marketing users

Sql server lesson13

Recommended

More Related Content

What's hot (9)

Similar to Sql server lesson13 (20)

More from Ala Qunaibi (14)

Recently uploaded (20)

Sql server lesson13