482
Part V: Enterprise Data Management
■ (^) -c: Starts SQL Server so that it is not running as a Windows service.
■ (^) -x: Enables maximum performance by disabling monitoring features. Because the
monitoring features are disabled, your ability to troubleshoot performance and
functional problems is greatly reduced.
■ (^) -g: Specifi es virtual memory (in MB) available to SQL Server for memory allo-
cations within the SQL Server process, but outside the SQL Server memory pool
(extended procedure .dll fi les, OLE DB providers referenced by distributed queries,
and automation objects referenced in Transact-SQL statements).
■ -n: Disables logging to the Windows application log.
■ -s: Starts a named instance of SQL Server. The instance name follows directly
after the s, with no spaces in between.
■ (^) -Ttrace#:Enables trace-specifi c fl ags by trace fl ag number. Refer to SQL Server
2012 Books Online topic “Trace Flags” for documented trace fl ags. Using trace fl ags
for an extended period of time is not recommended. Use trace fl ags for trouble-
shooting SQL Server issues and remove them when the issue is resolved. Also, never
use undocumented trace fl ags because they can cause more damage than they can
provide assistance with any issue.
■ -h: Assuming your hardware enables you to add physical memory without restart-
ing the server, use this option to enable SQL Server to immediately begin using the
hot add memory. This is only available on the SQL Server Enterprise Edition and can
be used on 64-bit SQL Server and 32-bit SQL Server with AWE enabled.
Startup Stored Procedures
You can fi gure SQL Server to scan for a startup stored procedure every time the SQL Server
starts — similar to how Microsoft DOS operating systems scan for the autoexec.bat fi le when
they boot up. All the startup procedures need to be in the master database, but there is no limit
on the number of startup procedures. The key is to remember that each startup procedure con-
sumes one worker thread while executing it. To mark an existing stored procedure to execute
automatically when SQL Server starts, use the sp_procoption system stored procedure as follows:
EXEC sp_procoption @ProcName = 'ExistingSP',
@OptionName = 'startup',
@OptionValue = 'on';
You use the same sp_procoption system stored procedure to stop a stored procedure from
executing at SQL Server startup as follows:
EXEC sp_procoption @ProcName = 'ExistingSP',
@OptionName = 'startup',
@OptionValue = 'off';
Although you can individually mark a stored procedure for automatic execution at SQL
Server startup, you can further control the execution of all the startup stored procedures by
using the scan for startup procs confi guration option. If this option is set to 1, SQL
Server scans for and runs all stored procedures marked for automatic execution at startup.
An example of a use case for a startup stored procedure would be if you have an expensive
query that takes a long time to run at fi rst execution. By using a startup stored procedure
c19.indd 482c19.indd 482 7/30/2012 5:42:51 PM7/30/2012 5:42:51 PM
http://www.it-ebooks.info