SQL 2008 ships with a very cool feature called the resource governor. Of course, the resources we can control in this release are CPU and memory (unfortunately we can not control IO yet). Anyway, if you have ad-hoc reporting or poorly written batch processes that are killing your transaction bound users then you can create some rules to throttle back the heavy hitters. Here are the steps:

Step 1. Create a Resource Pool and a Workload Group

CREATE RESOURCE POOL PoolAdHocReporting
CREATE RESOURCE POOL PoolWarehousing
GO
CREATE WORKLOAD GROUP GroupReporting USING PoolAdHocReporting
CREATE WORKLOAD GROUP GroupWarehousing USING PoolWarehousing
GO

Step 2. Create a Classifier Function

The classifier function maps users to the workload group. Basically you are putting your users in buckets so you can apply rules to them.

CREATE FUNCTION CLASSIFIER_FUNC()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
     DECLARE @val varchar(32)
     if 'ReportUserAcct' = SUSER_SNAME()
        SET @val = 'GroupReporting';
     else if 'WarehousingAcct' = SUSER_SNAME()
        SET @val = 'GroupWarehousing';

     return @val
END
GO

Step 3. Tell the Resource Governor to Use Our Classifier Function

ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.CLASSIFIER_FUNC)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Now at this point we have enabled the resource governor and applied our classifier function however we have not throttled any resources yet. So now its time to modify CPU and memory limits.

Step 4. Modify Resource Limits

Now you can use scripts to modify Memory and CPU but for the purposes of this demo I think it is easier to modify settings with management studio. To view the Resource Governor Properties expand the management folder of your instance and right click on the Resource Governor node and select Properties. You should now see this dialog.

Step 5. Laugh when End Users Say They Don't Have Time To Tune Their Queries

With the resource governor on your side you now have a little more leverage over your end users. If they refuse to tune their queries then just throttle their resources. Let's face it, most of us DBAs are constantly trying to identify and fix rogue queries on our servers. Sometimes queries are easily identified and fixed by adding new indexes, updating some stats or re-writing the T-SQL. Unfortunately, there are times when you have to resort to something like the resource governor in order to get things done.

There have been many occasions where I have developed an application where I want to persist data between sessions without using SQL Server. Therefore, I came up with the idea of using the DataSet's built-in ReadXML() and LoadXML() methods to persist data to disk as an XML file.

In an application that I am currently building I added the ability to launch external tools from a File Menu. I chose not to store this data in the central database because the application can move from machine to machine and the applications the end user makes shortcuts to, may differ on each workstation. I could have used the registry but to be honest I hate the registry. Anyway, In my application, I created a static class to handle the data manipulation. The basic plumbing looks something like this:

   1:  public static List<ExternalTool> LoadToolsFromDisk()

   2:  {

   3:     List<ExternalTool> tools = new List<ExternalTool>();

   4:   

   5:     if (File.Exists(Filename) == false) return tools;

   6:     DataSet ds = new DataSet(DATASET_NAME);

   7:     ds.ReadXml(Filename);

   8:     DataTable table = ds.Tables[TABLE_NAME];

   9:   

  10:     foreach (DataRow row in table.Rows) {

  11:        ExternalTool tool = new ExternalTool();              

  12:        tool.Title = GetFieldValue( row, COL_TITLE );

  13:        tool.Command = GetFieldValue( row, COL_COMMAND );    

  14:        tool.Arguments = GetFieldValue( row, COL_ARGUMENTS );

  15:        tools.Add(tool);

  16:     }

  17:     return tools;

  18:  }

The code starts by creating a new DataSet. If there is an xml file on disk matching the pre-determined filename then the ReadXML() method is called. Once ReadXML() finishes you can manipulate the data as usual. Just iterate over the rows in the DataTable to load it into the generic list. On the flipside, saving the data is also very simple:

   1:  public static void SaveToolsToDisk( List<ExternalTool> tools )

   2:  {

   3:     DataSet ds = new DataSet(DATASET_NAME);

   4:     DataTable table = new DataTable(TABLE_NAME);

   5:     table.Columns.Add(COL_TITLE, typeof(String));

   6:     table.Columns.Add(COL_COMMAND, typeof(String));

   7:     table.Columns.Add(COL_ARGUMENTS, typeof(String));         

   8:   

   9:     foreach (ExternalTool tool in tools) {

  10:        DataRow row = table.NewRow();

  11:        row[COL_TITLE] = tool.Title ?? String.Empty;

  12:        row[COL_COMMAND] = tool.Command ?? String.Empty;

  13:        row[COL_ARGUMENTS] = tool.Arguments ?? String.Empty;

  14:        table.Rows.Add(row);

  15:     }

  16:     ds.Tables.Add(table);

  17:     ds.AcceptChanges();       

  18:     ds.WriteXml(Filename);

  19:  }

The code creates a new DataSet from a generic list, shoves the contents of the list into a DataTable and then writes the data to disk using the WriteXML() method. The whole process is very simple and easy to maintain. Happy Coding!

Even though we live in a GUI world there will always be a place for a console app. In my day to day adventures as a Database Administrator I still find myself heavily dependent on batch files for automating processes. Sometimes, when the logic is a little bit to complex for dos commands to handle I will create a new console app in visual studio. Let's face it, if statements and for loops in C# are much easier on the eye when compare to the dos way of doing things.

