Lab 9.1: Database Monitoring & Troubleshooting with SQL Server

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 1 of 14 School of Workforce Development, Continuing Education, and Online Learning Network Technical Support (# 1470) Course Information Name SQL Server Code INFO1085 - Section 1 Faculty Information Professor/Instructor Firas Chahine, Ph.D. Email fchahine@conestogac.on.ca Office Number 113 Office Hours By appointment only Lab 9.1 Working with database monitoring and troubleshooting tools: 1- Preparations for this lab on DB1 server: a. Create database ASSIGNMENT9: CREATE DATABASE ASSIGNMENT9; b. Create tables EmployeePay, OrderDetail and OrderHeader from copying contents from AdventureWorks2016: USE ASSIGNMENT9; SELECT * INTO EmployeePay FROM AdventureWorks2016.HumanResources.EmployeePayHistory SELECT * INTO OrderDetail FROM AdventureWorks2016.Sales.SalesOrderDetail SELECT * INTO OrderHeader FROM AdventureWorks2016.Sales.SalesOrderHeader c. Create database user RogueAdmin with Sysadmin access to your instance DB1\SQLSVR01: CREATE LOGIN RogueAdmin WITH PASSWORD = 'Secret55!';

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 2 of 14 CREATE USER RogueAdmin FOR LOGIN RogueAdmin; ALTER SERVER ROLE SysAdmin ADD MEMBER RogueAdmin; 2- Working with Performance Monitor (perfmon.exe): a. Launch Performance Monitor and observe the following performance counters live to determine the health of your DB1 server: i. Processor Information: % Processor Time - measures how busy the processor is 1. Sustained values over 70% during normal activity should be investigated ii. System: Processor Queue Length - displays a count of the number of processes that are waiting for the processor to finish processing the active request iii. Memory: Available MBytes - measures the amount of physical memory that is available for allocation iv. Memory: Pages/sec - measures the activity transferring data between the physical disk and memory v. Logical Disk: Avg. Disk sec/Read - measures disk throughput 1. A high number indicates the disk if operating efficiently vi. Logical Disk: Avg. Disk sec/Write - measures disk throughput for processes writing data to the disk vii. Logical Disk: Avg. Disk Queue Length - provides a count of the number of requests waiting to read or write data from the physical disk

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 3 of 14 b. Users are complaining that performance of their applications degrade between 4-5pm on Sundays. From your DB1 server, create a Data Collector Set and schedule it and collect logs during that time: Data Collector Sets > User Defined > [Right click] New > Data Collector Set i. Name: DB1 Troubleshooting 4-5pm Sunday ii. Create manually (Advanced) iii. Create data logs: 1. [Check] Performance Counter iv. Add 7 performance counters below: 1. Processor > % Processor Time 2. System > Processor Queue Length 3. Memory > Available MBytes 4. Memory > Pages/sec 5. Logical Disk > Avg. Disk sec/Read 6. Logical Disk > Avg. Disk sec/Write 7. Logical Disk > Avg. Disk Queue Length v. Change Sample internal to 2 seconds vi. Choose location of log file (C:\Users\SQLAdmin\Desktop\DB1 Troubleshooting 4-5pm Sunday) vii. Set schedule to run at 4pm for 1 hour on a Sunday

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 4 of 14 3- Working with SQL Profiler: a. Your manager suspects that there is a rogue DBA and you are tasked with monitoring his activities on the database and collecting evidence of any malicious activities: Launch SQL Server Profiler > File > New Trace… i. > General: 1- Connect to your SQL instance 2- Trace name: RogueDBA Trace 3- [Check] Save to file and select location (C:\Users\SQLAdmin\Desktop\SQL Profiler\ RogueDBA Trace.trc) ii. > Event Selection > [Check] Show all columns iii. > Event Selection > Columns Filter > 1- LoginName = RogueAdmin iv. Run trace and leave it collecting events b. Become a rogue DBA, login to DB1\SQLSVR01 using RogueAdmin and attempt to modify sensitive records: Using PowerShell: SQLCMD.EXE -S DB1\SQLSVR01 -U RogueAdmin -P 'Secret55!' USE ASSIGNMENT9 GO SELECT * FROM EmployeePay WHERE BusinessEntityID = 10 GO UPDATE EmployeePay SET Rate = 65.80 WHERE BusinessEntityID = 10 AND Rate = 42.4808 GO

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 5 of 14 c. Go back to SQL Profiler and observe the rogue DBA and see if you can find any evidence of malicious acts: i. When done, stop the trace. d. Users are complaining that performance of their applications degrade between 4-5pm on Sundays. From Performance Monitor logs, you can see that during that time DB1 server experiences a jump in a number of performance counters. You suspect that this might be related to activities on the SQL database instance. You decide to monitor SQL Server events using SQL Profiler during that period. i. Assume it is Sunday 4pm. Run the Data Collector set you create manually: Performance Monitor > Data Collector Sets > User Defined > [Right-click] DB1 Troubleshooting 4-5pm Sunday > Start ii. Run a trace on SQL Profiler as follows: Launch SQL Server Profiler > File > New Trace… 1- > General: - Connect to your SQL instance

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 6 of 14 - Trace name: DB1 Troubleshooting 4-5pm Sunday 2- [Check] Save to file and select location (C:\Users\SQLAdmin\Desktop\SQL Profiler\ DB1 Troubleshooting 4-5pm Sunday.trc) 3- > Event Selection > [Check] Show all columns 4- > Event Selection > Columns Filter > - DatabaseName = ASSIGNMENT9 5- Run trace and leave it collecting events iii. Let’s simulate some SQL activities that for some reason (could be scheduled weekly reports for management) takes place during that period (select and run all commands together as a batch): USE ASSIGNMENT9; SELECT a.SalesOrderID, a.Status, a.OnlineOrderFlag, a.AccountNumber, a.CustomerID, a.ShipToAddressID, a.CreditCardID, b.LineTotal FROM OrderHeader a JOIN OrderDetail b ON b.SalesOrderID = a.SalesOrderID SELECT a.SalesOrderID, a.Status, a.OnlineOrderFlag, a.AccountNumber, a.CustomerID, a.ShipToAddressID, a.CreditCardID, b.LineTotal FROM OrderHeader a JOIN OrderDetail b ON b.SalesOrderID = a.SalesOrderID WHERE a.SalesOrderID = '44403' SELECT a.SalesOrderID, a.Status, a.OnlineOrderFlag, a.AccountNumber, a.CustomerID, a.ShipToAddressID, a.CreditCardID, b.LineTotal FROM OrderHeader a JOIN OrderDetail b ON b.SalesOrderID = a.SalesOrderID WHERE a.SalesOrderID = '44404'

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 7 of 14 SELECT a.SalesOrderID, a.Status, a.OnlineOrderFlag, a.AccountNumber, a.CustomerID, a.ShipToAddressID, a.CreditCardID, b.LineTotal FROM OrderHeader a JOIN OrderDetail b ON b.SalesOrderID = a.SalesOrderID WHERE a.SalesOrderID = '44405' SELECT a.SalesOrderID, a.Status, a.OnlineOrderFlag, a.AccountNumber, a.CustomerID, a.ShipToAddressID, a.CreditCardID, b.LineTotal FROM OrderHeader a JOIN OrderDetail b ON b.SalesOrderID = a.SalesOrderID WHERE a.SalesOrderID = '44406' SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM OrderDetail SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM OrderDetail WHERE SalesOrderID='44403' SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM OrderDetail WHERE SalesOrderID='44404' SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM OrderDetail WHERE SalesOrderID='44405' SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM OrderDetail WHERE SalesOrderID='44406' iv. Stop both the SQL Profiler trace and Performance Monitor Data Collector. v. Now it is time to corelate your SQL Profiler trace against the Performance Monitor log to see if your suspicion that certain SQL activities are the culprit causing performance degradation: 1- Launch SQL Server Profiler > File > Open > Trace File > [Select] DB1 Troubleshooting 4-5pm Sunday.trc

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 8 of 14 2- Using same SQL Server Profile > File > Import Performance Data > [Select] logs for Data Collector (DB1 Troubleshooting 4-5pm Sunday) > [Select] All counters 3- Notice that around the time the SQL batch was executing our CPU and other counters were reaching 100%. This is our culprit. 4- Working with Database Engine Tuning Advisor: a. We know the SQL batch that runs every Sunday between 4-5pm that produces management reports is the culprit for the performance degradation. You need to present your findings to your manager along with recommendations on how to resolve the issue. i. Since the issue is SQL query related, we can use Database Engine Tuning Advisor to validate the tables and databases being used. Launch Database Engine Tuning Advisor > 1- Connect to your SQL instance 2- Session name: DB1 Troubleshooting 4-5pm Sunday

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 9 of 14 3- Workload: File > [Select] DB1 Troubleshooting 4-5pm Sunday.trc 4- Database for workload analysis: ASSIGNMENT9 5- Select databases and tables to tune: ASSIGNMENT9 6- Start Analysis ii. [ ASSESSMENT ]: What are the recommendations that Database Engine Tuning Advisor suggested? iii. [ ASSESSMENT ]: How much of an improvement would these suggestions provide?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 10 of 14 iv. Apply the recommendations from Database Engine Tuning Advisor: Database Engine Tuning Advisor > Actions > Apply Recommendations 5- Working with and managing deadlocks and blocking locks: a. Execute an SQL transaction that selects OrderDetail and updates OrderHeader tables in ASSIGNMENT9 database: USE ASSIGNMENT9 DECLARE @SalesOrderId BIGINT; DECLARE @SumValue BIGINT; SET @SalesOrderId = 43659; BEGIN TRANSACTION SELECT @SumValue = SUM(LineTotal) -- we will round up to next int FROM dbo.OrderDetail WHERE SalesOrderId = @SalesOrderId UPDATE dbo.OrderHeader SET SubTotal = @SumValue , TaxAmt = @SumValue * .21 WHERE SalesOrderId = @SalesOrderId; b. Execute an SQL transaction that updates OrderDetail table (LineTotal) in ASSIGNMENT9 database: USE ASSIGNMENT9 DECLARE @SalesOrderId BIGINT; DECLARE @SumValue BIGINT; SET @SalesOrderId = 43659; BEGIN TRANSACTION UPDATE dbo.OrderDetail SET LineTotal = 0 WHERE SalesOrderId = @SalesOrderId; c. [Again] Execute an SQL transaction that updates OrderDetail table (LineTotal) in ASSIGNMENT9 database: USE ASSIGNMENT9

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 11 of 14 DECLARE @SalesOrderId BIGINT; DECLARE @SumValue BIGINT; SET @SalesOrderId = 43659; BEGIN TRANSACTION UPDATE dbo.OrderDetail SET LineTotal = 0 WHERE SalesOrderId = @SalesOrderId ; d. [Again] Execute an SQL transaction that selects OrderDetail and updates OrderHeader tables in ASSIGNMENT9 database: USE ASSIGNMENT9 DECLARE @SalesOrderId BIGINT; DECLARE @SumValue BIGINT; SET @SalesOrderId = 43659; BEGIN TRANSACTION SELECT @SumValue = SUM(LineTotal) -- we will round up to next int FROM dbo.OrderDetail WHERE SalesOrderId = @SalesOrderId UPDATE dbo.OrderHeader SET SubTotal = @SumValue , TaxAmt = @SumValue * .21 WHERE SalesOrderId = @SalesOrderId e. Now commit the above transactions and observe the deadlock: COMMIT TRANSACTION i. Open a New Query window and run: SELECT * FROM OrderDetail;

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 12 of 14 ii. Congratulations, you have caused a deadlock! iii. Let’s identify the process IDs of the deadlocks: 1. Open a New Query window and run: SP_WHO2; 2. Locate the blocking process ID (BlkBy): iv. Identify which database objects are causing the deadlock: SP_LOCK; 1. Locate the blocking process ID, database ID (dbid), Object ID (ObjId), object type (Type), and block type (Mode):

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 13 of 14 - Blocking Type: i. S/IS = Shared/Intent Shared ii. X/IX = Exclusive/Intent Exclusive - Object Type: i. TAB = Table 2. Identify the blocking database objects (use Object IDs and Database IDs from above): SELECT name, type_desc FROM sys.objects WHERE Object_id = 613577224 OR Object_id = 597577167 ; SELECT Name from Sys.Databases WHERE Database_id = 19;

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 14 of 14 f. Resolve the deadlock by killing the process that is causing it: SSMS > [Right-click] SQL Instance > Activity Monitor > Processes > [Right- click] Blocking Session ID > Kill Process: g. Return to your suspended query, what do you see now?

INFO1085 (Winter 2022)_Lab 9.1

Related Documents