JOIN Me

This month's T-SQL Tuesday (#23) is hosted by Stuart Ainsworth (Blog | Twitter) and he has selected JOINs as this month's topic.


I thought the topic seemed mildly interesting from a deep technical perspective.  If you agree, you might find some good reading here or here, and I've even touched on the topic of Legacy JOINs.  However, I do deep technical writing much of each day, and I sometimes like to relax and ramble a bit in the blog.  If it's SQL Server related rambling, so much the better.

Besides, I don't know anything particularly deep or technical about JOINs in SQL Server.  Come to think of it, there are a lot of deep technical things I don't know about SQL Server in general that can be solved by a few JOINs.


Here some JOINs that have helped me to solve deep technical problems in SQL Server.  Maybe they've helped you, too.


JOIN... the Professional Association for SQL Server
JOIN... your local SQL Server User Group
JOIN... your SQL Server Peers for a SQL Lunch
JOIN... an online SQL Server Forum
JOIN... an online SQL Server Community for the Inside scoop
JOIN... twitter for the #SQLHelp hash tag
JOIN... your workmates for lunch


All those things you don't know about SQL Server?  The places above are absolutely loaded with folks who DO know them... oftentimes even the coworker you didn't realize had seen it before.  And, do you know what else?  To somebody less experienced than you with SQL Server, you will be one of the ones with the answer.


So JOIN up with somebody else and promote some SQL Server excellence.

Sometimes It's Not The Database

The other day for Meme Monday I put together a list of 9 Things That Go Wrong With SQL Server That Aren't The Disk.  That got me to thinking about the old "It's the database" stand-by for when The Application isn't working right.  Many of us have been there... The Application is acting up, and the knee-jerk response by the development or support team is, "it's the database"

The Database... Seriously?

The frustrating thing with this is when the problem is found to lie in The Application or in the computing infrastructure, as it often is, but all the business stakeholders remember hearing was "it's the database."

Then there are the few cases when it's true... a broken clock is right twice a day, after all.  This happens just often enough that the other support and infrastructure teams sometimes actively refuse to do any troubleshooting unless, and until, the DBA can prove that the problem lies with a non-database component.  Cross-discipline troubleshooting/resolution team?  Not when it's obviously the database

I was at a shop that recently had some recurring database connectivity issues with helpful error messages like "SQL Server Does Not Exist Or Access Denied", "Timeout Expired" and "General Network Error."  Now, you know where the finger points when the words "SQL Server" appear in an error message.

So the database team dutifully combed event logs, performance monitor and server side trace data, and was able to demonstrate pretty conclusively that nothing was wrong with the database or the server it was on.  Till the next time The Application got busy and started throwing errors, when they had to do it all over again.

What it ultimately came down to was the owners of The Application server had turned off connection pooling while troubleshooting an installation issue weeks earlier.  It turns out that, by default, Windows only allows 4000 sockets (ports) per destination IP address.  With the amount of database server traffic generated by the application server -- it had recently been "consolidated" -- they were able to hit that threshold pretty reliably.

Microsoft has a KB article on considerations for servers with connection pooling disabled that describes this nicely:

If you rapidly open and close 4000 sockets in less than 4 minutes, you will reach the default maximum setting for client anonymous ports, and new socket connection attempts fail until the existing set of TIME_WAIT sockets times out.

After weeks of recurring connection errors with the word "SQL Server" in them, the connection pooling issue was quietly fixed by upping the socket limit to some random number like 65535 :) and The Application performance returned to normal.  I'm sure you can guess the words that were never spoken to the business stakeholders... "It's Not The Database."

Meme Monday: It's not the disk!

Thomas LaRock, aka @SQLRockstar (not THAT rock-star), has a fun way to get the blog month started: Meme Monday.  This month, it's just a list of 9 things we frequently see go wrong with a database server that are NOT related to disk issues.

In no particular order...

9. Good old boys

8. "It's just like a spreadsheet" database design

7. Out of date statistics 

6. Improperly partitioned tables (see 7 above)

5. Good old boys

4. Microsoft patches

3. Vendor software

2. Internally developed software

1. Good old boys

I couldn't believe the number of disk related problems that kept coming to mind while putting this nod-disk related list together - especially when considering the SAN as a black-box hard disk.

You may have noticed that Good old boys made the list slightly more than once.  Unfortunately, that is based on years of direct observation.  I'm even going to manufacture a statistic to support its frequency in the list:

