SQLChicken.com

SQL Server DBA Tips & Tricks

By

Tampa SQL BI User Group – Designing Slowly Changing Dimensions

So Monday night was the monthly SQL BI user group meeting. Attending these meetings is always interesting for me since I’m not a BI-guy (leave out the gutter jokes please…) so I definitely learn something new. I also leave with a raging headache since I have trouble wrapping my head around some of this crazy stuff. I tip my hat off to you guys that specialize on the BI side of SQL Server. I’ve come to realize that its kind of like Texas Hold ‘Em poker vs Omaha. When it comes to SQL we’re all playing the same game with very different rules. Anyhow, I digress, back to the recap.

We started off with a general “what’s going on” discussion in the world of tech/SQL/etc. We talked about some of the big stuff that happened that day such as Microsoft’s numerous announcements during their E3 presentation, the public launch of Microsoft’s new search engine: Bing, the current state of job market (looking better, contact Stever Turner as he has quite a few SQL jobs available), Microsoft’s Second Shot offer (expires at the end of this month), next month’s SQL User Group meeting (I’ll be presenting on Policy Based Management), and the announcements regarding SQL Server 2008 R2 and “Gemini”, which turned out to be a very hot topic for the night! For the record our group is looking for more info and demos on Gemini so if you’re available for a talk and want to visit sunny Tampa drop Rob Hatton a line!

Speaking of Rob, he was our featured presenter this month on the topic of Designing Slowly Changing Dimensions. The discussion was based around the Kimball Methodology which if you’re not familiar with then you are highly encouraged to go grab a copy of The Data Warehouse Toolkit by Ralph Kimball. On the whole I found the presentation really good but after awhile some of the concepts start flying over my head.Some things I did walk with is that when you’re doing a data warehouse project some things you need to realize is that 80% of your effort will go towards the ETL, 5-10% to the cube and the remaining to the relational stuff. Another little tidbit I picked up is that apparently 3/4 of the source data that the BI pros in the room have dealt with to load into the staging portion of the DW are CSV/txt files which surprised me. Then again I’m a DBA so I believe everything should live in nice relational databases! I don’t mean to skimp on the details of the presentation mostly because I wouldn’t do it justice and I’d probably be wrong about the details. Rob’s slide deck should be available on the user group’s website soon and when it is I’ll link it here.

As an added bonus, and due to our lengthy discussion and excitement about SQL 2008 R2 and “Project Gemini”, Robert Skoglund from Microsoft, managed to show us a quick 6 minute video clip from Donald Farmer’s presentation on Gemini. Unfortunately it was without audio so we couldn’t get detailed explanations of the features. If you’re curious about Gemini check out this link from Donald Farmer’s blog that has everything you need to know.

Share

By

Tampa Bay SQL Server BI User Group Meeting Tonight!

Just an FYI tonight is the monthly Tampa Bay SQL Server BI User Group meeting. Tonight’s presentation will be by our fearless leader Rob Hatton on Creating Slowly Changing Dimensions.

The meeting will begin at 6:30 PM in the new Microsoft Office at:

5426 Bay Center Drive
Suite 700
Tampa, FL 33609

Map picture

Our sponsor this month is Steve Turner (Twitter) of T2 Software Services.

T2 Software Services offers a wide range of people services to its Clients through:

·Consulting Services – IT Supplemental Staffing (Staff Augmentation)

·Project Outsourcing – With full Project Management & Responsibility

·Placement Services – Direct Placement (Contingency Search)

As per my new modus operandi, I will give a follow up post on the meeting in the next couple of days. See you there!

Share

By

Review: Quest POTW webcast – Getting Started with SQL Server Management Studio

Very Niiice! Today was Quest Software’s bi-weekly Pain-of-the-Week webcast and this week’s topic was Getting Started with SQL Server Management Studio. We were lucky enough to have not one, but two SQL rock stars presenting today in Brent Ozar (Blog | Twitter) and Michelle Ufford (Blog | Twitter).

