Category SQL Server

Entries 164 Total

T-SQL Tuesday – “Did he just say that?” edition

Jun 15, ’10 8:00 AM

As in, I didn’t participate in the most recent T-SQL Tuesday about my favorite feature in SQL 2008 R2.

Want to know my favorite 2008R2 features? PostgreSQL 9.0 and MongoDB.

PostgreSQL and MongoDB are rapidly advancing features that solve my daily problems. I’m an OLTP guy. Honestly, I don’t care about the latest reporting ding dongs and doo dads. I already know PowerShell. I manage 6 production servers and we’re unlikely to grow, so these MDM and Utility Control Points don’t make me giddy with excitement.

I solve problems using SQL.

You know what makes me happy? Support for window functions or better yet, improved support for window functions. What about exclusion constraints? Or column level triggers so I don’t have to use branching logic in my triggers? Yes, I use triggers. Or any other of these features.

What about MongoDB? I’ve just started playing with it, but it solves a lot of the problems I face at work. Not just in the day job, but in side projects as well. I’ve bitched about O/R-Ms before, but one of the biggest problems that users of O/R-Ms (developers) face is that the ideal way to model data for object-oriented programming bears no resemblance to the ideal way to store relational data. A recent article about scaling Farmville hints at this – the developers of Farmville managed scale by storing everything in a key-value store (memcached) before persisting to a relational data store later. Digg does something similar with Cassandra. It’s not like these guys are idiots, the blog posts from Digg show that they know their stuff.

MongoDB lets me solve these problems. I can control the frequency of syncs to disk (just as I can in PostgreSQL) to improve raw write performance to memory. I only have to worry about storing data the way my application expects to see the data – arrays and hashes – without worrying about building many-to-many join tables.

What about DMVs and data integrity and a write-ahead log and indexes? MongoDB has instrumentation and indexes. Yeah, you sacrifice some durability but many applications don’t need that. Hell, Amazon has even designed their systems to account for the potential of failure.

When I start my next application, I’m going to look long and hard at the platform I’m building on. There’s a good chance it’s not going to be a relational database and a really good chance it’s not going to be using SQL Server. It’s not because I have a problem with SQL Server or even RDBMSes, but because there are other choices that give me the flexibility I need to solve the problems I’m facing.

This is nothing against SQL Server 2008 R2, it’s a great step forward in the direction that SQL Server seems to be going. Sometimes I wonder if SQL Server and I are on the same road.

Comments (1)

This Is How We Handle Problems

Jun 11, ’10 11:45 PM

I had a production issue tonight. Still am, actually. I’ve admitted to it and here’s the email I’m sending to my management.

At 9:00PM I took a backup of database_a and database_b prior to running the database migration scripts. Once the backups were finished, I began the migration process at approximately 9:20.

I stopped the migration process at 10:15 after multiple failures and restarts. There are too many unknown cross-dependencies to go on with the roll forward. At this time I called SUPPORT PERSON and explained the situation. I also called MANAGEMENT and left a voice mail. I then began the process of restoring the production databases on SERVER_A.

No changes were made to SERVER_FIGHTING_MONGOOSE or SERVER_C.

Once I had restored database_b and database_a on SERVER_A, I began seeing multiple failures from replication and the rest of SQL Server indicating severe problems with the physical disk structure. I immediately stopped all replication involving database_a and database_b on SERVER_A and I have begun a physical drive integrity check using SQL Server’s built-in integrity check tool: DBCC CHECKDB. The CHECKDB for database_b finished at 11:15 with a clean bill of health. database_a is still running as of 11:31PM.

Once the CHECKDB process for database_a is complete, I will begin re-initialize the subscription for the database_a database on SERVER_A. Following the successful completion of the database_a re-initialization, I will begin the process of re-initializing the subscription to database_b.

If you have any questions, feel free to contact me at 867-5309.

See what I did there?

  • I stated how we got into this mess – I dropped a running chainsaw into the SAN.
  • I outlined my decision making process and took ownership of rolling back our production migration.
  • I described the situation after the migration was rolled back and provided an assessment based on what I had observed.
  • I outlined a course of action to mitigate our problems and restore our production database to an operational state as soon as possible.

Am I proud? Not really. I like it when things work. Am I tired and cranky? Yes.

Will I get this fixed before I go to bed? Hell yeah.

Is this something that I, in a sick way, live for? Only because it reminds me to keep studying and to stay on my toes.

