Handling long running queries on SQL Server can be one of the hardest things to deal with as a DBA. You have dozens of variables that must be considered before you can actually do anything about them. Usually if you ask a number of DBAs how they go about determining if the long runtimes can be resolved, you will get several different variations of troubleshooting. Even if the steps are blurred in sequential order, they will typical contain a few of the following

A while back I wrote about “Why English is really important to us non-English speakers.” and I still believe that is true. But lets not forget that English is our second language and that we sometimes miss out on the details, the words between the lines. Sometimes we even missinterpret the whole thing because we misunderstood a word. After All isn’t that what the movie “Lost in translation” was all about ;-), but that would be guessing on my part since I never watched it completely, however beautiful scarlet may be.

I see a lot of people doing this. Dim con As New System.Data.SqlClient.SqlConnection con.ConnectionString = "..." con.Open() Dim command As New System.Data.SqlClient.SqlCommand command.Connection = con command.CommandText = "update tbl set col = 'something'" command.ExecuteNonQuery() command.Dispose() con.Dispose() I like to use the interfaces instead and use the factory methods that come with it. Something like this. Dim con As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection con.ConnectionString = "..." con.Open() Dim command As System.Data.IDbCommand = con.CreateCommand command.Connection = con command.CommandText = "update tbl set col = 'something'" command.ExecuteNonQuery() command.Dispose() con.Dispose()``` this way if we ever have to change to an other database we just have to change the = New System.Data.SqlClient.SqlConnection. Of course we would just get a connection from a central place and then we only have to change that little piece of code once. IDbCommand also has a factory method to create parameters. Since you are using an sqlconnection the factory methods will create sql implementation classes which are optimized to work with MS-SQL-Server. And even then we have to hope that the databases understand the same dialect 😉 SO it’s not a perfect solution but it can help you on the way to create a more maintainable application.

Last week I was asked to create a normalisation routine for the Msp spectra that my application uses. No worry if you don’t know what Msp is. The thing is that it needed to find the maximum value in a collection of values and then do a calculation with that number over the other values in that collection. First the value object. Namespace Model Public Class MspCoordinate Private _A As Decimal Private _WavelengthInNm As Decimal Public Sub New() Me.New(0, 0) End Sub Public Sub New(ByVal A As Decimal, ByVal WavelengthinNm As Decimal) Me.A = A Me.WavelengthInNm = WavelengthinNm End Sub Public Property A() As Decimal Get Return _A End Get Set(ByVal value As Decimal) _A = value End Set End Property Public Property WavelengthInNm() As Decimal Get Return _WavelengthInNm End Get Set(ByVal value As Decimal) _WavelengthInNm = value End Set End Property Public Overrides Function ToString() as String Return string.Format ("_A: {0}, _WavelengthInNm: {1}", _A, _WavelengthInNm) End Function Public Overloads Function Equals(ByVal obj As MspCoordinate) As Boolean If ReferenceEquals(Nothing, obj) Then Return False If ReferenceEquals(Me, obj) Then Return True Return obj._A = _A End Function Public Overloads Overrides Function Equals(ByVal obj As Object) As Boolean If ReferenceEquals(Nothing, obj) Then Return False If ReferenceEquals(Me, obj) Then Return True If Not Equals(obj.GetType(), GetType(MspCoordinate)) Then Return False Return Equals(DirectCast(obj, MspCoordinate)) End Function Public Overrides Function GetHashCode() As Integer Return _A.GetHashCode() End Function End Class End Namespace``` And then some unittests to see if what I get from my mspobject is correct. ```vbnet Imports NUnit.Framework Namespace Tests &lt;TestFixture()&gt; _ Public Class TestMspLinq Dim _msp As Model.MspLinq Dim _mspCoordinates As IList(Of Model.MspCoordinate) &lt;SetUp()&gt; _ Public Sub Setup() _mspCoordinates = New List(Of Model.MspCoordinate) _mspCoordinates.Add(New Model.MspCoordinate(0.2, 380)) _mspCoordinates.Add(New Model.MspCoordinate(0.21, 381)) _mspCoordinates.Add(New Model.MspCoordinate(0.22, 382)) _mspCoordinates.Add(New Model.MspCoordinate(0.23, 383)) _mspCoordinates.Add(New Model.MspCoordinate(0.25, 384)) _mspCoordinates.Add(New Model.MspCoordinate(0.22, 385)) _mspCoordinates.Add(New Model.MspCoordinate(0.23, 386)) _mspCoordinates.Add(New Model.MspCoordinate(0.24, 387)) _mspCoordinates.Add(New Model.MspCoordinate(0.25, 388)) _mspCoordinates.Add(New Model.MspCoordinate(0.2, 389)) _mspCoordinates.Add(New Model.MspCoordinate(0.2, 390)) _msp = New Model.MspLinq(_mspCoordinates) End Sub &lt;Test()&gt; _ Public Sub MspCoordinatesNormalised_returns_same_number_of_results_as_MspCoordinates() Assert.AreEqual(_msp.MspCoordinates.Count, _msp.MspCoordinatesNormalized.Count) End Sub &lt;Test()&gt; _ Public Sub MspCoordinatesNormalized_highest_A_should_be_1() Assert.AreEqual(1, _msp.MspCoordinatesNormalized(4).A) End Sub &lt;Test()&gt; _ Public Sub MspCoordinatesNormalized_lowest_A_should_be_0_8() Assert.AreEqual(0.8, _msp.MspCoordinatesNormalized(0).A) End Sub End Class End Namespace``` There are more unittests I should have written but the blogpost is getting long enough as it is. I started of with a baseclass and let the implementation details of the normalization routine over to the subclasses. BASECLASS ```vbnet Imports maxcollection.Model Public MustInherit class MspBase Protected _MspCoordinates As IList(Of MspCoordinate) Protected _MspCoordinatesNormalised As IList(Of MspCoordinate) Public Sub New(ByVal MspCoordinates As IList(Of MspCoordinate)) Me._MspCoordinates = MspCoordinates End Sub Public ReadOnly Property MspCoordinates() As IList(Of MspCoordinate) Get Return _MspCoordinates End Get End Property Public ReadOnly Property MspCoordinatesNormalized() As IList(Of MspCoordinate) Get If _MspCoordinatesNormalised Is Nothing Then CreateNormalizedMspCoordinates() End If Return _MspCoordinatesNormalised End Get End Property MustOverride Protected Sub CreateNormalizedMspCoordinates() Protected Sub CalculateNormalizedCoordinates(ByVal _Max As Decimal) _MspCoordinatesNormalised = New List(Of MspCoordinate) Dim _MspCoordinate As MspCoordinate For Each _coordinate As MspCoordinate In _MspCoordinates _MspCoordinate = New MspCoordinate _MspCoordinate.A = _coordinate.A / _Max _MspCoordinate.WavelengthInNm = _coordinate.WavelengthInNm _MspCoordinatesNormalised.Add(_MspCoordinate) Next End Sub Public Overloads Function Equals(ByVal obj As MspLinq) As Boolean If ReferenceEquals(Nothing, obj) Then Return False If ReferenceEquals(Me, obj) Then Return True Return Equals(obj._MspCoordinates, _MspCoordinates) End Function Public Overloads Overrides Function Equals(ByVal obj As Object) As Boolean If ReferenceEquals(Nothing, obj) Then Return False If ReferenceEquals(Me, obj) Then Return True If Not Equals(obj.GetType(), GetType(MspLinq)) Then Return False Return Equals(DirectCast(obj, MspLinq)) End Function Public Overrides Function GetHashCode() As Integer If _MspCoordinates IsNot Nothing Then Return _MspCoordinates.GetHashCode() Return 0 End Function Public Overrides Function ToString() As String Return String.Format("_MspCoordinates: {0}", _MspCoordinates) End Function end class``` SUBCLASS LINQ ```vbnet Namespace Model Public Class MspLinq Inherits MspBase Public Sub New(ByVal MspCoordinates As IList(Of MspCoordinate)) MyBase.New(MspCoordinates) End Sub Protected Overrides Sub CreateNormalizedMspCoordinates() Dim _max = (From e In _MspCoordinates Select e.A).Max CalculateNormalizedCoordinates(_max) End Sub End Class End Namespace``` SUBCLASS FOR ```vbnet Namespace Model Public Class MspFor Inherits MspBase Public Sub New(ByVal MspCoordinates As IList(Of MspCoordinate)) MyBase.New(MspCoordinates) End Sub Protected Overrides Sub CreateNormalizedMspCoordinates() Dim _max As Decimal = -100 For i As Integer = 0 To MspCoordinates.Count - 1 If _max &lt; MspCoordinates(i).A Then _max = MspCoordinates(i).A End If Next CalculateNormalizedCoordinates(_max) End Sub End Class End Namespace``` SUBCLASS FOREACH ```vbnet Namespace Model Public Class MspForEach Inherits MspBase Public Sub New(ByVal MspCoordinates As IList(Of MspCoordinate)) MyBase.New(MspCoordinates) End Sub Protected Overrides Sub CreateNormalizedMspCoordinates() Dim _max As Decimal = -100 For Each _coordinate As MspCoordinate In MspCoordinates If _max &lt; _coordinate.A Then _max = _coordinate.A End If Next CalculateNormalizedCoordinates(_max) End Sub End Class End Namespace``` I want this to be as fast as possible since I could potentialy be doing this for thousands of Msp&#8217;s at a time. And yes all of them can be shown on the screen. AS you might have noticed I lazy loaded the normalized collection because I don&#8217;t want the calculations to be done everytime the object is created. And this a testfixture I created for testing the perfomance of this. ```vbnet Imports NUnit.Framework Namespace Tests &lt;TestFixture()&gt; _ Public Class PerformanceTestNormalizingMsp Dim _mspLinq As Model.MspLinq Dim _mspFor As Model.MspFor Dim _mspForEach As Model.MspForEach Dim _mspCoordinates As IList(Of Model.MspCoordinate) &lt;SetUp()&gt; _ Public Sub Setup() End Sub Private Sub SetupCoordinates(ByVal number As Integer) _mspCoordinates = New List(Of Model.MspCoordinate) Dim _mspcoordinate As Model.MspCoordinate Dim _Random As New Random For i As Integer = 1 To number _mspcoordinate = New Model.MspCoordinate _mspcoordinate.A = _Random.NextDouble() _mspcoordinate.WavelengthInNm = number _mspCoordinates.Add(_mspcoordinate) Next End Sub &lt;Test()&gt; _ Public Sub PerfomanceOfLinq() Dim stopwatch As New Stopwatch Dim number As Integer = 100000 SetupCoordinates(number) stopwatch.Start() _mspLinq = New Model.MspLinq(_mspCoordinates) Assert.AreEqual(number, _mspLinq.MspCoordinatesNormalized.Count) stopwatch.Stop() Debug.WriteLine("Linq with " & number & " coordinates took " & stopwatch.ElapsedMilliseconds) End Sub &lt;Test()&gt; _ Public Sub PerfomanceOfFor() Dim stopwatch As New Stopwatch Dim number As Integer = 100000 SetupCoordinates(number) stopwatch.Start() _mspFor = New Model.MspFor(_mspCoordinates) Assert.AreEqual(number, _mspFor.MspCoordinatesNormalized.Count) stopwatch.Stop() Debug.WriteLine("For with " & number & " coordinates took " & stopwatch.ElapsedMilliseconds) End Sub &lt;Test()&gt; _ Public Sub PerfomanceOfForEach() Dim stopwatch As New Stopwatch Dim number As Integer = 100000 SetupCoordinates(number) stopwatch.Start() _mspForEach = New Model.MspForEach(_mspCoordinates) Assert.AreEqual(number, _mspForEach.MspCoordinatesNormalized.Count) stopwatch.Stop() Debug.WriteLine("ForEach with " & number & " coordinates took " & stopwatch.ElapsedMilliseconds) End Sub End Class End Namespace``` And these are the results when I ran it with 10000 coordinates in the collection. <span class="MT_blue">first run</p> <p> Linq with 10000 coordinates took 40<br /> For with 10000 coordinates took 12<br /> ForEach with 10000 coordinates took 11 </p> <p> second run </p> <p> Linq with 10000 coordinates took 42<br /> For with 10000 coordinates took 12<br /> ForEach with 10000 coordinates took 11 </p> <p> third run </p> <p> Linq with 10000 coordinates took 40<br /> For with 10000 coordinates took 11<br /> ForEach with 10000 coordinates took 11</span> </p> <p> As you can see pretty consistent numbers. </p> <p> Linq being the slowest by a factor of 4. </p> <p> Now lets see what happens with 100000 coordinates </p> <p> <span class="MT_blue">first run</p> <p> Linq with 100000 coordinates took 152<br /> For with 100000 coordinates took 169<br /> ForEach with 100000 coordinates took 123 </p> <p> second run </p> <p> Linq with 100000 coordinates took 148<br /> For with 100000 coordinates took 163<br /> ForEach with 100000 coordinates took 128 </p> <p> third run </p> <p> Linq with 100000 coordinates took 154<br /> For with 100000 coordinates took 159<br /> ForEach with 100000 coordinates took 126</span> </p> <p> Mmm linq has caugth up with the other two. and is even faster than 4. </p> <p> When I add another 0 I get these results </p> <p> <span class="MT_blue">Linq with 1000000 coordinates took 1464<br /> For with 1000000 coordinates took 1392<br /> ForEach with 1000000 coordinates took 1419</span> </p> <p> <span class="MT_red">The differences being small. So what can I conclude of all this.</p> <p> Not much really. For many elements in the collection linq is just as fast as the others but it is more readable and shorter to write. When it doesn&#8217;t really matter the difference is big but when it does matter the difference is small so we can pick any one of them.</span> </p>

