Recently during some internal discussions we had a lot of talk around the stack sizes for various SQL builds. Here is the summary of that talk:

 

SQL Server Build	OS Build	Stack Size
SQL Server x86 (32-bit)	x86 (32-bit)	512 KB
SQL Server x86 (32-bit)	x64 (64-bit)	768 KB (see notes 1 and 2)
SQL Server x64 (64-bit)	x64 (64-bit)	2048 KB
SQL Server IA64 (64-bit Itanium)	IA64 (64-bit Itanium)	4096 KB (see note 3)

 

Notes:

1. This (wow64 stack size) is confirmed from http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx: SQL Server 2000 running on X64 in WOW mode also uses more memory per worker (768 KB = 512 KB for standard stack + 256 KB used by WOW to support the stack).
2. Also check http://support.microsoft.com/kb/916950 for an issue related to SQL 2000 x86 (really this should be the only case where this configuration might be used) running in WOW64 mode on x64 OS. http://msdn.microsoft.com/en-us/library/aa384219(VS.85).aspx mentions the overhead which is required for a 32-bit process running inside WOW64.
3. Slava Oaks blogged specifically about this http://blogs.msdn.com/slavao/archive/2005/03/19/399117.aspx

I hope this summary helps someone out there!

I only recently started using PowerShell and was amazed with the flexibility and ease of use. Here’s a sample script I put together after a few hours of playing around. I have also described the scenario around the requirement for this script.

Task at hand: A folder contains many files such as:

• File1v1
• File2v1
• File2v2
• ABCv3
• ABCv4
• ABCv5
• MyFile9
• MyFile15
• MyFile21

The requirement was to keep only the latest version of each file (and therefore identify the older files to move). In my case, I have over 400 such files at any given time (and the number will grow.) The only way to distinguish (in my specific case) which file was the ‘latest’ was to use the numeric suffix at the end of each file (timestamps etc. are not reliable for my requirement.)

So to automate this task, I figured I’d try to learn PowerShell and put it to use. Here is the script I used:

################# IdentifyOlderVersions.ps1 ######################

$files = dir X:myFolder*.* | Sort-Object

$prevfile = “”
$filenamewithoutsuffix = “”
$previousversions = @()

foreach ($file in $files)
{
    $filenamewithoutsuffix = ($file.BaseName -split “d+$”)[0]
    if ($filenamewithoutsuffix -eq $prevfile)
    {
        # this is the same file ‘lineage’, add the current file to the array
        $previousversions = $previousversions + $file
    }
    else
    {
        # new lineage, need to move files and then reset $previousversions
        $listoffilestobemoved = “”
        $tmpindex = 0
        foreach ($filetobemoved in $previousversions)
        {

            # The check below is to ensure that we only list the files to be moved

            if ($tmpindex -eq ($previousversions.length – 1))
            {
                break
            }           

            $listoffilestobemoved = $listoffilestobemoved + “,” + $filetobemoved
            $tmpindex++
        }
        $previousversions = @()         
        $previousversions = $previousversions + $file
        if ($listoffilestobemoved -ne “”)
        {
            Write-Host $listoffilestobemoved
        }       
    }

    # in both cases, assign the previous filename = current one
    $prevfile = $filenamewithoutsuffix
}

################# IdentifyOlderVersions.ps1 ######################

This worked as a charm, and as you can see, some pretty involved scripts can be put together. We can also create .NET classes inside a script, and use those to achieve more than what is normally possible.

 

I encourage you to try using PowerShell for all such tedious tasks. There are some great hints and information (including link to a free course book) at http://www.microsoft.com/technet/scriptcenter/topics/winpsh/toolbox.mspx. The starting point for most of us should be http://www.microsoft.com/technet/scriptcenter/topics/winpsh/pshell2.mspx. Get the CTP2 latest release and start playing around!

I’m sure many of you are aware of the wonderful RML Tools (description here and x86 download here) and specifically the ReadTrace tool. Maybe you are also aware of SQLNexus which relies on ReadTrace for the trace analysis.

At some point in time when you use these tools, you would come across the ‘Interesting Events’ table (ReadTrace.tblInterestingEvents in the SQLNexus database) which is populated with various errors and warnings. While ReadTrace comes with a way to view this information at a summary level, one thing it does not easily provide is the ability to tie sample batch text to the ‘interesting event’. Strictly speaking the ReadTrace schema does not provide a ‘by-design’ way to query this information, however best-effort guessing can be done by using the time the ‘interesting event’ was generated and then using it to query tblBatches. Here’s one such query I use to dig for clues to which batches led to such events:

 

SELECT TEC.name, TUB.OrigText, TB1.Cpu
FROM ReadTrace.tblInterestingEvents TIE
JOIN ReadTrace.tblBatches TB1
    ON TIE.SPID = TB1.SPID and TIE.StartTime >= TB1.StartTime AND TB1.EndTime >= TIE.StartTime
JOIN ReadTrace.tblBatches TB2
    ON TB1.BatchSeq = TB2.BatchSeq AND TB1.BatchSeq >= TB2.BatchSeq
JOIN ReadTrace.tblUniqueBatches TUB
    ON TB1.HashID = TUB.HashID
JOIN sys.trace_events TEC
    ON TEC.trace_event_id = TIE.EventID
WHERE name = ‘Hash Warning’

 

Of course, the ‘Hash Warning’ (more information about it available here) event name mentioned above could be replaced with others, such as ‘Sort Warning’, ‘Exception’ etc.