1 / 35

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. Database Server. Results. Transact-SQL. Application. Client.

nonnie
Download Presentation

Microsoft SQL Server Administration for SAP SQL Server Architecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Client SQL Server Relational Database Management System

  4. SQL Server Service

  5. Client Server Application SQL Server Database Interface Open Data Services Network Library Network Library Tabular Data Stream (TDS) 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. master distribution tempdb model msdb pubs northwind VIE Databases System Databases User Databases

  12. 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

  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. 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

  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:

  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 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

  22. 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)

  23. 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

  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. 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

  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. Write a summary for this module Summary

More Related