Download Microsoft.70-764.PassLeader.2018-11-23.239q.tqb
| Vendor: | Microsoft |
| Exam Code: | 70-764 |
| Exam Name: | Administering a SQL Database Infrastructure |
| Date: | Nov 23, 2018 |
| File Size: | 5 MB |
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 are examining information about users, sessions, and processed in an on-premises Microsoft SQL Server Database Engine instance.
You need to return information about processes that are not idle, that belong to a specific user, or that belong to a specific session.
What should you use?
- Activity Monitor
- sp_who3 stored procedure
- SQL Server Management Studio (SSMS) Object Explorer
- SQL Server Data Collector
- SQL Server Data Tools (SSDT)
- SQL Server Configuration Manager
Correct answer: B
Explanation:
Use sp_who3 to first view the current system load and to identify a session of interest. You should execute the query several times to identify which session id is most consuming teh system resources. Parameters sp_who3 null - who is active; sp_who3 1 or 'memory' - who is consuming the memory; sp_who3 2 or 'cpu' - who has cached plans that consumed the most cumulative CPU (top 10); sp_who3 3 or 'count' - who is connected and how many sessions it has; sp_who3 4 or 'idle' - who is idle that has open transactions; sp_who3 5 or 'tempdb' - who is running tasks that use tempdb (top 5); and, sp_who3 6 or 'block' - who is blocking. Use sp_who3 to first view the current system load and to identify a session of interest. You should execute the query several times to identify which session id is most consuming teh system resources.
Parameters
sp_who3 null - who is active;
sp_who3 1 or 'memory' - who is consuming the memory;
sp_who3 2 or 'cpu' - who has cached plans that consumed the most cumulative CPU (top 10);
sp_who3 3 or 'count' - who is connected and how many sessions it has;
sp_who3 4 or 'idle' - who is idle that has open transactions;
sp_who3 5 or 'tempdb' - who is running tasks that use tempdb (top 5); and,
sp_who3 6 or 'block' - who is blocking.
Question 2
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 observe that several indexes are fragmented.
You need to rebuild the indexes.
What should you use?
- Activity Monitor
- sp_who3 stored procedure
- Object Explorer in the SQL Server Management Studio (SSMS)
- SQL Server Data Collector
- SQL Server Data Tools (SSDT)
- SQL Server Configuration Manager
Correct answer: C
Explanation:
How to: Rebuild an Index (SQL Server Management Studio)To rebuild an index In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases, expand the database that contains the table with the specified index, and then expand Tables. Expand the table in which the index belongs and then expand Indexes. Right-click the index to rebuild and then click Rebuild. To start the rebuild operation, click OK. References: https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx How to: Rebuild an Index (SQL Server Management Studio)
To rebuild an index
- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- Expand Databases, expand the database that contains the table with the specified index, and then expand Tables.
- Expand the table in which the index belongs and then expand Indexes.
- Right-click the index to rebuild and then click Rebuild.
- To start the rebuild operation, click OK.
References: https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx
Question 3
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 need to examine information about logins, CPU times, and Disk I/O on a particular database in Microsoft Azure.
What should you use?
- Activity Monitor
- sp_who3 stored procedure
- SQL Server Management Studio (SSMS) Object Explorer
- SQL Server Data Collector
- SQL Server Data Tools (SSDT)
- SQL Server Configuration Manager
Correct answer: A
Explanation:
Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server. Activity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries.The Activity User Tasks Pane shows information for active user connections to the instance, including the following column:* Login: The SQL Server login name under which the session is currently executing.The Recent Expensive Queries Pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds, including the following column:* CPU (ms/sec): The rate of CPU use by the queryReferences: https://technet.microsoft.com/en-us/library/cc879320(v=sql.105).aspx Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server.
Activity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries.
The Activity User Tasks Pane shows information for active user connections to the instance, including the following column:
* Login: The SQL Server login name under which the session is currently executing.
The Recent Expensive Queries Pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds, including the following column:
* CPU (ms/sec): The rate of CPU use by the query
References: https://technet.microsoft.com/en-us/library/cc879320(v=sql.105).aspx
