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.
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.
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:
The login or application can run on any engine
An advisory message is sent to the connection logging in to the server
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").
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.
