Today I was reading few of my old articles, when I seen sp_configure there, idea of this article pops up in my mind as there are many properties to set and I have used few of them only in my past article. There are many more one can use in real life so I have started digging into Microsoft Book Online for those properties of sp_configure and here is a results of that.
Sp_configure: When you run this SP in your query window, it will show you current setting of the server. You can execute it like below
NOTE: many of the server property change will not take effect until and unless your restart server or its service that is why we have to use RECONFIGURE command rather than restart.
“Show Advanced Option”: This property will show you all advanced option you can set with sp_configure. If you run only EXEC sp_configure command as per above, you may see few rows as a result, I got 14 rows in my development server but there are lot more option other than these few rows. To see all the advanced option, run following commands in your query window.
–set advanced option to true
EXEC sp_configure ‘show advanced option’,1
–reconfigure the server so that property change
–take effect right a way
–list the properies after advanced option true
“Query Wait”: When memory is not available to execute the query, you can specify seconds, how many seconds will it wait to execute before gets time out?
EXEC sp_configure ‘query wait’,30
Min and Max server memory: These properties mainly used to change the memory usage of SQL Server. I have used these properties along with “awe enabled” property in my following article.
“Min memory per query”: default 1MB set as a “Min memory per query” but you can increase or decrease this limit for better performance but beware, increasing limit could cause memory shortage problem as well.
EXEC sp_configure ‘min memory per query’,1024
“Scan for startup procs”: I have used this property in my article of Startup Stored Procedure at http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html
This property is mandatory if you are making startup stored procedure and want SQL Server to scan that SP while SQL Server restart.
EXEC sp_configure ‘scan for startup procs’,1
Reference: Ritesh Shah