Interview Question of the Week #061 – How to Retrieve SQL Server Configuration?

I have observed this question in the interview question few days ago. I had a fun time to see candidates failing to answer this question. The most common answer candidate gave us was that they will right click on the server and see the properties. Well, that option will not give all the details which actual question intended. Let us see the question about SQL Server Configuration:

Question: What is the best way to see all the options set for your SQL Server programmatically?

Answer:

Method 1: Using sp_configure

Run following script and it will provide all information about the SQL Server. SQL Server provides in detail information if Advanced Options are turned on.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure
GO
EXEC sp_configure 'show advanced options', 0
GO

To change any configuration run following script using values from the NAME column of the following result set. E.g. To change max server memory run following script.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory', 2048
GO
RECONFIGURE
GO

Method 2: Using sys.configurations

You can run following script and it will display almost the same information as above query.

SELECT *
FROM sys.configurations</pre>
<pre>

Reference: Pinal Dave (https://blog.sqlauthority.com)