Sp_configure in SQL Server 2005

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

EXEC sp_configure

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

reconfigure

GO

--list the properies after advanced option true

EXEC sp_configure

GO

“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

reconfigure

GO

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.

http://www.sqlhub.com/2009/03/memory-configuration-of-sql-server-2005.html

“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

reconfigure

GO

“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

reconfigure

GO

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com