So Ward Pond and Michelle Ufford started this SQL rap contest. I replied on twitter that I loved their raps and they told me “bring your skillz!” Well I don’t think I have any skillz (in that regard) so here it goes _Who you trying to get crazy with SA? Don’t you know I’m loco………. ashes 2 ashes and dust 2 dust when u fire up ur queries ur RAM goes bust

A couple of days ago onpnt posted the following blogpost: Import directory of XML files into SQL Server 2005 In that post he was using SQLCLR to import a bunch of files. Some of you might not be so familiar with .NET so I am providing a T-SQL way to do something similar You will need to create a directory testxml on the c drive and put a bunch of XML files in there. If you don’t have any XML files then save the following two as file1.xml and file2.xml

This question came up recently so I decided to blog about it If you have your stored procedures in source control and you want to see if they are the same as the one on the server, all you need to do is script out the procs and do a diff right? Let’s see what happens, create this proc Create procedure prTest as set nocount on if exists (select 1 from sys.procedures where name = 'prTest') print 'yes' else print'no' Now script it out, you do this by finding the proc under your database–>programmability–>stored procedures. Right click on the proc, select script procedure as and then choose create to and pick a new window

The drive to work today was a short 2 hours over the normal 40 minute commute. It turns out there was a semi that drove off a bridge and landed face first on the train tracks below. This caused several problems with traffic this morning. Both north and south bound lanes of the interstate were at a crawl and to top it off, the major railway that brings commuters to and from the greater Chicago area was halted due to the semi kind of being in the way. Of course EMT and crews to clean the situation up were quick to the scene, but the task of this major cleanup would take hours. In the meantime the traffic situation in what is already known as the worst traffic area in America is at a stop. My first concern was the semi driver. I commute to the northern part of Wisconsin very often and when you drive the highways in the northern areas of Wisconsin, you start to get respect for what these people do. Then I started thinking about the situation as one large entity and quickly came to realize it was so similar to a poorly planned disaster/recovery plan that I thought it deserved this posting.

This is my first blog, so please go easy one me. So from what I’ve seen a lot of companies tend to need to see hierarchical data in a report, or see data in a hierarchical structure in a report. Usually the best and most simple way to display this data is a drill down report. For the purposes of this blog I’m going to act as if the reader hasn’t used SSRS before but has used Sql Server 2005 and some knowledge in TSQL.

I’m a fan of not allowing connectivity from Excel to my SSAS instances. I just don’t see the active connection ever being a truely manageable and secure connection. It remains though that the users like the way they do things and will fight hand and foot against change. Although it will be a hard task for you to remove the Excel connectivity to SSAS, you can provide them with a viable replacement given report models in reporting services.