Comments (1)

What the heck are you people talking about?

Jun 7, ’10 8:00 AM

The call for speakers is closed and the selection committees are about to begin reviewing the abstracts. Before they get started, here are a few little pieces of trivia about the sessions.

Keep in mind that I have done minimal cleansing on this data so it’s going to be a bit messy and unreliable for anything apart from entertainment purposes.

Raw Numbers

There were 574 sessions submitted this year by 209 speakers working for a total of 163 separate companies.

Out of a total of 127 job titles, the most common one was “Consultant”. Or, it would be until you take into account the different variations of DBA, Database Administrator, etc. Taking that into account, 20 of you are some kind of DBA.

If you want to get a bit fuzzier about the numbers:

  • 17 of you are an engineer and are probably measuring something with calipers right now
  • 12 of you identify as a manager
  • 12 of you think you’re a developer
  • 27 of you claim to be a consultant
  • 11 of you work in B.I.
  • 32 of you claim to be architects. You draw squares and arrows to represent work.

All of you work for a total of 163 different companies. Unless some of you can’t spell your company’s name right. Then you may work for fewer, I’m not sure. The data, as I said, is dirty.

The Submissions

The average length of a session abstract was 617.7 characters or 99.19 words with a standard deviation of 243 characters and 39 words. Out of all the presentations, 187 have never been presented before. They may very well be 100% fresh in November when you arrive.

Here’s the distribution between the different levels:

  • 100 Level (Novice) – 109 sessions
  • 200 Level (Intermediate) – 239 sessions
  • 300 Level (Advanced) – 178 sessions
  • 400 Level (Expert) – 44 sessions
  • 500 Level (Advanced Expert) – 2 sessions

Pre/Post Conference

42 total pre/post conference sessions were submitted. That’s a lot of competition for a few slots.

  • 14 were B.I. (9 for architecture and 5 for reporting/delivery)
  • 13 relate to DBA work
  • 12 relate to application development
  • 3 relate to professional development

The only conclusion I can draw is that very few of us are prepared to talk about our careers for a single work day. Are you playing Legend of Zelda at work all day? What’s up with that, guys? People making a living talking about this kind of stuff.

Spotlight Sessions

There were 58 spotlight sessions submitted and I’m afraid to say that your day jobs faired even worse in this section.

  • 20 were DBA related topics
  • 15 were development topics (this better not be all IronPerl … I mean PowerShell)
  • 13 were somehow related to B.I. architectural whatnot
  • 5 were related to B.I. reporting and delivery. Apparently these guys are too busy making money to talk at the PASS Summit for 8 hours.
  • 5 of you thought that you could talk about your job for 90 minutes. Good for you. That’s long enough to get to the good parts in Krull or watch any number of amazing kung fu movies.

The distribution is starting to skew a bit here. If we combined the BI tracks it would look a lot more like the pre/post conference situation.

Community Sessions

Here is where you guys clearly shine. You submitted a whopping 472 sessions. That’s an average of 2.25 per person. While not bad, you could have submitted 4. I’m just saying that maybe next year you should pick it up a little bit.

  • 137 application development topics. We’re number 1, we’re number 1
  • 135 DBA topics put this in at a close second.
  • 113 BI architecture topics
  • 48 Professional development (you can talk for 75 minutes about your job, but not 90?)
  • 39 B.I. reporting topics. I think these people really must have jobs to do, because that’s not a lot.
Comments (3)

The Missing Speakers

Jun 1, ’10 6:38 PM

Allen Kinsel (twitter | blog) and I have been fielding a draft email back and forth this afternoon. Rather than bore you with the backstory, here’s the body of the email:

After we heard from a few Microsoft employees who work outside of the normal speaking groups, we decided to do something about it. We’re asking you, and anyone else you know who falls into this category, to submit your sessions during the community call for speakers. This is our way of making sure that you don’t slip through the gap. Please pass this on to any of your colleagues who may find themselves in the same situation.

One last thing: The Call for Speakers ends Saturday, so you will have to hurry.

http://sqlpass.eventpoint.com/cft/

Why would we do this? Microsoft have their own call for speakers, right? They do, but it’s a Microsoft internal process – we can only make suggestions about the final speaker selections.

Here’s the deal – there are some speakers out there that we feel are an important part of the community, speakers who are outside of the normal Microsoft speaker track. They aren’t on the product development team, they aren’t architects, they aren’t general managers at Microsoft. These are the DBAs who keep Microsoft’s databases running, CSS/PSS, and other DBAs out there in the trenches. Most importantly, they’re also members of our community.

