Starting and stopping engines with sp_engine

You can dynamically stop or start engines using sp_engine, which allows a System Administrator to reconfigure CPU resources as processing requirements fluctuate over time.

The syntax for sp_engine is:

sp_engine {“online” | [offline | can_offline] [, engine_id] | 
[“shutdown”, engine_id]

For example, the following brings engine number one online. Messages are platform specific (in this example, Sun Solaris was used):

sp_engine "online", 1 
02:00000:00000:2001/10/26 08:53:40.61 kernel  Network and device connection limit is 3042.
02:00000:00000:2001/10/26 08:53:40.61 kernel  SSL Plus security modules loaded successfully.
02:00000:00000:2001/10/26 08:53:40.67 kernel  engine 2, os pid 8624  online
02:00000:00000:2001/10/26 08:53:40.67 kernel  Enabling Sun Kernel asynchronous disk I/O strategy
00:00000:00000:2001/10/26 08:53:40.70 kernel  ncheck: Network fc0330c8 online

You can check whether or not a specific engine can be brought offline with the can_offline parameter. The following determines whether engine 1 can be brought offline:

sp_engine can_offline, 1

sp_engine specifies a return code of 0 if you can bring the specified engine offline. If you do not specify an engine_id, sp_engine describes the status of the engine in sysengines with the highest engine_id.

Engines can be brought online only if max online engines is greater then the current number of engines with an online status, and if enough CPU is available to support the additional engine.

To bring an engine offline, enter the engine ID. The following takes engine number one offline:

sp_engine offline, 1

Adaptive Server waits for any tasks that are associated with this engine to finish before taking the engine offline, and returns a message similar to the following:

01:00000:00000:2001/11/09 16:11:11.85 kernel  Engine 1 waiting for affinitated process(es) before going offline
01:00000:00000:2001/11/09 16:11:11.85 kernel  Process 917518 is preventing engine 1 going offline
00:00000:00000:2001/11/09 16:16:01.90 kernel  engine 1, os pid 21127  offline

You cannot take engine number zero offline.

sp_engine “shutdown” forces any tasks associated with the specified engine to finish in a five-second period, and then shuts down the engine. The following shuts down engine number one:

sp_engine "shutdown", 1

For more information about sp_engine, see the Reference Manual.


Relationship between network connections and engines

Due to the operating system limit on the number of file descriptors per process, reducing the number of engines reduces the number of network connections that the server can have.There is no way to migrate a network connection created for server-to-server remote procedure calls, for example, connections to Replication Server and XP Server, so you cannot take an engine offline that is managing one of these connections.


Logical process management and dbcc engine(offline)

If you are using logical process management to bind particular logins or applications to engine groups, use dbcc engine(offline) carefully. If you offline all engines for an engine group:

Since engine affinity is assigned when a client logs in, users who are already logged in are not migrated if the engines in the engine group are brought online again with dbcc engine("online").


Monitoring CPU usage

To maintain the correct number of engines, monitor CPU usage with an operating system utility. See the configuration documentation for your platform for the appropriate utility for your operating system.