DBA Days: Scripts from Downtime Train

SQL Server
8 Comments

ostress is so much fun

It’s totally free to download and use as part of Microsoft’s RML Utilities. What else is in there? Stuff I’ve never used! I hear you can read trace files or something else perfectly adequate.

Even though it’s a CLI, it’s still a bit less insane and confusing than HammerDB. Plus it’s way easier to plug in your own queries.

I wanna stress you up

Let’s look at one of the commands I ran, so it’s a bit easier to see what’s going on. This is the first one, which caused some tempdb contention. The query itself is just a simple dump of 10,000 rows into a temp table.

Here’s an explanation of the flags I’m using, in order:

Flag Argument
“-S” Server name you’re connecting to
“-d” Database name you want to run commands in
“-Q” Query you want to run
“-E” Use Windows authentication
“-q” Run without displaying query results
“-n” Number of connections you want to spawn
“-r” Number of times to loop through your code
“-o” Directory to output logging files to

Other useful commands that I’m not using here:

Flag Argument
“-T” Any trace flags you want turned on for your session
“-U” Username for a SQL login
“-P” Password for SQL login
“-i” Path to a SQL file you want to execute. You can use a *.sql wildcard here to execute a bunch of scripts, but it doesn’t support much else for pattern matching.

One sort of weird quirk I’ve found is that it doesn’t like spaces in between flags and their arguments. That’s an interesting choice.

Fabled variables

My contempt for table variables knows no bounds. It’s always fun to show people that they’re not ‘in-memory’, and can cause all sorts of other issues. This is a simple script for creating tempdb ugliness with them.

Remember, kids

The time to run this on a production server is before it goes live. Don’t haul off and point scripts like this at anything business critical. Better yet, don’t point them at anything that isn’t your local instance until you know how it will behave. Unless your laptop is business critical.

And it might be.

I’ve seen some of the servers out there.

Sheesh.

Previous Post
DBA Days: Money for Nothing, Chips for Free
Next Post
DBA Days: Chaos Sloth

8 Comments. Leave new

  • “I wanna stress you up”?, I see that’s your ode to Color Me Bad LOL. When did this whole SQL Server myth of table variables living in memory start? Is there a genesis to this? I myself have to debunk this myth constantly, as much as I have to debunk the notion that people think that ocean water is blue…

    Reply
    • I don’t know when it started, but I remember being someone under this impression, briefly, around 2010. I think part of the problem is naming. A table variable is a variable and variables are just memory pointers. No problem, right?

      Reply
      • Of course it’s not a problem, it’s a feature not a bug :). I can see how the naming could be the issue. No documentation out there by Microsoft even hints at table variables living in memory. You might be right on this being the source of the issue.

        Reply
  • Is the “in-memory” myth the only reason you dislike table variables? I’ve found them to be simpler to remember to use (no need to remember to drop them at the end of whatever code I’m running, regardless of transaction).

    Reply
    • No, there are several reasons.

      1. Modifications are all forced serial
      2. Unless you recompile, you get a 1 row estimate, or 100 rows after 2014
      3. No column level statistics
      4. They’re a pain to index (again, prior to 2014, but even after you don’t get column level stats)

      Like cursors, they’re rather good for administrative tasks, but they’re ass for set-based anything.

      Reply
  • Snicker snicker, “german temp tables” snicker snicker.

    Reply
  • Great article. Love the name of your German temporary table too!!!

    Reply

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.