Stop ! Or your Mom will shoot !!

Stop ! Or your Mom will shoot !!

Right right right.

Collect thoughts. \”Respond, don\’t React\”, as my therapist would say. Find Happy Place.

OK, here we go…

I wrote a post a bit back about why DBAs and Devs don\’t get on. If you didn\’t read it, I can summarise here by saying \”If you stop doing crazy stuff with my software, we\’ll get along just fine.\”

But I appreciate that it\’s not all the Devs fault, so I\’m going to have the SAME RANT at software vendors now. And it\’s a Friday, I should be in my local sports bar watching the game, but I\’m here, running 3rd-party upgrade scripts. And getting more and more hacked off about it, I can tell you.

Here\’s the construct that has been really grinding my gears this evening….

CREATE TABLE dbo.myStupidA**Table…..
(add your own column defs here)

INSERT INTO dbo.myStupidA**Table (BizarreCol1, BizarreCol2……)
values(\’I\’\’\’ve given up caring at this point\’, \’more lame text\’, ……

Followed immediately by:

DELETE FROM dbo.myStupidA**Table
WHERE BizarreCol4 LIKE \’%I SHOULD NEVER HAVE INSERTED THIS ROW IN THE PRECEDING BATCH !!%\’

Long pause…..

Deep breath….

Exhale….

Respond:

Imagine the scene….

It\’s late on a holiday weekend. Your boss rings you up to ask if you could do a favour for a small project / client. You agree. Your boss tells the vendor / client your mom\’s address, where your kids go to school, how much you\’ve got in your 401K, and your partner\’s vital statistics.

You\’d feel a bit violated, right ? Like, no-one has the right to do that to you ? No way, no how ??!!??

OK, so STOP DOING THAT KIND OF STUFF TO MY SERVER. Or I\’ll feel I like I have to come round there, and hit you. With your mom.

It sounds like I\’m being a bit proprietorial here, but if you treated everything with this kind of contempt, you\’d end up in prison fairly quickly.

Why should normal societal rules apply when it\’s only software ?

I\’ll tell you why – because it makes you look like you don\’t care, and the last thing that anyone wants to be told is that other people don\’t care about what they do.

Try this on for size… Imagine (again with the \’imagine\’ thing – what\’s with that ??!?). Whatever you\’re doing right now is worth less than a gnat\’s toot in a hurricane. And everything your mom and pop did. And everything your kids will ever do. And their kids.

Bleak, huh ?

Hold that thought.

Yeah, that\’s pretty much what I felt when I eyeballed that code. Tumbleweed through my career, through my life, through the lives of those I care about.

Vendors, please play nice with your DBA\’s emotions. Don\’t forget that behind the voicemail, behind the insincere auto-signature and the slightly aggressive attitude, there\’s probably a very insecure and hurt professional who can\’t understand why others seem to want to do him harm, who really just wants to come to work and have fun, and who (but for an accident of education) is almost EXACTLY like you.

This is EVEN MORE important to if you actually sell stuff to large corporations. Your customer is not only the guy who signs the check – the tech support team behind that guy are pretty important too, especially when it come to renewal time. I\’ve seen vendors come in with a bad attitude, and leave at the next renewal because the client didn\’t want to have to take their kind of BS any more, and their app was replaced with a home-grown solution.

Nothing in this world is perfect, but if we all treat each other with the respect we deserve, we could get pretty damn close. And let me be the first to say that I\’m as guilty as anyone else in being dismissive and intractable, but, to be honest, I\’m tired of that now, and I\’d like to move on to a place where true magic happens because people just accept each other for what and who they are.

Care to join me in that ?

Back soon…

Performance Tuning on the cheap – \'Please, Sir, can I have some more…\'

Performance Tuning on the cheap – \’Please, Sir, can I have some more…\’

How many times have you been tuning a query, only to find that it works fine on your dual-core, 2GB laptop but tanks badly when deployed to production ? If you\’re anything like me, that\’s probably quite a lot. Or at least more often that you\’d like.

I came across a REALLY useful technique the other day that tricks out the Query Optimiser into thinking you have more data in your database that you really have. Basically, you create a database in test that has no real data in it, but DOES have the statistics information that the QO would use when building an execution plan.

It\’s at: http://www.mssqltips.com/sqlservertip/1999/how-to-create-a-sql-server-clone-statistics-only-database, and I\’ll leave you to read that at your leisure. And trust me, it works lovely…

But there\’s still a small issue – your dev / test machine doesn’t have the same hardware resources available to it as your whizzy-fast production kit (yeah, like you wish :-)), so you can\’t REALLY tune that gnarly statement as much as you\’d like, and there still a risk that all your testing comes to nought because you haven\’t got full-fat CPUs or memory.

