Free Microsoft 70-765 Exam Dumps Questions & Answers

User report that a query takes a long time to execute. The query has the following wait statistics.

Which resource causes the issue?

• A.  network
• B.  disk
• C.  processor
• D.  blocking

A company has an infrastructure that runs Microsoft SQL Server 2017 in Azure.
You need to ensure validation is performed on a memory-optimized Linux system.
Which Transact-SQL statement should you run?

• A.  DBBC CHECKALLOC
• B.  DBBC CHECKCATALOG
• C.  DBCC CHECKTABLE
• D.  DBCC CHECKSUM

A company has an on-premises Microsoft SQL Server 2016 environment. All futures databases must meet the following requirements:
The recovery model must be set to simple.
The compatibility level must be set to SQL server 2014 (120).
Your need to configure the SQL server 2016 environment.
In the table below, identify the database you must modify for each requirement.

Correct Answer:

Explanation:
Model: Change from full to simple Recovery Model
Newly created user databases use the same recovery model as the model database.
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database.
Model: Set compatibility level to 120
For all installations of SQL Server, the default compatibility level is set to the version of the Database Engine. Databases are set to this level unless the model database has a lower compatibility level.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017

You have a SQL Server 2016 database named DB1.
You plan to import a large number of records from a SQL Azure database to DB1.
You need to recommend a solution to minimize the amount of space used in the transaction log during the import operation.
What should you include in the recommendation?

• A.  A new log file
• B.  A new partitioned table
• C.  A new file group
• D.  The full recovery model
• E.  The bulk-logged recovery model

You have a table named Table1 that contains 500 million records and is 200 GB. The indexes for Table! are configured as shown in the following table.

You need to reduce the fragmentation. The solution must minimize disk I/O and the impact on users.
Which approach should you use for each index? To answer, drag the appropriate approaches to the correct indexes. Each approach may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation:
References:
https://blog.sqlauthority.com/2012/04/04/sql-server-cleaning-up-sql-server-indexes-defragmentation-fillfactor-video/
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017