Term
| What resources can Resource Governor control? |
|
Definition
| CPU and memory resources. |
|
|
Term
| What are the objects that are used for Resource Governor implementation? |
|
Definition
| Resource pools, workload groups, classification functions. |
|
|
Term
| A user-query is using too many resources. How can the Resource Governor limit this activity? |
|
Definition
| It cannot. Classification occurs at the time a connection is created. Existing connections or even types of queries are beyond RG. |
|
|
Term
| Resource Governor : What resource pool is assigned if the Classification function returns NULL? |
|
Definition
| The default resource pool. |
|
|
Term
| Resource Governor: Define workload group. |
|
Definition
| A label associated to a connection when it is creates so that RG can assign it the appropriate resource pool. |
|
|
Term
| Resource Governor: Define classification function. |
|
Definition
| Assigns a connection to a workload group. Created in the master DB. |
|
|
Term
| Resource Governor: Classification function List arguments & return values. |
|
Definition
| Takes no arguments, returns a scalar. |
|
|
Term
| Resource Governor: Describe parity between connections, workload groups, and resource pools. |
|
Definition
| A connection(s) can belong to only a single group. Each group(s) are assigned to a single pool. |
|
|
Term
| Resource Governor: What limitation is placed on the SUM of minimum values for the resource pools? |
|
Definition
|
|
Term
| Resource Governor: Resource allocation: How are connections within a resource pool assigned priority? |
|
Definition
| They are not. All connections within a pool are treated with equal weight. SS balances the resources available to the pool across all pool connections. |
|
|
Term
Order these steps to implement Resource Governor:
1. Create one or more pools.
2. Associate each group to a pool.
3. Enable Resource Governor. 4. Associate the class funt to RG.
5. Create & test a class funct. 6. Create one or more groups. |
|
Definition
| * Enable Resource Governor. * Create one or more pools. * Create one or more groups. * Associate each group to a pool. * Create & test a class funct. * Associate the class funt to RG. |
|
|
Term
| Database Tuning Advisor: What is available in Advanced Options? |
|
Definition
Limit space used
Max columns per index
Whether recommendations are implmented on/off line.
p.371 |
|
|
Term
Database Tuning Advisor
What is selected in the "Physical Design Structures to use" section? |
|
Definition
Choose what type of indexes to analyze.
Ex: (non) clustered, indexed views
p.371 |
|
|
Term
Database Tuning Advisor
"Physical Design Structures to use" section.
What is the "Evaluate utilization of existing PDS only" option for? |
|
Definition
Locates indexes & views that can be dropped because they are not being used.
p. 371 |
|
|
Term
Database Tuning Advisor
"Physical Design Structures to keep" section.
Explain the Keep VS "Do not Keep" options. |
|
Definition
| Whether recommendations have to consider existing PDS, or whether they can be removed as part of the recommendations. p. 372 |
|
|
Term
Database Tuning Advisor
What impact can running the DTA have on a database? |
|
Definition
DTA generates statistics to make its recommendations.
Statistics can place a heavy load on the database.
p. 372 |
|
|
Term
| Resource Governor: When can a resource pool utilize more resources that it's MAX setting? |
|
Definition
When there are free resources available.
p. 385 |
|
|
Term
| Difference between connections and sessions? |
|
Definition
Connections are user connections apart from system processes. Normally SPID > 50 are only reported with sys.dm_exec_connections. |
|
|
Term
| Purpose of DMV category: dm_db_* |
|
Definition
| General database space and index utilization |
|
|
Term
| Purpose of DMV category: dm_exec_* |
|
Definition
| Statistics for queries that still have plans in the query cache. |
|
|
Term
| Purpose of DMV category: dm_io_* |
|
Definition
| Disk subsystem statistics. |
|
|
Term
| Purpose of DMV category: dm_os_* |
|
Definition
| Statistics related to the use of hardware resources. |
|
|
Term
| sys.dm_db_index_usage_stats |
|
Definition
| Contains the number of times (and the last time) each index was used to satisfy a seek, scan, or lookup. |
|
|
Term
| How can you tell if an index is not being used? |
|
Definition
| sys.dm_db_index_usage_stats will not have any seeks, scans, or lookups. |
|
|
Term
| sys.dm_db_index_operational_stats |
|
Definition
| Returns locking, latching, and access statistics for each index. Shows how heavily an index is being used and can help diagnose contention issues due to locking and latching. |
|
|
Term
| What is a latch or latching? |
|
Definition
| Locks and latches are similar objects but they have a slightly different purpose. Locks ensure that the same data element cannot be modified by two different connections at the same time; latches, on the other hand, simply ensure that the data page on which the data element resides is physically readable and writable. A latch is only maintained on the data page while the data is being changed, as opposed to locks which are maintained for the duration of the entire transaction. |
|
|
Term
| sys.dm_db_index_physical_stats |
|
Definition
| Returns size and fragmentation statistics for each index and should be the primary source for determining when an index needs to be defragmented. |
|
|
Term
| What set of DMVs are used to find missing indexes? |
|
Definition
| sys.dm_db_missing_index_* |
|
|
Term
| If the database setting ____ is on, then SQL Server will automatically create statistics for non-indexed columns that are used in your queries. |
|
Definition
|
|
Term
Missing index DMV: The index advantage needs to reach what value to identify a useful index?
To identify an index where the benefit far outweighs any cost? |
|
Definition
10,000 to be useful.
50,000 to outweigh cost. |
|
|
Term
|
Definition
| One row for each connection to the instance. View when the connection was made, total reads, writes, last activity. |
|
|
Term
|
Definition
| One row row each of current session, query state, and execution status. Accumulated reads, writes, CPU, and query execution duration for the session. |
|
|
Term
|
Definition
| One row for each currently executing request in the instance. Blocking_sesion_id, database and command being executed, along with handles for the SQL statement and query plan. |
|
|
Term
|
Definition
| Detailed statistics on the performacne and resources consumed for every query in the query cache. Lists the last time the query was executed and how many times, Min/Max execution time, number of reads/writes. |
|
|
Term
|
Definition
| Returns the text of the SQL statement associated to the handle that was passed in. |
|
|
Term
|
Definition
| Shows query plan for query handle passed in. |
|
|
Term
| sys.dm_io_virtual_file_stats |
|
Definition
| Reads ,writes, and IOStalls for every database file. |
|
|
Term
|
Definition
| When SQL Server has to wait for a disk subsystem to become available for a read or write operaion, an IOStall occurs. The time is measured in milliseconds. |
|
|
Term
| sys.dm_io_pending_requests |
|
Definition
| One row for each request that is waiting for the disk subsystem to complete an I/O request. |
|
|
Term
|
Definition
| SQL Server has one UMS per processor. Any request that exceeds this number is added to the runnable queue. When a request makes it to the UMS and then has to wait for a resource to become available, it is swapped back into the waiting queue. The request waits here until the needed resource is available. Then it is moved to the bottom of the runnable queue. p. 391 |
|
|
Term
|
Definition
When a request is sent to the waiting queue, a value called the "wait type" designates the type of resource that the request is waiting on.
wait time = the amount of time it had to wait. p. 391 |
|
|
Term
| runnable queue: signal wait |
|
Definition
| How long it takes a process to get to the top of the runnable queue. p. 391 |
|
|
Term
| What DMV lists the aggregate amount of signal wait and wait time for each wait type? |
|
Definition
| sys.dm_os_wait_stats p.391 |
|
|
Term
How are most DMVs' cleared?
How is the sys.dm_os_wait_stats DMV cleared? |
|
Definition
By restarting the instance.
DBCC SQLPERF(WAITSTATS,CLEAR) |
|
|
Term
| Name the sources the Database Tuning Advisor can use as a workload source. |
|
Definition
| trace or SQL file. table with trace data or T-SQL commands. p.369 |
|
|
Term
| You need to create a trace file in Profiler to use as a workload for the Database Tuning Advisor. What are the only 4 events needed? |
|
Definition
RPC: starting / completed
SQL: Batch starting / completed p. 369 |
|
|
Term
Which are used to locate blocked processes?
sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_os_waiting_tasks
sp_who2 |
|
Definition
sys.dm_exec_requests
sp_who2 |
|
|
Term
| What recommendations can the Database Tuning Adviser make? |
|
Definition
Adding / dropping indexes
Partitioning tables
Storage aligning tables
p. 369 |
|
|
Term
| What is an index seek, scan, and lookup? |
|
Definition
seek = The index is used to find the exact data.
scan = The index is scanned one row at a time to find data. There is little difference between a table and index scan.
lookup = The index is not covered, so the RID or CX must be used to retrieve the data. |
|
|
Term
| What is the Performance Studio? |
|
Definition
| Another name for the Performance Data Warehouse. |
|
|
Term
| Which features is the Performance Data Warehouse based on? |
|
Definition
| The PDW is built upon the Data Collector infrastructure. Data collection is based on SSIS packages and SQL Server Agent jobs. |
|
|
Term
| What collector types are available in SS 2008? |
|
Definition
| SS 2008 ships with T-SQL Query, SQL Trace, Query Activity, and Performance Counter collector types. |
|
|
Term
|
Definition
| Query or performance counter that you want to track in the Perfromance Data Warehouse. p.553 |
|
|
Term
|
Definition
| A group of collection items that are combined together and managed by a Data Collector. p.553 |
|
|
Term
|
Definition
| The instance, database, or object that a collection set is executed against. p.553 |
|
|
Term
| Dynamic Management Function (DMF) |
|
Definition
| A function which ships with SQL Server that provides configuration, object, or diagnostic information p.555 |
|
|
Term
| Dynamic Management View(DMV) |
|
Definition
| A view which ships with SQL Server that provides configuration, object, or diagnostic information. |
|
|