You probably know some of them – they’ve spoken at the PASS Summit, at SQL Saturdays, and at user groups. They’re highly rated speakers and they’re valuable members of the community.

If you know someone who falls into this group of people, pass this message along. We are working to make sure that this year’s PASS Summit is the best one that we’ve had and we know these people will make it even better.

Thank you.

Comments (5)

Push Ups and String Concatenation

Jun 1, ’10 8:00 AM

SQL sucks at string manipulation. It’s not just SQL Server, it’s the SQL language in general. I’ve mentioned this before on Stack Overflow in answer to the question “What are five things you hate about your favorite language?” It’s almost easier to return the raw rows to application code than it is to work with them inside of a database. There’s one place where SQL Server has made it easier to work with string data – string building.

Continue Reading… Comments (8)

How Much Space Am I Taking Up?

May 27, ’10 8:00 AM

Are you running out of space? Filling up drives? Don’t know where to go? Is your SAN administrator complaining about your continued demands for more spindles? Do you need to juggle databases around on different servers but don’t know how you’re going to figure out the size of your index, table, and materialized view filegroups as well as the filegroups you’ve created for every schema just to piss off the DBA?

Take a step back and relax. I’ve got it covered.

-- i hate you too, temp table
IF OBJECT_ID(N'tempdb..#db_files') IS NOT NULL
  DROP TABLE #db_files ;

-- load up the temp table
SELECT  DB_NAME([dbid]) AS database_name ,
        [filename] ,
        UPPER(LEFT([filename], 1)) AS drive_letter ,
        ( size * 8 ) / 1024 AS size_in_mb
INTO    #db_files
FROM    sys.sysaltfiles AS saf
ORDER BY saf.[dbid]

-- how full is each drive?
SELECT  drive_letter ,
        SUM(size_in_mb) AS size_in_mb
FROM    #db_files AS df
GROUP BY drive_letter
ORDER BY drive_letter ;

-- how big is each database?
SELECT  COALESCE(database_name, 'RESOURCE DB') AS database_name ,
        SUM(size_in_mb) AS size_in_mb
FROM    #db_files AS df
GROUP BY database_name
ORDER BY database_name ;

-- what do the details look like?
SELECT  database_name ,
        [filename] ,
        size_in_mb
FROM    #db_files ;
Comments (9)

Better When You’re Not Alone

May 24, ’10 8:00 AM

You, in the back, are you having fun?
In the last month, I’ve gone to three different concerts – once by myself and twice with a friend. They’ve all been great shows, but for different reasons.

The show I went to alone was one of my favorite bands. It was a great show, I had a great time and ran into a few old acquaintances, but it was still awkward being there alone: I didn’t know anyone and it’s hard to make new friends at a loud concert.

The next two shows, I went with friends. The experience was completely different. When you’re hanging out at a show with a friend, there’s a lot of shared fun that goes on. Everything is more entertaining. When a song is really good, you both notice it, you comment on it. Afterwards you have someone to talk to about the experience. What you witnessed gets better because there’s someone else to share the experience. For the record, I really didn’t like the most recent concert I went to. It was an average performance, but I had a good time. Why? I was there with a friend.

Going to user groups and conferences is the same way. It’s better if you already know someone, but what if you don’t?

I’m a naturally shy person and an introvert. I prefer one-on-one interaction to large groups of people. At the same time, when I go to a SQL Saturday, speak at a user group, or attend a conference I go out of my way to meet new people.

Why am I going out of my way to meet new people? Because odds are that I don’t know anyone at an event and if I don’t know someone, chances are that there are other people present who don’t know anyone. If you have a good time, you’re more likely to do something again, right? And, if you know somebody it’s easier to have fun, right?

I’m making new friends, having more fun, and we’re all more likely to do this again.

The next time you’re at a user group meeting, SQL Saturday, or a conference, take the time to make a friend. I guarantee that you’ll have fun.

Comments (3)

Encrypted Stored Procedures and Their Effect on my Rug

Apr 8, ’10 8:15 AM

This is a letter, or a rant, to any ISVs in the world who encrypt their stored procedures. It is, by no means, a condemnation of this horrible feature of SQL Server. The feature condemns itself.