Nearly 60% of production database outages are, directly or indirectly, caused by the incompetence fostered by Good old boy cronyism...  and that's NOT the disk.

Nice Teeth

Some of the commentary about the recent Amazon "Cloud" has focused on the reliability of cloud offerings, and their aggressively high availability Service Level Agreements (SLA).  Just for reference, the Microsoft Azure SLA is 99.9%, and  Amazon’s availability SLA happens to be 99.95%

Didn't see that one coming

The interesting thing about an SLA is that it doesn't make the service, or its servers, more reliable, it just specifies penalties and/or contract options for when the services become unreliable unavailable.  To go with their aggressive promises, the SLA usually provides creative definitions for “available” to help ensure the SLA is met, e.g., if the service drops half your connections, it’s still available, and you’re Simply Out of Luck

If a company actually uses its data to make money, the SLA doesn't fund the enterprise while the service is unavailable.  And the SLA typically doesn’t cover service degradation, just the creatively defined “availability.”  It also doesn’t manage or repair your tarnished reputation with your customers or your business.

Want to bet your reputation, and that of your organization on the cloud?  It’s probably a good bet, since you’re likely not getting 99.9% uptime from your own servers.  But be prepared when that .1% kicks your reputation in the teeth.

Passing the Word

So, I had this long running trace going through profiler last night (don't worry, it was just a DEV thing) and when I came in this morning, profiler had ended the trace with the following Trace Error:

-- 'Ӈ耇' was found in the text of this event.

-- The text has been replaced with this comment for security reasons.

At first, I feared a Chinese Injection Attack, but after a little digging around, I was able to reproduce it by running a trace, then stopping the traced SQL Service while profiler was still running the trace.  It's apparently a built in error code.

I thought I had seen similar behavior in the past around queries that include the word "PASSWORD" in them, so I started playing around to see if I remembered correctly.  It turns out my recollection wasn't perfect.

When you run a security statement that might contain a login's password, SQL Profiler will remove the TextData from the event.

Here are a few such statements where TextData is stripped:

sp_password

CREATE LOGIN

ALTER LOGIN

Of course, before rediscovering this, I had already run a statement using the word "PASSWORD" and profiler happily displayed my query text.  I thought I'd share, not because of the query is so profound, but because I wrote it simply for the joy of writing it.

CREATE DATABASE PASSWORD;

go

USE PASSWORD;

go

CREATE SCHEMA PASSWORD;

go

CREATE TABLE PASSWORD.PASSWORD (PASSWORD SYSNAME);

go

INSERT PASSWORD.PASSWORD.PASSWORD (PASSWORD) VALUES ('PASSWORD');

go

CREATE INDEX PASSWORD ON PASSWORD.PASSWORD.PASSWORD (PASSWORD);

go

SELECT  PASSWORD.PASSWORD.PASSWORD.PASSWORD

FROM    PASSWORD.PASSWORD.PASSWORD WITH(INDEX(PASSWORD))

WHERE   PASSWORD.PASSWORD.PASSWORD.PASSWORD = PASSWORD.PASSWORD.PASSWORD.PASSWORD;

go

DROP TABLE PASSWORD.PASSWORD;

go

DROP SCHEMA PASSWORD;

GO

USE TEMPDB;

go

DROP DATABASE PASSWORD;

GO

It's also amazingly blue because nearly every word is reserved.  If you'd like to play with it, it should run for you on any system that doesn't already have a database named "PASSWORD"

I hope you also find such light-hearted simple ways to enjoy the tools you work with.

The Absolute Best

This month's T-SQL Tuesday is hosted by Jes Schultz Borland ( Blog | Twitter ) and she has selected Aggregate Functions as this month's topic.


Besides helping data make the transformation to information and to tell their story better, I would suggest that Aggregate Functions are the basis for all meaningful communication. 

Big claim, right?  Just remember, it can't be the worst claim without an aggregate function.  To illustrate, I'm going to tell a short story two times.  One time without aggregate fuctions, and one time with aggregate functions.

The story starts with Bobby.  Yes, that's Bobby Tables, but he's all grown up now, and sells leather-free cowboy boots.

Bobby went to the computer store and purchased a post-PC device.  He had just received his annual sales bonus, and wanted to splurge on something new.  After making it through the checkout process and returning home, Bobby was pleased to discover that he could access his home network through the device.  Bobby was content with his purchase.

Here it is again.  See if you can spot the aggregate functions...

Bobby went to his favorite computer store and purchased the market leading post-PC device.  He had just received his largest annual sales bonus, and wanted to splurge on something new.  After making in through the most customer friendly checkout process and returning home in record time, Bobby was pleased to discover that this device had the easiest setup for accessing his home network.  Bobby thought this was the best purchase he had ever made.

Did you spot the aggregate functions?

• favorite computer store: having preference = max(preference)
• market leading post-PC device: having market_share = max(market_share)
• largest annual sales bonus: having bonus = max(bonus)
• most customer friendly: having customer_experience = max(customer_experience)
• returning home in record time: having transit_time = min(transit_time)
• easiest setup: having setup_difficulty = min(setup_difficulty)
• best purchase ever: having buyers_remorse = min(buyers_remorse)

Whether it's to define What's in a Name, or to describe an evening out on the town, we can't communicate effectively without aggregate functions, and we can't learn anything from our data without them.  Without aggregate functions, there is no superlative.  Nothing is better than average, nothing comes first, nothing comes last, there is no typical.

So, next time somebody tells you the bathroom is the first door on the left, or you do something the fastest, or you are rated the highest, take a moment to thank your aggregate functions.  

They are the best.

Are you my type?

Sometimes it's fun to take a look at how people tick, even when those people are, well, myself.

I've taken the online Myers Briggs Type Indicator a few times over the years, and typically seem to wind up with INTP.

Beyond telling me that I'm slightly out-of-touch with the reality of day-to-day living, I don't know how much life-altering significance there is in this type of thing beyond explaining why I'm always first in the meeting to shout "Bingo!"

There are a few good sites explaining what the various personality types are.  PersonalityPage has a pretty good INTP overview.

Here is the description that Myers Briggs provided, which, aside from saying I should be a master designer of all kinds of theoretical systems, also gives me credit for being pragmatic - about ideas, so it's not quite completely contradictory.

Rational Portrait of the Architect (INTP)

Architects need not be thought of as only interested in drawing blueprints for buildings or roads or bridges. They are the master designers of all kinds of theoretical systems, including school curricula, corporate strategies, and new technologies. For Architects, the world exists primarily to be analyzed, understood, explained - and re-designed. External reality in itself is unimportant, little more than raw material to be organized into structural models. What is important for Architects is that they grasp fundamental principles and natural laws, and that their designs are elegant, that is, efficient and coherent.

Architects are rare - maybe one percent of the population - and show the greatest precision in thought and speech of all the types. They tend to see distinctions and inconsistencies instantaneously, and can detect contradictions no matter when or where they were made. It is difficult for an Architect to listen to nonsense, even in a casual conversation, without pointing out the speaker's error. And in any serious discussion or debate Architects are devastating, their skill in framing arguments giving them an enormous advantage. Architects regard all discussions as a search for understanding, and believe their function is to eliminate inconsistencies, which can make communication with them an uncomfortable experience for many.

Ruthless pragmatists about ideas, and insatiably curious, Architects are driven to find the most efficient means to their ends, and they will learn in any manner and degree they can. They will listen to amateurs if their ideas are useful, and will ignore the experts if theirs are not. Authority derived from office, credential, or celebrity does not impress them. Architects are interested only in what make sense, and thus only statements that are consistent and coherent carry any weight with them.

Architects often seem difficult to know. They are inclined to be shy except with close friends, and their reserve is difficult to penetrate. Able to concentrate better than any other type, they prefer to work quietly at their computers or drafting tables, and often alone. Architects also become obsessed with analysis, and this can seem to shut others out. Once caught up in a thought process, Architects close off and persevere until they comprehend the issue in all its complexity. Architects prize intelligence, and with their grand desire to grasp the structure of the universe, they can seem arrogant and may show impatience with others who have less ability, or who are less driven.

Have you found these types of evaluations to be useful?  Do certain types work better together on different projects, or does it all depend on how each type is manifested?

The Abaculator

I enjoy using Red-Gate products.  Really.  I even suspect that the SQL Prompt code-completion product reduces the time it takes me to write SQL.

I enjoyed their recent add likening Hand Coding your SQL to an Abacus, while SQL Prompt was likened to a Calculator:

 

Think how much more efficient you could be with this product!  We can even get supporting evidence from the inter-webs showing just how slow the Abacus really is when compared to our electron chomping marvel:

Oops!  I had a feeling that the abacus would turn out to be pretty fast, but three times faster?  And that's just a virtual abacus - think of the power and air conditioning costs this 9 year old mathematician will save.

Now I'm slightly embarrassed to be a SQL Prompt user.

Red-Gate might want to take a lesson from MathSecret on this one.

Hacking The Doctor's Credentials

Brent Ozar just published his Microsoft Certified Master certificate along with a call for enhancements.

I never excelled at Photoshop Tennis, but thought I'd take a stab at it using Microsoft's recent jab at VMWare as a starting point.

Of course, Brent's breadth and depth of SQL and IT experience, along with his presentation and writing skills, do a SQL master make.

Congratulations on the MCM!

Lies, Damned Lies, and Resolutions

Thank you to Microsoft MVP and rampant blogger, tweeter, and silver-screen maven Jen McCown for hosting this month's T-SQL Tuesday (#14).  Congratulations, Jen! (Yes, I'm a fan of the Midnight DBAs)

