What is Your Greatest Weakness

I was tagged earlier this week by Tim Ford to comment on my biggest weakness. I wish I could say that it’s the color yellow (stupidest weakness, ever), or kryptonite. I don’t have to wear ruby-quartz glasses everywhere (and good thing too). Certainly getting tied up by a man isn’t my weakness (eeewww). And while I do agree that with great power comes great responsibility, I don’t feel responsible for everything. I’m not suffering from a terminal heart condition (or shards of metal in my blood stream). So, I’ve avoided all the obvious (or silly) weaknesses. No, unfortunately, my weaknesses, and they are myriad, varied, and far too numerous, are pretty much of the straight human variety, despite my rather obviously being a mutant. Trying to pick one that stands out, it almost depends on the hour of the day, from the hundreds & thousands there are to choose from, is tough. The one weakness that does immediately come to mind though is communication and communication style.

I don’t have Asburger’s, I think. I  tend to speak directly and bluntly. That’s when I speak at all. With effort I can be personable and friendly and, on rare occasions, charming. But it’s an effort. I have to try, and I have to keep thinking about talking to people, it’s a struggle and it shows. Left alone, left to my own devices, I stop talking or I only blurt out stuff occasionally or I try to run conversations, talking over people. It’s tough. The interruptions especially get people upset.

The obvious shortcomings of poor communication aren’t even what bothers me about it the most. The one thing that I think hurts me, and makes this truly a major weakness, is that I make people angry, but I do it unintentionally. Don’t get me wrong. I don’t mind making people angry. I’ve even gone out of my way to do get certain individuals good and upset. It’s when I do it accidently, because I’ve interrupted or because I’ve blurted out that their pet theory, whatever it might be, is stupid, that really hurts, me and that person. The fact is, what they’re saying may very well be stupid, but people don’t appreciate getting told that, especially in such a blunt manner. If you make people angry, they remember that. They don’t remember that you did help fix their problem, or that you saved the company oodles of cash or that you kept the project afloat. Nope, they remember that you really made them mad, which means that you’re “hard to work with.”

I’ve been working on this, a lot, over the years. Some of you who have met me out at conferences or at user group meetings might never have noticed this behavior. That’s because I’m using all that communication time to try to work on it. Some of you at those same functions may have been all too aware of it. I really have gotten pretty good at staying “on” and communicating at least reasonably well for long periods of time, but it’s bloody tiring.

My version of kryptonite is now on the table. Let’s see who has trouble with materials made out of wood, the color yellow, or gets weak when tied up by men. How about I tag, oh, Mike Walsh (I’ll bet it’s that tieing up thing) and Gail Shaw (probably red kryptonite).

T-SQL Tuesday #1: Date/Time Tricks

I’m going to try out Adam Machanic’s idea for a blog party. The topic this month are Date/Time tricks.

Instead of supplying a trick for Date/Time, I’m going to caution you about the tricks that you use. Let’s take a simple issue. You want to pull back data from a table, let’s use the Production.TransactionHistoryArchive in AdventureWorks2008, for a given month of data. Before we run the query, let’s create an index on the table:

CREATE INDEX ixTest ON Production.TransactionHistoryArchive
(TransactionDate)

The query itself is pretty simple. This is one mechanism that will retrieve the data for the month of July in 2003:

SELECT tha.TransactionID
FROM Production.TransactionHistoryArchive AS tha
WHERE DATEPART(yy,tha.TransactionDate) = 2003
AND DATEPART(mm,tha.TransactionDate) = 7

In theory you should be able to use the index that was created earlier, but instead, you’ll see this execution plan:

The problem is occuring because there is a function running against the columns. This is going to force a scan, even though you have a good index. Rewriting the query so that it looks like this:

SELECT tha.ProductionID
FROM Production.TransactionHistoryArchive AS tha
WHERE tha.TransactionDate
BETWEEN '2003/7/1'
AND '2003/7/31'

Eliminates the function on the column so that the execution plan is now a nice clean index seek:

Whatever tricks you begin to apply to date/time, be careful of how you apply them. And, if you try a fix like I supplied above, be sure it returns the data you think it returns, testing is the key to applying anything you read on the internet.

Dr. DeWitt’s Key Note at the PASS Summit

If you missed this, here’s your chance to make it up. If you were there, and like me, you need to rewatch it about six to eight times to try to understand everything that was presented, here’s your chance. Dr. DeWitt’s key note was probably the high point of the Summit or at least in the top 5. It’s not to be missed.

SQL Standard Update

Andy Warren in the latest PASS Connector has posted an update on where we’re at with the SQL Server Standard. I’m so happy that Andy has been keeping this out in front of people. It provides some impetus to get the work done. Except for the authors thanks (and they’re receiving $500, so they should say thank you), there’s very little feedback on the Standard to show whether or not people are interested, if the goals and ideas are worthy… In other words, you guys need to let us know what you think about the thing.

Two more articles are in the hopper to be published. Another has gone into copy edit. We’re technical editing two others. That’s five more, so you guys can expect to see another ten weeks worth of SQL Server Standard. But after that…

