Microsoft SQL Server Administration for SAP SQL Server Architecture
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 2 Database Server Results Transact-SQL Application Client SQL Server Relational Database Management System 3 SQL Server Service 4 Client / Server Client Server Application SQL Server Database Interface Open Data Services Network Library Network Library Tabular Data Stream (TDS) 5 SQL Server Client Network Utility 6 SQL Server Network Utility 7 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 9 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 pubs northwind VIE distribution User Databases 11 Database Files Database Data (file) Log (file) .mdf or .ndf .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 In row text 13 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 System Area Networks Support of SAN Network Interfaces cLan from GigaNet Servernet II from Compaq 20% perf gain 15 I/O components - Disks How fast is a disk? SQL Server reads 8K random (page reads) Depends on the type of I/O workload 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 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 sec 8K 64K RealisticI/Otransfer rates: Sequential I/O Random I/O 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 RID 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 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 Index Changes Secondary index lookup on key “Adams” Adams 6 Adams 6 Lewis 1 1 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 System Functions sp_help authors select getdate() Information Schema Views select * FROM INFORMATION_SCHEMA.TABLES 26 Common System Procedures and System Tables System stored procedures System tables sp_help, sp_helpindex sp_helpdb, sp_helpfile/filegroup sp_who, sp_lock sp_depends, sp_recompile sp_readerrorlog sysobjects, sysindexes, sysprocesses System functions Object_name/id, index_col 27 Login Authentication, Database User Accounts Windows NT Verifies Password Windows NT Group User SQL Server Assigns Logins to User Accounts and Roles Windows NT SQL Database User Account 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
© Copyright 2024