SQL LAB @ HOME 12 ~ NIIT POST

sql lab @ home 12

1.  You are a database administrator at AdventureWorks, Inc. You have been notified by the users

that the database works very slowly during peak business hours. You have decided to monitor the

T-SQL statements to identify the statements that are taking time to execute. How will you monitor  the server

ANS.

 open sql server profiler

connect to the server dialog box is displayed

click at New Trace 

connect to the sql server

the trace properties window is displayed

type the Name of the trace in the Trace name in the text box

select the save to file: check box

 clear the existing connection check box

click at run

and then click at YES button 

2.You are a database administrator at AdventureWorks, Inc. As a part of regular maintenance, you need to check the following details:

Current sessions
File space usage
Current transactions
Current connections

How will you perform this task?

ANS.

SELECT * FROM sys.dm_exec_sessions


SELECT * FROM sys.dm_db_file_space_usage


SELECT * FROM sys.dm_tran_current_transaction


SELECT * FROM sys.dm_exec_connections

---

3.You are a database administrator at AdventureWorks, Inc. The following statements are executed to check the state of I/O operations:

Select * sys.dm_db_backup_tapes
Select * from sys.dm_db_pending_io_requests
Select * from sys.dm_db_cluster_shared_drives
Select * from sys.dm_db_virtual_files_stats

However, the preceding statements generated an error. Identify the error and provide the solution.

ANS.

SELECT * FROM sys.dm_io_backup_tapes
SELECT * FROM sys.dm_io_pending_io_requests



SELECT * FROM sys.dm_io_cluster_shared_drives
SELECT * FROM sys.dm_io_virtual_file_stats
(DB_ID (N'AdventureWorks2012'), NULL);

---

4.You are a database administrator at AdventureWorks, Inc. You want to check the information about the operating system resources that are specific to SQL Server. How will you do this?

ANS.

SELECT * FROM sys.dm_os_buffer_descriptors
SELECT * FROM sys.dm_os_memory_pools



SELECT * FROM sys.dm_os_child_instances
SELECT * FROM sys.dm_os_sys_info



SELECT * FROM sys.dm_os_loaded_modules
SELECT * FROM sys.dm_os_tasks



SELECT * FROM sys.dm_os_memory_clerks
SELECT * FROM sys.dm_os_workers

---

5.You are a database administrator at AdventureWorks, Inc. You want to get the information regarding the amount of disk activity generated for the Employee table. How will you perform this task?

ANS.

SET STATISTICS IO ON
SELECT * FROM HumanResources.Employee
SET STATISTICS IO OFF

---

6. You are a database administrator at AdventureWorks, Inc. and want to generate an effective execution plan on the Employee table. For this, create a statistics on the Employee table. In addition, show the list of all the statistics maintained on the Employee table?

ANS.

CREATE STATISTICS EmployeeInfo
ON HumanResources.Employee
(EmployeeID,ContactId,BirthDate,MaritalStatus)


sp_helpstats 'HumanResources.Employee', 'ALL'

---

7.An employee having contact ID as 2 got married and her last name has been changed to Smith. You have to make the required changes in the Contact table and track these changes by using the CDC method. (Use the AdventureWorks database)

ANS.

UPDATE Person.Contact
SET Lastname = 'Smith'
WHERE ContactID = 2


SELECT * FROM cdc.Person_Contact_CT

---

8.An employee having contact ID as 4 has changed his title from (Sr.) to (Mr.). You need to update its record into Contact table and track these changes by using the track changing method. (Use the AdventureWorks database)

ANS.

ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING



UPDATE Person.Contact
SET Title = 'Mr.'
WHERE ContactID = 4



SELECT * FROM CHANGETABLE
(CHANGES Person.Contact, 0) AS CT