Bummer.

Then I came across this:

http://www.sqlity.net/en/828/optimizer-what-if-i-had-more-cpus

I\’ll distill this one for you, if I may.

There are several \’undocumented\’ features of SQL Server (as I\’m sure you\’re all too aware), such as sp_MSForEachTable and sp_MSForEachDB, and, annoyingly, some of them would be really useful if they WERE fully documented. Such as being able to tell the QO that you really do have 32 cores in your laptop and you really do have 64GB of memory installed.

In combination with the statistics-only database clone, you can use DBCC OPTIMIZER_WHATIF() to accomplish just that. BUT I would have to caution that you should NEVER use this in production, would you. Just saying. It\’s undocumented for a reason – you can do a whole load of damage to your consolidated server if these settings are used with abandon, but it can give you more of an indication on how your query would perform on production servers, with all their resources available.

Firstly, in order to see the output of any DBCC command (other than the really useful \’The command(s) completed successfully\’), you need to turn on a trace flag, so let\’s do that:

DBCC TRACEON (3604) WITH NO_INFOMSGS;

OK, now we\’re ready to rock. One small point: the settings only work in the session you\’re currenly using, so you\’d need to execute your problem script in the same query window you use to change the optimiser settings.

To check what the current QO values are, execute:

DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS;

Note that the parameters passed to the OPTIMIZER_WHATIF statement are Case-Sensitive.

Your output should be something like this:

———————————————————

Optimizer what-if status

———————————————————

property_number current_value default_value property_name

———————————————————

         1                  0             0 CPUs

         2                  0             0 MemoryMBs

         3                  0             0 Bits

         4               1000          1000 ParallelCardThreshold

         5                  1             1 ParallelCostThreshold

         6                200           200 ParallelCardCrossProd

Note: On my test system, these are the only properties available. The link at the top indicates that there may actually be a few more on yours.

The property_names we\’re interested in are CPUs and MemoryMBs current_value.

To make it seem that you\’re using more (or less, if the mood takes you) of either of these, set the parameter and value pair accordingly:

DBCC OPTIMIZER_WHATIF (CPUs, 16) WITH NO_INFOMSGS;

DBCC OPTIMIZER_WHATIF (MemoryMBs, 32768) WITH NO_INFOMSGS;

If you then interrogate the status again, you should get:

———————————————————

Optimizer what-if status

———————————————————

property_number current_value default_value property_name

———————————————————

         1                 16             0 CPUs

         2              32768             0 MemoryMBs

         3                  0             0 Bits

         4               1000          1000 ParallelCardThreshold

         5                  1             1 ParallelCostThreshold

         6                200           200 ParallelCardCrossProd

Now get your gnarly query / proc and generate an execution plan, and you should be able to see some differences between that and one generated without the parameters set at the QO. In my testing and prep for this post, I spotted a parallelism task that was not in my original execution plan 🙂

Once you\’re happy with your tuning, don\’t forget to reset the QO WHAT_IF values:

DBCC OPTIMIZER_WHATIF(ResetAll) WITH NO_INFOMSGS;

And possibly turn off the DBCC trace flag:

DBCC TRACEOFF (3604) WITH NO_INFOMSGS;

So, as every day you learn something, today I learned that you can get close to production performance estimations without the cost of the hardware resources of that environment. Which these days has to be a good thing.

Back soon…