Reading...
Play button
Play button
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;
50 Cards in this Set
- Front
- Back
|
What recommendations can DTA make?
|
Adding indexes
Dropping indexes Partitioning tables Storage aligning tables |
|
|
What are the only events that DTA is concerned with?
|
RPC:Starting
RPC:Completed SQL:Batch Starting SQL:Batch Completed |
|
|
What are the four steps that comprise DTA analysis?
|
1. Generate a workload for analysis
2. Start DTA and connect to a server running SQL Server that contains a database to analyze the workload against 3. Select the workload to use 4. Specify tuning options |
|
|
Why would you want to consider running DTA against a test system rather than a production system?
|
The creation and destruction of statistics by DTA can place a very heavy load on the database being analyzed.
|
|
|
What are the valid input sources for DTA to analyze?
|
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.
|
|
|
What three components does Resource Governor work with?
|
Resource Pools
Workload groups Classification functions |
|
|
What resources can be managed by Resource Governor?
|
CPU and memory
|
|
|
What is a workload group?
|
A label that you associate to a connection when it is created so that Resource Governor can assign the connection to the appropriate resource pool.
|
|
|
What is a classifier function?
|
A function that you create in the master database; only one classifier function can be active for Resource Governor at a time. It returns the name of the workload group that the session should be classified into. Executes after authentication but before connection handle returned to user's applications; performance issues in classification function could cause connection timeout.
|
|
|
When is the default resource pool used?
|
If a classifier function is not associated to Resource Governor, or the classifier function does not exist, returns NULL, or returns a nonexistent workload group, the user session is associated to the default resource pool.
|
|
|
What steps do you take to implement Resource Governor?
|
1. Enable Resource Governor.
2. Create one or more resource pools. 3. Create one or more workload groups. 4. Associate each workload group to a resource pool. 5. Create and test a classifier function. 6. Associate the classifi er function to Resource Governor. |
|
|
What views can you use to return information about the Resource Governor configuration?
|
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups SELECT * FROM sys.resource_governor_configuration GO When Resource Governor is active, the group_id column of sys.dm_exec_sessions is the ID of the workload group to which the session is assigned. |
|
|
Where are all DMVs stored?
|
The sys schema.
|
|
|
What is the general purpose of DMVs with a prefix of: dm_db_* ?
|
General database space and index utilization.
|
|
|
What is the general purpose of DMVs with a prefix of: dm_exec_* ?
|
Statistics for queries that are executing as well as queries that have completed and still have plans in the query cache
|
|
|
What is the general purpose of DMVs with a prefix of: dm_io_* ?
|
Disk subsystem statistics
|
|
|
What is the general purpose of DMVs with a prefix of: dm_os_* ?
|
Statistics related to the use of hardware resources
|
|
|
What does the sys.dm_db_index_usage_stats view contain?
|
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 on each index.
|
|
|
What 4 optional parameters does sys.dm_db_index_operational_stats take?
|
database_id
object_id index_id partition_id |
|
|
What is db_index_operational_stats used for?
|
The function returns locking, latching and access statistics for each index--helps in determining contention issues.
|
|
|
What are the five optional parameters that sys.dm_db_index_physical_stats takes?
|
database_id
object_id index_id partition_id mode |
|
|
What is sys.dm_db_index_physical_stats takes used for?
|
It returns the size and fragmentation statistics for each index and should be the primary source for determining when an index needs to be defragmented.
|
|
|
What is the sys.dm_db_missing_index_* views used for?
|
Every time an index miss (when the Optimizer determines that an index would be beneficial but doesn't exist) occurs, SQL Server logs the details.
|
|
|
What is sys.dm_exec_connections used for?
|
Contains one row for each connection to the instance. Contains time connection was made along with properties of the connection and encryption settings. Can also tell you the total number of reads and writes for the connection as well as the last time a read or write was executed.
|
|
|
What is sys.dm_exec_sessions used for?
|
Contains a row for each currently authenticated session. In addition to login info, also track the current state of each possible query option and the current execution status. Also returns the accumulated reads, writes, CPU and query execution duration for the session.
|
|
|
What is sys.dm_exec_requests used for?
|
Contains one row for each currently executing request in the instance. you can use the blocking_session_id column to diagnose contention issues. Also contains the start time, elapsed time, estimated completion time, reads wrties and CPU for the request. In addition, you can retrieve the database and command being executed, along with handles for the SQL statement and query plan associated with the request.
|
|
|
What is sys.dm_exec_query_stats used for?
|
contains detailed stats on the performance and resources consumed for every query in the query cache. Lists the last time the query was executed and how many times the query was executed, along with the min and max execution time, logical/physical reads/writes/CPU, and a handle to the query plan generated by the Optimizer.
|
|
|
What is a handle
|
It is the unique value by which SQL Server identifies the stored query plan and text stored in the query cache.
|
|
|
What is sys.dm_exec_sql_text used for?
|
It returns the text of the SQL statement associated to the handle that was passed in.
|
|
|
What is sys.dm_exec_query_plan used for?
|
It accepts a plan handle and returns the corresponding XML showplan.
|
|
|
What statement would you use to return the query and XML showplan for every query currently executing?
|
SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(sql_handle)
|
|
|
What does the sys.dm_io_virtual_file_stats function return?
|
Statistics about the reads and writes for every database file. Provides aggregate number as well as the bytes written to each file since the instance was started. You can also retrieve IOStalls. You use info from this function to determine whether disk contention is contributing to performance issues. You can also determine if you have disk I/O balanced across database files or if you have a disk hot spot.
|
|
|
What is an IOStall?
|
When SQL has to wait for the disk subsystem to become available to satisfy either a read or write operation; the time for IOStalls, measured in ms, is logged for each database file.
|
|
|
What does the sys.dm_io_pending_requests return?
|
Contains a row for each request that is waiting for the disk subsystem to complete an I/O request. If you have a request that appear frequently or stays for a long time, you probably have a disk bottleneck.
|
|
|
Data collection for the Performance Data Warehouse is configured using which collector types?
|
T-SQL Query
SQL Trace Performance Counter Query Activity |
|
|
Because the Data Collector dynamically generates the table based on the results of the query defines, what must you ensure?
|
|
|
|
What features is the Performance Data Warehouse based on?
|
The Performance Data Warehouse is built upon the Data Collector infrastructure. Data collection is based on SSIS packages and SQL Server Agent jobs.
|
|
|
What are the two types of clusters?
|
Standard windows cluster and majority node set cluster.
|
|
|
Where is the quorum database stored in each type of cluster?
|
In Standard windows cluster, it is on the shared array. In a majority node set cluster, it is stored locally on each node in the cluster.
|
|
|
What permissions does the cluster administrator account need?
|
It needs to be a member of the local administrators group on the machine.
|
|
|
What drives can you use in clustering? What if you are building a cluster that contains more than two nodes, has Windows Datacenter or has the 64-bit version of windows?
|
SCSI/iSCSI and Fibre. If any of those conditions are present, you are limited to only Fibre drives.
|
|
|
Can you use dynamic disks in clusters?
|
No, only basic disks and mount points.
|
|
|
What are some examples of cluster resources?
|
Cluster resources can be hardware, software, or networking. Some examples are IP addresses, network names, disk mounts, and Windows services.
|
|
|
How many network connections does a node need for clustering? Why?
|
Each node needs at least two network connections: One connection is used for public communications to applications on the network, and the other is used for private internal communications within the cluster.
|
|
|
How does the health check within a Windows cluster work?
|
The basic health check that is performed is called a LooksAlive test. This test consists of each node pinging the others.
|
|
|
What is the main difference between a standard cluster and a majority node set cluster?
|
A standard cluster uses a shared quorum database. A majority node set cluster maintains a separate quorum database on each node that is synchronized across
all nodes. The majority of nodes (more than 50 percent) must be online for a majority node set cluster to function. |
|
|
Which types of Windows accounts and groups can you use with a SQL Server cluster instance?
|
Domain users and domain groups must be used with SQL Server failover cluster instances. The SID for accounts and groups used must be resolvable across all
nodes in the cluster. The SID for a local account or group cannot be resolved across machines. |
|
|
With how many clustered instances can a single drive letter be used?
|
Although a clustered instance can address multiple drive letters, you can configure a given drive letter for only a single instance. This confi guration prevents the
possibility of having SQL Server running on one node while a different node has ownership of the disk resources required by the clustered instance. |
|
|
What are the two health checks performed in a cluster, and which operations are executed?
|
The LooksAlive check executes every 5 seconds by default and issues a ping from all nodes to the IP address of the SQL Server clustered instance. The IsAlive check
executes every 60 seconds by default, connects to the SQL Server clustered instance, issues SELECT @@SERVERNAME, and must receive a valid result set. |
|
|
What are the three database mirroring roles? Which are required and which are optional?
|
principal - req
mirror - req witness - optional |
