Administering Microsoft SQL Server 2012 Databases
Question No: 111 – (Topic 2)
You administer a Microsoft SQL Server 2012 database named Orders.
Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.
You need to gather more data to be able to determine which processes are being blocked and to identify the root cause.
What should you do?
Schedule A SQL Agent job to run every 60 Seconds and insert the results of executing the SP_who2 stored procedure table
Use System Monitor to catch the Lock Wait Time event
Use Sp_Configure to set the blocked process threshold. Start a Trace using SQL Server Profiler to catch the Blocking Pro-Report event.
Start a Trace using SQL Server Profiler to catch the Lock: Timeout event
Question No: 112 – (Topic 2)
What is the minimum recommended amount of RAM for SQL Server 2012 Enterprise?
Question No: 113 DRAG DROP – (Topic 2)
You administer a Microsoft SQL Server 2012 server along with a Windows Azure SQL Database database.
For both servers, you need to grant users the ability to create logins and create databases. You need to determine which permission to grant users in each instance.
Which permission or permissions should you choose? (To answer, drag the appropriate permission or permissions to their corresponding instance type or types in the answer area.
Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
Question No: 114 – (Topic 2)
You have an index called Employee_IDX on the table Employees. Which of the following will rebuild this index online?
ALTER INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);
CREATE INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);
REBUILD ONLINE INDEX Employee_IDX ON Employees;
ALTER INDEX REBUILD ONLINE Employee_IDX;
Question No: 115 – (Topic 2)
In which of the following statements about audit information stored in an SQL Server 2012 environment is TRUE?
It is located in a file specified in the File Path property of the Audit.
It is located in the Audit table in the MSDB database.
Each login has an audit record, which is viewable under Logins -gt; Audit.
SQL 2012 does not store any audit information.
Question No: 116 – (Topic 2)
Which of the following statements will set the maximum amount of system memory that is managed by the SQL Server Memory Manager to 4GB?
sp_configure ‘max memory’, 4;
ALTER SYSTEM SET ‘max server memory’=4096;
sp_configure ‘max server memory’, 4;
sp_configure ‘max server memory’, 4096;
Question No: 117 – (Topic 2)
You plan to install a Microsoft SQL Server 2012 instance.
The instance will support a database that has the following requirements:
->Store Excel workbooks on the file system.
->Access the workbooks through Transact-SQL.
->Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met. Which feature should you use?
SQL Server Integration Services (SSIS)
Question No: 118 DRAG DROP – (Topic 2)
You administer a Microsoft SQL Server 2012 instance.
An application executes a large volume of dynamic queries.
You need to reduce the amount of memory used for cached query plans.
What should you do? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization
Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason a large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.
Let us now enable the option of optimizing ad hoc workload. This feature is available in all the versions of SQL Server 2008.
sp_CONFIGURE #39;show advanced options#39;,1 RECONFIGURE
sp_CONFIGURE ‘optimize for ad hoc workloads’,1 RECONFIGURE
SQL Server 2008 has feature of optimizing ad hoc workloads.
Question No: 119 DRAG DROP – (Topic 2)
You are a database administrator for a Microsoft SQL Server 2012 environment that
contains two SQL Server instances named Server01 and Server02. The Contoso database is located on Server01. Through database mirroring, a copy of the Contoso database is on Server02.
During the last manual failover of database mirroring, the recovery took longer than usual.
You need to determine where the synchronization bottleneck is for the database mirroring topology.
Which performance counter or counters should you use for each server? (To answer, drag the appropriate performance counter or counters to their corresponding server or servers in the answer area. Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
Question No: 120 – (Topic 2)
Which of the following Microsoft SQL Server 2012 components is responsible for executing scheduled administrative tasks?
SQL Server Instance
SQL Server Jobs File
SQL Server Agent
SQL Server SSIS
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|