Microsoft SQL Server Administration for SAP SQL Server Architecture

1
Microsoft SQL Server Administration for SAPSQL
Server Architecture
2
Overview

• SQL Server Architecture
• SQL Server with SAP
• Performance Monitoring and Tuning
• Administration and Troubleshooting
• Database Backup and Restore

3
Database Server
Results
Transact-SQL
Application
SQL Server
Client
Relational Database Management System
4
SQL Server Service
5
Client / Server
6
SQL Server Client Network Utility
7
SQL Server Network Utility
8
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

9
Configuring SQL Enterprise Manager
10
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

11
Databases
System Databases
User Databases
12
Database Files
13
Data Files

• Filegroups
• Data log files autogrow
• Proportional fill
• Pages and Extents
• GAM
• SGAM
• PFS
• IAM
• In row text

14
Storage Engine

• Data Cache Proc cache
• Cache hit ratio / free buffers
• Transaction log
• Lazy Writer
• Lock Manager
• Log Writer
• Checkpoint / log truncation
• Background task

15
Hardware Configuration

• Balance and push the bottleneck to expensive
  component
• CPU and L2 cache
• Memory
• Hard and Soft Paging
• System Area Networks
• Support of SAN Network Interfaces
• cLan from GigaNet
• Servernet II from Compaq
• 20 perf gain

16
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

17
I/O components - Disks

• 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
• Realistic transfer rates

I/O sec 8K 64K
Sequential I/O 150 1.2 MB/sec 9.4 MB/sec
Random I/O 100 0.8 MB /sec 6.3 MB/sec
18
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

19
Row IdentifiersBase table organizations

• Fixed Row Identifier (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

Fixed RID
20
Row IdentifiersBase table organizations

• Clustered index
• Rows identified by unique clustering key
• Clustering key used as lock resource

Clustered index
21
Secondary Indexes

• Secondary indices
• 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

Secondary index
Key
Locator
Points to either
22
Index ChangesSecondary index lookup on key
Adams
Adams
6
Adams
6
Lewis
1
Smith
11
...
...
6
1
Lewis
Dan
Adams
Kim
11
Smith
Ken
Clustering index data
Key
Locator (clustering Key)
23
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

24
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

25
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

26
Metadata Retrieval

• System Stored Procedures
• sp_help authors
• System Functions
• select getdate()
• Information Schema Views
• select FROM INFORMATION_SCHEMA.TABLES

27
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
• System functions
• Object_name/id, index_col

28
Login Authentication, Database User Accounts
29
(No Transcript)
30
Server Properties
31
Server Properties
32
Database Properties
33
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

34
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

35
Summary

• Write a summary for this module