-->
Applies to:SQL Server (all supported versions)
- For example, if you open a database with ODS 8.0 with InterBase 4.2, it will upgrade ODS to 8.2, which is not understandable by InterBase 4.0/4.1. So, the minor upgrade of ODS makes incompatible databases within same major server version. This is also true for Firebird 2.5 and InterBase 7.5.
- Ninox is a simple flat-file database system. Tables- With Ninox you manage your data in tables that you can browse, filter, sort and group.; Data fields- Ninox offers a variety of data fields, including text, numbers, Yes / No, list, date, time, picture and web address.; Relations- Link tables to each other to represent complex situations.; Forms- For neat enter data, make simple drag-and.
This topic describes how to configure the max degree of parallelism (MAXDOP) server configuration option in SQL Server by using SQL Server Management Studio or Transact-SQL. When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, the Database Engine detects whether parallelism can be used. The degree of parallelism sets the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. For more detail on the limit set by max degree of parallelism (MAXDOP), see the Considerations section in this page. SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, parallel inserts, online alter column, parallel stats collection, and static and keyset-driven cursor population.
Note
L profile, steel equal angles sections sizes, dimensions, properties, specifications. Current table represents steel equal angles (L profile) sizes, dimensions, properties, specifications. Manufactured according to standards: STN 42 5550 EN 10024 DIN 1025 TDP: STN 42 0135. On a beach there are many grains of sand. Each grain is separate—it is an element in a collection. The water nearby is calm and clear. I found Ninox on v1 and worked through the learning curve. Today, Ninox v2.4 gives me a central point to coordinate the activities, common data of all my cloud based processes including: QuickBooks Online, G-Suites for Business, MailChimp, RingCentral, and Asana. I can hardly wait till v2.5 to see what else I can do to make my business easier.
SQL Server 2019 (15.x) introduces automatic recommendations for setting the MAXDOP server configuration option during the installation process based on the number of processors available. The setup user interface allows you to either accept the recommended settings or enter your own value. For more information, see Database Engine Configuration - MaxDOP page.
Before You Begin
Considerations
- This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server professional.
- If the affinity mask option is not set to the default, it may restrict the number of processors available to SQL Server on symmetric multiprocessing (SMP) systems.
- Setting max degree of parallelism (MAXDOP) to 0 allows SQL Server to use all the available processors up to 64 processors. However, this is not the recommended value for most cases. For more information on the recommended values for max degree of parallelism, see the Recommendations section in this page.
- To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used during a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.
- The max degree of parallelism limit is set per task. It is not a per request or per query limit. This means that during a parallel query execution, a single request can spawn multiple tasks up to the MAXDOP limit, and each task will use one worker and one scheduler. For more information, see the Scheduling parallel tasks section in the Thread and Task Architecture Guide.
- You can override the max degree of parallelism server configuration value:
- At the query level, using the MAXDOPquery hint.
- At the database level, using the MAXDOPdatabase scoped configuration.
- At the workload level, using the MAX_DOPResource Governor workload group configuration option.
- Index operations that create or rebuild an index, or that drop a clustered index, can be resource intensive. You can override the max degree of parallelism value for index operations by specifying the MAXDOP index option in the index statement. The MAXDOP value is applied to the statement at execution time and is not stored in the index metadata. For more information, see Configure Parallel Index Operations.
- In addition to queries and index operations, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP. You can disable parallel execution plans for these statements by using trace flag 2528. For more information, see Trace Flags (Transact-SQL).
Recommendations
Starting with SQL Server 2016 (13.x), during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. The Database Engine places logical processors from the same physical core into different soft-NUMA nodes. The recommendations in the table below are aimed at keeping all the worker threads of a parallel query within the same soft-NUMA node. This will improve the performance of the queries and distribution of worker threads across the NUMA nodes for the workload. For more information, see Soft-NUMA.
Starting with SQL Server 2016 (13.x), use the following guidelines when you configure the max degree of parallelism server configuration value:
Server configuration | Number of processors | Guidance |
---|---|---|
Server with single NUMA node | Less than or equal to 8 logical processors | Keep MAXDOP at or below # of logical processors |
Server with single NUMA node | Greater than 8 logical processors | Keep MAXDOP at 8 |
Server with multiple NUMA nodes | Less than or equal to 16 logical processors per NUMA node | Keep MAXDOP at or below # of logical processors per NUMA node |
Server with multiple NUMA nodes | Greater than 16 logical processors per NUMA node | Keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16 |
Note
NUMA node in the above table refers to soft-NUMA nodes automatically created by SQL Server 2016 (13.x) and higher versions, or hardware-based NUMA nodes if soft-NUMA has been disabled.
Use these same guidelines when you set the max degree of parallelism option for Resource Governor workload groups. For more information, see CREATE WORKLOAD GROUP (Transact-SQL).
Use these same guidelines when you set the max degree of parallelism option for Resource Governor workload groups. For more information, see CREATE WORKLOAD GROUP (Transact-SQL).
From SQL Server 2008 through SQL Server 2014 (12.x), use the following guidelines when you configure the max degree of parallelism server configuration value:
Server configuration | Number of processors | Guidance |
---|---|---|
Server with single NUMA node | Less than or equal to 8 logical processors | Keep MAXDOP at or below # of logical processors |
Server with single NUMA node | Greater than 8 logical processors | Keep MAXDOP at 8 |
Server with multiple NUMA nodes | Less than or equal to 8 logical processors per NUMA node | Keep MAXDOP at or below # of logical processors per NUMA node |
Server with multiple NUMA nodes | Greater than 8 logical processors per NUMA node | Keep MAXDOP at 8 |
Security
Permissions
Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.
Using SQL Server Management Studio
To configure the max degree of parallelism option
- In Object Explorer, right-click a server and select Properties.
- Click the Advanced node.
- In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.
Using Transact-SQL
To configure the max degree of parallelism option
- Connect to the Database Engine.
- From the Standard bar, click New Query.
- Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to configure the
max degree of parallelism
option to16
.
For more information, see Server Configuration Options (SQL Server).
Follow Up: After you configure the max degree of parallelism option
The setting takes effect immediately without restarting the server.
See Also
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
affinity mask Server Configuration Option
Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)
Query Processing Architecture Guide
Thread and Task Architecture Guide
Configure Parallel Index Operations
Query Hints (Transact-SQL)
Set Index Options
affinity mask Server Configuration Option
Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)
Query Processing Architecture Guide
Thread and Task Architecture Guide
Configure Parallel Index Operations
Query Hints (Transact-SQL)
Set Index Options
Next steps
Using the
CREATE
DATBASE
SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE
statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.If you have existing scripts for creating your database, consider editing those scripts to take advantage of new Oracle Database features.
The instructions in this section apply to single-instance installations only. Refer to the Oracle Real Application Clusters (Oracle RAC) installation guide for your platform for instructions for creating an Oracle RAC database.
Note:
Single-instance does not mean that only one Oracle instance can reside on a single host computer. In fact, multiple Oracle instances (and their associated databases) can run on a single host computer. A single-instance database is a database that is accessed by only one Oracle instance, as opposed to an Oracle RAC database, which is accessed concurrently by multiple Oracle instances on multiple nodes. See Oracle Real Application Clusters Administration and Deployment Guide for more information on Oracle RAC.Complete the following steps to create a database with the
CREATE
DATABASE
statement. The examples create a database named mynewdb
.Tip:
If you are using Oracle Automatic Storage Management (ASM) to manage your disk storage, you must start the ASM instance and configure your disk groups before performing these steps. For information about Automatic Storage Management, see Oracle Database Storage Administrator's Guide.Step 1: Specify an Instance Identifier (SID)
Decide on a unique Oracle system identifier (SID) for your instance, open a command window, and set the
ORACLE_SID
environment variable. Use this command windows for the subsequent steps.ORACLE_SID
is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on the same host computer. The maximum number of characters for ORACLE_SID
is 12, and only letters and numeric digits are permitted. On some platforms, the SID is case-sensitive.Note:
It is common practice to set the SID to be equal to the database name. The maximum number of characters for the database name is eight. For more information, see the discussion of the DB_NAME
initialization parameter in Oracle Database Reference.The following example for UNIX and Linux operating systems sets the SID for the instance that you will connect to in Step 6: Connect to the Instance:
- Bourne, Bash, or Korn shell:
- C shell:
The following example sets the SID for the Windows operating system:
See Also:
Oracle Database Concepts for background information about the Oracle instanceStep 2: Ensure That the Required Environment Variables Are Set
Depending on your platform, before you can start SQL*Plus (as required in Step 6: Connect to the Instance), you may have to set environment variables, or at least verify that they are set properly.
For example, on most platforms,
ORACLE_SID
and ORACLE_HOME
must be set. In addition, it is advisable to set the PATH
variable to include the ORACLE_HOME/bin directory. On the UNIX and Linux platforms, you must set these environment variables manually. On the Windows platform, OUI automatically assigns values to ORACLE_HOME
and ORACLE_SID
in the Windows registry. If you did not create a database upon installation, OUI does not set ORACLE_SID
in the registry, and you will have to set the ORACLE_SID
environment variable when you create your database later.Step 3: Choose a Database Administrator Authentication Method
You must be authenticated and granted appropriate system privileges in order to create a database. You can authenticate as an administrator with the required privileges in the following ways:
- With a password file
- With operating system authentication
In this step, you decide on an authentication method.
If you decide to authenticate with a password file, create the password file as described in 'Creating and Maintaining a Password File'. If you decide to authenticate with operating system authentication, ensure that you log in to the host computer with a user account that is a member of the appropriate operating system user group. On the UNIX and Linux platforms, for example, this is typically the
dba
user group. On the Windows platform, the user installing the Oracle software is automatically placed in the required user group.See Also:
- 'Database Administrator Authentication' for information about password files and operating system authentication
Step 4: Create the Initialization Parameter File
When an Oracle instance starts, it reads an initialization parameter file. This file can be a text file, which can be created and modified with a text editor, or a binary file, which is created and dynamically modified by the database. The binary file, which is preferred, is called a server parameter file. In this step, you create a text initialization parameter file. In a later step, you create a server parameter file from the text file.
One way to create the text initialization parameter file is to edit the sample presented in 'Sample Initialization Parameter File'.
For convenience, store your initialization parameter file in the Oracle Database default location, using the default file name. Then when you start your database, it will not be necessary to specify the
PFILE
clause of the STARTUP
command, because Oracle Database automatically looks in the default location for the initialization parameter file.For more information about initialization parameters and the initialization parameter file, including the default name and location of the initialization parameter file for your platform, see 'About Initialization Parameters and Initialization Parameter Files'.
See Also:
Oracle Database Reference for details on all initialization parametersStep 5: (Windows Only) Create an Instance
On the Windows platform, before you can connect to an instance, you must manually create it if it does not already exist. The
ORADIM
command creates an Oracle instance by creating a new Windows service.To create an instance:
- Enter the following command at a Windows command prompt:where
sid
is the desired SID (for examplemynewdb
) andpfile
is the full path to the text initialization parameter file. This command creates the instance but does not start it.
Warning:
Do not set the -STARTMODE
argument to AUTO
at this point, because this causes the new instance to start and attempt to mount the database, which does not exist yet. You can change this parameter to AUTO
, if desired, in Step 14.See the section 'Using ORADIM to Administer an Oracle Database Instance' in Oracle Database Platform Guide for Microsoft Windows for more information on the
ORADIM
command.Step 6: Connect to the Instance
Start SQL*Plus and connect to your Oracle Database instance with the
SYSDBA
system privilege.- To authenticate with a password file, enter the following commands, and then enter the
SYS
password when prompted: - To authenticate with operating system authentication, enter the following commands:
SQL*Plus outputs the following message:
Note:
SQL*Plus may output a message similar to the following:If so, this means that the instance is already started. You may have connected to the wrong instance. Exit SQL*Plus with the
EXIT
command, check that ORACLE_SID
is set properly, and repeat this step.Step 7: Create a Server Parameter File
The server parameter file enables you to change initialization parameters with the
ALTER
SYSTEM
command and persist the changes across a database shutdown and startup. You create the server parameter file from your edited text initialization file.The following SQL*Plus command reads the text initialization parameter file (PFILE) with the default name from the default location, creates a server parameter file (SPFILE) from the text initialization parameter file, and writes the SPFILE to the default location with the default SPFILE name.
You can also supply the file name and path for both the PFILE and SPFILE if you are not using default names and locations.
Tip:
The database must be restarted before the server parameter file takes effect.Note:
Although creating a server parameter file is optional at this point, it is recommended. If you do not create a server parameter file, the instance continues to read the text initialization parameter file whenever it starts.Important—If you are using Oracle-managed files and your initialization parameter file does not contain the
CONTROL_FILES
parameter, you must create a server parameter file now so the database can save the names and location of the control files that it creates during the CREATE
DATABASE
statement. See 'Specifying Oracle-Managed Files at Database Creation' for more information.See Also:
- Oracle Database SQL Language Reference for more information on the
CREATE
SPFILE
command
Step 8: Start the Instance
Start an instance without mounting a database. Typically, you do this only during database creation or while performing maintenance on the database. Use the
STARTUP
command with the NOMOUNT
clause. In this example, because the initialization parameter file or server parameter file is stored in the default location, you are not required to specify the PFILE
clause:At this point, the instance memory is allocated and its processes are started. The database itself does not yet exist.
Ninox Database For Pc
See Also:
- Oracle Database Concepts for an overview of the Oracle instance.
- Chapter 3, 'Starting Up and Shutting Down', to learn how to use the
STARTUP
command
Step 9: Issue the CREATE DATABASE Statement
To create the new database, use the
CREATE
DATABASE
statement.Example 1
The following statement creates database
mynewdb
. This database name must agree with the DB_NAME
parameter in the initialization parameter file. This example assumes the following:- The initialization parameter file specifies the number and location of control files with the
CONTROL_FILES
parameter. - The directory
/u01/app/oracle/oradata/mynewdb
exists.
A database is created with the following characteristics:
- The database is named
mynewdb
. Its global database name ismynewdb.us.oracle.com
, where the domain portion (us.oracle.com
) is taken from the initialization file. See 'Determining the Global Database Name'. - Three control files are created as specified by the
CONTROL_FILES
initialization parameter, which was set before database creation in the initialization parameter file. See 'Sample Initialization Parameter File' and 'Specifying Control Files'. - The passwords for user accounts
SYS
andSYSTEM
are set to the values that you specified. Beginning with Release 11g, the passwords are case-sensitive. The two clauses that specify the passwords forSYS
andSYSTEM
are not mandatory in this release of Oracle Database. However, if you specify either clause, you must specify both clauses. For further information about the use of these clauses, see 'Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM'. - The new database has three redo log files as specified in the
LOGFILE
clause.MAXLOGFILES
,MAXLOGMEMBERS
, andMAXLOGHISTORY
define limits for the redo log. See Chapter 10, 'Managing the Redo Log'. MAXDATAFILES
specifies the maximum number of datafiles that can be open in the database. This number affects the initial sizing of the control file.Note:You can set several limits during database creation. Some of these limits are limited by and affected by operating system limits. For example, if you setMAXDATAFILES
, Oracle Database allocates enough space in the control file to storeMAXDATAFILES
filenames, even if the database has only one datafile initially. However, because the maximum control file size is limited and operating system dependent, you might not be able to set allCREATE DATABASE
parameters at their theoretical maximums.For more information about setting limits during database creation, see the Oracle Database SQL Language Reference and your operating system–specific Oracle documentation.- The
US7ASCII
character set is used to store data in this database. - The
AL16UTF16
character set is specified as theNATIONAL CHARACTER SET,
used to store data in columns specifically defined asNCHAR
,NCLOB
, orNVARCHAR2
. - The
SYSTEM
tablespace, consisting of the operating system file/u01/app/oracle/oradata/mynewdb/system01.dbf
is created as specified by theDATAFILE
clause. If a file with that name already exists, it is overwritten. - The
SYSTEM
tablespace is created as a locally managed tablespace. See 'Creating a Locally Managed SYSTEM Tablespace'. - A
SYSAUX
tablespace is created, consisting of the operating system file/u01/app/oracle/oradata/mynewdb/sysaux01.dbf
as specified in theSYSAUX DATAFILE
clause. See 'About the SYSAUX Tablespace'. - The
DEFAULT
TABLESPACE
clause creates and names a default permanent tablespace for this database. - The
DEFAULT TEMPORARY TABLESPACE
clause creates and names a default temporary tablespace for this database. See 'Creating a Default Temporary Tablespace'. - The
UNDO TABLESPACE
clause creates and names an undo tablespace that is used to store undo data for this database if you have specifiedUNDO_MANAGEMENT=AUTO
in the initialization parameter file. If you omit this parameter, it defaults toAUTO
. See 'Using Automatic Undo Management: Creating an Undo Tablespace'. - Redo log files will not initially be archived, because the
ARCHIVELOG
clause is not specified in thisCREATE
DATABASE
statement. This is customary during database creation. You can later use anALTER DATABASE
statement to switch toARCHIVELOG
mode. The initialization parameters in the initialization parameter file formynewdb
relating to archiving areLOG_ARCHIVE_DEST_1
andLOG_ARCHIVE_FORMAT
. See Chapter 11, 'Managing Archived Redo Logs'.
Tips:
- Ensure that all directories used in the
CREATE
DATABASE
statement exist. TheCREATE
DATABASE
statement does not create directories. - If you are not using Oracle-managed files, every tablespace clause must include a
DATAFILE
orTEMPFILE
clause. - If database creation fails, you can look at the alert log to determine the reason for the failure and to determine corrective actions. See 'Viewing the Alert Log'. If you receive an error message that contains a process number, examine the trace file for that process. Look for the trace file that contains the process number in the trace file name. See 'Finding Trace Files' for more information.
- If you want to resubmit the
CREATE
DATABASE
statement after a failure, you must first shut down the instance and delete any files created by the previousCREATE
DATABASE
statement.
Example 2
This example illustrates creating a database with Oracle Managed Files, which enables you to use a much simpler
CREATE
DATABASE
statement. To use Oracle Managed Files, the initialization parameter DB_CREATE_FILE_DEST
must be set. This parameter defines the base directory for the various database files that the database creates and automatically names. The following statement is an example of setting this parameter in the initialization parameter file:Ninox Download
With Oracle Managed Files and the following
CREATE
DATABASE
statement, the database creates the SYSTEM
and SYSAUX
tablespaces, creates the additional tablespaces specified in the statement, and chooses default sizes and properties for all datafiles, control files, and redo log files. Note that these properties and the other default database properties set by this method may not be suitable for your production environment, so it is recommended that you examine the resulting configuration and modify it if necessary.Tip:
If your CREATE
DATABASE
statement fails, and if you did not complete Step 7, ensure that there is not a pre-existing server parameter file (SPFILE) for this instance that is setting initialization parameters in an unexpected way. For example, an SPFILE contains a setting for the complete path to all control files, and the CREATE
DATABASE
statement fails if those control files do not exist. Ensure that you shut down and restart the instance (with STARTUP
NOMOUNT
) after removing an unwanted SPFILE. See 'Managing Initialization Parameters Using a Server Parameter File' for more information.See Also:
- Oracle Database SQL Language Reference for more information about specifying the clauses and parameter values for the
CREATE DATABASE
statement
Step 10: Create Additional Tablespaces
To make the database functional, you need to create additional tablespaces for your application data. The following sample script creates some additional tablespaces:
For information about creating tablespaces, see Chapter 12, 'Managing Tablespaces'.
Step 11: Run Scripts to Build Data Dictionary Views
Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:
The at-sign (
@
) is shorthand for the command that runs a SQL*Plus script. The question mark (?
) is a SQL*Plus variable indicating the Oracle home directory. The following table contains descriptions of the scripts:Script | Description |
---|---|
CATALOG.SQL | Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms. |
CATPROC.SQL | Runs all scripts required for or used with PL/SQL. |
PUPBLD.SQL | Required for SQL*Plus. Enables SQL*Plus to disable commands by user. |
Step 12: Run Scripts to Install Additional Options (Optional)
You may want to run other scripts. The scripts that you run are determined by the features and options you choose to use or install. Many of the scripts available to you are described in the Oracle Database Reference.
If you plan to install other Oracle products to work with this database, see the installation instructions for those products. Some products require you to create additional data dictionary tables. Usually, command files are provided to create and load these tables into the database data dictionary.
See your Oracle documentation for the specific products that you plan to install for installation and administration instructions.
Step 13: Back Up the Database.
Take a full backup of the database to ensure that you have a complete set of files from which to recover if a media failure occurs. For information on backing up a database, see Oracle Database Backup and Recovery User's Guide.
Step 14: (Optional) Enable Automatic Instance Startup
You might want to configure the Oracle instance to start automatically when its host computer restarts. See your operating system documentation for instructions. For example, on Windows, use the following command to configure the database service to start the instance upon computer restart:
Ninox Database 2 5 8 Equals
You must use the
-SPFILE
argument if you want the instance to read an SPFILE upon automatic restart.Ninox App
See the section 'Using ORADIM to Administer an Oracle Database Instance' in Oracle Database Platform Guide for Microsoft Windows for more information on the
ORADIM
command.