Mitch Wheat - Treat the cause, not the symptoms!

I’ll be presenting a TSQL developer focused talk at the Perth .NET User Group tomorrow (Thurs 1st March, 5:30pm). I’ll cover a few techniques for speeding up and improving TSQL code.

• TOPIC:  TSQL Developer Tips: Mitch Wheat
• DATE:   Thurs Mar 1st, 5:30pm - 7:00pm
• VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
• COST:   Free. All welcome

If you haven’t noticed, the UG web site is currently down (due to circumstances beyond my control). I’m trying to get it back up, but it might take a little while…

This HTML5 introduction, Dive into HTML5, also contains an interesting account of how certain HTML features evolved.

For the most comprehensive read and all the details: HTML5 specifications (still being drafted).

Out of the box, SQL Server’s maintenance plans enable you to set up common maintenance tasks such as running a CHECKDB, backing up up databases and logs, rebuilding indexes etc.

In terms of rebuilding indexes, there are two flavours: a reorganise and a full-blown rebuild (Reorganizing and Rebuilding Indexes). Reorganising an index is usually done when the index is not heavily fragmented.

It is not uncommon to find a maintenance plan that first full rebuilds all indexes (regardless of fragmentation level, but more on that in a moment), and then subsequently updates all statistics. Not only is this unnecessary, it is actually worse than simply rebuilding all indexes!  The update statistics task samples your data, whereas a full index rebuild also updates the associated statistics but does so by performing a FULL SCAN of your data. That’s right: updating statistics with the default setting of sample after a full index rebuild will leave you with (potentially) less accurate statistics.

Rather than rebuilding all indexes regardless whether they need it or not, Ola Hallengren’s maintenance script contains a production tried-and-tested stored procedure SQL Server Index and Statistics Maintenance for rebuilding and reorganizing indexes and updating statistics. It has ability to run for a set period to fit into tight maintenance windows, set fragmentation threshold levels, and update statistics, such as unindexed column statistics that are not updated as part of an index rebuild. Highly recommended.

1) Has the table schema been examined to minimise storage space per row? Whilst obvious, it is surprising how much can be gained from this. (I’ve managed to halve the row sizes in a previous project’s data warehouse)

Techniques include using the smallest data types possible:

a) Use varchar instead of nvarchar.

b) Use smalldatetime unless you need the precision. [The smalldatetime datatype is accurate to the nearest minute, whereas datetime has a precision of 3.33 milliseconds.]

2) Turn on database compression (this has a CPU overhead, but can reduce total database size by over half).

3) Turn on backup compression.

4) Ensure instant file initialisation is turned on.

5) * Use multiple file groups to manage backups.

6) * Place critical tables in PRIMARY file group to enable piecemeal restore (SQL Server 2005 onwards)

7) Use partitioning to break a table out into several pieces that can be stored on different sets of drives: rule of thumb is around the ’20 - 30 million rows per partition’ mark. Of course, this somewhat depends on the ‘natural’ partitioning key range (for example, 1 month per partition)

8) Have a clear idea of your high availability requirements (maximum downtime) up front, and validate your disaster recovery plans.

Refs.

• Very Large Databases
• A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network
• Restoring File/Filegroup Backups

The term "Bootstrapping" comes from the term "pulling yourself up by your own bootstraps" (Wikipedia). In computing parlance, a bootstrap loader is the code that initially runs when a machine starts, whose job is to load the rest of the operating system. [I can remember (just) old mainframes where the bootstrap code was entered by hand using switches on the front panel!]

If you’ve never read Ken Thompson’s “Reflections on Trusting Trust” (his Turing Award acceptance speech), it’s definitely worth reading:

It describes a very clever backdoor mechanism based on the fact that people only review source (human-written) code, and not compiled machine code. A program called a compiler is used to create the second from the first, and the compiler is usually trusted to do an honest job.

Thompson's paper describes a modified version of the Unix C compiler that would:

• Put an invisible backdoor in the Unix login command when it noticed that the login program was being compiled, and as a twist
• Also add this feature undetectably to future compiler versions upon their compilation as well.

