Are you Monitoring the rights stats on your database server?

The blog is based on real world issues that I have seen over the past few years.
SQL server is so easy to setup and cluster that anyone with good knowledge of general infrastructure can do this. This is good but can lead to incorrect configuration. How many people tweak MAXOP ?  do you lock pages in memory? do you create one tempdb per CPU core? A good DBA will know what all these questions mean. Also quite rightly most people put LOGS and DATA on their own drive. But for high transactional database have you thought about tempdb and snapshot isolation? Far too much to go over in this blog.
Lets assume the following has happened; You have a hosting requirement for CRM (I picked CRM as at the moment , I ‘do a lot’ of CRM). So your CRM Architect sizes the servers as :


Your infrastructure team build this and put in the right firewalls etc. Now comes the monitoring. The SQL server has a the following drives :

OS	120GB	C:
DATA	120GB	D:
LOGS	60GB	L:

CRM is install and it creates the database. The LOG or DATA files are not pre-sized you just let the system run. You have set backups based on your standard backup policy, e.g. LOGS nightly and DATA weekly.
On some days CRM’s ReIndex job starts and the LOG file gets very large. Your disk monitoring software detects the low disk space. Someone logs in to the database server looks at the log drive and see that it has low disk space. So… they open google and typing “Log file large SQL” and if you follow the first article without reading in correctly you may find yourself ready to enter something like :
DBCC SHRINKFILE (’CRMORG_Log’, 1000)
Lets assume that the backup has run so the log file has lots of free space.
You look at free space on disk and wow.. you have fixed it. The next day comes and the first few CRM transactions need to grow the log. In other words when a user updates a record in CRM it could trigger a transaction that has the side affect of ‘growing’ the log. This will take time and other transaction may have to queue up behind.
This performance issue is noted so you contact your DBA. The DBA may run a script like :

SELECT databasename,e.name   AS eventname,cat.name AS [CategoryName],starttime,e.category_id,
       loginname,loginsid,spid,hostname,applicationname,servername,textdata,objectname,eventclass,
       eventsubclass
FROM   ::fn_trace_gettable(@path, 0)
       INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE  e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )
ORDER  BY starttime DESC

This will show that the log file has been auto growing. The DBA may then pre-size the log to fix this.
Now… we are back to the start and you run low on disk space. To be honest the DBA may not get involved and you may not pre-size the log.

What should we do?

Firstly, you need to understand the role of a DBA. The ability to install SQL and create a database does not mean you are a DBA. No offence meant its just that Microsoft have made SQL so easy to manage that people don’t hire a DBA and that's a key issue. You should at least ‘get one in’ for a day or two. I have visited many customers and sat with their DBA and pointed out many issues with the health of their SQL server.
If the databases have been sized correctly. Based on; Number of users, User Transactions, Backlog e.g. data load, data type e.g. any blobs? and Backup Strategy (You need to understand the backup strategy to size the logs).
We have this disk layout

OS	120GB	C:
DATA	120GB	D:
LOGS	60GB	L:

This is based on a CRM log requirement of 50GB, you should pre-size the log at 50GB and monitor the free space within the log. The following SQL will do show you the free space:
DBCC SQLPERF(LOGSPACE)
You will need a monitoring tool capably of view the output of SQL statements.

What else?

I intend to write more blogs on this subject as I come across issues.
I have also seen systems that monitor backups but they don't monitor the SQL backups. In simple terms you have a SQL jobs that BACKUPS a DATABASE to DISK. You have a backup tool that backs up the BACKUP from DISK to TAPE. You monitor the backup tool but what if the SQL backup jobs have been failing? who is looking at that? This would also cause large transaction logs.

Testing Console Applications

I did some work this week on a command line installer for RBS. I needed to make some modifications to the install process. I wanted to-do this the TDD way. I did not want to refactor the entire project just to make it testable, I thought I would try to come up with a nice way to test my console application. I needed to capture the output of the console application to check that my new features were working correctly.

RBS is remote blob storage, see the RBS blog http://blogs.msdn.com/sqlrbs/

Firstly I tried to launch the exe and capture the output, this worked well but I could not debug my new code. I know true TTDers don't use debuggers, but I needed to :)

I managed to get this working by referencing my Console Project from my test project (yes you can reference an EXE). I could then add code like :

InstallProviderSetup.Main(new string[] { "-CLIENTCONFIG" });

As you can see, you can just call the static Main method from code. To see if my test worked I need to check the output of the console application. I did this with the following code :