Brent kicked it off with a quick intro and then handed it over to Michelle to walk us through the basics such as creating a database, adding tables/columns/objects/etc as well as going through all the options available such as creating Maintenance Plans, creating backups from within SSMS, and the SQL Server Agent. They even demoed some of the nice little tools available in the latest SSMS such as activity monitor and the built-in reports. A question was raised about if the new SSMS offered a view equivalent to the taskpad view in SQL 2000 Enterprise Manager and the answer is…kind of. If you’re using SSMS 2008 (which you should be since you can install it without having to have a SQL 2008 instance) that view has been replaced by the built-in reports. You can access the reports by right-clicking your Instance or even a database itself, go to Reports, go to Standard Reports and select which report you’d like to view. And like we learned yesterday, some of these reports come from our default trace! Another fun tidbit of information learned from this presentation was that if you choose to (granted this isn’t recommended) right-click a table and select the Edit Top 200 records, it opens the records in an Access-like grid and allows you to change data directly. But this is cool and useful, why isn’t it recommended? Well when you open records like this it puts a lock on those records so nobody else can get to them. So in a production environment clearly this is a no-no but at least you know the feature’s there. For the record the recommended method would be to do something like this:


 

After Michelle wrapped up our tour of SSMS, Brent then gave us a quick tour of Quest Software’s Toad for SQL Server product. This is a pretty slick product that is aimed towards the development community. It gives you basically the same things as SSMS for the most part but it also gives you so much more. For instance you are able to do a query and re-sort your results using column sorting as you would in Excel (i.e. click column name to change order). You can also do filtering via this method as well. What’s the big deal about this? Well every time you change your ordering you don’t have to make a roundtrip to the server to re-query your data, it holds everything locally so its speedy. This is where Borat pops up and says “very niiiiiiiiice”. There was also some slick thing it did with pivots but that was the point where my desktop froze so all I got was audio from the phone call, though the people commenting on Twitter seemed to like…whatever it is it did. But don’t take my word for it, try it out yourself and go download a 30-day demo of the product at Quest’s website!

 

That pretty much summed up the webcast this week! They’ll be doing a follow-up webcast on June 11th with more advanced tips as well. you can register for that webcast here. If this post didn’t quite do it for you, you can catch all POTW webcasts on-demand over at Quest’s website. Today’s presentation should be up in a few days.

Share

By

PASS DBA SIG: Understanding the Default Trace

Today during lunch was the monthly PASS webcast for the Database Administration SIG. This month’s topic was understanding the default trace and was presented by Jonathan Kehayias (BLOG Twitter). Jonathan did a great job as he very clearly explained the nuances of the default trace (i.e. what it really is, how you query it, what’s it do, etc.).

The presentation files are available at his blog. If you get a chance definitely check out the on-demand replay which should be available next week. On a side note this is my second or third one of these I’ve attended and I absolutely love it! I think PASS is doing a great job providing this sort of on-going training for the community and I also tip my hat to people like Jonathan who are willing to take time out of their day to present.

Share

By

SSWUG Virtual Conference: Follow-up Thoughts


Ok a few weeks ago was SSWUG‘s second virtual conference and my first experience with the virtual conference experience. Granted this review is a bit late but better than never I suppose. First off I have to tip my hat off to Stephen Wynkoop, Chris Shaw and the rest of the folks at SSWUG that put this event together.

First off this conference was a fantastic value at only $99 for 3 days of high quality SQL training. For those who haven’t experienced a virtual conference before, it is layed out much like a real conference you would attend. You enter the conference and are placed in a lobby-type page that leads to either sessions lobby, exhibit hall or on demand section (sessions are available on demand after the conference). Entering the sessions lobby lets you select which conference you registered for. This conference offered a track for SQL Server, Business Intelligence, Sharepoint Once inside you are presented with several rooms to choose from. Like a real conference you choose which session you want to attend in which room.