Don’t you want $500? Just submit an abstract and your writing history to grant.fritchey@sqlpass.org. We’ll get you started.

How do -You- use SQL Server

I’ve been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That’s a tough one. It would make a much shorter, and easier, blog post to describe the things we don’t use it for. However, keeping with the spirit of these tags, I’ll try to lay out it.

For those that don’t know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we’ll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70’s and 80’s propped up by ancient men with pocket protectors, spit, bailing wire and happy thoughts. This also means that we have apps running on SQL Server 7, 2000, 2005 and 2008. Give me a couple of weeks and I’m sure I can get an R2 app deployed. There is also a few Oracle databases, our warehouse and Peoplesoft in particular. We even have a DB2 and, I think, one Sybase database somewhere.

I don’t want to go into lots of details about the type of data we store, lest I get in trouble, but basically, think insurance and you’ll get a pretty good idea of a lot of it. Add in the fact that my company prides itself on engineering to avoid risk and you’ll know that we gather quite a bit of information about the things that we insure. There are lots and lots of very small databases. Our largest db’s are just breaking 100gb, but must are in the 20-60gb range. We have a ton of OLTP systems gathering all the different data. These have been developed in just about every possible way. We even have a couple of systems using nHibernate under development. We move, mostly, pretty standard structured data. We have a few processes that are using XML, mostly from third party sources, to import data, so we’ve learned how to shred that stuff into the database. Spatial data, insurance remember, is the big new thing on the block. We’re seeing lots more implementations taking advantage of this. We don’t see much in the way of unstructured data, but some of the reports from the engineers falls into this realm. We also get quite a few photo’s from them that want us to store. We’re working on using FileStream to keep those in sync with the db rather than storing them within the database itself.

Currently, and I hate this, the overwhelming majority of our OLTP data is collected in SQL Server. All our datamarts used for reporting are in SQL Server. BUT, in the middle sits our Oracle warehouse. So we have to convert our data from SQL Server into Oracle and then back into SQL Server. It’s fun. Swapping data types over & over, shrinking column names only to re-expand them into something a human being can read… It’s lots of fun.

We use SSIS for almost all our ETL processes, although we have a few DTS packages still chugging away on some of the 2000 servers. We’re running a bit of replication, but nothing major. We have several fail-over clusters in production. We’re running virtual machines in lots of places. We’re expanding our Reporting Services implementation pretty rapidly. After attending Buck Woody’s sessions at PASS this year we’re getting Central Management Servers and Policy Based Management implemented.

Most of my time is spent working with development teams. We do most our deployment work using Visual Studio. I do database design, stored procedure writing and tuning, data access methods… you name it and I’m involved with it. The level of our involvement varies from team to team, but by & large, we get involved early in most development projects and are able to influence how databases are developed.

For monitoring we’re using Microsoft System Center Operations Manager (or whatever it’s called this week). We’ve modified it somewhat, adding special rules & monitors to keep an eye on the system better. We also use Idera’s SQL Diagnostic Manager to help keep a more detailed eye on the systems. I already mentioned that we use Visual Studio for most of our development & deployments. We also use several Red Gate products, Compare, Data Compare, Prompt, pretty much every day.

That’s pretty much it. We keep busy and keep the systems up, running & happy most of the time.

Because I think they’ll have interesting answers, I’m going to pass this on to Jeremiah Peschka and Tim Ford.

SQL Saturday #34/New England Data Camp v2.0

It’s official sports fans. Well, it’s been official since last week since Adam Machanic set up the web site. The New England SQL Server Users Group and the Southern New England SQL Server Users Group are again jointly hosting a full day of SQL Server goodness on January 30th. The call for sponsors and speakers is open. We already have several local luminaries lined up to present including Aaron Bertrand on Management Studio Tips & Tricks and Scott Abrants on Automating Database Deployments with Visual Studio.

Please register to spend a day with your peers, learning and networking. It’s being held at the Microsoft Waltham office, a great facility. We should have a full day with lots to do and learn.

SQL Server XQuery For Idiots

I’m still struggling with learning XQuery. My latest little revelation was small, but vital.

The difference between:

@inXML.nodes('/rss/channel/item')

and

@inXML.nodes('/rss[1]/channel[1]/item')

Is the difference between a 10 minute query and a 4 second query. Kind of shocking really. My understanding is that XQuery assumes there are multiple possible paths that look like “/rss/channel” so it searches over and over again through the XML to see if it can find them. But by identifying it as a root, showing that only one possible path is available, it stops stumbling around in the dark and simply reads the data.

It’s a little thing, but it made an enormous difference. I’ve still got a long way to go in learning how to use XPath within XQuery.

UPDATED: I modified the title so that it’s clear I’m talking about SQL Server here.

Slow Blogging

Sorry, I’ve been travelling for three weeks and, except for the posts around the PASS Summit, I’ve been blogging very intermittently. I’m back in town, all travel done. The posts will pick up from this point forward.

Record of a PSS Call