Many people claim they don't make new year resolutions.  A few of them are telling the truth.  The rest have secret resolutions they won't disclose for fear of failing publicly.  I fall in the latter category.

With this in mind, I have not resolved to get a SQL Server Certification: I have pre-purchased an Exam 4-Pack to cover a double MCITP and am sitting the MCTS: SQL Server 2008, Database Developer today.

I have also not resolved to participate in a T-SQL Tuesday:  I am writing and submitting my first post right now. Insecure?  Yep.  Letting it trip me up?  Not this time.

These things started off as secret professional resolutions, but I've decided to take action on them.

I still have other secret resolutions, mainly recycled secret failures from years past.  Will I be acting on them?  Some yes, and some no.  Will I put them out there as "resolutions" and risk the public failure?  I guess so.

• That 100 extra pounds (45 Kg / 7 stone) I've acquired since settling down?  Nope.  Not going to resolve it.  I am, however, "resolving" to spend some quality time with the exercise bike so I can manage the stairs better and let the weight take care of itself.  You have my permission to call me on it in a couple months.

• Replace the broken dishwasher?  Nope.  I'm going to hire somebody who's good at it to do it for me.  I make my living specializing in SQL Server and don't expect my clients to be especially brilliant with database systems.  Why should I expect to hack together a home repair that's anything but mediocre?  A quick note here: my house of full of mediocre but largely functional do-it-yourself jobs, so letting this go is a bit of a challenge for me.

