Benchmarker

	using System;
	using System.Diagnostics;
	using Diagnostics.Performance;
	namespace Diagnostics.Performance
	{
	public static class Benchmarker
	{
	private static void DisplayResults(string benchmarkName,
	TimeSpan totalTime,
	TimeSpan averageTime,
	TimeSpan minTime,
	TimeSpan maxTime)
	{
	Console.WriteLine("---------------------------------");
	Console.WriteLine(benchmarkName);
	Console.WriteLine("\tTotal time : " + totalTime.TotalMilliseconds + "ms");
	Console.WriteLine("\tAverage time: " + averageTime.TotalMilliseconds + "ms");
	Console.WriteLine("\tMin time : " + minTime.TotalMilliseconds + "ms");
	Console.WriteLine("\tMax time : " + maxTime.TotalMilliseconds + "ms");
	Console.WriteLine("---------------------------------");
	Console.WriteLine();
	}
	public static void MeasureExecutionTime(string benchmarkName,
	int noOfIterations,
	Action action,
	bool collectGcAfterEachIteration = true,
	bool performWarmup = false)
	{
	var minTime = TimeSpan.MaxValue;
	var maxTime = TimeSpan.MinValue;
	if (performWarmup) action();
	if (collectGcAfterEachIteration) CollectGC();
	var sw = Stopwatch.StartNew();
	for (int i = 0; i < noOfIterations; i++)
	{
	sw.Restart();
	action();
	sw.Stop();
	if (collectGcAfterEachIteration) CollectGC();
	var thisIteration = sw.Elapsed;
	if (thisIteration > maxTime) maxTime = thisIteration;
	if (thisIteration < minTime) minTime = thisIteration;
	}
	var averageTime = new TimeSpan(sw.ElapsedTicks/noOfIterations);
	var totalTime = new TimeSpan(sw.ElapsedTicks);
	DisplayResults(benchmarkName, totalTime, averageTime, minTime, maxTime);
	}
	private static void CollectGC()
	{
	GC.Collect();
	GC.WaitForPendingFinalizers();
	GC.Collect();
	}
	}
	}
	namespace BenchmarkerDemo
	{
	class Program
	{
	static void Main(string[] args)
	{
	Benchmarker.MeasureExecutionTime("Benchmarker demo", 10000, () =>
	{
	var i = 1 + 10 + 100 / 42;
	});
	Console.ReadKey();
	}
	}
	}

view raw Benchmarker.cs hosted with ❤ by GitHub

Simple SQL script for all tables

SELECT
    'delete from ' + table_name 
FROM 
    information_schema.tables 
WHERE
    table_name LIKE 'tbl_junk_%'

RegEx Search and Replace

(?s)<Client>.*?</Client> 

Moving dates between sql and dotNet

	declare @myDate datetime = getdate();
	select
	CurrentDateTime = @myDate,
	Formatted = CONVERT(varchar(8), @myDate, 112) + REPLACE(CONVERT(varchar(8), @myDate, 114),':','')
	/*
	.NET
	Parse in: DateTime.ParseExact()
	Write out: DateTime.ToString("yyyyMMddHHmmss")
	*/

view raw moving-dates-between-sql-dotNet.sql hosted with ❤ by GitHub

Recursive copy of files

xcopy C:\sourceFolder\*.dll C:\destinationFolder /sy