var stdout = GetStdOut();
Assert.AreEqual(true, stdout.Contains("The required switch CONFIGURATIONFILE"),"The required switch CONFIGURATIONFILE not in console out");
Assert.AreEqual(true, stdout.Contains("The required switch NAME"),"The required switch NAME not in console out");

The rest of the code is :

MemoryStream memoryStreamConsole;
StreamWriter streamWriterConsole;

[TestInitialize()]
public void TestInitialize()
{
memoryStreamConsole = new MemoryStream();
streamWriterConsole = new StreamWriter(memoryStreamConsole);
Console.SetOut(streamWriterConsole);
}

protected string GetStdOut()
{
streamWriterConsole.Flush();
var rval = Encoding.Default.GetString(memoryStreamConsole.ToArray());
System.Diagnostics.Trace.WriteLine(rval);
return rval;
}

Feel free to view the entire project at codeplex

http://sqlrbs.codeplex.com/

 

Diagonal are using Microsoft RBS to give Wisdom (an Electronic Records Management System (EDRM) ) Content Addressable storage (CAS). So far we have integrated with EMC. As more CAS vendors create RBS providers. Wisdom will support more CAS systems.
You can find out more about Diagonal and Wisdom by visting the wisdom website

Trees & Hierarchies in SQL

Hierarchies and trees are a challenge in SQL.
This is an example of a very simple tree

Parent ID	ID	Name
	0001	Earth
0001	0002	EU
0001	0003	USA
0002	0004	UK
0002	0005	France
0004	0006	England
0006	0007	Yorkshire

With our application the Parent ID and ID columns are both GUIDs. To help with readability I have used numbers in the examples.


This looks like
Earth
  EU
    France
    UK
      England
        Yorkshire
  USA


The table structure above will work very well in most cases. It's very easy to select the parent of an item and select the children of an item. You can easily add new items or delete items. You get problems when you need to find things like all the decedents of an item.

You can use CTE (common table expressions) but under heavy load and with lots of data it just does not work.

We looked at the nested model. This does not work with simple deletes and inserts. (http://www.developersdex.com/gurus/articles/112.asp )

The final approach we took was to add an incremental number to our table and a row path column. As follows :

Row bitint
RowPath varbinary(160)
When we generate the rowpath we pad it with zeros so each row number starts at the same place. We do not use delimiters we used fixed spaces.

This is a simple representation of how the table will look. This is simple as I am just padding the rowpath with 2 zeros (not 16).

Row	Parent ID	ID	Name	RowPath
1		0001	Earth	01
2	0001	0002	EU	0102
3	0001	0003	USA	0103
4	0002	0004	UK	010204
5	0002	0005	France	010205
6	0004	0006	England	01020406
7	0006	0007	Yorkshire	0102040607

This is an example of how the data looks in SQL with it padded with 16 zeros.

0x0000000000000795
0x00000000000007950000000000000796
0x0000000000000797
0x00000000000007970000000000000798
0x000000000000079700000000000007980000000000000799
0x00000000000007970000000000000798000000000000079A
0x00000000000007970000000000000798000000000000079B
To get the child items, then we need to run the following SQL :

@path is the path that we need to find the child items from
@pathplusone is our path plus one, see the function below for how to increment this.
Select Name from Table where RowPath > @path and RowPath < @pathplusone


We increment the path with the following SQL

CREATE FUNCTION [dbo].[IncrementRowPath]
(
    @VarBinary VARBINARY(160) = 0x0000000000000000
)
RETURNS VARBINARY(160)
AS
BEGIN
    DECLARE @Result    VARBINARY(160)
    -- If the data is the wrong size.
    IF(@VarBinary IS NULL OR DATALENGTH(@VarBinary) % 8 != 0)
        SET @Result = 0x0000000000000000
    -- Increment the last 8 byte segment.
    ELSE
    BEGIN
        DECLARE @LastSegment BIGINT
        SET @LastSegment = CAST(SUBSTRING(@VarBinary, DATALENGTH(@VarBinary) - 7, 8) AS BIGINT)
        SET @Result = SUBSTRING(@VarBinary, 1, DATALENGTH(@VarBinary) - 8) + CONVERT(VARBINARY(8), @LastSegment + 1)
    END
    RETURN @Result
END

I hope this helps you out with trees.

The only issue is that you need and index on rowpath, this index is going to be large, but in our testing and our live implementations it's not caused an issue.