Tip #1. - How to display debug information only when the project is running from Visual Studio.

The trick here is to use the System.Diagnostics.Debugger.IsAttached property. You could create an extension method or a normal method to handle your logging. For example:

   1:  static void DebugLog(string s)

   2:  {

   3:     if (System.Diagnostics.Debugger.IsAttached)

   4:        Console.WriteLine(s);

   5:  }

This is handy if you want to see diagnostic information during debugging but you want to hide it when you run it in production. The TraceWriter class does a similar thing but my technique is quick and dirty and it definitely gets the job done.

Tip #2. - (Newbie level) Sometimes when you run a console app from visual studio the application run so fast and closes that you never see the output.

Ahhh...this is an easy fix. Just add the statement Console.Readline() as the last line in your program. This will keep the window open until you hit the enter key.

Tip #3. - How do I parse out all of the command line arguments?

Regular expressions is the answer. I basically use this block of code for parsing my command line arguments. This code block relies on you using an argument pattern similar to osql or sqlcmd. For example, to connect to a server named mysqlserver with windows authentication in osql you would issue the following command:

osql -S mysqlserver -E

The -S argument is for the servername and the -E argument means that you want to use windows authentication.

   1:  static void ProcessArguments(string[] args)
   2:  {
   3:     string argString = String.Empty;
   4:     //do a little work to remove unecessary spaces between the option and value
   5:     //add commas in order to make it easier to split the statements
   6:     foreach (string s in args) {
   7:        if (s.StartsWith("-") && argString.Length > 0)
   8:           argString += ",";
   9:   
  10:        argString += s;
  11:     }
  12:   
  13:     //split the arguments up by using the commas we just added
  14:     string[] fixedArgs = argString.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
  15:   
  16:     string pattern = @"-(?<option>[a-zA-Z])(?<value>.+)#";
  17:     foreach (string argument in fixedArgs) {
  18:        Match match = Regex.Match(argument, pattern, RegexOptions.IgnorePatternWhitespace);
  19:        if (match != null) {
  20:           //split the options into the key, value pair and set the appropriate static variable
  21:           string option = match.Groups["option"].Value.ToUpper();
  22:           string value = match.Groups["value"].Value;
  23:   
  24:           //Console.WriteLine( "{0}: {1}", option, value );
  25:           if (option == "D") {
  26:              _directory = value;
  27:           }
  28:           else if (option == "H") {
  29:              _headerFile = value;
  30:           }
  31:        }
  32:     }
  33:  }

On a related note. If you are doing a lot of work with regular expressions you may be interested in the Regular Expression Designer from rad software. I found an article about it a few months ago in a magazine and it has been on my workstation ever since. Basically the tool allows you to learn, develop and test Regular expressions. Best of all, it is a free download.

I found an interesting link today while I was surfing the social bookmarking site http://del.icio.us. Basically I found a blog where a developer created a "Programmer Competency Matrix". It basically breaks programming knowledge down into basic groups and levels. Here is an example: 

This matrix could serve as an interesting way to rank programmers in an organization or to even figure out compensation rates for your staff. Anyway, to see the full matrix visit the IndianGeek website.

Most of the time when there are performance problems in a database it is usually caused by the lack of an index. However, on the flip side, having too many indexes can also lead to problems. Every time you modify data in a table the associated indexes need to be updated. If you have a table with millions of rows and a large amount of indexes then DML statements will tend to cause a lot of unnecessary IO. In order to identify all the indexes in one of my SQL instances I developed this script. It basically iterates over every database and table in a instance and catalogs the name of each index and its associated metadata into a table:

SET NOCOUNT ON 

USE [DBA]
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[IndexData]') AND type in (N'U'))
   DROP TABLE [dbo].[IndexData]
GO

CREATE TABLE [IndexData]
(
  database_name sysname collate database_default NOT NULL,
  table_name sysname collate database_default NOT NULL,
  index_name   sysname collate database_default NOT NULL,  
  description varchar(210) NULL,
    index_keys   nvarchar(2126) collate database_default NOT NULL
)
GO

CREATE TABLE ##indexes
(     
   index_name   sysname collate database_default NOT NULL,  
   description varchar(210) NULL,
   index_keys   nvarchar(2126) collate database_default NOT NULL
)  

DECLARE @sql nvarchar(4000)
SET @sql = 'USE [$]

      IF( ''$'' NOT IN ( ''tempdb'' ) ) BEGIN       
      
      exec  sp_msforeachtable ''truncate table ##indexes
           insert into ##indexes
                   EXEC sp_helpindex ''''?''''
      
                   insert into [DBA].[dbo].[IndexData]
                   select DB_NAME(db_id()), ''''?'''', index_name, description, index_keys from ##indexes''
      END'
                   
exec sp_msforeachdb @sql, @replacechar = '$'
                   
drop table ##indexes

select * from [DBA].[dbo].[IndexData]

The result of the script is a table called IndexData. It can easily be queried to find redundant indexes.  Here is an example:

select 
    * 
from 
    IndexData 
where 
    database_name = 'Northwind'
    and table_name = '[dbo].[Shippers]'
    and index_keys like '%SegmentID%'

The results (I modified the original northwind for test purposes)!