70-465 | You need to recommend changes to the ERP…

Question: 16

You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application. What should you recommend changing?

A. The collation of the Products table
B. The index on the ProductName column
C. The collation of the ProductName column
D. The data type of the ProductName column

Answer: C

70-465|You need to recommend a backup solution for the user databases

Question: 15

You have a server that has SQL Server 2012 installed. The server contains 100 user databases.
You need to recommend a backup solution for the user databases. The solution must meet the following requirements:

Perform a transaction log backup every hour.
Perform a full backup of each database every week.
Perform a differential backup of each database every day.
Ensure that new user databases are added automatically to the backup solution.

What should you recommend?

More than one answer choice may achieve the goal. Select the BEST answer.

A. A maintenance plan
B. SQL Server Agent jobs
C. Policy-Based Management
D. A Data Definition Language (DDL) trigger

Answer: A

70-465 | During performance testing, you discover that database INSERT operations against the Inventory table are slow.

Question : 14

During performance testing, you discover that database INSERT operations against the Inventory table are slow.You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations.What should you recommend?

A. Partition the nonclustered index.
B. Partition the Inventory table.snapshot replication
C. Create a column store index.Master Data Services
D. Drop the clustered index.change data capture

Answer : A

70-465 | You administer a SQL Server 2014 instance.Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.

Question : 13

You administer a SQL Server 2014 instance.Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.You want to write a query of the system views that will report the following:
– Number of users that have a connection to the server
– Whether a user’s connection is active
– Whether any connections are blocked
– What queries are being executed
– Whether the database restore is still executing and, if it is, what percentage of the restore is complete.Which system objects should you use in your query to best achieve this task?

A. sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects
B. sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects
C. sys.sysprocesses, sys.dm_exec_query_text, sys.objects
D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text

Answer : D

70-465 Sample Question : 12

Question : 12

You need to recommend a feature to support your backup solution.What should you include in the recommendation?

A. Transparent Data Encryption (TDE)
B. Column-level encryption
C. An NTFS file permission
D. A Secure Sockets Layer (SSL)

Answer : A

70-465 Sample Question : 11

Question : 11

You need to recommend a solution to allow application users to perform tables. The solution must meet the business requirements.What should you recommend?

A. Create a Policy-Based Management Policy.
B. Create a user-defined database role and add users to the role.
C. Create stored procedures that use EXECUTE AS clauses.
D. Create functions that use EXECUTE AS clauses.

Answer : D

70-465 Sample Question : 10

Question : 10

During performance testing, you discover that database INSERT operations against the Inventory table are slow.You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations.What should you recommend?

A. Partition the nonclustered index.
B. Partition the Inventory table.snapshot replication
C. Create a column store index.Master Data Services
D. Drop the clustered index.change data capture

Answer : A

70-465 Sample Question : 9

Question : 9

You are the new database administrator for a SQL Server 2014 instance. You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring. You want to set up monitoring to see if query performance is being affected. You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.What should you do?

A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.
B. Use the missing_column_statistics extended event.
C. Query the sys.statistics system view to see all cases where the statistics were last needed.
D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.

Answer : B

70-465 Sample Question : 8

Question : 8

You manage a SQL Server 2014 instance that contains a database named DB1. sers report that some queries to DB1 take longer than expected. Although most queries run in less than one second,some queries take up to 20 seconds to run. You need to view all of the performance statistics for each database file.Which method should you use?

A. Query the sys.dm_os_tasks dynamic management view.
B. Query the sys.dm_os_performance_counters dynamic management view.
C. Query the sys.dm_io_virtual_file_stats dynamic management function.
D. Examine the Data File I/O pane in Activity Monitor.

Answer : C

70-465 Sample Question : 7

Question : 7

You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media. You plan to synchronize SQLDev and SQLProd.You need to recommend a solution that meets the following requirements:
– The database schemas must be synchronized from SQLDev to SQLProd.
– The database on SQLDev must be deployed to SQLProd by using a package.
– The package must support being deployed to Windows Azure SQL Database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

A. A database snapshot
B. SQL Server Integration Services (SSIS)
C. Change data capture
D. A data-tier application

Answer : B

70-465 Sample Question : 6

Question : 6

Your network contains an Active Directory domain that has two groups named Group1 and Group2.The domain contains two SQL Server instances named SQLDev and SQLProd.Each SQL Server instance has access to various storage media.The SQL Server instances have a database that contains a table named Table1.Table1 contains a column named Column1.The value for Column1 can be either Value1 or Value2.You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.What should you recommend?

A. A dynamic management view
B. Filegroups
C. Snapshot isolation
D. User-defined views

Answer : D

70-465 Sample Question : 5

Question : 5

You have a database named DB1.You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next. Occasionally, the identifying value must be reset to its initial value. You need to design a mechanism to hold the identifying values for the stored procedure to use.What should you do?More than one answer choice may achieve the goal. Select the BEST answer.

A. Create a sequence object that holds the next value in the sequence.Retrieve the next value by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed.
B. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure.Increment the sequence object to the next value by using an ALTER SEQUENCE statement.Reset the value as needed by using a different ALTER SEQUENCE statement.
C. Create a fourth table that holds the next value in the sequence. At the end each transaction, update the
value by using the stored procedure. Reset the value as needed by using an UPDATE statement.
D. Create an identity column in each of the three tables.Use the same seed and the same increment for each table. Insert new rows into the tables by using the stored procedure.Use the DBCC CHECKIDENT command to reset the columns as needed.

Answer : A

70-465 Sample Question : 4

Question : 4

You have a SQL Server 2014 instance named SQL1. SQL1 creates error events in the Windows Application event log. You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log.Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)

A. A policy
B. A maintenance plan
C. An alert
D. A job
E. A trigger

Answer : DE

70-465 Sample Question : 3

Question : 3

You have a SQL Server instance on a server named Server1.
You need to recommend a solution to perform the following tasks every week:
– Rebuild the indexes by using a new fill factor.
– Run a custom T-SQL command.
– Back up the databases.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

A. A trigger
B. An alert
C. A maintenance plan
D. Windows PowerShell
E. A system policy

Answer : C

70-465 Sample Question : 2

Question : 2

You have a SQL Server instance on a server named Server1. The safer , easier way to help you pass any IT exams.You need to recommend a solution to perform the following tasks every week:
– Rebuild the indexes by using a new fill factor.
– Run a custom T-SQL command.
– Back up the databases.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

A. A trigger
B. An alert
C. A maintenance plan
D. Windows PowerShell
E. A system policy

Answer : C

70-465 Sample Question : 1

Question : 1

You create a stored procedure that retrieves all of the rows from a table named Table1. You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Table1 simultaneously.What should you recommend?

A. Snapshot isolation
B. A database snapshot
C. Filegroups
D. Indexes

Answer : A

Search Words: 70-465 Questions Answers real exam questions answers practice test braindumps, free Q&A online dumps download and free 70-465 Questions Answers discount coupon code available.

Leave a Comment.

7 − 2 =