Uncategorized

SQL Diagnostic Runner Updated

by

David Vogelaar (and others) kindly reported a bug: I wasn’t converting invalid filename characters when using an SQL Server instance name for the auto-generated results filename. This has been fixed. You can download version 1.0.2 from the previous download links or the ones below.

SQLDiagCmd.zip

SQLDiagUI.zip

There is a known issue:   

The results file is generated OK but sometimes when you open it in Excel a seemingly ‘nasty’ message is shown:

"Excel found unreadable content in ‘???.xlsx’. Do you want to recover the contents of this workbook?"

The file will open OK if you chose to recover: simply accept the prompts and save over the original. I will fix as soon as I can.

SQL Server: Differences between Temp Tables and Table Variables

by

I had been thinking of collating the differences between temp tables and table variables and posting it, but Martin Smith has already written a great summary over at DBA StackExchange:

What’s the difference between a temp table and table variable in SQL Server?

It’s broken up into the following categories:

  • Storage Location
  • Logical Location
  • Visibility to different scopes
  • Lifetime
  • Transactions
  • Logging
  • Object Metadata
  • Cardinality
  • Column statistics
  • Recompiles
  • Locking
  • Indexes
  • Parallelism
  • Other Functional Differences
  • Memory Only?

    • List of Freely Available Programming Books

    by

    One of the things I think StackOverflow has got wrong is hiding, closed, highly useful questions that are deemed in some way not to ‘fit’ the site’s philosophy (whatever that might be). If your rep is higher than 10K, you can view these hidden closed questions. The site has bigger problems such as the increasing amount of very, very poor quality questions that amount to nothing more than “I can’t be bothered doing/looking up X. Please do X for me”.
    Here’s an example: List of freely available programming-books
    Can’t see it? I’d obviously prefer to link to the entire question and answers, but assuming you can’t see it, here’s an excerpt from the answer begun by George Stocker (who ironically is one of the people who closed it), and then contributed to by many people as a community wiki:
    Meta-Lists

    Language Agnostic

    NET (C# / VB / Visual Studio)

    SQL (implementation agnostic)

    31 Characters Should be Enough for Anyone, Right?

    by

    I’ve always had a good laugh at Oracle for having a 30 character limit on table/column/index names (and probably other objects I don’t know about)

    Mentioned here on StackOverflow:

    “Not just millions of lines of DBA written code, but plenty of oracle internal code no doubt too. This topic came up in a session with Steven Feuerstein and he said he didn’t think they would ever change it.”

    “They couldn’t exactly trumpet it as a new feature, either… they’d spend a lot of time extending the limit, and then announce “you can now use names longer than 30 characters!”. They’d be the laughing stock”

    While writing SQLDiagCmd (a runner for Glenn Berry’s SQL Server diagnostic scripts), I re-discovered that Excel 2010 still has a limit of 31 characters for Worksheet names (and several weird bits of behaviour relating to that limit). Really?!? Why would anyone want more than 31 characters for a work sheet name? It is 2013 right, not 1970?

    Add that to the fact that worksheet names have to be unique (I understand the need for that), and Voila! unnecessary code to guarantee uniqueness with 31 characters! Someone please tell me there’s a way to override this ludicrous limit…

    .NET: Parse Structured Text Files

    by

    I wasn’t aware of the handy TextFieldParser class, that parses a text file in a reader oriented way:

    The TextFieldParser object provides methods and properties for parsing structured text files. Parsing a text file with the TextFieldParser is similar to iterating over a text file, while the ReadFields method to extract fields of text is similar to splitting the strings.

    Maybe because it’s in the Microsoft.VisualBasic.FileIO namespace… How to: Read From Comma-Delimited Text Files in Visual Basic

    Sure, you can write one yourself, but it’s handy for quick spikes:

    using (var reader = new TextFieldParser(@"c:\logs\bigfile"))
    {
        reader.TextFieldType = FieldType.Delimited;
        reader.Delimiters = new [] { Constants.vbTab };

        while (!reader.EndOfData)
        {
            try
            {
                // If ReadFields encounters blank lines, they are skipped and next non-blank line is returned.
                string[] currentRow = reader.ReadFields();

                // Process row....
                foreach (string currentField in currentRow)
                {

                }

            }
            catch (MalformedLineException ex)
            {
                // Handle malformed line....
            }
        }
    }

    SQLDiagCmd: a Standalone Runner for Glenn Berry’s SQL Server Diagnostic Scripts

    by

    If I said that I use Glenn Berry’s SQL Server diagnostic queries a fair bit it would be an understatement. Glenn releases versions of these very useful TSQL scripts (comprised of 50 plus queries) and a blank Excel Template to save the results in.

    Rather than running each query individually, highlighting the results in SSMS and then copying and pasting to Excel, I decided to write a standalone, no install required runner that would do the donkey work for me:

    • It should run from command line (or through a UI)
    • It should be standalone, require no installation, and not require Excel or Office to be installed.
    • It should work with new (and old) versions of his scripts, and not require updating when the scripts are updated.
    • It should not require a blank excel template file.
    • It should identify and run Server and database specific queries automatically.
    • It should be able to target multiple databases at once.

    The only (optional) alteration to Glenn’s scripts, is a small addition to the comment line which describes each query, that is used as the worksheet name (if this is missing, it uses the text at the start of the comment, but this won’t be unique due to Excel’s ridiculous limitation of having a 31 character maximum length for worksheet names!).

    So, for each comment preceding a query, instead of:

    -- SQL Server Services information (SQL Server 2008 R2 SP1 or greater)  (Query 5)

    I’ve altered to explicitly specify what text to use as the worksheet name (in bold):

    -- SQL Server Services information (SQL Server 2008 R2 SP1 or greater) (Query 5)(SQL Server Services Info)

    I’ve emailed Glenn to see if I can get this addition in future scripts.

    You can download version 1.0 of the zipped executable from here: SQLDiagCmd.zip (targets .NET Framework 4.0)

    OK, How do I run it?

    Download Glenn’s diagnostic script that targets your version of SQL Server and download SQLDiagCmd.exe.

    Assuming they are in the same directory:

    SQLDiagCmd.exe -E -S MUTLEY -i ".\SQL Server 2008 Diagnostic Information Queries (January 2013).sql" 
 -o c:\temp -d "AdventureWorks2008R2;AnotherUserDatabase" -A

    Typing SQLDiagCmd.exe –-help displays usage and parameters:

    Usage: 

SQLDiagCmd -E -S MUTLEY -i queries.sql -o C:\outputfolder -d databaselist –A 
SQLDiagCmd -S MUTLEY -U username -P password -i queries.sql -o C:\outputfolder -d databaselist –A

     

    -S, –servername Required. Server name or instance to run queries against.
    -E Use a trusted connection (Windows Authentication).
    -U, –username Username for SQL Login.
    -P, –password Password for SQL Login.
    -i, –inputfile Required. Query diagnostic file to run.
    -o, –outputfolder Required. Folder location to write results file.
    -A, –autofit Auto-fit Excel columns.
    -t, –timeout Query timeout in seconds. Defaults to 360 seconds (for longer running queries).
    -d, –databases Semicolon separated list of specific databases to examine. Separate each database with a semicolon. Do not include spaces between databases and semicolon.
    –help Display this help screen

     
    The SQLDiagCmd git repository is available at github: SQLDiagCmd

    This project uses  EPPlus and the Command Line Parser Library.

    Several Reasons Why Coding is Hard!

    by

    I was looking up something Unicode related and I came across Jon Skeet’s talk on why coding is hard: why do fundamental datatypes such as floating point, strings and DateTime cause so many problems. The slide deck and transcript is here.

    The only thing I’d add is that arithmetic might be easier if we had 12 digits rather than 10, rather than the 8 mentioned, but that’s another story…