Sp_Configure Options

EXAM Objective: Installing and Configuring SQL Server 2008

Sub Objective: sp_configure

Sp_Configure Options

In a previous post I discussed sp_configure and how to use it. In this post I will be covering some of the options and their meanings. Many of the meanings are pretty straight forward but a few of them are a bit obscure. The options I will be looking at now are listed below. These are either options I have come across in preparation for the SQL Server 2008 MCITP certification or that I felt might be of practical value to know.

• cost threshold for parallelism
• max degree of parallelism
• max server memory (MB)
• min server memory (MB)
• index create memory
• min memory per query
• AWE enabled
• ad hoc distributed queries
• filestream access level
• backup compression default
  

Using MAX(datetime) and Sub-Queries to Find the Most Recent Item

Today I was preparing a query that pulled some information from the inventory and sales tables of our ERP. The query itself was pretty easy to craft, but the VP of Sales threw a wrench into the mix; it should only contain information regarding the most recent sales order. Let's imagine my query was providing output like this table. Of course the actual query was more complex, but this gives the important information to understand the problem.

Part_No	Rev	On_Hand	Safety_Stock	SO_No	SO_Date
12345	A	10	15	S1234	12/14/2009
12345	A	10	15	S1233	10/01/2009
12345	A	10	15	S1232	08/02/2009
12346		5	0	S1231	08/01/2009
12347	-	0	0	S1230	10/20/2009
12347	-	0	0	S1229	07/15/2009

What I actually needed the query to deliver were lines 1, 4, and 5. These are only the most recent sales orders associated with each part number.

Removing Duplicate Items from an Array in PowerShell and C#

As a DBA I sometimes have to do some programming or scripting. One of the things that I find very useful is knowing how to remove duplicate items from an array. Most recently I had a C# application I had built that was generating duplicate items (they weren't really duplicates the part numbers were the same, however the revision was different. But the fact that the rev was different was irrelevant to the people consuming the data. Now one of the fields I needed to select was of the text data type, so I could not include DISTINCT in the query's select clause. I had to remove the duplicates from the array holding the data set. The .NET Framework 3.5 had made this a snap. You used to have to loop through the array and perform a comparison, but now you can do it with just a single line of code!

Simulating Log Shipping with Windows PowerShell

Over the past few months I have been writing the high availability and desaster recovery documentation and procedures. When I first took this position we had no sort of disaster recovery plan in place. We used tape backups and SQL Server backups that were only done once a day. T-SQL log backups were done only once during the day and there was nothing set up to notify the database administrator (that is me) if one of the jobs failed. I also had nothing to restore the backup to, if there was a hardware failure until the replacement equipment was received. Add to the fact that the SQL Server itself is out of any sort of warranty and we had a potentially nast situation if something did go wrong.

In addition to changing the backup plans so that we are doing t-log backups every hour and adding alerts, operators, and notifications tot he server I started kicking around teh idea of using transaction log shipping to add an extra layer of protection to the system. But the issue was the production database server is SQL Server 2000 Standard, which does not include log shipping.

I decided to write a PowerShell script that would use the backups I was alreeady creating to simulate log shipping to a warm standby server. Here is what I have so far.

Creating a SQL Server 2008 Cluster in a Test Lab

Part One – Installing the Systems and Setting up Clustering

This article gives the basic steps in Server 2008 R2 to create a test lab with a 2-node, SQL Server failover cluster. To perform the steps in this article you will need a virtualization platform capable of running two instances of Windows Server 2008 R2 and an iSCSI target, such as iSCSI Cake or StarWind. Please note that many popular Open Source iSCSI systems, such as OpenFiler, do not support persistent reservations, which is a requirement of creating a failover cluster in Server 2008/Server 2008 R2. You should also have a working Active Directory infrastructure already installed.

Installing and Configuring SQL Server 2008: sp_configure

EXAM Objective: Installing and Configuring SQL Server 2008

Sub Objective: sp_configure

What is sp_configure?

Sp_configure is a system stored procedure used to display and modify information about the configuration of SQL Server. If you simply type sp_configure into a query window and run it you will get output that looks like the following table, which is a sample of the output of running this command on my developmental server.

name	minimum	maximum	config_value	run_value
access check cache bucket count	0	16384	0	0
access check cache quota	0	2147483647	0	0
Ad Hoc Distributed Queries	0	1	0	0
affinity I/O mask	-2147483648	2147483647	0	0
affinity mask	-2147483648	2147483647	0	0
….
locks	5000	2147483647	0	0
max degree of parallelism	0	64	0	0
max full-text crawl range	0	256	4	4
max server memory (MB)	16	2147483647	2147483647	2147483647
max text repl size (B)	-1	2147483647	65536	65536
max worker threads	128	32767	0	0
…

The full list can be found at the end of this post. The above should give you a good idea of the settings and values that can be seen and changed through sp_configure. Many DBAs who have become dependent on using GUI based tools like SSMS and Configuration Manager might be reluctant to use this tool even to view the settings. But the fact is it will always be faster to use a query than to do any of this through the GUI. While I can agree that GUI based tools add a feeling of security, we should not be afraid to challenge ourselves to grow as professionals and true mastery only comes from being able to perform tasks accurately and in the most efficient manner possible. That being said, do not attempt to make changes to production servers using this or any other tool without a complete understanding of the settings you are changing. Be sure to consult BoL 2008 for a thorough description of what the settings do.