Database Management Fundamentals: SQL Server and IMS

ROWID is the physical disk address of a row, so accessing by ROWID is the fastest method - Oracle goes directly to the exact location. Primary key and unique index require traversing an index structure first, and full table scan reads every row in the table.

The Database Maintenance Plan Wizard is the standard SQL Server tool designed for administrators to easily configure common database maintenance tasks, including backup strategies. Database Engine Tuning Advisor, Configuration Manager, and general design surfaces do not serve as guided backup strategy wizards.

Read Uncommitted isolation level allows dirty reads, meaning it can read data that has been modified but not committed by other transactions. Since the requirement is to avoid dirty reads, Read Uncommitted must be avoided. Read Committed, Repeatable Read, and Serializable all prevent dirty reads by only reading committed data.

In IMS (Information Management System), a database record is defined as the root segment occurrence plus all its dependent segment occurrences. The root segment is the entry point, and all subordinate segments form the complete database record hierarchy.

DL/I (Data Language/I) databases, which are part of the IMS system, have a maximum limit of 15 levels in the hierarchical structure. This means a segment can have at most 14 levels of child segments beneath it, forming the 15-level depth.

PCB in IMS stands for Program Communication Block. It is the communication interface between the application program and the IMS system, defining the database access structure and controlling data transfer.

In IMS, status code 'II' indicates that an attempt was made to insert a duplicate segment - a segment with the same key sequence as an existing segment. This is a constraint violation preventing duplicate data.

In IBM's IMS (Information Management System) database, the Database Description (DBD) defines the physical structure of the database. The Program Specification Block (PSB) defines the logical view (subschema) for an application program, and the ACB (Application Control Block) is created by merging DBD and PSB information.

In SQL Server, blocking processes can be identified and killed using Activity Monitor. This tool provides a real-time view of process activity and allows terminating blocking processes. System Monitor is for performance counters, SQL Profiler traces events, and SSRS is for reporting.

Navigational data access languages required users to understand the physical database structure - table relationships, index hierarchies, and access paths. Unlike SQL's declarative approach where you specify WHAT data you want, navigational languages required knowing HOW to navigate to it.

A major problem with SQL is that logically equivalent queries can be written in multiple ways, and each variation may produce vastly different execution plans. This makes query optimization challenging - a simple syntax change can dramatically affect performance.

sys.dm_exec_query_stats provides performance statistics for cached query plans, including execution counts and recompilation counts. This DMV allows identifying queries with high recompilation rates. sys.dm_exec_requests shows currently executing queries, sys.dm_exec_sessions shows session info, and sys.dm_exec_query_optimizer_info shows optimizer metrics.