Cheap and Easy

We’ve all done it. We all know what and where it is, because we’ve probably left a comment for ourselves to go fix it later.

Either it was expedient, it seemed to work locally, it was fast on a small amount of data, or we just didn’t have time to do better.

Maybe we only learned it was a bad idea later on. But there it still sits.

Mocking. Needling. Cajoling.

Like the Ab Roller in the back of your closet.

Bag Of Pudding

Our application that was reliably quick and svelte in it’s teen-gigs is a slow and flabby disaster in it’s midlife gig crisis.

If things keep up, it won’t live to see 65 gigs.

It’s hit a wall, you see. And that steady diet of Fast Food Code is starting to take its toll.

Your monitoring tool has stopped showing you charts and graphs, and has started trying to sell you a burial plot.

The signs have been there for years, but you didn’t pay attention.

XXXL

It’s not just about performance. Data safety and security is a big deal, too.

Despite, like, decades of incidents, people keep carrying on writing unsafe dynamic SQL.

Data theft and vandalism are things.

Things that happen.

Things that happen to people a lot smarter than you.

It’s That Time Of Year

No, not the Rapture.

And only tangentially the time of year when I walk into the gym and see people doing plyometrics or some other useless waste of time in a squat rack for some reason.

Let’s make a resolution to stop feeding our databases Fast Food Code. When we see the warning signs, let’s stop and do something about them.

• Unsafe dynamic SQL
• Functions or calculations in joins and where clauses
• Cleaning data on the fly
• Cursors for relational tasks
• Nested views
• Scalar UDFs
• Multi-Statement UDFs
• Mismatched data type comparisons
• Table variables (most of the time)

You don’t have to fix them all at once. Sometimes it’s just a matter of stopping new development with these practices first. Then, as you go through your worst performing code, keep an eye out for the stuff on this list.

To get started, download our FirstResponderKit.zip here, and use sp_BlitzCache to find your naughty bits. It’ll flag a lot of the things on that list (the rest aren’t captured in the plan XML). That’ll make your tuning path a lot more clear.

Thanks for reading!