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.
|
1 |
ostress -SNADASUPERIOR\NADA2016 -d"tempdb" -Q"SELECT TOP 10000 m.message_id, m.text INTO #ScheisseSturm FROM sys.messages AS m;" -E -q -n450 -r1 -o"C:\temp\ErikOut" |
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.
|
1 |
ostress -SNADASUPERIOR\NADA2016 -d"tempdb" -Q"DECLARE @ScheisseSturm TABLE(message_id INT, [text] NVARCHAR(4000)) INSERT @ScheisseSturm SELECT TOP 10000 m.message_id, m.text FROM sys.messages AS m;" -E -q -n450 -r1 -o"C:\temp\ErikOut1" |
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.
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…
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?
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.
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).
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.
Snicker snicker, “german temp tables” snicker snicker.
You should see what I name my AGs and Clusters!
Great article. Love the name of your German temporary table too!!!