70-464 Free Practice Test

- (Exam Topic 8)
You discover a sudden increase in processor utilization on a server that has SQL Server installed. You need to correlate server performance and database activity for an extended time period. Which two tools should you use? Each correct answer presents part of the solution.

1. A. Activity Monitor
2. B. Performance Monitor
3. C. SQL Server Profiler
4. D. sp_who2
5. E. SQL Server Extended Events

Correct Answer: BE
The Performance Monitor side, we have a few SQL Server monitoring tools AKA counters that can be used when troubleshooting CPU performance. The following counters are simple and easy to use:
Processor % Processor Time == < 80 xss=removed xss=removed>https://www.sqlshack.com/sql-server-monitoring-tool-for-cpu-performance/

- (Exam Topic 8)
You have a SQL Server database.
The recovery model is set to full. The transaction log is backed up every night. You discover that the transaction log never decreases in size.
You execute the DBCC SHRINKFILE statement for the transaction log and you discover that the transaction log is unaffected.
You need to ensure that you can reduce the size of the transaction log. What should you do first?

1. A. Truncate the transaction log
2. B. Kill long-running transactions
3. C. Change the recovery model to bulk-logged
4. D. Perform a full backup

Correct Answer: A
The transaction log must be truncated before running the DBCC SHRINKFILE operation.

- (Exam Topic 8)
You plan to deploy two stored procedures named SP1 and SP2 that read data from the database. Your company identifies the following requirements for each stored procedure:
< > >< > >You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)

Solution:
Note:
* READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.
* SERIALIZABLE
Specifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary.
Reference: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 4)
You are testing disaster recovery procedures.
When you attempt to restore ProductsDB to another server, you receive the following error message: "Msg 33111, Level 16, State 3, Line 5
Cannot find server certificate with thumbprint ' 0x9D876A3468B911ElBA4CFCBF4724019B\ Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore ProductsDB to another server. Which code segment should you execute on the other server?

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: D

- (Exam Topic 8)
You create a table named Customers by using the following code segment:
< ><>>>< ><>

1. A. Replace LEFT(name,3) = \'smi\' by using name like \'smi%\'
2. B. Replace LEFT(name,3) = \'smi\' by using substring(name,l,3) = \'smi\'
3. C. Recreate IX_Name as a unique index
4. D. Recreate IX Name as a clustered index

Correct Answer: A