http://en.wikipedia.org/wiki/Reflections_on_Trusting_Trust#Reflections_on_Trusting_Trust

How did I get onto this topic? Someone asked the question “How can you write a compiler in it's own language?”

If you were writing a new C++ compiler, you would just write it in C++ and compile it with an existing C++ compiler. If you were creating a compiler for a completely new language, you would need to write the new compiler in another language first. Usually this would be another programming language, but it can be assembly, or even machine code.

If you want to bootstrap a compiler, you would generally write a compiler for a small subset of the language. Then in this minimal version of the language, you would write a compiler for the full language. This often occurs iteratively rather than in a single pass.

http://en.wikipedia.org/wiki/Bootstrapping_(compilers)

The first self-hosting compiler (excluding assemblers) was written for Lisp by Hart and Levin at MIT in 1962. They wrote a Lisp compiler in Lisp, testing it inside an existing Lisp interpreter. Once they had improved the compiler to the point where it could compile its own source code, it was self-hosting.

There is an interesting article about bootstrapping a compiler from the ground up titled Bootstrapping a simple compiler from nothing.

I’m guessing everyone knows that Visual Studio (Premium and Ultimate Editions) has a code profiler built-in. But did you know you can use it to compare and show differences and improvements between profiling sessions?

• How to: Compare Profiler Data Files : You can compare the results of two different profiler data files (.vsp or .vsps) by creating a comparison ("Diff") report or view. The comparison shows the differences, performance regressions, and improvements that occurred from one profiling session to the other. The ‘Just My Code’ feature is also quite useful for profiling code that you either wrote or you have control over during sampling mode.

• Beginners Guide to Performance Profiling

• Find Application Bottlenecks with Visual Studio Profiler

• Analyzing Application Performance by Using Profiling Tools

• Visual Studio Profiler Team Blog: Just My Code

There are also other commercial code profiling tools available from RedGate and JetBrains, and Eqatec (which has a free version).

Microsoft have recently released an updated version of the SQL Server Migration Assistant (SSMA):

SSMA simplifies database migration process from Oracle/Sybase/MySQL and Microsoft Access to SQL Server and SQL Azure. SSMA automates all aspects of migration including migration assessment analysis, schema and SQL statement conversion, data migration as well as migration testing to reduce cost and reduce risk of your database migration project. The new version of SSMA - SSMA 5.2 provides the following major enhancements:

• Support conversion of Oracle %ROWTYPE parameters with NULL default
• Support conversion of Sybase’s Rollback Trigger
• Better user credential security to support Microsoft Access Linked Tables

Download SQL Server Migration Assistant (SSMA) v.5.2
Launch the download of the SSMA for Oracle.
Launch the download of the SSMA for Sybase.
Launch the download of the SSMA for MySQL.
Launch the download of the SSMA for Access.

There are migration whitepapers for each database product available here.

I love it when Scott Hanselman updates his Ultimate Developer and Power Users Tool List for Windows. I saw Bins and immediately went and purchased it ($4.99 PayPal). For each program pinned to the taskbar, you can create a group and put four shortcuts in the space of just one regular pinned icon. You can even choose which is the default click program, or hover to see all the programs in the group.

[The same author created the very useful (and free) Fences)]

I happened to be on StackOverflow perusing a few questions (not that I spend much time there, ahem!), and I came across this great List of freely available programming books, which includes 97 Things Every Programmer Should Know

A few of my favorites are:

