Microsoft SQL Server Administration for SAP SQL Server Architecture
Overview
SQL Server Architecture SQL Server with SAP Performance Monitoring and Tuning Administration and Troubleshooting Database Backup and Restore
Database Server
Results
Transact-SQL
Application Client SQL Server Relational Database Management System
SQL Server Service
Client / Server
Client
Application Database Interface Network Library
Server
SQL Server Open Data Services Network Library
Tabular Data Stream (TDS)
SQL Server Client Network Utility
SQL Server Network Utility
SQL Server Client Tools
SQL Enterprise Manager SQL Query Analyzer Service Manager Server & Client Network Utility Performance Monitor SQL Profiler Index Tuning Wizard Data Transformation Services
8
Configuring SQL Enterprise Manager
Threads and Fibers
SQL Server process maintains pool of threads to process client requests SQL Server can also be configured to use lightweight pooling(fibers) max worker threads
10
Databases
System Databases
master
model
tempdb
msdb
distribution
pubs
northwind
VIE
User Databases
11
Database Files
Database
Data (file)
.mdf or .ndf
Log (file)
.ldf
Tables, Indexes
Data Page (8 KB) Max row size = 8092 bytes
Extent (8 contiguous 8-KB pages)
12
Data Files
Filegroups Data & log files autogrow Proportional fill Pages and Extents
GAM SGAM PFS IAM
13
In row text
Storage Engine
Data Cache & Proc cache
Cache hit ratio / free buffers
Transaction log Lazy Writer Lock Manager Log Writer Checkpoint / log truncation Background task
14
Hardware Configuration
Balance and push the bottleneck to expensive component CPU and L2 cache Memory
Hard and Soft Paging
Support of SAN Network Interfaces cLan from GigaNet Servernet II from Compaq 20% perf gain
System Area Networks
15
I/O components - Disks
How fast is a disk?
Depends on the type of I/O workload
SQL Server reads 8K random (page reads)
Singleton (or few) select/update/insert Lazy writer Checkpoint processing
SQL Server reads 64K sequential (extent reads)
Table and index scans Backup, index creation, DBCC, etc
16
I/O components - Disks
Sequential I/O Random I/O
Max throughput ~10 MB/sec Max sequential transfers/sec ~150 Max random transfers/sec ~100 OLTP constrained by transfers/sec DSS constrained by MB/sec I/O sec 8K 64K Realistic transfer rates:
150 100 1.2 MB/sec 0.8 MB /sec 9.4 MB/sec 6.3 MB/sec
17
Hardware Configuration
Disk
Raid 0, 10, 5 Pagefile, tempdb, log and data files location h/w bandwidth limitations Disk queue length Write caching controllers Latch wait time
18
Row Identifiers
Base table organizations
Fixed Row Identifier (RID)
Fixed RID
Rows identified by RID (8 bytes consisting of File#, Page#, Slot#) RIDs do not change unless a row is deleted and reinserted elsewhere RIDs can be reused once the transaction that deletes a row commits RIDs used as lock resource
19
Row Identifiers
Base table organizations
Clustered index
Clustered index
Rows identified by unique clustering key Clustering key used as lock resource
20
Secondary Indexes
Secondary index
Secondary indices
Key
Locator
Points to either
Index terms consist of key, locator pairs Locators are stable with respect to base table organization (unlike 6.X) Locator may be either RID (Fixed RID) or clustering key (clustered index) Unique key/locator pair used as lock resource
21
Secondary index lookup on key Adams
Adams
Index Changes
6
Adams
Lewis
Smith
11
Lewis Dan
... 6
Adams Kim
... 11
Smith Ken
Clustering index data Key Locator (clustering Key)
22
Index on Views
Create View with SCHEMABINDING Create Unique Clustered Index on View All Nonclustered Indexes are dropped if Clustered Index is dropped Indexed Views are maintained automatically SET Options EXPAND VIEWS and NOEXPAND
23
Referring to SQL Server Objects
Fully Qualified Names Partially Specified Names
Server defaults to local server
Database defaults to current database
Owner defaults to the user name in the database
pubs.dbo.authors
24
System Tables
Store Information (Metadata) About the System and Database Objects Database Catalog Stores Metadata About a Specific Database System Catalog Stores Metadata About the Entire System and All Other Databases
25
Metadata Retrieval
System Stored Procedures
sp_help authors
System Functions
select getdate()
Information Schema Views
select * FROM INFORMATION_SCHEMA.TABLES
26
Common System Procedures and System Tables
System stored procedures
sp_help, sp_helpindex sp_helpdb, sp_helpfile/filegroup sp_who, sp_lock sp_depends, sp_recompile sp_readerrorlog
System tables
sysobjects, sysindexes, sysprocesses
Object_name/id, index_col
System functions
27
Login Authentication, Database User Accounts
Windows NT Group User Windows NT Verifies Password
SQL Server Assigns Logins to User Accounts and Roles
Windows NT Database User Account
SQL SQL Server
SQL Server Login Account
SQL Server Verifies Password
Database Role
28
29
Server Properties
30
Server Properties
31
Database Properties
32
Installing SQL Server
Installation Path Character Set and Sort Order Collations Network Support SQL Server Service Account Licensing Mode Instance Name
SQL Server 2000 only
Only Clients needs to be installed on Application Server
33
Lab work
Install SQL Server Start and Stop SQL Server Service Register your server with SQL Enterprise Manager Search a Topic in Books Online
Online help for Create Table
Create a Table with Clustered Index Check at the Server and Database Properties
34
Summary
Write a summary for this module
35
