Posted by decipherinfosys on February 23, 2009
In RDBMS, there are a lot of ways to get to the same information and every DBA/Database Developer have their own favorite way of getting to the same piece of information. One of such examples is looking at the instance wide parameter settings. One can use server wide catalog view: sys.configurations or use sp_configure (with the show advanced option set) though the difference is that using sp_configure you can also set the parameter values and sys.configurations only provides you the data to read but does provide you a few extra data points like whether the value is dynamic and whether it is an advanced option or not. If it is dynamic, then the variable setting takes into effect only if we use the RECONFIGURE statement and if it is an advanced option, one then needs to use “show advanced option” in order to see the value for that variable.
Now, after comparing the output of sys.configurations between SQL Server 2005 (SP2 + Cumulative Update #3) and SQL Server 2008, we saw these differences between the two versions:
- SQL Server 2005 had 67 parameters where as SQL Server 2008 has 72.
- The parameter: “Web Assistant Procedures” which was present in SQL Server 2005 is no longer present in SQL Server 2008 since the web assistance feature has been deprecated.
- The 6 new parameters in SQL Server 2008 which are not present in the SQL Server 2005 list are:
- EKM Provider Enabled: EKM stands for Extensible Key Management. EKM allows us to store the keys used to encrypt the data separately as compared to the data that we are protecting. This is made possible by exposing the encryption functionality to the hardware vendors that address EKM using Hardware Security Modules (HSM).
- backup compression default: We had discussed backup compression feature in SQL Server 2008 in one of previous blog posts here. What this parameter setting is used for is to specify whether we want compression to be enabled or not.
- filestream access level: This option controls the filestream access level for a particular instance of SQL Server. A value of 0 disables it, 1 enables it for T-SQL access and 2 enables it for both T-SQL access as well as Win32 streaming access. You can read more about the filestream feature here.
- optimize for adhoc workloads: We have discussed the need of having this parameter before – you can read about it here. This parameter does help in working around the issue though is not the same as the options presented in the case of Oracle where you are given a lot more granular control over the cache configuration.
- access check cache bucket count and access check cache quota: When objects are accessed in SQL Server, the access checks are cached in the “access check result cache”. These 2 options control the number of enteries as well as the number of hash buckets that are used for the result cache. We haven’t played around with yet nor have had the need to do so – documentation also seems to state that these should be changed only when recommended by MSFT PSS. You can read more on these options in this KB article here.