370 likes | 680 Views
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.
E N D
Microsoft SQL Server Administration for SAPSQL 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
Client Server Application SQL Server Database Interface Open Data Services Network Library Network Library Tabular Data Stream (TDS) Client / Server
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
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
master distribution tempdb model msdb pubs northwind VIE Databases System Databases User Databases
Database Data (file).mdf or .ndf Log (file).ldf Tables, Indexes Extent (8 contiguous 8-KB pages) Data Page (8 KB) Max row size = 8092 bytes Database Files
Data Files • Filegroups • Data & log files autogrow • Proportional fill • Pages and Extents • GAM • SGAM • PFS • IAM • 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
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 Hardware Configuration
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…
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:
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
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
Row IdentifiersBase table organizations • Clustered index • Rows identified by unique clustering key • Clustering key used as lock resource Clustered index
Secondary Indexes Secondary index • 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 Key “Locator” Points to either
Index ChangesSecondary index lookup on key “Adams” Adams 6 Adams 6 Lewis 1 Smith 11 ... ... 1 Lewis Dan 6 Adams Kim 11 Smith Ken Clustering index data Key Locator (clustering Key)
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 Index on Views
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
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
Metadata Retrieval • System Stored Procedures • sp_help authors • System Functions • select getdate() • Information Schema Views • select * FROM INFORMATION_SCHEMA.TABLES
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
Windows NT Verifies Password SQL Server Assigns Logins to User Accounts and Roles Windows NT Group User Windows NT SQL Server SQL SQL Server Verifies Password SQL Server Login Account Database Role Database User Account Login Authentication, Database User Accounts
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
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
Write a summary for this module Summary