Day-Long DMV Virtual Conference on March 3rd

Posted 2/26/2010 at 10:23 AM by Brent Ozar

We’re doing a free all-day live virtual conference on how to use dynamic management views (DMVs) to do SQL Server performance tuning and troubleshooting. The whole thing will be broadcast live in 720p, and we’ll be taking questions via chat and Twitter. We’re going to have sessions at the beginner, intermediate, and advanced levels taught by:

  • Kevin Kline (BlogTwitter) – Microsoft MVP since 2004, author of SQL in a Nutshell, founding board member of the Professional Association for SQL Server, and all around good guy.
  • Ari Weil (BlogTwitter) – Product Manager for Quest’s performance products, and knows way more about waits, performance tuning, and SQL Server architecture than anybody should.
  • Brent Ozar (BlogTwitter) – your humble author. Okay, well, author anyway.

Register now and just for registering, you’ll be entered into a contest for free goodies!

Performance Analysis Trojan Issue Resolved

Posted 2/18/2010 at 2:40 PM by Ari Weil

Some time ago a component executable in the Performance Analysis client was temporarily flagged as a trojan by some leading anti-virus companies.  A virus definition file update two days after the initial identification resolved this issue.  In order to be 100% certain there were no potential threats, we fully recompiled the client and worked to ensure that subsequent updates to the virus definition files would not recreate the problem.  So, we are happy to report that the issue is resolved, and the updated Performance Analysis clients are available on quest.com for Oracle and for SQL Server.

See how to setup Log Shipping using LiteSpeed for SQL Server

Posted 2/17/2010 at 4:42 PM by Andy Grant

Looking to use LiteSpeed for SQL Server for Log Shipping?  Check out this video, created by Nina Philippova our lead technical writer for LiteSpeed, for a step-by-step overview of configuring a Log Ship plan through the LiteSpeed UI.  Please provide any feedback that you may have, we’d love to hear it.  Thanks!

Get the Flash Player to see the wordTube Media Player.

Looking to monitor Analysis Services? Have you tried Spotlight?

Posted 2/12/2010 at 10:47 AM by Ari Weil

Quest introduced monitoring for SQL Server Analysis Services (SSAS) with Spotlight on SQL Server Enterprise 6.0 last November.  Since then we’ve gotten great feedback from new and existing customers on how Spotight gives them the confidence they need to keep tabs on every aspect of their SQL Server environment. 

We recently closed an important case with Microsoft support that resolves an issue where the WMI classes required to monitor default SSAS instances were missing on the host server.  What this means to you is if you want to monitor the SQL Server relational engine, Analysis Services, or just the Windows server hosting the solution,  you really should check out Spotlight on SQL Server Enterprise.  We’ve got you covered from Windows and SQL Server 2000 to the latest releases of 2008.

Why Aren’t You Using Data Compression?

Posted 2/11/2010 at 2:47 PM by Brent Ozar

Microsoft SQL Server 2008 introduced data compression, a new feature that will compress your objects.  The theory is that it will cut space requirements inside the database, and a smaller database is easier to read off the hard drives.  Your IO time should decrease, and your CPU time shouldn’t be affected that much.

But I never hear about people using it.

So I’m asking you in a short survey: why aren’t you using it?

Free SQL Server Training Videos

Posted 2/11/2010 at 10:59 AM by Brent Ozar

At Quest, we have a Pain of the Week webcast series that’s gotten a lot of great community feedback lately.  We spend the first 30-45 minutes talking about a SQL Server problem and how to solve it just with the native tools like SQL Server Management Studio and T-SQL scripts.  In the last 10-20 minutes, we talk about how a Quest product can solve it even easier.

You might not know, though, that we’ve been doing these webcasts for years, and you can watch any of our archives absolutely free of charge.  Here’s just a sampling of our free training sessions:

For more webcasts, check out the SQL Server webcast archive at Quest.

Database Selection in a LiteSpeed Maintenance Plan

Posted 2/9/2010 at 12:48 PM by Jason Hall

Continuing a string of posts on tips and tricks for LiteSpeed, I thought I would take an opportunity to discuss how LiteSpeed allows you to select databases to backup in a maintenance plan.  Whether or not to use maintenance plans to backup your SQL Servers is very much a matter of personal preference.  LiteSpeed has added some great functionality to make maintenance plans more configurable than what you get with SQL Server.

Consider the screen that allows you to select which databases to backup.  In SSMS 2008 (and 2005/2000) you can select to backup either all databases, all system DB’s, all user DB’s, or you can select a subset of your DB’s to backup with an associated plan.

Native Database Selector

Native Database Selector

There are two main challenges with manually selecting a subset of your databases.

  1. Once you select a subset of your databases, any new database added to the system will not be automatically picked up by the maintenance plan.
  2. The order in which the databases are backed up is not configurable.  The critically of your databases is not determined alphabetically, and if you have a maintenance plan run long, wouldn’t you want your most important databases backed up first?

