• 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/18

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;

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