483
Chapter 19: Confi guring SQL Server
19
to execute this query at startup, it pushes the data into memory for quick access for subse-
quent requests. It should be noted that while this is a way to “warm the cache” with data,
this particular method should be used carefully. While in theory it’s possible to do this for
multiple tables, you may fi nd yourself pushing the physical resources (i.e. memory) fairly
quickly or accidently pushing other items you’ve loaded out of cache if you’re not careful.The default value of this option is 0, which skips automatic execution of startup stored
procedures. The scan for startup procs confi guration option is automatically set to 1
when you execute sp_procoption to mark the fi rst stored procedure for automatic execu-
tion and is set to 0 when you unmark the last stored procedure for automatic execution.
Use the following code to skip automatic execution for all startup stored procedures:EXEC sp_configure 'scan for startup procs', 0;
RECONFIGURE;Memory-Confi guration Properties
SQL Server can either dynamically request memory from the operating system or consume a
fi xed amount of memory. You can confi gure these settings on the SQL Server Properties Memory
tab, as shown in Figure 19-6, or from code by means of the sp_configure stored procedure.FIGURE 19-6
Memory tab of Management Studio’s SQL Server Properties dialog.c19.indd 483c19.indd 483 7/30/2012 5:42:51 PM7/30/2012 5:42:51 PM
http://www.it-ebooks.info