Working with large tables in SQL Server

by Andrei Hetel 1. June 2013 18:56

Example of a bad written query, take a look at the snippet below. At the time it was written the table size was very small couple of hundred records maybe. On the RequestedPage field there is clustered index.

DECLARE @description NVARCHAR(MAX)
DECLARE @ArticleLinkId INT
-- Obtain the value for @description variable using a simple process - not important here
SELECT @ArticleLinkId = LinkId FROM PageCollection with (FORCESEEK)  WHERE RequestedPage = @description

Query plan look like in the following picture:

Index Scan

That 'simple' operation took over 30 seconds to complete. Please note by looking at 'Estimated Number of Rows' that the table has more 29 million records. It took some time until I realized that the data type of @description variable was NVARCHAR(MAX) and only after I tried to use FORCESEEK hint (which of course didn't work).

By simply changing the data type to VARCHAR(255) which is also the data type of RequestedPage column everything start to fly, see picture below.

Index Seek

As a conclusion, for a small table it doesn't make any significant difference, but for large table it does. Happy coding!

SQL Server MemToLeave

by Andrei Hetel 9. February 2012 20:11

 

The last SQL Server problem that I was facing was too interesting - so I decide to put it here quickly.

I'm using SQLCLR (SQL Common Language Runtime) for a very long time and never had any major issue with it until yesterday. Error message, as follows:

 

.NET Framework execution was aborted by escalation policy because of out of memory.

 

SQL Server error log gives no more information. So, I start searching for a solution. I thought that I'll be lucky because in the first 10 minutes I found and download a patch from Microsoft that was solving exactly the issue that I described above. But... no, it was a patch for SQL Server 2005 SP1 and SP2 was already installed on the server (forgot to mention that the serves is not a beast but still a decent machine). As a conclusion didn't let me install it, saying that issue was addressed in SP2 (obviously not - or more exactly not in my case).

 

 

Another unhappy programmer was lucky to solve a problem using this statement:

 

DBCC FREESYSTEMCACHE ('ALL')

 

 

As you can imagine, it didn't work. I read a lot of stuff about configuring SQL server memory, MemToLeave usage ect and I won't enter in detail explaining how it works. Last resort was to modify a SQL Server startup parameter (famous -g).

 

For this you need to open SQL Server Configuration Manager and select your instance, see picture.

 

SQL Server Configuration Manager

 

Right click, Properties, Advanced. Modify "Startup Parameters" by adding -g500 (for example - default value is 256).

 

SQL Server Service Properties

 

Happy coding!

 

Silverlight 3 combobox selectedvalue

by Andrei Hetel 25. July 2011 12:22

I was very unpleasant surprised to find out that there is no selectedvalue property for a combobox in Silverlight 3. Most of the time you want to perform an action based on a combo box selection change. So, I come up with a simple solution which will be detailed below.

 

XAML code:

<ComboBox x:Name="MyColors">

 

We need two simple classes to bind the combobox:

 

Public Class CComboItem

     Private _ID As Int64
     Private _Description As String

     Public Sub New(ByVal pid As Int64, ByVal pDesc As String)
         _ID = pid
         _Description = pDesc
     End Sub

     Public Property ID() As Int64
         Get
             Return _ID
         End Get
         Set(ByVal value As Int64)
             _ID = value
         End Set
     End Property

     Public Property Description() As String
         Get
             Return _Description
         End Get
         Set(ByVal value As String)
             _Description = value
         End Set
     End Property

End Class

 

Second class, more exactly a collection:

 

Imports System.Collections.ObjectModel

Public Class CComboList
     Inherits ObservableCollection(Of CComboItem)

     Public Sub addItem(ByVal itm As CComboItem)
         Add(itm)
     End Sub
End Class

 

Load the combo as follows:

 

Dim colorList As New CComboList
colorList.Add(New CComboItem(1, "red"))
colorList.Add(New CComboItem(2, "yellow"))
colorList.Add(New CComboItem(3, "blue"))
MyColors.ItemsSource = colorList
MyColors.DisplayMemberPath = "Description"
MyColors.SelectedIndex = 0

 

Finally, because the post is already too long, get selected value from the combo box:

 

Private Sub MyColors_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles MyColors.SelectionChanged
     Dim id As Int64
     If MyColors.SelectedItem Is Nothing Then Exit Sub
     ' here is the selected value...
     id = DirectCast(MyColors.SelectedItem, CComboItem).ID
End Sub

 

Pretty simple, but at least is doing the trick.