The sessions are pre-recorded videos and while you’re watching the session there is also a chat room dedicated to that room. During the session the speaker is in the chat room answering any questions anyone has regarding the content. I thought this was great as normally you wouldn’t have this sort of interaction with the presenter. The other great thing is that sometimes the conversation in the chat would spin off on another great topic/point. Granted the downside to this format is that your attention is drawn away from the video but that’s the beauty of them being on demand!
The other great communication tool that was implemented in to this was Twitter. SSWUG placed a Twitter feed in the main lobby that allowed those not on Twitter to see what was going on with those Tweeting about the conference. This was done by encouraging participants to tag their tweets using the #sswugvc hashtag. I’m now a big Twitter user so this was a major plus for me. Another place to interact was, of all places, the vendor booths! During this conference it seems like the Quest Software booth was the place to be as most of the people I interact with on Twitter made their way over there. In addition to hocking Quest products in the booth I actually got to know people a little better like Tim and Lori Edwards, Brent Ozar, Chuck Boyce Jr. and Tom LaRock to name a few.
At noon everyday there was a part of the conference that had to be, hands-down, one of my favorite parts and that was the live Q & A sessions with Stephen Wynkoop and Chris Shaw. They would have a general topic of discussion and take questions live over the chat room and Twitter. It’s great seeing two major players in the SQL Community discussing their thoughts on things. Plus with the live aspect of things we could ask questions and the conversation could go in various directions. Another fun thing that came out of the live talks was the keynote bingo that I came up with on the last day. I think everyone that participated had fun with that and funnily enough Stephen and Chris got in to it as well as they purposely started mentioning things from the card towards the end of the final live session for kicks.
All in all I thought this was an absolute blast! Granted I was watching the sessions in my office and got sidetracked every so often with work but that’s also the beauty of it. Your organization still has you around so you’re still work-functional (sorry) but you’re also getting your training out of it. If you miss something you can either catch the afternoon replay session or just take advantage of the on-demand option or order the DVD from them which includes all demos, scripts, videos, etc. The other thing that this conference made me realize was the true power of social networking specifically Twitter. Out of this conference I got a slew of followers and managed to cement relationships with other SQL professionals. Plus out of Twitter came the infamous SQL Editions onlsaught of Tweets which I believe actually originated from the people hanging out in the Quest booth!
A huge thanks goes out to SSWUG and all the vendors who sponsored, an event like that couldn’t be put on without your support. Events likes this are also key when the economy is in the state it is in. For me, sadly, I don’t think I’ll be able to attend PASS this year due to travel restrictions with our organization right now but hopefully I’ll be able to attend next year. Until then virtual conferences like this will be key to my on-going training as well as attending regional and local events such as SQL Saturday and local user group meetings.
Share

By

Tampa SSUG 5/19/09 Review

Tuesday night we had our monthly SSUG. Our special guest this month was Jack Corbett (aka unclebiguns) joining us from Orlando. Jack is a software developer for New Tribes Mission in Orlando. For his take on the evening check out his blog entry on the night.

We kicked off the night with a discussion I led about social networking. As most discussions tend to lead, the topic dominator was Twitter. A few of us shared how Twitter has managed to help us in various facets of our job be it job opportunities, problem resolution or just general networking. A few of us in the audience are already on Twitter such as Jonathan Kehayias, Jack Corbett, Pam Shaw, and Steve Turner. Besides Twitter we also covered sites such as LinkedIn. LinkedIn was specifically mentioned as helping people find jobs. One member in attendance shared with us that he actually landed his current position because of Linked in which shows that social networking does work! One new thing I did learn out of this conversation was something called MSDN Social which I’ll have to check out. Thanks to Travis Page for pointing this one out to us.

Next up was Jack’s talk on SQL Server Profiler basics. I thought I knew enough about profiler but boy was I wrong! What’s funny is that Jack asked the room how many of us currently use profiler as part of our DBA arsenal. Only about a quarter of those in attendance raised their hands. He followed up by asking how many of us knew that SQL Server 2005/2008 had a trace running by default on install and even less hands went up. This is pretty surprising considering its a feature specifically built in to make it easier for us DBA’s to do performance analysis. Considering Extended Events is the next “big thing” in this arena coming out of the Redmond camp for SQL Server I think either we need to blog more about these sorts of things or Microsoft better come up with a Mojave Trace profiler and say “surprise, its really SQL 2005!”. Anyhow back on track…another thing I learned was that the profiler takes wildcards. Yes, that’s right, you trace on ‘ADv%’ or ‘%acon’. I thought this was pretty cool.

Another thing that’s new in 2005 Profiler (thankfully, since this particular issue drives me nuts) is the ability to pause a trace mid-stream, modify your trace values, and continue the trace without losing any of your previous data already collected. Speaking of pausing, a button that I just never noticed all this time was right up top (Auto Scroll) that stops the profiler from skipping to the latest data. I don’t know how many times I’ve been staring at the data scrolling by, see what I think is a problem query, click on it and by the time I do the screen rolls over two or three times so I have to go back and hunt for that line. In the words of Charles Barkley, “just turrr-ible”.