I saw something in a blog the other day (that I wish I could find it again) where the author essentially said that encrypting stored procedures is an absolute necessity. I couldn’t help but incredulously think “Really? What part of your code is so cunning that I can’t see it?”

Yes, I understand that someone might comment here and say “Well, certain encryption algorithms might need to be encrypted in stored procedures so that people don’t discover and break them.” To which I must respond: grow up. Nobody is writing encryption code in T-SQL. If they are, I don’t want to read it.

The fact is, Mr. ISV, your ideas are not so precious or original. I don’t have a degree in Computer Science, but I do know the basics of algorithms and design patterns. There are only so many ways to skin a particular cat. I can watch the behavior of my SQL Server and figure out just how craptacular your code is and make a pretty educated guess that you’re using cursors written by someone whose understanding of programming concepts stopped when VB was a threat to PowerBuilder.

Please, for all of us, just stop. Your insistence that your secret sauce is important is laughable. Nobody cares. Your competitors don’t care. They’re either too busy catching up or else too busy lighting cigars with hundred dollar bills to care. Nobody that I work with is going to spend their free time reverse engineering your software so we can stop paying maintenance fees – we pay you because paying you is cheaper than doing this work in house.

When you encrypt your stored procedures, I can’t help you make it better. It’s like you only come home drunk, throw microwave burritos at the cat, crap on the rug, and then leave. There’s no possibility for discourse about what you could do better. I don’t want to talk about what you’re doing wrong, I want to make this deal sweet for both of us – I want to make my system faster and help you make your other customers’ systems faster.

Please, for the sake of me and your other customers, stop shitting on my rug.

Comments (20)

SQL Saturday 42

Apr 6, ’10 9:00 AM

SQL Saturday is coming to Columbus, OH! I’m pretty excited about it.

Want to help out? Speak at the event. Maybe you just want to attend? That’s okay, too!

The point is that there’s going to be a day of free SQL Server training in Columbus, OH. You could be there!

Comments (0)

Free SQL Server Training in April

Mar 30, ’10 10:00 AM

I’m going to be speaking at a few events in April. I bet you want to come visit and hear the crazy sounds that I’m going to be making!

Thursday April 8th – Columbus, OH

Fundamentals of SQL Server Internals

The Abstract: Want to know what makes SQL Server tick?
Ever wonder what SQL Server is doing when you run a query?
Ever wonder which parts of SQL Server are responsible for specific functionality?

Jeremiah Peschka can’t promise answers to every question, but he can set you on the path to knowledge about the inner workings of SQL Server.

Location: Battelle for Kids – 1160 Dublin Rd Suite 500, Columbus, OH 43215

Time: 6:30 PM, but you can show up at 6:00 PM for refreshments and socializing.

The Details: This is a fun little romp around SQL Server’s internals. This isn’t intended to be an exhaustive introduction. It’s supposed to be a fun look at how SQL Server works internally and give you more information to get around.

Saturday April 10 – Richmond, VA

I’m speaking at SQL Saturday 30 in Richmond, Virginia!

Fundamentals of SQL Server Internals

This one starts at 8:30 in the morning, so bring your coffee!

A Dynamic World Demands Dynamic SQL

The Abstract: Dynamic SQL is a misunderstood and much maligned part of a DBA’s tool kit – it can be used to solve difficult business problems, respond to diverse data needs, and alleviate performance problems. Many DBAs reject dynamic SQL outright as a potential source of SQL injections, being poorly performing, or just for being a hacky solution in general. Not so! Jeremiah Peschka has been making extensive use of dynamic SQL throughout his career to solve a variety of problems. He’ll set about dispelling these misconceptions and demonstrate how dynamic SQL can become a part of every DBA’s tool kit.

The Skinny: I sometimes catch flak for this, but I use a ton of dynamic SQL on a daily basis. It’s the only way I could possibly build some of our most complex reports. Frankly, dynamic SQL can perform just as well as anything else that you’re doing so why not take advantage of the tools at your disposal? Come along and learn more.

Saturday April 17 – Chicago, IL

Double whammy! You can travel to Chi-town and see me present both of my talks again but in reverse order!

A Dynamic World Demands Dynamic SQL

Bring your dancin’ shoes we’re getting that party started at 9:00 AM right after the welcome and keynote.

Fundamentals of SQL Server Internals

Less dancing, more learning this one goes on at 3:00PM. Hopefully your brain won’t be full by this point in time. If it is, I suggest that you empty it.

Comments (2)

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 401 items have been purified.