SQL Server 2008: Using Hex Values in TSQL Scripts

I was updating several TSQL scripts today containing ARGB values and thinking (not for the first time) that it was a shame I couldn’t use the Hex values directly (eg 0xFFB40000) rather than converting to the harder to manage base10 integer equivalent (-4980736).

The SQL Server 2008 release updated the CONVERT() function to be able to do just that!

     CONVERT(varbinary(4), ‘0xFFB40000’, 1)

The last Style parameter governs the the conversion.

From Books Online:

Binary Styles

When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

 

Value

Output

0 (default)

Translates ASCII characters to binary bytes or binary bytes to ASCII characters. Each character or byte is converted 1:1.

If the data_type is a binary type, the characters 0x are added to the left of the result.

1, 2

If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.

If the length of the converted expression is greater than the length of the data_type the result will be right truncated.

Fixed length data_types that are larger then the converted result will have zeros added to the right of the result.

If the data_type is a character type, the expression must be a binary expression. Each binary character is converted into two hexadecimal characters. If the length of the converted expression is greater than the data_type length it will be right truncated.

If the data_type is a fix sized character type and the length of the converted result is less than its length of the data_type; spaces are added to the right of the converted expression to maintain an even number of hexadecimal digits.

The characters 0x will be added to the left of the converted result for style 1.

ASP.NET Code Samples Collection

If you missed this in the last MSDN Flash, J. D. Meier (Developer Guidance, Patterns & Practices) has a roundup of various ASP.NET code sample on his blog: ASP.NET Code Samples Collection

The ASP.NET Code Samples Collection is a roundup and map of ASP.NET code samples from  various sources including the MSDN library, www.ASP.net, Code Gallery, CodePlex, and Microsoft Support.

Determine if your .NET Application has a Memory Leak

You can use Perfmon to determine if your .NET application is leaking memory (managed or unmanaged).

Run Perfmon.exe and add these counters:

  • Process / Private bytes
  • .NET CLR Memory / # bytes in all heaps
  • .NET CLR LocksAndThreads / # of current logical threads

Run your application for a representative length of time and exercise its functionality.

If Private bytes is increasing but # bytes in all heaps is not, then unmanaged memory is leaking. If both both are increasing, then managed memory is leaking.  If the # of current logical threads is increasing beyond what you expect, then thread stacks are leaking.

If Private Bytes is periodically jumping in 1MB increments with a corresponding increase in # of current logical Threads, a thread stack leak is the likely cause.

[Tracking it down is another matter! For that, one of the commercially available tools is invaluable: Memprofiler, ANTS Memory Profiler, dotTrace]

.NET CLR Large Object Heap

I was recently talking to several colleagues about the CLR garbage collector, and the question of where the size of 85,000 bytes as the threshold for objects on the Large Object Heap (LOH) comes from (instead of the seemingly more obvious value of 65,535).

This value was determined as a result of performance tuning by the CLR garbage collector team.

One of the reasons you should try to keep your object allocation sizes below this value (and off the Large Object Heap) is because unlike the Generation 0,1 and 2 area, the LOH is not compacted.

An excellent reference for the LOH is here: Large Object Heap Uncovered

There were changes made in .NET 4.0 as to how the LOH performs, but according to this connect issue there are still improvements that could be made:

Based on the example provided, we were able to allocate nearly 23 times as much memory before running out of memory on the large object heap going from version 3.5 to version 4. That’s not to say we are finished addressing fragmentation issues—we will continue to pay attention as we improve in future versions. In the .NET 4 release, we heard from customers that latency was a high priority. So that is where we have spent much of our focus. [Brandon Bray: Lead program manager responsible for the garbage collector in the .NET Framework]

.NET 4.0 introduced differences between the workstation and server versions of the garbage collector:

Comparing Workstation and Server Garbage Collection

Threading and performance considerations for workstation garbage collection:

  • The collection occurs on the user thread that triggered the garbage collection and remains at the same priority. Because user threads typically run at normal priority, the garbage collector (which runs on a normal priority thread) must compete with other threads for CPU time.

    Threads that are running native code are not suspended.

  • Workstation garbage collection is always used on a computer that has only one processor, regardless of the setting. If you specify server garbage collection, the CLR uses workstation garbage collection with concurrency disabled.

Threading and performance considerations for server garbage collection:

  • The collection occurs on multiple dedicated threads that are running at THREAD_PRIORITY_HIGHEST priority level.

  • A dedicated thread to perform garbage collection and a heap are provided for each CPU, and the heaps are collected at the same time. Each heap contains a small object heap and a large object heap, and all heaps can be accessed by user code. Objects on different heaps can refer to each other.

  • Because multiple garbage collection threads work together, server garbage collection is faster than workstation garbage collection on the same size heap.

  • Server garbage collection often has larger size segments.

  • Server garbage collection can be resource-intensive. For example, if you have 12 processes running on a computer that has 4 processors, there will be 48 dedicated garbage collection threads if they are all using server garbage collection. In a high memory load situation, if all the processes start doing garbage collection, the garbage collector will have 48 threads to schedule.

If you are running hundreds of instances of an application, consider using workstation garbage collection with concurrent garbage collection disabled. This will result in less context switching, which can improve performance.

Ref.: Fundamentals of Garbage Collection

Reminder: Perth .NET User Group Meeting: Thurs Nov 4th, Production .NET Debugging with Piers Williams

In this talk, you will learn how to debug your application in the field with this exploration of advanced debugging techniques for managed code applications. Hang, crash, leak or other – we will go beyond Visual Studio debugging and talk about replicating and isolating problems using Perfmon, WMI, WinDbg, and automating the diagnosis with PowerShell using PowerDbg.

  • TOPIC:  Production .NET Debugging with Piers Williams

  • DATE:   Thursday, Nov 4th, 5:30pm – 7:00pm

  • VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth

  • COST:   Free. All welcome

Piers is a .NET Architect with Beacon Technology, and a contributor to PowerDbg (http://powerdbg.codeplex.com).

There will be a door prize of a choice of license from JetBrains (one of ReSharper , TeamCity Build Agent, dotTrace Profiler, RubyMine, IntelliJ IDEA), and several coupons to Tekpub‘s high-quality, online screencasts for programmers.

If you have any problems getting in to the venue, please call me on 0422 971 731