LiteSpeed Maintenance Plans have addressed these two concerns.  In the database selector for a LiteSpeed maintenance plan, you have two options that you don’t get with native plans.

  1. LiteSpeed lets you select databases for exclusion (not inclusion as in a native plan).  Essentially, you are telling LiteSpeed to backup all databases except the chosen databases.  This means that any newly created databases will be picked up by your plan, yet you can define databases that should never be backed up.
  2. You can order the databases that you select.  By moving databases up and down you can determine which databases get backed up first.  I highly recommend that you let your maintenance plan tackle the most critical databases first so that should anything occur during your maintenance window, your most critical databases have a higher probability of completing successfully.
LiteSpeed Database Selector

LiteSpeed Database Selector

In the example above, master and msdb are backed up first, followed by the Quest repositories, followed by the remainder of my databases.

As you can see, not only does LiteSpeed compress and/or encrypt your database backups, but also has functionality built in to assist with the management of your backups as well.  Plenty more tips and tricks to come.  If you have any questions about this or anything else, feel free to comment or visit the forums.

Native Restore From Litespeed Backup

Posted 2/8/2010 at 12:11 PM by Thomas LaRock

More than once I have had the need to recover the native backup file from within the compressed Litespeed backup file. Fortunately Quest provides an extractor utility in order to help with the process. Otherwise, you would be forced to do a restore followed by a native backup. If your database is of a considerable size then the time needed to accomplish that task could be prohibitive; it will always be simpler to do the extraction as opposed to a restore and a backup.

Extraction is very straightforward. First step is to open a command window and navigate to the directory where the utility is located, this is typically the C:\Program Files\Quest Software\LiteSpeed\SQL Server directory.

Next, issue the following command to extract the native backup files.  Yes, files. In fact you will have one file created for each thread that was used when Litespeed was taking the initial database backup. The command is roughly as follows:

extractor.exe -F [path to backup file taken by litepseed] -E [name of file you want to be created]

So, if you had a backup of AdventureWorks saved to C:\AdWks.bak, and you wanted to extract the native dump from that file and create new files in the same directory, you would run the following command:

extractor.exe -F c:\AdWks.bak -E c:\AdWks_ext.bak

This command results in the creation of multiple files. You can configure a path and filename here if you want to have the files stored somewhere other than the current directory.

OK, so what do you do with these files? Well, you restore them, of course, using either T-SQL or SSMS. One common reason for needing to have a native backup is for those rare times when you need to provide a vendor with a copy and that same vendor does not have a copy of Litespeed. And while the extraction utility makes it easy to provide the necessary files, you have no idea who is on the receiving end of the multiple files. I have lost count of the number of vendors I have sent the extracted files to and received an email back that either says “we don’t know what to do with these files” or “can you send us just one file instead?”

Sure, I can do that as well. Or, I could take a minute to teach you how to do it for yourself. Here goes:

Open up SSMS and all you need to do is point to the files. Just add in each file as shown below, and click OK. Then, select your restore point and click OK again. You should be fine, the database restore should be underway at this point. 

Now, if you prefer T-SQL, the equivalent syntax would be:

RESTORE DATABASE [AdventureWorks]
FROM  DISK = N’C:\AdWks_ext.bak2′,
DISK = N’C:\AdWks_ext.bak1′,
DISK = N’C:\AdWks_ext.bak0′
GO

It’s that simple.

Error Handling with the LiteSpeed XP’s

Posted 2/8/2010 at 9:53 AM by Jason Hall

One question that comes up quite a bit pertains to how you can handle errors generated by the LiteSpeed Extended Stored Procedures (XP’s) in your own custom scripts.  Scripting with XP’s is fairly straight forward, because they accept parameters just like any other stored proc, but how they handle errors is a bit different.  Unlike  standard SQL Statements that will populate @@error and can be handled with TRY/CATCH blocks, XP’s simply return an error code.  By capturing the value returned by an XP you can succesfully trap and code around many types of errors.  View the following block of code to show how this is done.  In this example, the drive I am trying to backup to does not exist, therefore the backup fails with error code 50003.

DECLARE @rc INT
EXEC @rc = master.dbo.xp_backup_database
  @DATABASE='master'
  ,@filename ='V:\backup\database.BKP';
SELECT @rc

I know some folks out there have done some pretty cool things scripting with LiteSpeed.  If anyone has any scripts they’d like to share, we’d love to take a look!!

Getting Speakers for User Group Meetings

Posted 2/5/2010 at 8:02 AM by Brent Ozar

I love presenting at user group meetings.  I can’t get enough of it.  If I could do it every day, I would.  I’ve got dozens of presentations I can use, and I’m continuously refining them based on attendee feedback.  I love helping people get more out of their servers and advance their careers.

