Tag SQL Server

Entries 136 Total

SQL Saturday 42 Musings

Jun 29, ’10 7:28 AM

SQL Saturday 42 has been and gone. I don’t have a ton to say, but I wanted to try to post my thoughts on the event before they were completely gone from my mind.

The people who put this event on – Dave Schutz, Stuart Johnson, Marc Kuyper, Jim Stoltz, and John Jakubowski – deserve a big round of applause. They put together a very strong event and it seemed like they were incredibly well prepared.

Despite Brent Ozar suffering from incredible jet lag, and another speaker not showing up, things went off well. Allen White and I were able to cover Brent’s time slots, but not his presentations. This led to the funny moment of an attendee walking into the room and saying “You’re not Brent Ozar.” I replied, “No, I am not, but I’m talking about SQL Server Internals, it’ll be fun.” They just said, “Oh,” and walked away. So, apparently, I’m nowhere near as engaging as Brent talking about his stupid and dangerous T-SQL tricks. Clearly they don’t know that I’m just stupid and dangerous.

Outside of that, things went very well. My Dynamic SQL talk went over well, although I think I need to re-work it. I felt like I was flipping back and forth between demos and code a bit too much. It gave the entire experience a jerky feel. I suspect I’m overly critical of the presentation since I’ve given it a few times, but it’s always good to improve.

My internals presentation wasn’t well attended – this is probably due to me not being Brent Ozar. However, that didn’t stop us from having fun talking about SQL Server Internals. If anything, having a room of 6 people made the topic more fun to talk about. We were able to digress onto different topics, delve a little bit deeper than normal, and have a lot of fun watching me not trip over an extension cord.

Last but not least was my Indexes and Other Free Performance Boosts. I had a full room on this – I suspect it had something to do with the word “Free” in the presentation title. This is a really fun presentation because it’s a whirlwind tour of indexes, keys, statistics, and how they work together to coax SQL Server into giving us data faster. Once again, I think I need to smooth up the code samples, but on the whole things went really well.

I don’t have any of the evaluations, but I hope the SQL Saturday team is able to get those out to the speakers soon. From the ones I glanced at, I did a passable job. I’m looking forward to the next event in Columbus.

Comments (3)

Free Training – SQL Saturday 42

Jun 23, ’10 9:01 AM

Good news! I’m speaking at SQL Saturday 42 this Saturday. Got nothing to do? Head on down to Goodwill Columbus at 1331 Edgehill Rd, Columbus, OH. Got something to do? Cancel it.

I’m excited about the presentations I’m giving – I haven’t given the indexing presentation in a long time and it should be a lot of fun. And the Dynamic SQL presentation is one of the first in the day. It’s a nice easy way (I think) to get your day started.

A Dynamic World Demands Dynamic SQL

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.

Indexes And Other Free Performance Boosts

The database is often viewed as a major performance bottleneck. There are a number of quick, easy, painless techniques that can increase the performance of an application not just by a small amount, but by orders of magnitude. These techniques includes simple indexing techniques, T-SQL techniques, and general database application design patterns that give great gains in performance. In this session, you will learn how to look at a database to identify these problem areas and how to resolve common issues that you will encounter.

What else?

Let’s say you’re interested in something else. What should you go see? Well, Michael Swart (blog | twitter) put together a nice little blog post about How I plan to spend my weekend.

If you like business intelligence, I suggest you hit up Dave Rodabaugh’s presentations. I cannot speak highly enough of Dave’s work. Not only is he one of the brightest BI people I know, he’s also been a teacher, friend, and mentor to me for a long time.

There will be some kind of dinner/drinks/whatever going on afterwards at Barley’s Smokehouse (map). I plan on being there for a little bit. Even if you can’t make it to the event, head on over there around 6:00PM. I’ll be there.

Comments (0)

Shrink, Damn’d Log! Shrink, I Say!

Jun 17, ’10 8:00 AM

Ever have a database log file grow to epic proportions on the weekend? I have. Ever forget to set up job monitoring on that server? I have. Ever get so pissed off that you decided to write a job to automatically shrink the log files whenever they grow? I have.

Continue Reading… Comments (8)

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)

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)

Submitting a Winning Abstract at PASS

Apr 27, ’10 7:09 PM

For those who couldn’t make today’s presentation, or those who could but want to hear Tim Ford and Buck Woody trashing Wisconsin, I’m pleased to share with you both the slides and the recording of today’s presentation. Or rather, two recordings.

The Powerpoint

The Presentation

Submitting a session to the PASS Summit really isn’t terrifying. What’s terrifying is being accepted and being faced with the prospect of speaking… I kid, I kid. There’s nothing terrifying about it. You put together an abstract, revise it a few times, and then submit it. When you submit an abstract you’ll want to put together a list of objectives – problems that you’re going to solve for the attendees.

Well, that’s about all there is to that. If you have questions, post them in the comments. I can’t tell you what PASS is looking for because, well, I don’t know. It’s based on a number of different things and depends, in part, on your abstract itself. So, write some abstracts, give them a once over, and submit them to PASS.

Want some more ideas about writing abstracts? Look no further than Brent Ozar’s blog post How to Get Readers to Pay Attention.

Want to know more about public speaking? I would suggest Confessions of a Public Speaker.

Comments (6)

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)

Double MCITP

Mar 18, ’10 1:00 PM

Yesterday I took the afternoon off of work with the intention of spending the entire afternoon taking two Microsoft certification tests. I had no intention of passing the tests, but I went ahead and scheduled to take 70-432 and 70-450 back to back

I was pretty nervous about the exams. After 18 months on the job as a DBA you would think I could have passed them in my sleep. Of course, if I didn’t pass it, was I really that good at my job? Sure, servers weren’t going to suddenly start exploding if I failed one or both exams, but it would have been a huge blow to my confidence.

Thankfully, I passed.

This was a pretty big milestone as I’ve been a developer for 90% of my professional career. It’s only recently that I’ve switched around and gone with the support route. Even then I don’t consider myself a DBA. I solve problems. Right now SQL Server is how I accomplish that. This certification is just some icing on top of the last 18 months of experience. It might not prove that I know my shit, but it proves that I know a minimally acceptable amount of somebody’s shit.

Comments (7)

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.