Tuesday, July 23, 2013

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

image

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.

image

select * from sys.dm_os_memory_clerks

image

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

image

image

SQLOS is equivalent of the PMON process in Oracle.

image

 

CPU/Thread allocation

Oracle has 2 connection mode: See here more detail.

  • Dedicate Server mode
  • Share Server mode

image

SQL Server always use share server mode.

image

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.

 image

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

image

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

image

Here is the comparison processes between Oracle and SQL server.

image

image

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

select session_id,status,command from sys.dm_exec_requests

image

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates