04 October 2011

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.

05 May 2011

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."

02 May 2011

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.

22 April 2011

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.

13 April 2011

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.

08 March 2011

The Absolute Best

This month's T-SQL Tuesday is hosted by Jes Schultz Borland ( BlogTwitter ) 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.

28 February 2011

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?