Transcript [OUTER] JOIN table_or_view ON

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.
 Use SQL Server monitoring tools like
SQL Profilers, Enterprise Manager etc.
Optimization
 Goals
– Optimizing response time
– Optimizing throughput
– Maximizing availability
 What to watch
–
–
–
–
Database, log, and file sizes
Data consistency issues
User activity
Server performance
Performance bottlenecks
 Hardware bottlenecks
– Disk throughput
– Memory usage
– Processor usage
 SQL Server bottlenecks
– Locking conflicts
– Resource contention
– tempdb activity
Key factors






SQL Server configuration and activity
Database design and implementation
End-user applications
Network throughput
Server hardware
Server operating system
Monitoring Tools






System Monitor
Task Manager
SQL Profiler
Query Analyzer
Enterprise Manager
DBCC commands
Using System Monitor
 Gathering baseline values
 Monitoring Windows NT/Windows
2000 counters
 Isolating bottlenecks
 Comparing server performance
 Monitoring critical resources
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
System Monitor
Performance counters
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
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
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
Event Viewer
 Event Viewer logs
– Security log
– System log
– Application log
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
Trace General properties
Trace Events properties
Trace Data Columns
properties
Trace Filters properties
Standard templates (Profiler)








SQLProfilerSP_Counts
SQLProfilerStandard
SQLProfilerTSQL
SQLProfilerTSQL_Duration
SQLProfilerTSQL_Grouped
SQLProfilerTSQL_Replay
SQLProfilerTSQL_SPs
SQLProfilerTuning
Class Assignments
 Page 532
 Page 537
 Take a break
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
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]
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]]
Current activity and Locking
Process ID - all current conn
Locks / Process ID - SPID’s
Locks / Object - info on Locks
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
Lock types
 Basic locks
– Shared
– Exclusive
 Special locks
–
–
–
–
–
–
Intent
Intent exclusive
Shared with intent exclusive
Update
Schema
Bulk update
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]
Clearing deadlocks
1. The deadlocked transaction is rolled
back.
2. The application initiating the
transaction is notified of the rollback
with a message number 1205.
3. The transaction’s current request is
cancelled.
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.
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.
Types of Joins
 INNER JOIN
 OUTER JOIN
– LEFT OUTER JOIN
– RIGHT OUTER JOIN
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)
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)
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)
Outer joins
 FULL OUTER JOIN (FULL JOIN)
– All rows from both tables are returned by
a FULL OUTER JOIN clause.
 FULL JOIN syntax:
SELECT select_list
FROM table_or_view
FULL [OUTER] JOIN table_or_view
ON (join_condition)
Class Assignments
 Page 543
 Try the Join examples included in the
word document
 Use Query Analyzer for this
 Take a break
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?