Download Administering a SQL Database Infrastructure.70-764.PassGuide.2019-01-18.242q.tqb
| Vendor: | Microsoft |
| Exam Code: | 70-764 |
| Exam Name: | Administering a SQL Database Infrastructure |
| Date: | Jan 18, 2019 |
| File Size: | 5 MB |
How to open VCEX files?
Files with VCEX extension can be opened by ProfExam Simulator.
Discount: 20%
Demo Questions
Question 1
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have an on-premises server that runs Microsoft SQL Server 2016 Standard Edition.
You need to identify missing indexes.
What should you use?
- Activity Monitor
- Sp_who3
- SQL Server Management Studio (SSMS) Object Explorer
- SQL Server Data Collector
- SQL Server Data Tools (SSDT)
- SQL Server Configuration Manager
Correct answer: D
Explanation:
Data Collector can gather performance information from multiple SQL Server instances and store it in a single repository. It has three built-in data collecting specifications (data collectors) designed to collect the most important performance metrics. The information collected by default is about disk usage, query statistics, and server activity. The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance. Missing indexes can be found with the execution plans. References: https://www.sqlshack.com/sql-server-performance-monitoring-data-collector/ Data Collector can gather performance information from multiple SQL Server instances and store it in a single repository. It has three built-in data collecting specifications (data collectors) designed to collect the most important performance metrics. The information collected by default is about disk usage, query statistics, and server activity.
The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance.
Missing indexes can be found with the execution plans.
References: https://www.sqlshack.com/sql-server-performance-monitoring-data-collector/
Question 2
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table.
You need to monitor WingDB and gather information for troubleshooting issues.
What should you use?
- sp_updatestats
- sp_lock
- sys.dm_os_waiting_tasks
- sys.dm_tran_active_snapshot_database_transactions
- Activity Monitor
Correct answer: B
Explanation:
The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are happening on your system. This procedure returns much of its information from the syslock info in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests. Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are happening on your system. This procedure returns much of its information from the syslock info in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests.
Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.
sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql
Question 3
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
- Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
- Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.
You need to create a backup plan for Instance4.
Which backup plan should you create?
- Weekly full backups, nightly differential. No transaction log backups are necessary.
- Weekly full backups, nightly differential backups, transaction log backups every 5 minutes.
- Weekly full backups, nightly differential backups, transaction log backups every 12 hours.
- Weekly full backups, nightly differential backups, nightly transaction log backups.
Correct answer: B
Explanation:
From scenario: Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. The recovery point objective of Instancse4 is 60 minutes. RecoveryPoint Objectives are commonly described as the amount of data that was lost during the outage and recovery period.References: http://sqlmag.com/blog/sql-server-recovery-time-objectives-and-recovery-point-objectives From scenario: Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. The recovery point objective of Instancse4 is 60 minutes. RecoveryPoint Objectives are commonly described as the amount of data that was lost during the outage and recovery period.
References: http://sqlmag.com/blog/sql-server-recovery-time-objectives-and-recovery-point-objectives
HOW TO OPEN VCE FILES
Use VCE Exam Simulator to open VCE files
HOW TO OPEN VCEX AND EXAM FILES
Use ProfExam Simulator to open VCEX and EXAM files
ProfExam at a 20% markdown
You have the opportunity to purchase ProfExam at a 20% reduced price
Get Now!