Monday, August 11, 2014

SQL 2012 Wait Type : Dirty Page Poll

Whilst running some tried and tested scripts to look at wait types, I came across one I had not seen before. It is a background thread that looks to see if there are dirty pages that need to be flushed to disk. Given I found it on a barely used test box, I was not worried.

To quote the ToadWorld site, 'It is a benign wait type and shouldn’t be any concern if you happen to see high values for this wait type on your systems'.



Toadworld : Dirty Page Poll Wait Type in SQL 2012


Thursday, August 7, 2014

Adventureworks : Large Sample Database

A colleague wanted a 'large database' (he didn't care what) for testing perfomance of a backup solution. Given privacy, security, ethics etc prevent me from parting with a genuine database and creating mock/random data is time consuming I was faced with an issue.

Creating an empty database and simply sizing the files wouldn't work as the backups would be highly compressable i.e would be minuscule. Likewise the data compression that I've applied to my backups by default also needs to be off.

I recalled seeing a tweet about a custom version of AdventureWorks and after a short search I found Enlarging the AdventureWorks Sample Databases. This script adds random data, increaing the size of AdventureWorks by creating SalesOrderHeaderEnlarged and SalesOrderDetailEnlarged tables. It can also easily be adapted to create larger datasets (Hint : Increase the number of UNIONed tables in the derived table).



Monday, July 7, 2014

Function dbo.StripUnwantedCharacters - Cleaning up unwanted ASCII characters

Having determined I had some unwanted NUL characters in my data (see ASCII Value Character Counts Procedure) I set out to remove them.

I tried permutations of LIKE and CHAR to find the character as well as REPLACE and CHARINDEX , all to no avail. Then I spotted this -

 0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.

This was on the MSDN page for CHARINDEX

You can read the whole string character by character however and recreate it, removing those characters. This is what my function below does.


CREATE FUNCTION dbo.StripUnwantedCharacters (@InputString VARCHAR(MAX)) 
RETURNS VARCHAR(MAX)
AS 
BEGIN

DECLARE @OutputString varchar(MAX)
DECLARE @CurrentCharacterValue INT
DECLARE @Position INT

SET @OutputString = ''
SET @Position = 1

WHILE @Position < LEN(@InputString)+1
 BEGIN
 SET @CurrentCharacterValue = ASCII(SUBSTRING(@InputString, @Position, 1))

 IF @CurrentCharacterValue >= 9 -- Exclude Characters where ASCII value less that 9
  BEGIN
  SET @OutputString = @OutputString + CHAR(@CurrentCharacterValue)
  END

 SET @Position = @Position + 1
 END
RETURN @OutputString

END
GO

Code to use this procedure is as simple as ...

UPDATE tablename
SET cleandata = dbo.StripUnwantedCharacters(dirtydata)

Or too generate sql for all columns ...

SELECT 
'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] SET ' + COLUMN_NAME + ' = RTRIM(dbo.StripUnwantedCharacters(' + COLUMN_NAME + '))' 
 from INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('char','varchar')
GO