There’s a few things that limit how often I can present, though.

Travel requires a lot of downtime. When I have to fly to another city, that means at least 4 hours of downtime to get there, and 4 hours to get back.  (Pack, subway, airport security, boarding, flight, taxi, etc.)  That’s a day of downtime, plus the time I spend at the user group, and during those travel windows, I can’t usually be as productive as I would be back in my home office.  If I have to take multiple flights because I can’t get there in one cheap flight from Chicago, my downtime skyrockets.

Travel is expensive. User group meetings are usually in the evenings, so I can’t fly straight back home afterwards.  That means I have to get dinner, a hotel, and breakfast the next morning.  Presenting at a user group costs around $300-$400, and that’s before I get other Quest people involved.  See, if I’m going to take the time and money to present, then Quest probably wants to have a salesperson in the room to handle questions afterwards, and the salesperson costs money too.  Hopefully we’ll make a sale in order to pay for all that expense, but it’s a gamble, and that gamble has to be chosen by the sales staff.  They decide where they want to invest their travel budgets.

Multi-city travel is risky and it sucks. I can mitigate the downtime & expense of travel by stringing together several cities in a row, but that is daaaangerous.  Read through my post on The Glamour of International Travel to see just how wrong it can go.  In one trip, I lost a bag, broke a laptop, and ran out of clean clothes.  All it takes is one delay or goof, and suddenly the whole trip is in jeopardy.  I love you guys, but I abhor going on these multi-hop trips.

Doghouses are uncomfortable. When I was young and single, working for a hotel company, I lived on the road full time.  Those days are over.  I’ve committed to Erika that I won’t average more than one week per month of travel.  For reasons I cannot comprehend, she seems to enjoy my company.  She can’t take off and travel along with me because she’s got a job.  When she takes vacations, we want to go somewhere that I don’t have to work at all.

I could avoid the travel hassles altogether if I could present remotely, but…

User groups don’t like remote presenters. The presentation experience is better when there’s an actual person in the room rather than just a talking head on a projector.  I totally understand where they’re coming from.  User group leaders have been spoiled for the last decade or so when vendors were throwing tons of money at travel, but as companies slowed down their software spending, that makes it harder to justify travel expenses.

User groups don’t have a lot of attendees. Some user groups can bring in 100 people, and that’s awesome, but most of the time it’s closer to 10-30 people.  Regional events like SQLSaturdays and SQLBits can bring in hundreds of people at once.  The more attendees I can reach, the easier it is to justify the travel money.

Help Me Help You

If you want to get out-of-town speakers at your local user group, there’s a few steps you can take to increase your odds of success.

Work with other area user groups. Coordinate schedules with .NET and SharePoint user groups within driving distance.  If you can work together to set up several meetings in a row, one night after another, all within driving distance, there’s a much higher likelihood that we can make it happen.  That opens up the possibility of me and a salesperson doing a one-week road trip and covering several groups in a row.

Schedule a regional event with vendor sessions. Set up a SQLSaturday and get the vendor to sponsor it.  Let the vendor have a lunch session – a meeting room where they can bring in a speaker to talk for an hour about their product.  Some vendors employ people like me and Kevin Kline who can talk about not just SQL Server, but about their products too.

Document your attendee history. Take pictures of your monthly meetings and show butts in seats.  Salespeople get excited when they see pictures of lively attendees.

Work with the vendor’s available speakers. When Kevin and I can’t make it to a particular user group meeting, Quest usually suggests other Quest employees.  We’re not the only ones who work with SQL Server – in a company of 3,500, we have some really SQL-savvy people who do great presentations.  User groups often turn up their nose at these guys because they’re not “famous” – but they’re not famous because they have real jobs.  If you compromise and let some of the other folks speak, then those guys may bring a good report back to the mother ship.  I’ve heard Quest people say, “The ___ user group wants a speaker.  I spoke there a few months ago and they had really good turnout – we should send Kevin or Brent this time.”  It really does work.

Work with the vendor’s salespeople. Find out who your regional salesperson is, and find out what customers they’re working with.  Salespeople want to meet more customers, and if you run a local user group, you have something the salespeople want.  If I was a user group leader, I’d talk to the salesperson and say, “So, at the last user group meeting, we talked about how to do performance monitoring, and I noticed there were a couple of DBAs from a major financial firm that were asking a lot of questions.  I don’t feel comfortable telling you exactly who they were, but maybe you should do a presentation here about monitoring.  Is there anything I can do to help make that happen?”

I wish getting in-person speakers was easier, but in today’s economy, you’ve gotta give a little to get a little.  I want to cover as many user groups and SQLSaturday events as I can in 2010, but to do it, I’ve gotta minimize my travel and maximize the return for Quest.  Being educated about that will improve your odds of getting an in-person speaker.