Not everyone has the opportunity to call Microsoft Premier Support. For those who have not yet had this experience, I’ll document my most recent, non-critical, call. Critical calls are a different critter entirely.

We were experiencing a very odd error on one server in our system. When a particular procedure was run with a particularly large set of data, it would produce an error, but only when called from the application. The exact same error with exactly the same data called from SSMS did not produce an error. We went through a very extensive set of tests and were unable to fix the problem on the server. After moving the production system that was experiencing the issue to a different server, we decided to contact PSS.

8:48 Am, Tuesday: I made the initial call and went through the process of validating my identity, our company, etc. I had to explain everything that we had done, what versions of the software involved everything was, etc. This took 10 minutes. Since I had called early in the day and this was non-critical call, although labeled as important since we are talking about a production system, I had to wait for the MS guys to come in.

9:47 AM, Tuesday: I received an email from PSS asking me for the SQL Server logs. I zipped up the most recent log with the error and sent it in to the guy. This took me about five minutes to get everything together and zip it up for him.

10:53AM, Tuesday: I received my first phone call on the process. I re-explained the issue to the support person. they suggested that we set up a server-side trace and get the query to error out when run from the app, and to run successfully from Management Studio and see what differences there might be, if any, in the basic trace. It took me about 30 minutes to set up the server-side trace, test it, generate the errors and run the query successfully and then zip it all up with sample code and sample data to ship off to MS.

Does it feel like this is going to take a long time? You’re right. It continues.

12:10PM, Tuesday: I get an email back with some initial thoughts. Unfortunately, it looks like we don’t have complete information. I respond in the email, providing more.

12:26PM, Tuesday: Another email back from MS confused about the trace. They had me use the default trace, which captures RPC:Completed and SQL:BatchStarting and SQL:BatchCompleted. They claimed that the app wasn’t run and must have errored before calling the database. But that’s because the app errored which means no RPC:Completed. We didn’t capture RPC:Starting. I pointed this out in a response. Nothing back yet.

1:32PM, Tuesday: I get my response back, am I sending a new trace? Nuts. I knew when I realized what they were looking for that I should have restarted the trace. There’s an hour shot.

2:35PM, Tuesday: I’ve finished rebuilding our trace, retesting and sending everything off for evaluation, again. It took longer for me this time because I was waiting on a developer who was away from his desk. PSS calls require everyone to be available, all the time. When we do critical calls we just jam everyone into the same room. It makes it easier.

6:39PM, Tuesday: The data wasn’t sufficient. It doesn’t show when the error occurs. PSS sends me a new trace template to try out. Unfortunately, I’m not at work any more and what’s more, the developers who’s support I need are long gone. I can’t do anything with this until morning.

DAY 2

8:08AM, Wednesday:  I’m setting up the trace to see what they’re having me gather while I wait for the developers to show up. Odin’s all seeing eye! They’re capturing quite a few events here. Just about every single event in “Errors and Warnings.” OLEDB Errors. Auto Stats, Degree of Parallelism, Showplan All and Showplan XML Statistics Profile from “Performance.” Server Memory Change. PreConnect Completed and Preconnect:Starting from Sessions (I’ve never even seen these before). RPC:Completed, RPC:Starting, SP:Completed, SP:Recompile, SP:Starting, SP:StmtCompleted (I knew that one was coming),SP:StmtStarting from “Stored Procedures.” Finally SQL:BatchCompleted,  SQL:BatchStarting, SQL:StmtCompleted, SQL:StmtRecompile, SQLStmtStarting from “TSQL.” A full load. This should be fun.

I’m converting it to a script and adding a filter to only capture data from the database I’m having troubles with. I’m also converting to a script for another reason, which I’ll post a little blog post about seperate from this one.

11:11AM, Wednesday: The joys of developing software. The developer I was working with yesterday wasn’t in, so I got a different developer to help out. He didn’t know how to run the code the way the other guy ran it. So it was run in a different way. I captured everything and shipped it off to Microsoft. While waiting for their response, I read through the data gathered by the trace. No error. In fact, everything ran successfully. We did a bunch of tests and found that as long as we were running the big trace, we didn’t get the error. It was almost like the error was caused by having too many resources.

12:00PM, Wedensday: The PSS person comes back and says that the data contained no error. Yep. They don’t have a single suggestion. All the indications are, the faster, more powerful server, is causing the problem.

DAY 3

I get a response back from PSS. They’re asking if I’m suggesting that I’m supposed to run the trace all the time. This is the point where I decide to bail on PSS. I’m leaving the case in a non-closed state, but I’ve been talking to some other resources and have a troubleshooting scheme from those resources that we’re going to try out.

More when I know more.

Proof

SQL Rockstar tells me that unless there are photo’s, it didn’t happen. Brad McGehee published some pictures from DevConnections. See, I really do try to get people to buy my book… uh, I mean present technical sessions at conferences.

Note the laptop with the styling stickers from:
SQL PASS
SQL Batman (defunct)
SQL Serverpedia
SQL Server Central
Thrive
SQL Server Bible
SQL AgentMan
SQL Rockstar

One of these things is not like the others…