During the presentation portion where we were discussing dissecting deadlock issues, someone mentioned that one time they had a deadlock issue but it wasn’t showing up properly when they traced it using the 2005 profiler. Jonathan Kehayias enlightened us that mutli-deadlock victims won’t show in 2005 profiler and that this situation is common in parallelism issues (which this guy had). The 2008 profiler, on the other hand, does handle that issue and display it properly. The next tip I picked up and can’t wait to use was the fact that with 2005 profiler you do correlation of data between profiler and performance monitor. In order to do this you need to have started and ended both with scheduled times. Another note is the Counter Log from perfmon needs to be in Binary File type in order for this to work. Once you have your traces done go to File menu, and save your profiler trace to a file. Then open that trace file. Once its open you should be able to go to File menu and select Import Data. Navigate to where you saved your perfmon trace and open it. Once you have selected it you will see a graph overlayed underneath your trace. Now if you click anywhere on that graph you will get a line that shows exactly which point in your profiler trace that performance spike happened so you can see exactly which SQL is causing (if any) problems! Click on the image above for an example of this.
Overall I thought this was a great meeting. We had a packed house and good discussion and tips. Personally I feel I even got a little more out of it than usual thanks to social networking. Prior to Jack’s arrival for our meeting he and I have been interacting on Twitter so it made it a little easier to meet him for the first time which was pretty cool. Next month I’m slated to do a presentation on Policy Based Management so I’m pretty excited for that. Another exciting announcement is that apparently we’re getting Kevin Kline to come speak at our group which should be really awesome.

Share

By

Tampa Bay SQL User Group Meeting tomorrow night!

Tampa Bay SQL User Group

Meeting starts at 6:30 pm. Directions to meeting

Date: 5/19/2009
Topic: Getting Started with SQL Server Profiler
Speakers: Jack Corbett
Location: Franklin Templeton Buidling
Description:
Jack Corbett will present Getting Started with SQL Server Profiler. We will earn how to use SQL Server Profiler to troubleshoot, audit, and tune your SQL Server. This will include creating custom templates, integrating with Performance Monitor data, and creating scripts for creating server-side traces.

Jack has been working with SQL Server since 1999 and has worked with versions 6.5 – 2005. He is experienced in VB 6 and .NET (ASP.NET, VB.NET, C#). He is currently working as a Software Developer for New Tribes Mission (www.ntm.org).

He has spoken at OPASS and SQLSaturday #8 – Orlando, written 4 articles for SQLServerCentral and is one of the more active forum participants. He is also the author of a video series on Profiler available on JumpstartTV. Check out his blog at: http://www.wiseman-wiseguy.blogspot.com.

Share

By

Got Corruption?

So today I’ve been dealing with one hellacious case of database corruption and thanks to the beauty of social networking I was able to get help from SQL Jedi-master Paul Randal. I’ll post my entire in an upcoming post but just wanted to share a new trick I learned. Paul posted an article tonight (thanks to my situation) about how to get back all the errors generated by your corrupted database via DBCC CHECKDB if you have more than 1,000 errors. This method involves using the osql utility. Go check out his article and stay tuned for the thrilling saga of what NOT to do with a database.

Share

By

PowerShell: Giving in to the Inevitable?


Here’s a quick question posed to my technical brethren: Have you drank the PowerShell Kool-Aid yet?

I’ve sat back and tested the waters here and there and I see that you can do some pretty cool stuff with it but I’m an admitted GUI lover at the moment so what does this buy me? This topic comes on the heels of the Microsoft Tech-Ed keynote and the fact that PowerShell scripts will now be generated on-the-fly ala SQL Server Management Studio did for T-SQL (Thanks Brent Ozar for pointing this out). I’ll admit I have learned quite a lot just in T-SQL by using that Script To function but will having this power in PowerShell really do much for me? What are your thoughts? Comment your techy hearts out.

Share

By

SQL Rap by DJ Majik Poultry

Yo…check it. I’m sittin’ here chillin with my Tweetdeck rollin’
When I get word of a SQL Rap contest, the illest rhymes we be extollin’
What’s up for grabs is an iPod Touch
Whatcha gotta do? Well, it ain’t much
Cook up the illest rhymes and sell em like a 3rd party tool
And this first rhyme I’m spittin is aimed at SQLFool

Go-daddy, Go-momma don’t matter the sexI suck at this
This contest brings the baddest DBA’s like an ill-formed Index
What you and Ward started, like Ma$e can’t be stopped
These other sucka emcees are just another table my T-SQL’s gonna drop

Now Ward, aka the SQLTwit
Ready yourself for these rhymes I’m about to spit

You live in the woods, probably pickin berries
Since you’re backwoods I don’t wanna make this too complex like queries
Your lyrical rap styles are tuned like fine code
I heard you can ask for the BrentO discount when purchasing Quest Toad

Speaking of Brent O, his raps are well taken
Damn rapping about him now makes me crave #bacon
He looks like the SQL world’s Tina Fey
Next time you drop by Twitter, drop him a line and say ‘HEYYYY’

My VM snapshots are wrapping up from their orphaned state madness
Sorry to leave you guys with much lyrical sadness
These rhymes are whack but my skills are finger lickin’
So peace to your mothers, a love note from the SQL Chicken!

Share