Validating XML with XSD

	using System;
	using System.IO;
	using System.Xml;
	using System.Xml.Schema;
	using System.Xml.XPath;
	namespace XSD_fun
	{
	class Program
	{
	private static string xml =
	@"<?xml version=""1.0"" encoding=""utf-8"" ?>
	<bookstore xmlns = ""http://www.contoso.com/books"">
	<book genre=""autobiography"" publicationdate=""1981-03-22"" ISBN=""1-861003-11-0"">
	<title>The Autobiography of Benjamin Franklin</title>
	<author>
	<first-name>Benjamin</first-name>
	<last-name>Franklin</last-name>
	</author>
	<price>8.99</price>
	</book>
	<book genre = ""novel"" publicationdate=""1967-11-17"" ISBN=""0-201-63361-2"">
	<title>The Confidence Man</title>
	<author>
	<first-name>Herman</first-name>
	<last-name>Melville</last-name>
	</author>
	<price>11.99</price>
	</book>
	<book genre = ""philosophy"" publicationdate=""1991-02-15"" ISBN=""1-861001-57-6"">
	<title>The Gorgias</title>
	<author>
	<name>Plato</name>
	</author>
	<price>9.99</price>
	</book>
	</bookstore>";
	private static string xsd =
	@"<?xml version=""1.0"" encoding=""utf-8""?>
	<xs:schema attributeFormDefault = ""unqualified"" elementFormDefault=""qualified"" targetNamespace=""http://www.contoso.com/books"" xmlns:xs=""http://www.w3.org/2001/XMLSchema"">
	<xs:element name = ""bookstore"">
	<xs:complexType>
	<xs:sequence>
	<xs:element maxOccurs = ""unbounded"" name=""book"">
	<xs:complexType>
	<xs:sequence>
	<xs:element name = ""title"" type=""xs:string"" />
	<xs:element name = ""author"">
	<xs:complexType>
	<xs:sequence>
	<xs:element minOccurs = ""0"" name=""name"" type=""xs:string"" />
	<xs:element minOccurs = ""0"" name=""first-name"" type=""xs:string"" />
	<xs:element minOccurs = ""0"" name=""last-name"" type=""xs:string"" />
	</xs:sequence>
	</xs:complexType>
	</xs:element>
	<xs:element name = ""price"" type=""xs:decimal"" />
	</xs:sequence>
	<xs:attribute name = ""genre"" type=""xs:string"" use=""required"" />
	<xs:attribute name = ""publicationdate"" type=""xs:date"" use=""required"" />
	<xs:attribute name = ""ISBN"" type=""xs:string"" use=""required"" />
	</xs:complexType>
	</xs:element>
	</xs:sequence>
	</xs:complexType>
	</xs:element>
	</xs:schema>";
	private static string xmlFilename = "books.xml";
	private static string xsdFilename = "books.xsd";
	static void Main()
	{
	try
	{
	File.WriteAllText(xmlFilename, xml);
	File.WriteAllText(xsdFilename, xsd);
	XmlReaderSettings settings = new XmlReaderSettings();
	settings.Schemas.Add("http://www.contoso.com/books", xsdFilename);
	settings.ValidationType = ValidationType.Schema;
	XmlReader reader = XmlReader.Create(xmlFilename, settings);
	XmlDocument document = new XmlDocument();
	document.Load(reader);
	ValidationEventHandler eventHandler = new ValidationEventHandler(ValidationEventHandler);
	// the following call to Validate succeeds.
	document.Validate(eventHandler);
	// add a node so that the document is no longer valid
	XPathNavigator navigator = document.CreateNavigator();
	navigator.MoveToFollowing("price", "http://www.contoso.com/books");
	XmlWriter writer = navigator.InsertAfter();
	writer.WriteStartElement("anotherNode", "http://www.contoso.com/books");
	writer.WriteEndElement();
	writer.Close();
	// the document will now fail to successfully validate
	document.Validate(eventHandler);
	}
	catch (Exception ex)
	{
	Console.WriteLine(ex.Message);
	}
	}
	static void ValidationEventHandler(object sender, ValidationEventArgs e)
	{
	switch (e.Severity)
	{
	case XmlSeverityType.Error:
	Console.WriteLine("Error: {0}", e.Message);
	break;
	case XmlSeverityType.Warning:
	Console.WriteLine("Warning {0}", e.Message);
	break;
	}
	}
	}
	}

view raw validating-xml-with-xsd.cs hosted with ❤ by GitHub

