The Owl Has Been Fed

After two and one half years of on-again off-again effort - mostly a couple months up front and a few weeks at the end, I finally completed the final section in the Duolingo Spanish curriculum.  They optimistically rate me at 57% fluency, but I take that with quite a few grains of salt.

What it seem to mean is that, given time to think about it, I can recognize many of the major constructs of the language and piece together the meaning of various words and phrases.  

However, it doesn’t involve a lot of vocabulary and I still display an almost complete lack of verbal fluency.  I do notice I can pick out a little more of the commentator dialog when watching soccer, but cannot understand snippets of conversation in real-life.

All-in-all it was an enjoyable refresher for my 20-year old Spanish minor and, if I spend some time each day keeping the 62 sections colored gold, I would expect the average time to recognize and/or generate a word or phrase will continue to drop, as well as improved competence with the various verb tenses which came at a rapid pace near the end.

Next up will be "Duolingo Stories" which is a beta product that consists of four sets of ten "mini stories" that focus more on listening and reading comprehension for intermediate to advanced learners.  Should be a nice supplement to the basics supplied by the original courses and a good way to build out some more vocabulary and develop an "ear" for the language.

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.