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;
18 Cards in this Set
- Front
- Back
cmd that contains a detailed breakdown of the amount of input/output (I/O) going to each file in a database. Having extremely high values that are not balanced between the data files in a database is one indicator that more I/O is being directed to a particular file, which could lead to I/O contention issues.
|
sys.dm_io_virtual_file_stats
|
|
function that takes four optional parameters:
database_id, object_id, index_id, partition_id and returns 1)locking 2)latching 3)access statistic for each index that helps to determine how heavily an index is being used |
sys.dm_db_index_operational_stats
|
|
query for DMV that returns the text of the SQL statement associated to the handle that passed int
|
sys.dm_exec_sql_text
|
|
A stored procedure that creates a new trace object. Equivalent to the definition on the General tab of the New Trace dialog in Profiler.
|
sp_trace_create
|
|
A stored procedure that allows you to define your own trace event.
|
sp_trace_generateevent
|
|
A stored procedure that adds a data column for an event to be captured by a trace. You need to call sp_trace_setevent once for each data column being captured for an event. Equivalent to the event and data column selection grid in the Events Selection tab of the New Trace dialog in Profiler.
|
sp_trace_setevent
|
|
A stored procedure that adds a filter to a trace. Equivalent to the Edit Filter dialog box in Profiler.
|
sp_trace_setfilter
|
|
A stored procedure that starts, stops, and closes a trace.
A status of 0 stops a trace. A status of 1 starts a trace. A status of 2 closes a trace and removes the trace definition from the instance. |
sp_trace_setstatus
|
|
A function that returns the events and data columns being captured by a trace.
|
fn_trace_geteventinfo
|
|
A function that returns the filters applied to a specified trace.
|
fn_trace_getfi lterinfo
|
|
A function that returns status and property information about all traces defined in the instance.
|
fn_trace_getinfo
|
|
A function that reads one or more trace files and returns the contents as a result set. Commonly used to import trace files into a table.
|
fn_trace_gettable
|
|
What DMV can be used to monitor index usage statistics?
|
SYS.DM_DB_INDEX_USAGE_STATS
|
|
query for DMV that returns statistics about the reads and writes for every data file. Provides aggregate reads and writes bytes read and write to each file
|
sys.dm_io_virtual_file_stats
|
|
cmd/view to use to determine whether a process is blocked
|
sys.dm_exec_requests
|
|
query for DMV that returns the XML showplan
|
sys.dm_exec_query_plan
accepts a plan handle as parameter |
|
query for a view that contains
1)number of time 2)last time 3)number of updates performed 4)last time an update was performed on index was used to satisfy a seek, scan or lookups |
sys.dm_db_index_usage_stats
|
|
query to disable
Ole Automation Procedures SQL Mail Xps xp_cmdshell |
EXEC sp_configure 'Ole Automation Procedures', 0
EXEC sp_configure 'SQL Mail Xps', 0 EXEC sp_configure 'xp_cmdshell', 0 |