SQL Script for Memory Stats

	SET NOCOUNT ON
	-- Get size of SQL Server Page in bytes
	DECLARE @pg_size INT, @Instancename varchar(50)
	SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
	-- Extract perfmon counters to a temporary table
	IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters
	SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters
	-- Get SQL Server instance name
	SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM #perfmon_counters WHERE counter_name = 'Buffer cache hit ratio'
	-- Print Memory usage details
	PRINT '----------------------------------------------------------------------------------------------------'
	PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
	PRINT '----------------------------------------------------------------------------------------------------'
	SELECT 'Memory visible to the Operating System'
	SELECT CEILING(physical_memory_kb/1048576.0) as [Physical Memory_GB], CEILING(virtual_memory_kb/1048576.0) as [Virtual Memory GB] FROM sys.dm_os_sys_info
	SELECT 'Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters'
	SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Total Server Memory (KB)'
	SELECT 'Memory needed as per current Workload for SQL Server instance'
	SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Target Server Memory (KB)'
	SELECT 'Total amount of dynamic memory the server is using for maintaining connections'
	SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Connection Memory (KB)'
	SELECT 'Total amount of dynamic memory the server is using for locks'
	SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Lock Memory (KB)'
	SELECT 'Total amount of dynamic memory the server is using for the dynamic SQL cache'
	SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'SQL Cache Memory (KB)'
	SELECT 'Total amount of dynamic memory the server is using for query optimization'
	SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Optimizer Memory (KB) '
	SELECT 'Total amount of dynamic memory used for hash, sort and create index operations.'
	SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Granted Workspace Memory (KB) '
	SELECT 'Total Amount of memory consumed by cursors'
	SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total'
	SELECT 'Number of pages in the buffer pool (includes database, free, and stolen).'
	SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages'
	SELECT 'Number of Data pages in the buffer pool'
	SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages'
	SELECT 'Number of Free pages in the buffer pool'
	SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages'
	SELECT 'Number of Reserved pages in the buffer pool'
	SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages'
	SELECT 'Number of Stolen pages in the buffer pool'
	SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages'
	SELECT 'Number of Plan Cache pages in the buffer pool'
	SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages' and instance_name = '_Total'
	SELECT 'Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references'
	SELECT cntr_value as [Page Life in seconds],CASE WHEN (cntr_value > 300) THEN 'PLE is Healthy' ELSE 'PLE is not Healthy' END as 'PLE Status' FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Page life expectancy'
	SELECT 'Number of requests per second that had to wait for a free page'
	SELECT cntr_value as [Free list stalls/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free list stalls/sec'
	SELECT 'Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed'
	SELECT cntr_value as [Checkpoint pages/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Checkpoint pages/sec'
	SELECT 'Number of buffers written per second by the buffer manager"s lazy writer'
	SELECT cntr_value as [Lazy writes/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Lazy writes/sec'
	SELECT 'Total number of processes waiting for a workspace memory grant'
	SELECT cntr_value as [Memory Grants Pending] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Pending'
	SELECT 'Total number of processes that have successfully acquired a workspace memory grant'
	SELECT cntr_value as [Memory Grants Outstanding] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Outstanding'

view raw sql-memory.tsql hosted with ❤ by GitHub

Data reconciliation against SQL Servers

Requirement:
Compare two differently shaped data tables across two SQL Servers on a nightly basis.

Solution:
1. Create a view on each server that gets the data into a consistent (matching) shape.  Use CAST to ensure the datatype also match:

2. Create a RedGate SQLDataCompare project that compares the two views:

3. Run a SQLDataCompare command nightly:

	sqldatacompare
	/Project:"C:\FolderName\FileName.sdc"
	/Out:"C:\FolderName\Output.txt"
	/Export:"C:\FolderName" /force

view raw sqldatacompare.bat hosted with ❤ by GitHub

4. Which produces the following results:

In this case, 2 records were identical, two records were different and one record was found in "DB2" that didn't exist in "DB1".

Here is the summary and detail output, which is configurable from within your project file.  I've opted to some identical records here, in production you probably wouldn't want to:

5. Email yourself the results 

Batch script for creating an empty file

copy NUL c:\myfoldername\myfilename.txt

Run all SQL files in a directory

Add this .bat file to the directory and run it:

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause

Adding speech marks around a list of data

# Ever had to add text characters to a list of data, like speech marks to each line before and after the data and a comma?

Here's an example of how to do this using Notepad++:

Filter XML with XSLT

	<?xml version="1.0" encoding="utf-8"?>
	<teams>
	<team><name>Arsenal</name><stadium name="Emirates" capacity="60000" /></team>
	<team><name>Crystal Palace</name><stadium name="Selhurst" capacity="26000" /></team>
	<team><name>Liverpool</name><stadium name="Anfield" capacity="40000" /></team>
	<team><name>Man Utd</name><stadium name="Old Trafford" capacity="70000" /></team>
	<team><name>West Ham</name><stadium name="Bolelyn" capacity="30000" /></team>
	</teams>

view raw source.xml hosted with ❤ by GitHub

	<?xml version="1.0"?>
	<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:output method="xml" indent="yes"/>
	<xsl:strip-space elements="*"/>
	<xsl:template match="teams">
	<teams>
	<xsl:apply-templates select="team[name='Crystal Palace' or stadium/@capacity > 50000]" />
	</teams>
	</xsl:template>
	<xsl:template match="*">
	<xsl:copy-of select="."/>
	</xsl:template>
	</xsl:stylesheet>
	<!--
	output.indent and strip-space tidy up the xml output
	. = self (includes current node)
	* = child elements and attributes (excludes current node)
	-->

view raw filter.xslt hosted with ❤ by GitHub

	<?xml version="1.0" encoding="utf-8"?>
	<teams>
	<team>
	<name>Arsenal</name>
	<stadium name="Emirates" capacity="60000" />
	</team>
	<team>
	<name>Crystal Palace</name>
	<stadium name="Selhurst" capacity="26000" />
	</team>
	<team>
	<name>Man Utd</name>
	<stadium name="Old Trafford" capacity="70000" />
	</team>
	</teams>

view raw result.xml hosted with ❤ by GitHub

SQL pivot without aggregration

Pivot data to return the first two country entries for each person.

	select * from Source_Table;
	select * from
	( select
	person,
	country,
	'country' + cast(rank()
	over (partition by person order by id)
	as varchar(10))
	as countryrank
	from dbo.Source_Table) as rankedSource
	pivot
	( max (country) for countryrank in (country1, country2)) as pivottable;

view raw sql-pivot.sql hosted with ❤ by GitHub