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.
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?
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.
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
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.
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);
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);
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);
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
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
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
ANS.
SET STATISTICS IO ON
SELECT * FROM HumanResources.Employee
SET STATISTICS IO OFF
SET STATISTICS IO ON
SELECT * FROM HumanResources.Employee
SET STATISTICS IO OFF
SELECT * FROM HumanResources.Employee
SET STATISTICS IO OFF
ANS.
CREATE STATISTICS EmployeeInfo
ON HumanResources.Employee
(EmployeeID,ContactId,BirthDate,MaritalStatus)
sp_helpstats 'HumanResources.Employee', 'ALL'
CREATE STATISTICS EmployeeInfo
ON HumanResources.Employee
(EmployeeID,ContactId,BirthDate,MaritalStatus)
sp_helpstats 'HumanResources.Employee', 'ALL'
ON HumanResources.Employee
(EmployeeID,ContactId,BirthDate,MaritalStatus)
sp_helpstats 'HumanResources.Employee', 'ALL'
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)
UPDATE Person.Contact
SET Lastname = 'Smith'
WHERE ContactID = 2
SELECT * FROM cdc.Person_Contact_CT
SET Lastname = 'Smith'
WHERE ContactID = 2
SELECT * FROM cdc.Person_Contact_CT
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
ENABLE CHANGE_TRACKING
UPDATE Person.Contact
SET Title = 'Mr.'
WHERE ContactID = 4
SELECT * FROM CHANGETABLE
(CHANGES Person.Contact, 0) AS CT
4 Comments
I think you are sharing the information towards the respective details by keeping our environment as clean and neat. So i hope this post will be useful for many people. And please keep update like this.
ReplyDeleteHouse Cleaning Services Mumbai
Car Wash Mumbai
Sofa Cleaning Mumbai
This is extremely helpful info!! Very good work. It is very interesting to learn and easy to understood. Thank you for giving information. Please let us know and more information get post to link.
ReplyDeleteDigital marketing Training in Chennai
Hi, nice blog check out for employee scheduling software is a very effective thing used. activity management software helps to check the daily work and activity. Get in touch with us and grab the tool for your office. http://smartmanager.com.qa/app
ReplyDeleteHard Rock Hotel and Casino Las Vegas
ReplyDeleteHard Rock Hotel 수원 출장안마 and Casino Las Vegas · 태백 출장마사지 The Hotel · The Mirage Hotel and 전주 출장마사지 Casino · Treasure Island Hotel & 인천광역 출장마사지 Casino · The Hotel & Casino · Wynn Las Vegas 밀양 출장샵
USE ANONYMOUS PROFILE IF YOU WANT D'NT SHARE EMAIL