• Speak at the local SQL Server User Group?  If they'll have me again.

So, nothing spectacular, just a few public steps out of the comfort zone.  Won't you join me?

You can't afford what?

Tibor Nagy recently did a nice intro to Populating a SQL Server Test Database with Random Data.

It was a good primer to generating gobs of data, but part of the problem the solution was addressing wasn't technical: "I heard about some excellent commercial tools but they are expensive and my company cannot afford them."  The first commercial tool that comes to mind is Red Gate's SQL Data Generator, which isn't expensive by any measure, least of all a DBAs time.

It sickens me when a company can afford to squander days of a $50/hour DBA's time hand rolling something that could be had commercially for only a few hundred dollars.  How can a company that can afford a DBA not afford a simple tool that will save days of effort and free up that expensive human time so it can be spent solving business problems?

To illustrate, a company where I used to work had an extravagantly expensive suite of database design, monitoring, and migration tools.  That was well and good, but the migration tool took hours to compare a simple database, and often didn't succeed (though you still had to wait hours to find out).  The result was a lot of squandered time and production releases that were often missing database objects or permissions.  Ouch!

This company frowned on any suggestion to evaluate a different tool-set, so one of the DBAs, who believed more in getting things done than toeing the line (this gave him a reputation as a something of a cowboy), downloaded a trial copy of Red Gate SQL-Compare and started playing with it.  This DBA's compare times went from hours to minutes (or less) and he no longer had last minute manual compare frenzies prior to production releases.

After a few more DBAs started using the evaluation - the tool completely paid for its purchase price in the first week of evaluation - the company finally realized that all this "free" salary time was being squandered, and decided to make the purchase.  The enterprise-class database tool was put to rest, along with its rather generous annual maintenance fees, and they never looked back.

Though I have yet to work there, someday I will work at or run a shop where each DBA has his or her own annual tools budget equal to roughly a single day's pay that can be used to purchase simple tools about which they are passionate.  They gain experience managing a corporate budgetary item and get the power and confidence to use that money to save even more money without any corporate red-tape, thank you very much.