New

Long-Term Learning

Learn efficiently and remember over time.

Start Long-Term Learning

Get personalized study reminders at intervals optimized for better retention.
Track your progress on this set by creating a folder
Or add to an existing folder

Add this set to a folder

  • Single, can be

    More than a ____ instance of SQL server ____ installed at a time.

    Schemas

    Object ownership groups

    File system, filegroups

    In the ____, in files, and often group into ____ is how databases are stored

    SQL Server Configuration Manager

    Used to manage the SQL server instances and set up the clients/client protocols.

    Default TCP port

    1433

    Reporting Services Configuration Manager

    Used to configure and manage the reporting services.

    Event class, trace

    ____ instances that you can monitor in the ____ process are what captured events in the Database Engine become.

    SQL Server Profiler

    Used to utilize captured traces, and capture and save data to analyze later. Can also create templates to define event classes and data columns

    .trc extension

    The file extension of trace files

    5

    There are ____ predefined templates for SQL Server Profiler.

    Standard Template

    Captures all the stored procedures and T-SQL batches that are run. Used to monitor the general database server activities

    TSQL Template

    Captures all T-SQL statements submitted by clients and the time issued. Used for debugging client applications.

    TSQL_Duration Template

    Capture statements submitted and their execution time. Used to identify the slower queries.

    Standard, TSQL, TSQL_Duration, TSQL_Grouped, TSQL_SPs

    ____ are the predefined templates for SQL Server Profiler (5)

    TSQL_Grouped Template

    Captures all statements submitted with information grouped by the client that submitted it. Use it to investigate queries from specific clients.

    TSQL_SPs Template

    Captures detailed information about the executing stored procedures. Used to get information about stored procedures that are executed.

    The Standard Template

    ____ is the default template for SQL Server Profiler.

    Management Studio

    The primary interface for managing the databases.

    Right-click, Properties

    ____ on the object and select ____ to get information about an objects properties in Management Studio.

    Query Window

    Provides a basic editor to write T-SQL statements/scripts

    Don't have

    You ____ to use the Query Window in order to write T-SQL.

    9

    There are ____ fixed Server Level Roles in SQL Server

    sysadmin,
    serveradmin
    securityadmin
    processadmin
    setupadmin
    bulkadmin
    diskadmin
    dbcreator
    public

    ____ are the fixed Server Level Roles provided by SQL Server. (9)

    Can, user-defined

    You ____ create ____ server level roles.

    Cannot

    Permissions ____ be changed for fixed Server Level Roles.

    sysadmin role

    Granted the permission to preform any activity in the server

    serveradmin role

    Granted the permission to change server-wide configuration options and shut down the server

    securityadmin role

    Granted the permission to manage logins and their properties, as well as GRANT, DENY, and REVOKE permissions at server-level, and if they have access to database-level permissions. They can reset passwords

    processadmin role

    Granted the permission to end processes that are running.

    setupadmin role

    Granted the permission to add or removed linked servers

    bulkadmin role

    Granted the permission to execute the BULK INSERT statement

    diskadmin role

    Granted the permission to manage disk files.

    dbcreator role

    Granted the permission to create, alter, drop and restore any database. They cannot alter others databases.

    Public

    Every log in belongs to the ____ server role.

    Cannot, public

    You ____ remove someone from the ____ server role.

    Management Studio, CREATE LOGIN

    The ____ or ____ statement are two ways to configure logins and their passwords.

    HASHED option

    Specifics that the password that follows the PASSWORD argument is already hashed

    migrating, server

    The HASHED argument should be used only when ____ databases from one ____ to another.

    MUST_CHANGE option

    Has the server prompt the user for a new password on their first login with that password

    CHECK_EXPIRATION option

    Tell whether password expiration policy should be enforced on that login

    Off, default

    ____ is the ____ state for the CHECK-EXPIRATION option.

    CHECK_POLICY option

    Says the Windows password policies of the local server computer should be enforced on this particular login.

    On, default

    ____ is the ____ state for CHECK_POLICY.

    WINDOWS option

    Specifies that the login is to be mapped to a Windows login

    Credentials

    Provides an identity outside of the SQL Server. Can be mapped to more than one login.

    Cannot, credential

    Multiple logins ____ be mapped to a ____.

    CREATE CREDENTIAL, Management Studio

    ____ statement or ____ are two ways to set up credentials.

    Script Login

    An option in Management Studio, allows you to script a login

    User must change password at next login
    Enforce password expiration
    Enforce password policy

    ____ are the optional password policies available. (3)

    Windows, SQL Server

    The ____ password policies are enforced for ____ logins.

    Older applications, third-party, mixed operating systems

    If you need to provide support for ____, ____ applications, or if you have ____ in place is when you would use SQL Server Authentication.

    Compatibility

    ____ is the biggest advantage of SQL Server Authentication

    Logon Triggers

    Triggers that can fire stored procedures when a LOGON event is raised.

    Authentication, user session

    After ____ is finished and before the ____ is established is when logon triggers fire.

    CREATE TRIGGER statement

    You create a logon trigger with the ____.

    Can, trigger

    There ____ be more than one ____ defined on the LOGON event.

    Database Engine service

    The executable process of the SQL Server Database Engine.

    SQL Server Agent

    A Windows service that executes scheduled administrative tasks.

    SQL Server Browser

    A Windows service that listens for incoming requests.

    One, Browser

    You need ____ SQL Server ____ for all SQL Server instances installed on the same server.

    Pause

    You will prevent new users from connecting to the Database Engine if you ____ it.

    Pausing, does not

    ____ the Database Engine service ____ remove users currently connected.

    Cannot, Agent

    You ____ pause the SQL Server ____.

    -c option

    Used for shortening the startup time when starting SQL Server from the command prompt

    -f option

    Used for starting an instance with minimal configuration.

    Configuration values, -f

    When there a is particular ____ preventing the server from starting is when you most often use the ____ option.

    -g option

    Used to specify the size of memory that SQL Server can leave available for allocations within the process outside of the Server memory pool

    -m option

    Allows you to limit connections to a specified client application

    Client Application Name

    The '____' should be included with the -m option.

    -n option

    Disallows the use of Windows application log to record events.

    -s option

    Allows you to start only a named instance of SQL Server

    -E option

    Allows you to increase the number of extents allocated for each file in a filegroup.

    Autocommit mode

    ____ is the default transaction management mode.

    Autocommit mode

    A mode that means every T-SQL statement will be committed or rolled back when it is completed.

    BEGIN TRANSACTION, SET IMPLICIT_TRANSACTIONS

    ____ and ____ are the two statements that can end Autocommit mode.

    Explicit transaction

    ____ is the kind of transaction started with the BEGIN TRANSACTION statement.

    Implicit transaction mode

    ____ is what is started with the SET IMPLICIT_TRANSACTIONS statement.

    SET IMPLICIT_TRANSACTIONS OFF

    ____ is the statement that will end implicit transaction mode.

    COMMIT TRANSACTION

    The ____ statement, practically, marks the end of a successful implicit or explicit transaction.

    ROLLBACK TRANSACTION statement

    Removes all data modifications made and rolls back to the beginning of the transaction

    Does not, implicit

    Another statement ____ need to be written to mark the start of a new ____ transaction.

    Resource Governor

    A feature you can use to specify limits on resource consumption by incoming requests

    Resource Governor

    The ____ can be used in real time.

    ALTER RESOURCE GOVERNOR RECONFIGURE, Management node

    Using the ____ statement or Right-clicking on it in the ____ of the Management Studio and selecting Enable are the two ways can you enable the Resource Governor.

    Workload Groups

    Are containers for session requests with similar or identical classification criteria.

    Aggregate monitoring of sessions

    You would use workload groups to ____.

    Management, resource pools

    Workload groups located under the ____ node - ____ in Management Studio.

    CREATE WORKLOAD GROUP, ALTER RESOURCE GOVERNOR RECONFIGURE

    ____ and ____ are the two T-SQL statements that are needed to make a new workload group,

    Resource pool

    You can reconfigure the ____ while configuring the Resource Governor.

    Lower

    The max CPU percentage cannot be ____ than the min CPU percentage.

    Memory, memory

    The max ____ percentage cannot be lower than the min ____ percentage.

    100

    The sums of the minimum CPU and minimum memory percentages for all available resource polls cannot go over ____ percent.

    CREATE RESOURCE POOL, ALTER RESOURCE GOVERNOR RECONFIGURE

    ____ then ____ are the two T-SQL statements that are needed for creating resource pools.

    View, Resource Governor

    You can reconfigure Resource Governor in Management Studio Under ____ - Template Explorer - ____ - Configure Resource Governor.

    Locks

    Used to prevent concurrent use of a resource by multiple different transactions

    Increase concurrency and improve performance

    Minimizing locks will ____.

    Latches

    Internal SQL Server resource locks related to user activity and resource usage.

    Performance bottlenecks

    Locks commonly cause ____.

    Transaction Isolation Levels

    Definitions of the types of locks that can be acquired on read operations.

    One, isolation

    There can be ____ transaction ____ levels set at a time.

    SET TRANSACTION ISOLATION LEVEL

    ____ is the T-SQL statement used for transaction isolation levels.

    READ UNCOMMITTED level

    Will not issue shared locks. They are not blocked by exclusive locks, and it means dirty reads.

    More restricted

    READ COMMITTED is ____ than READ UNCOMMITTED.

    READ COMMITTED

    Statements cannot read data that has been changed but not yet committed.

    READ COMMITTED

    ____ is the SQL Server default transaction isolation level.

    Off, SNAPSHOT

    ____ is the default state of READ_COMMITTED ____.

    REPEATABLE READ

    Statements cannot read data modified but not yet committed and no other transactions can modify data that has been read until current transaction is completed.

    SNAPSHOT

    A transaction can only recognize data modifications committed prior to the start of the transaction. Modifications made after the start will be invisible.

    Table hints

    You can use ____ to apply locks when you select, modify or insert rows.

    ROWLOCK hint

    The row locks are to be taken

    TABLOCK hint

    The acquired lock is to be applied at the table level

    TABLOCKX hint

    Refers to an exclusive lock on the table

    UPDLOCK hint

    Update locks that are held in place until a transaction is completed.

    XLOCK

    Applies exclusive locks

    ROWLOCK, PAGLOCK, TABLOCK

    ____ are the hints that XLOCK can be specified with.

    SERIALIZABLE

    Produces the same effect as setting HOLDLOCK on all tables in the SELECT statement.

    sys.dm_tran_locks

    You can query ____ to find out if there are many locks outstanding.

    SQL Server Agent, automation

    You would use ____ for database ____. Designed for processing alerts, and running scheduled jobs. Can also send out relevant alerts and notifications through email

    Database Mail or SQL Mail

    ____ are the two email systems SQL Server Agent can use to send emails.

    SQL Mail

    The ____ email system is depreciated.

    Database, SMTP

    To set up ____ Mail you would configure one or more databases as mail hosts and then define client settings so SQL Server Agent can send message via ____ mail server

    sp_start_job

    Used to instruct SQL Server Agent to execute a job immediately

    sp_update_job

    Used to instruct SQL Server Agent to make changes to a job

    sp_get_schedule_description

    Used to extract and read the job schedule.

    sysjobs_view

    Used to find out further information on the various jobs

    sp_help_jobactivity

    Used to produce information on the status of a job run

    sp_help_jobcount

    Used to produce a count on how many jobs a schedule is tied to.

    sp_configure

    Used to show or change server-level settings

    ALTER DATABASE statement

    The ____ is how you change Database level settings.

    SET statement

    The ____ is how you make changes to the current user session only.

    RECONFIGURE statement

    The ____ is how you update current configured values that have been changed with sp_configure.

    sp_configure, all, GUI, most commonly

    The difference between the Management Studio GUI and sp_configure is while ____ allows access to ____ options the ____ contains the ____ used server configuration options.

    Affinity mask, affinity I/O mask

    ____ and ____ are the two affinity mask options.

    affinity masks

    Used for assigning processors to specific threads.

    Dynamically, CPU

    An affinity mask is used for ____ controlling ____ affinity.

    RECONFIGURE or RECONFIGURE WITH OVERRIDE statements

    The affinity mask can be configured without a restart with the ____.

    I/O affinity task

    Used for directly affecting the I/O affinity mask.

    Are mutually

    The CPU and I/O affinities ____ exclusive.

    ALTER SERVER CONFIGURATION, PROCESS AFFINITY

    You can configure processor affinity options in SQL Server 2012 by using the ____ statement and then the ____ argument.

    Should, dynamically

    SQL Server ____ be allowed to adjust and use memory ____.

    Management Studio, sp_configure

    The ____ or ____ is how you change memory options manually.

    Please allow access to your computer’s microphone to use Voice Recording.

    Having trouble? Click here for help.

    We can’t access your microphone!

    Click the icon above to update your browser permissions above and try again

    Example:

    Reload the page to try again!

    Reload

    Press Cmd-0 to reset your zoom

    Press Ctrl-0 to reset your zoom

    It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

    Please upgrade Flash or install Chrome
    to use Voice Recording.

    For more help, see our troubleshooting page.

    Your microphone is muted

    For help fixing this issue, see this FAQ.

    Star this term

    You can study starred terms together

    NEW! Voice Recording

    This is a Plus feature