3 Learning Note: SQL Server VS Oracle–Instance architecture

This is my learning note base on the “SQL Server Essentials for Oracle DBAs Jump Start” . Many of the screenshots are from MSFT.

 

Memory Allocation

Oracle:  PGA + SGA = Total server usage. AMM is Oracle way to simplify the memory configuration. Even so, We have much more  granularity control of different memory setting in Oracle.

SQL Server does not have granularity memory configuration like Oracle.  As you can see from below screenshot, we only can configure MAX/MIN Server memory, Index creation memory and Minimum memory per query.

select * from sys.dm_os_memory_clerks

select * from sys.dm_os_sys_info

select * from sys.dm_os_sys_memory

select * from sys.dm_os_process_memory

select * from sys.dm_os_memory_nodes

SQLOS is equivalent of the PMON process in Oracle.

 

CPU/Thread allocation

Oracle has 2 connection mode: See here more detail.

• Dedicate Server mode
• Share Server mode

SQL Server always use share server mode.

Background processes

• SQL Server use thread model and we can configure to use fiber optionally.
• Oracle use process model on Linux but use thread model on windows.

Below is the screenshot of the SQL Server threads.

 

Below is the screenshot of the Oracle 12c threads on Windows Server 2012.

Below is the screenshot of the Oracle 11g process on the Linux

Here is the comparison processes between Oracle and SQL server.

To see background process from SQL Server. Session_id < 50 are the system processes.

select session_id,status,command from sys.dm_exec_requests