• Ask "What Would the User Do?" (You Are not the User) 
• Code Reviews
• Comment Only What the Code Cannot Say  (I have actually seen a ‘programmer’ write i = i + 1  // increment i by 1, no one I currently work with, I hasten to add!)
• Improve Code by Removing It
• Use the Right Algorithm and Data Structure  (if code has quadratic complexity, it will not scale: BubbleSort springs to mind!)
• Write Code as If You Had to Support It for the Rest of Your Life

Join us at the Perth .NET user group, Thurs Feb 2nd 5:30pm, where you’ll see what’s coming up in the next version of Visual Studio and Team Foundation Server. Anthony will demonstrate features from the Developer Preview versions of both Visual Studio and Team Foundation Server and whet your appetite for the Beta releases expected in February. Some of the things you’ll see include the new team explorer, code review workflow, exploratory testing, local workspaces and much, much more.

• TOPIC:  Visual Studio and Team Foundation Server vNext Preview: Anthony Borton
• DATE:   Thurs Feb 2nd, 5:30pm - 7:00pm
• VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
• COST:   Free. All welcome

Anthony Borton is the lead ALM consultant for Enhance ALM Pty Ltd, an Australian consulting and training company specializing in Application Lifecycle Management and Microsoft Visual Studio. He has been working with Visual Studio Team System full-time since 2005 and has worked with a variety of companies ranging up to some of Australia’s largest companies and financial institutions. Anthony is a sought after trainer and has delivered technical training and consulting in the United States and all across the Asia Pacific region. He is a Microsoft MVP (Visual Studio ALM), a Professional Scrum Developer Trainer and a Microsoft Certified Trainer. Anthony also runs the QLD ALM Users Group and the Canberra ALM Users Group.

In addition to the usual JetBrains license raffle prize, Anthony has arranged to provide everyone that attends our February user group meeting with an invitation code for the preview of Microsoft’s Hosted TFS offering.

The Windows Azure Training Kit includes a comprehensive set of technical content including hands-on labs, presentations, and demos that are designed to help you learn how to use Windows Azure.

The December 2011 refresh of the training kit includes the following updates:

• 33 updated/new PowerPoint presentations
  
  
• [New Hands-On lab] Running a parametric sweep application with the Windows Azure HPC Scheduler
  
  
• [New Hands-On lab] Running SOA Services with the Windows Azure HPC Scheduler
  
  
• [New Hands-On lab] Running MPI Applications with the Windows Azure HPC Scheduler
  
  
• [New Demo] Node.js On Windows Azure
  
  
• [New Demo] Service Bus Relay
  
  
• [New Demo] Service Bus Queues
  
  
• [New Demo] Image Rendering Parametric Sweep Application with the Windows Azure HPC Scheduler
  
  
• [New Demo] BLAST Parametric Sweep Application with the Windows Azure HPC Scheduler
  
  
• [Renamed Lab] Connecting Applications through Windows Azure Service Bus (formerly Introduction to Service Bus Part 1)
  
  
• [Renamed Lab] Windows Azure Service Bus Advanced Configurations (formerly Introduction to Service Bus Part 2)

To coincide with the new Azure web site, Scott Guthrie's talk is worth watching: Keynote: Getting Started with Windows Azure

Given a uniformly distributed random variable x (such as the output from a standard Random Number Generator), how do we generate some other non-uniform distribution, q(y)?

Solution: Generate x, and take y = f(x). But how do we calculate f() given q() ?

The required function is the inverse cumulative distribution function given by:

Example: Exponential distribution (to simulate the lifetimes of radioactive nuclei)

, (τ≥0,and = 0 otherwise)

So inverse is

Ref.

http://en.wikipedia.org/wiki/Cumulative_distribution_function

http://en.wikipedia.org/wiki/Inverse_transform_sampling

Note: Everything that follows is performed at your own risk.

If, like me and may others, you installed Windows 7 Service Pack 1 over Windows 7 vanilla, you probably have quite a bit of wasted space on your primary drive (around 1GB). This is obviously not desirable if you have a relatively small SSD as your primary OS drive.

Given that it is extremely unlikely that anyone would ever remove Service Pack 1 and go back to vanilla, you can quite easily reclaim this space (Note: you will no longer be able to uninstall Windows 7 Service Pack 1):

1. Open an elevated command prompt (i.e. Run as administrator).
2. Run this command:  dism /online /cleanup-image /spsuperceded /hidesp

For anyone interested in mathematics/science, the Mahler lectures for 2009 and 2011 are now available as pdf’s.

They vary in complexity, but most have a wide audience such as Terence Tao’s “The Cosmic Distance Ladder”, which I attended in person when held at UWA.