Microsoft SQL Server Administration for SAP SQL Server Architecture - PowerPoint PPT Presentation

Loading...

PPT – Microsoft SQL Server Administration for SAP SQL Server Architecture PowerPoint presentation | free to view - id: 4f16e3-ZDYwZ



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Microsoft SQL Server Administration for SAP SQL Server Architecture

Description:

Microsoft SQL Server Administration for SAP SQL Server Architecture Overview SQL Server Architecture SQL Server with SAP Performance Monitoring and Tuning ... – PowerPoint PPT presentation

Number of Views:425
Avg rating:3.0/5.0
Slides: 36
Provided by: RameshMe2
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Microsoft SQL Server Administration for SAP SQL Server Architecture


1
Microsoft SQL Server Administration for SAP SQL
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 Identifiers Base 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 Identifiers Base 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 Changes Secondary 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
About PowerShow.com