1 / 41

Agenda for Today

Agenda for Today. Do Chapter 14 Final Project Review for Final. Monitoring and optimization. Monitoring is important for peak performance and activity Optimize database performance. Choices are Use Windows monitoring tools like System Monitor, Task Manager etc.

mendel
Download Presentation

Agenda for Today

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Agenda for Today • Do Chapter 14 • Final Project • Review for Final

  2. Monitoring and optimization • Monitoring is important for peak performance and activity • Optimize database performance. • Choices are • Use Windows monitoring tools like System Monitor, Task Manager etc. • Use SQL Server monitoring tools like SQL Profilers, Enterprise Manager etc.

  3. Optimization • Goals • Optimizing response time • Optimizing throughput • Maximizing availability • What to watch • Database, log, and file sizes • Data consistency issues • User activity • Server performance

  4. Performance bottlenecks • Hardware bottlenecks • Disk throughput • Memory usage • Processor usage • SQL Server bottlenecks • Locking conflicts • Resource contention • tempdb activity

  5. Key factors • SQL Server configuration and activity • Database design and implementation • End-user applications • Network throughput • Server hardware • Server operating system

  6. Monitoring Tools • System Monitor • Task Manager • SQL Profiler • Query Analyzer • Enterprise Manager • DBCC commands

  7. Using System Monitor • Gathering baseline values • Monitoring Windows NT/Windows 2000 counters • Isolating bottlenecks • Comparing server performance • Monitoring critical resources

  8. System Monitor - continued • System Monitor includes • Performance Objects which correspond to hardware resources - Table 14-1 has more info • Counters that are data items with specific information on a component • Instances are objects of the same type like databases, hard disks etc

  9. System Monitor

  10. Performance counters

  11. Memory Object Counters • Memory:Available Bytes • Memory:Pages/sec • Process:Page Faults/sec • Process:Working Set • SQLServer:Buffer Manager:Buffer Cache Hit Ratio • SQLServer:Buffer Manager:Total Pages • More info in Table 14-7

  12. I/O Object Counters • PhysicalDisk:% Disk Time • PhysicalDisk:Avg. Disk Queue Length • PhysicalDisk:Current Disk Queue Length • Memory:Page Faults/sec • SQLServer:Buffer Manager:Page Reads/sec • SQLServer:Buffer Manager:Page Writes/sec • Table 14-8

  13. Processor Object Counter • Processor:% Processor Time • Processor:% Privileged Time • Processor:% User Time • System:% Total Processor Time • System:Context Switches/sec • System:Processor Queue Length • More in Table 14-9

  14. Event Viewer • Event Viewer logs • Security log • System log • Application log

  15. SQL Profiler • Very Important Monitoring Tool • Primarily used to monitor stored procedures, connections,SQL batches • Uses a number of filters to isolated the problem • Profiler uses what called a trace to capture SQL Server events • The trace can be paused and replayed • Events that can be monitored are listed in Table 14-2

  16. Trace General properties

  17. Trace Events properties

  18. Trace Data Columns properties

  19. Trace Filters properties

  20. Standard templates (Profiler) • SQLProfilerSP_Counts • SQLProfilerStandard • SQLProfilerTSQL • SQLProfilerTSQL_Duration • SQLProfilerTSQL_Grouped • SQLProfilerTSQL_Replay • SQLProfilerTSQL_SPs • SQLProfilerTuning

  21. Class Assignments • Page 532 • Page 537 • Take a break

  22. Other SQL Server tools • System stored procedures • Table 14-4 has more info. Sp_who and sp_lock are important ones • DBCC commands • These act as the “database consistency checker” for SQL Server • Table 14-5 has more info on these commands

  23. DBCC commands • DBCC HELP • Help on syntax for DBCC commands • syntax: • DBCC HELP ('dbcc_statement') • DBCC CHECKALLOC • Checks the allocation and use of all pages in the specified database • syntax: • DCC CHECKALLOC ('database'[, NOINDEX | REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD]

  24. DBCC commands • DBCC CHECKDB syntax: DBCC CHECKDB ('database'[, NOINDEX | REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD] ) [WITH [ALL_ERRORMSGS | NO_INFOMSGS] [, TABLOCK][, ESTIMATEONLY][, PHYSICAL_ONLY] • DBCC CHECKFILEGROUP syntax: DBCC CHECKFILEGROUP ('filegroup'|filegroup_id[, NOINDEX] ) [WITH [ALLERRORMSGS | NO_INFOMSGS][, TABLOCK][,ESTIMATEONLY]]

  25. Current activity and Locking

  26. Process ID - all current conn

  27. Locks / Process ID - SPID’s

  28. Locks / Object - info on Locks

  29. Issuing locks • Locking ensures correct updates • SQL Server can issue locks for: • A row identifier (RID), locking a single row in a table • A table, which locks all data rows and indexes • A database, which is used when restoring a database • A page, locking an 8-KB data or index page • An extent, locking a contiguous group of pages during space allocation

  30. Lock types • Basic locks • Shared • Exclusive • Special locks • Intent • Intent exclusive • Shared with intent exclusive • Update • Schema • Bulk update

  31. Lock information – T-SQL • Use sp_who to retrieve information about users and processes. sp_who ['login_name'] • Use sp_lock to view lock information. sp_lock [spid]

  32. Clearing deadlocks • The deadlocked transaction is rolled back. • The application initiating the transaction is notified of the rollback with a message number 1205. • The transaction’s current request is cancelled.

  33. Joining tables • Joins are created through instructions in the SELECT clause. • Joins connect two or more tables by using a join operator. • Joins exist only for the duration of that query. • Joins do not make changes to any database tables.

  34. Join columns • The order of columns displayed is the order of columns chosen in the SELECT clause, with * choosing all columns in the order of the base table. • All tables being joined must be named in the FROM clause. • Create aliases for table names to reduce typing and make queries easier to read. • If NULLs are allowed in either connecting column, matches are not made with other NULLs.

  35. Types of Joins • INNER JOIN • OUTER JOIN • LEFT OUTER JOIN • RIGHT OUTER JOIN

  36. Inner joins • Only those rows that satisfy the join condition are displayed in the result. • Inner join syntax: SELECT select_list FROM table_or_view [INNER] JOIN table_or_view ON (join_condition)

  37. Outer joins • LEFT OUTER JOIN (LEFT JOIN) • All rows from the left table named in the LEFT OUTER JOIN clause are returned in the result set. • LEFT JOIN syntax: SELECT select_list FROM table_or_view LEFT [OUTER] JOIN table_or_view ON (join_condition)

  38. Outer joins • RIGHT OUTER JOIN (RIGHT JOIN) • All rows from the right table named in the RIGHT OUTER JOIN clause are returned in the result set. • RIGHT JOIN syntax: SELECT select_list FROM table_or_view RIGHT [OUTER] JOIN table_or_view ON (join_condition)

  39. Outer joins • FULL OUTER JOIN (FULL JOIN) • All rows from both tables are returned by a FULL OUTER JOINclause. • FULL JOIN syntax: SELECT select_list FROM table_or_view FULL [OUTER] JOIN table_or_view ON (join_condition)

  40. Class Assignments • Page 543 • Try the Join examples included in the word document • Use Query Analyzer for this • Take a break

  41. Final Test • Next week (5:30 - 7:30) • Chapter 8-14 • Open Book Open Notes • Straight Scale >90 = A, >80 = B and so on • In class material (slides and handout) • 100 Questions (multiple choice) • 200 points • Any questions?

More Related