Tag Archive: performancetuning

My Weekly Bookmarks for September 14th

Written on September 14, 2009 by in SQL Server

Good news and bad news – the good news is that everybody’s been going wild and crazy for the SQLServerPedia PASS contest.  The bad news is that editing the articles & setting up the bloggers has eaten up every moment of my spare time, hahaha.  As a result, I had to do the unthinkable this weekend: scan through Google Reader and then hit mark-all-as-read.  Here were the survivors, and I’m sure I missed some good stuff:

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

1 Comment - Leave a comment!

My Weekly Bookmarks for September 4th

Written on September 5, 2009 by in SQL Server

Here’s my bookmarked links for September 1st through September 4th:

Tech Links

The Junk Drawer

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

3 Comments - Leave a comment!

My Weekly Bookmarks for August 28th

Written on August 28, 2009 by in SQL Server

Here’s my bookmarked links for August 23rd through August 28th.  I’m using an automatic plugin to build this list, and I can see that this probably isn’t going to work – I just found way too many things interesting in one week, and it doesn’t break stuff out into categories.  Blogger fail.  Here it is anyway as an example of What Not To Do during my Better Blog Week:

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

29 Comments - Leave a comment!

The Ozar Family Tradition of Performance Monitoring

Written on July 23, 2009 by in SQL Server

When my Grandpa Ozar wanted to find out if his 10 megabyte 5.25″ hard drive was responding fast enough to handle his database loads, he monitored the Perfmon counter Physical Disk: Disk Queue Length. This metric told him how many requests that the 486sx/33 processor had sent off to the hard drive that hadn’t come back yet. Average disk queue lengths greater than 2 meant that the hard drive wasn’t quite keeping up.

How My Grandpa Ozar Met Grandma Ozar (No, Not Really)

How My Grandpa Ozar Met Grandma (No, Not Really)

To fix performance problems, Grandpa upgraded to RAID arrays with multiple hard drives. When his end users complained that his array of 9 gigabyte SCSI drives weren’t returning their queries quickly, he still monitored that same Disk Queue Length metric. He interpreted the results differently, though, because he had multiple drives in each array. He multiplied the old 2-queue-length guideline times the number of drives he had in the array. A RAID 5 array with 8 drives might be underperforming if the queue lengths averaged 16 or more. At night, Grandpa followed Usenet debates about whether to include the parity drive, and how to handle differences between read and write queues on RAID 10.

Dad Started Using a Stopwatch

By the time Dad started getting involved in system administration, Storage Area Networks (SANs) had made things complicated. He couldn’t count the number of drives in a particular array because that number kept changing, and those drives were shared between multiple servers. In a SAN, a twenty-drive RAID 10 array might be shared between a couple of database servers and a file server with different load patterns, different peak load hours, and different capacities.

Instead of measuring queue lengths, Dad watched response times. He used Avg Disk Seconds per Read to find out how long the SAN took to return data back to the Pentiums, and he referred to Microsoft’s published guidelines for OLTP and OLAP expected response times for reads and writes. Just finding out if drives were “fast enough” meant finding out what kinds of databases were involved and what they were doing, which made things confusing for end users.

The New New Way: Wait Stats

While I’d like to think my family line has been carefully bred for systems administration abilities, the reality is that I’m the product of decades of hospitality industry work in close proximity to large quantities of alcohol. Mercifully, systems administration has changed over time to be less math-oriented and more logic-oriented.

To find out what to tune, ask a simple question: what’s the server waiting on?

I love me some Perfmon metrics, but more and more database administrators are turning to a different way of performance tuning: wait statistics. SQL Server tracks what it spends time waiting for, and analyzing those statistics are the most efficient way to find the bottleneck. Who cares if the storage takes 10 or 100 milliseconds to return data from a particular query – the more important question is, was SQL Server waiting during that time, or was it working on something else anyway?

This is especially important in the age of virtualization and consolidation. Corporate management is less concerned about achieving the best possible performance, and more concerned about Good Enough. We have to be very careful to spend money only on the components that will truly make an order-of-magnitude performance difference, and not throw away money on things that will only make an incremental improvement.

Wanna learn how to measure performance with wait stats? Here’s my recommended reading:

After wait stats, what’s the future of performance management? I have no idea – but I’m the end of the Ozar family line anyway.

(Note – I’m on vacation, so I probably won’t be responding to comments for a few days. I scheduled this post ahead of time. I’m on a sailboat in Lake Michigan, and I’ll respond if I get within wireless range. And yes, I’m bringing my laptop.)

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

9 Comments - Leave a comment!

SQL Server Index Tuning Tip: Identify Overlaps

Written on July 6, 2009 by in SQL Server, SQLServerPedia Syndication
Performance Tuning 101 - Add More Spoilers

Performance Tuning 101 - Add More Spoilers

If you’ve got performance troubles with an application that stores data in SQL Server, and especially if it’s a home-grown application (not a store-bought app), you can get dramatic performance improvements simply by focusing on some basic indexing techniques.  These tips and tricks pay off more than pouring money into hardware that might look good sitting in the datacenter, but doesn’t really make the application significantly faster.

When I go into a shop to speed up an application I’ve never seen before, two of my favorite quick-hits are from the index performance tuning queries from SQLServerPedia:

  • Find unused indexes – these are indexes the SQL Server engine says it’s not using.  Unused indexes incur a speed penalty because SQL Server still has to add/update the indexes as records change, so they make writes slower.
  • Find missing indexes – these are indexes SQL Server wishes it had available.

I’m not going to cover those in detail this week because I’ve already recorded tutorial videos over at SQLServerPedia for those, but I do want to focus on something these queries won’t pick up.  Sometimes a table has two nearly-identical indexes, and they’re both being used for reads.  Take these two:

CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType] ON [dbo].[MyTable]
([RunID] ASC,
[SiteID] ASC,
[DataSource] ASC,
[OutputType] ASC,
[PeriodType] ASC
)
 
CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType_QuotaItemDriverID] ON [dbo].[MyTable]
([RunID] ASC,
[SiteID] ASC,
[DataSource] ASC,
[OutputType] ASC,
[PeriodType] ASC,
[QuotaItemDriverID] ASC,
)
Performance Tuning 201 - Even Wood 2x4s Can Be Spoilers

Performance Tuning 201 - Even Wood 2x4s Can Be Spoilers

They’re two different indexes, and they’re both getting used – but does that mean we need them both?

They’re very nearly identical – but the second index has one extra field.  When the SQL Server engine gets a query that needs RunID, SiteID, DataSource, OutputType, and PeriodType – but not QuotaItemDriverID – then it will use the first index.  When it gets a query that needs all six fields, then it’ll use the second index.

In cases like this, I prefer to drop that first index and let the slightly bigger index pick up the slack.  Reading a slightly larger index will take slightly more time: if a query didn’t need that QuotaItemDriverID field, it still has to pull it off the disk in order to perform the query.  However, dropping the index pays off during inserts/updates/deletes, because it’s one less index SQL Server has to manage.  It also makes the database smaller, thereby making database maintenance tasks smaller/faster.

If:

  • I have two indexes with the exact same fields in the same order, but
  • One has 1-2 extra fields, and
  • There aren’t include fields, or the include fields are the same

Then I’ll drop the shorter index with extreme prejudice.

When Indexes Have Include Fields

If they have “include” fields, then I’ll merge the include fields between the two indexes to make one index to serve both needs.  Say we have these two indexes:

CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType_Includes] ON [dbo].[MyTable]
([RunID] ASC,
[SiteID] ASC,
[DataSource] ASC,
[OutputType] ASC,
[PeriodType] ASC
) INCLUDE ( [YTDRevenue], [MTDRevenue] )
 
CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType_QuotaItemDriverID_Includes] ON [dbo].[MyTable]([RunID] ASC,
[SiteID] ASC,
[DataSource] ASC,
[OutputType] ASC,
[PeriodType] ASC,
[QuotaItemDriverID] ASC,
) INCLUDE ( [SalespersonID], [MTDRevenue] )

The first index includes the YTDRevenue field, but the second index doesn’t.  If I just drop the first index, then queries that needed that field won’t get the full speed benefits from the second index.  To merge the two indexes, I need to drop both indexes and recreate the second one with the YTDRevenue field included, like this:

CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType_QuotaItemDriverID_Includes] ON [dbo].[MyTable]([RunID] ASC,
[SiteID] ASC,
[DataSource] ASC,
[OutputType] ASC,
[PeriodType] ASC,
[QuotaItemDriverID] ASC,
) INCLUDE ( [SalespersonID], [MTDRevenue], [YTDRevenue] )

In this example, I tacked the YTDRevenue field on to the end of the include field list.  The order of the included fields doesn’t matter, since SQL Server doesn’t sort by those.

Performance Tuning 301 - Beauty Just Adds Weight

Performance Tuning 301 - Beauty Just Adds Weight

Things to Watch Out For

In my examples, I kept things simple by omitting all of the extra indexing options like partitioning and sorting in TempDB.  When doing index tuning in real life, though, you’ll want to check those options to make sure they’re consistent from index to index.

Field order matters in indexes; if two indexes have the same fields but in different order, that doesn’t mean you can drop one of them.

Ideally, after making index changes, we would restart the SQL Server instance to reset the DMV counters that monitor index use.  In reality, though, that’s not so easy to pull off, so we need to log our changes to understand what the changes have been.  After making index changes, log the changes somewhere. I keep the output of the index performance tuning DMV queries in Excel spreadsheets because it’s easier to email those back and forth from machine to machine, especially when I’m consulting. The next time you do index performance tuning on the same database, you can use the historical spreadsheets to determine whether or not your changes worked the way you’d planned.

SQL Server 2008 Query Performance Tuning Distilled

SQL Server 2008 Query Performance Tuning Distilled

Learn More About SQL Server Index Tuning

I really like Grant Fritchey’s book SQL Server 2008 Query Performance Tuning Distilled, and I wrote a book review about it.  I can’t recommend it highly enough, and I’d start there.

If you don’t have the patience to wait for a book, here’s a few more blog posts about performance tuning:

Performance Tuning with Perfmon – how to set up Perfmon, what SQL Server Perfmon counters to track what the indicators mean.

Data Mining Your SQL Server Perfmon Counters – want to take your Performance Monitor statistics to a new level? I wrote an article on SQLServerPedia explaining how to use Microsoft’s free Table Analysis Tools for the Cloud plugins to dive deeply into your data.

Primary Keys and Indexes – I explain the concepts behind keys and indexes using phone books as an example. Indexes have huge impacts on performance, and if you master these you can make your server go a whole lot faster without spending more money.

SQL Server 2005 Setup Checklist – some simple configuration tweaks can get you 20-30% performance increases right from the start without spending any extra money.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

12 Comments - Leave a comment!

SAN Multipathing Part 2: What Multipathing Does

Written on May 18, 2009 by in SQL Server, SQLServerPedia Syndication

In Part 1 of my multipathing series, I talked about what paths are, and today I’m going to be talking about multipathing.  SAN multipathing software has two goals, in this order:

  1. Protection
  2. Performance

Using SAN Multipathing for Failover Protection

What Could Go Wrong?

What Could Go Wrong?

Your server absolutely, positively has to be able to access its drives at all times.  When servers can’t access their hard drives, horrendous things happen.  When hard drives were directly attached to servers, this wasn’t a big risk, but storage area networks bring in a lot of risky factors.  Cables get unplugged or get bent beyond repair.  Switches fail.  Network configurations don’t go according to plan.

(Side note: I think this was one of the biggest reasons SAN administrators didn’t want to go to iSCSI.  They saw how our network cables looked, and they didn’t want their precious fiberoptic cables getting that same treatment.)

Multipathing software mitigates this risk by enabling the SAN admin to set up multiple routes between a server and its drives.  The multipathing software handles all IO requests, passes them through the best possible path, and takes care of business if one of the paths dies.

In the event of a problem like an unplugged cable, the multipathing software will sense that IO has taken too long, then reset the connections and pass the request over an alternate path.  The application (like SQL Server) won’t know anything went wrong, but the IO request will take longer than usual to perform.  Sometimes in SQL Server, this shows up as an application-level alert that IO has taken more than 15 seconds to complete.

To make this work, SAN administrators build in redundancy at every possible layer of the SAN infrastructure – multiple HBAs, multiple switch networks, multiple connections from the controllers, and so forth.  But most of the time, all this extra connectivity sits around idle.  It’s designed to be used for protection, but not necessarily performance: it’s active/passive gear where only one thing is active at a given time.   The secondary goal of multipathing is performance, but it’s a far, far second.  SAN administrators are so conservative, they make database administrators look like gambling addicts.  They’re perfectly comfortable leaving half or more of the infrastructure completely unused.

Do We Really Need More Bandwidth?

Depending on the SAN infrastructure, the theoretical speed limits are around:

  • 1GB Fibre Channel or iSCSI – around 125 MBs/second (this is the most commonly deployed iSCSI speed)
  • 2GB Fibre Channel – around 250 MBs/second
  • 4GB Fibre Channel – around 500 MBs/second (this is the most commonly deployed FC SAN speed)
  • 10GB iSCSI – around 1250 MBs/second

These limits were fine ten or fifteen years ago when hard drives weren’t all that fast, but here’s some sample read speeds from today’s desktop-class SATA drives:

  • One drive – around 130 MBs/second (from TomsHardware reviews)
  • RAID 5 array of five drives – around 300 MBs/second (from my home lab)

Forget 15k drives or solid state drives – even just with today’s SATA drives, 4GB Fibre Channel can get saturated fairly quickly during large sequential read operations, like SQL Server backups or huge table scans on data warehouses.  Sadly, I see so many cases where the IT staff bought a SAN with dozens or hundreds of hard drives, hooked it up to a server with just two 4GB fiberoptic connections, and they can’t understand why their storage isn’t much faster than it was with local disks.  Even if they get savvy to the basics of multipathing and try connecting more 4GB HBAs, their storage speed doesn’t necessarily increase.

Enter Active/Active Multipathing

Active/active multipathing is the ability to configure a server with multiple paths to the storage and simultaneously use all of them to get more storage bandwidth.  This type of multipathing software is usually sold by the SAN vendor, not a third party, because it’s a lot more complicated than it looks at first glance.  Talk to your SAN vendor and ask how much their active/active multipathing software costs, and what it’s compatible with.  EMC’s PowerPath even works with gear from multiple vendors.

But before you plunk down a lot of hard-earned cash – well, it’s not that hard-earned for storage administrators, but I’m talking to database administrators here – you need to ask one very important question: what exactly does this software mean by active/active?  In your feeble mind, you probably believe that you can have one array, accessed by one server, and spread the load evenly over two or more Host Bus Adapters.  Not so fast – some vendors define active/active as:

  • Only one path can be active per array at a given time. If you have four HBAs, you’ll need four arrays in the SAN, and SQL Server will need to spread the data across all four arrays.  This means designing your database filegroups and files specifically for the number of HBAs in use on your server.
  • All paths work for sending data, but only one can receive. I’ve seen this in iSCSI active/active multipathing solutions.  For SQL Server, this means you can insert/update/delete/bulk-load data at breakneck speeds, but your selects still crawl.
  • Active/active works, but failover sticks. Say you have two paths to your data, and one of the paths goes bad for some reason.  All traffic fails over to the alternate path.  When the bad path comes back up (like the cable is plugged back in, the power comes back on, the port is replaced, etc) traffic doesn’t automatically balance back out.  It stays on the single path.  The only way to find this out is with expensive SAN-monitoring software or by browsing through SAN configuration screens periodically.

For virtual servers, I’ve got bad more news: the only true active/active SAN multipathing today is in VMware vSphere 4.0 with EMC PowerPath.  Stephen Fosketts explains the storage changes in vSphere.  If you’re on VMware v3.5 or prior, on Windows Hyper-V, or on vSphere 4.0′s lower licensing tiers, you’re stuck with one HBA of throughput per server per LUN (array).  This is one reason why you might not want to virtualize your high-end SQL Servers yet: they don’t get quite the same level of throughput that you can get on physical hardware.  Don’t let that scare you off virtualization, though – remember, you’re probably reading this article because you don’t have true active/active multipathing set up on your physical SQL Servers, either.

There’s a lot of catches here, and the SAN salespeople are always going to smile and nod and say, “Oh yeah, ours does that.  That’s good, right?”  It’s up to you: you have to ask questions and test, test, test.  Get a time-limited evaluation copy of their multipathing software and test your SAN performance with SQLIO, as I explain over at SQLServerPedia.  It’s the only way to know for sure that you’re getting the most out of your storage investment.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

5 Comments - Leave a comment!

Book Review: SQL Server 2008 Query Performance Tuning Distilled

Written on April 20, 2009 by in SQL Server Book Reviews, SQLServerPedia Syndication

My gauge of an amazing book is simple: if I’ve got a question, and I reach for the book BEFORE I search the web, then it’s an amazing book.

Several times in the last two weeks, I reached for this book first.

The Book’s Audience: Database Developers and Performance Tuners

SQL Server 2008 Query Performance Tuning Distilled

SQL Server 2008 Query Performance Tuning Distilled

This book is for people who:

  • Spend ten hours or more a week asking themselves, “How can I make this application run faster?”
  • Have the ability to change the database schema or queries
  • Know T-SQL well enough to get the data results they want (but not necessarily the fastest way to get those results)

This book is not for people who:

  • Ask themselves, “How can I make this server run faster?”
  • Don’t have the ability to change schema or code (for example, people managing third-party apps from vendors or consultants)

Notice that neither of these categories drew a line between developer or DBA.  I’ve worked with developers who knew how to query SQL Server to get the data, and their applications worked great – but just not as fast as they’d like.  They’re going to love this book.

What Query Performance Tuning Means

Query performance tuning is the art of reading a query’s execution plan, figuring out why it’s not fast, and then determining the most cost-effective way to make it faster.  Anybody can throw more indexes in and just hope it speeds up, but as the book illustrates, sometimes that can hurt more than help.

To do a good job, the tuner needs to know about indexing, statistics, execution plans, compilations, blocking, deadlocks, and query design issues that can force a query to perform poorly.  Some of this stuff is covered in abstract terms in college classes, but for the most part, all of us – developers, DBAs, sysadmins – are pretty much unprepared to guess what’s going on inside the SQL Server engine.

When you first design and deploy an application, that’s a great thing: you don’t need to know what’s going on inside the black box.  SQL Server handles a lot of load with the default settings, with pretty much any application design, before things start to creak and groan.  I’ve seen people build amazingly big SQL Server applications without any knowledge of how indexes or execution plans work.  True story: I walked into one shop to help with a performance problem, and the database administrator didn’t know that a table could have more than one index.  For the most part, their servers were still performing within their needs – but of course, we achieved some fantastic results with some simple performance tuning.  That DBA – and frankly, all of us – would have benefitted from a copy of this book.

Appropriate for Both Junior and Senior Levels

If you fall into this performance tuner target audience, regardless of your seniority level, you’re going to find this book’s price an extremely worthwhile investment. The book’s authors, Grant Fritchey and Sajal Dam, strike a great balance between bringing you up to speed versus diving into advanced concepts.

Chapter 4 on Index Analysis is a great example.  It spends the first few pages bringing the beginners up to speed on what an index is and how B-trees work.  Then it gradually layers on an explanation of how you would approach index design and why the width of your index matters.  The explanation includes queries that prove the concepts, with screenshots of results where appropriate.  The discussion ramps up to more advanced topics like covering indexes, filtered indexes, and compression.

When I talk about index tuning to groups of DBAs, the discussion inevitably turns to statistics – and sure enough, the book starts covering those a couple of chapters later.  I really like the organization of this book because it progresses in the same way that I’d recommend training for a performance tuner.  If you need to make an application run faster, read the book in order.  Don’t be tempted to jump to, say, execution plan analysis – you’ll make poor decisions without understanding the concepts discussed earlier.

I’ve been performance tuning applications for years, trying to wring every last dollar’s worth out of my hardware to make our applications run faster, and I keep learning things as I go through this book.  Normally, I try to read the entire book cover to cover before posting a review, but in this case, it’s going to be quite a while before I finish the book.  I just keep reading a chapter, catching enough things I didn’t know before, and then stopping to apply that knowledge and test it out in my lab.  I highly recommend it.

You can buy SQL Server 2008 Query Performance Distilled from Amazon, and there’s a Kindle version too.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

6 Comments - Leave a comment!

Getting Help With A Slow Query

Written on March 22, 2009 by in SQL Server, SQLServerPedia Syndication

StackOverflow users often ask, “I’ve got this query that runs slow – can you help?”  Here’s a few tips to get better, faster answers.

Get the query execution plan.

DBA Porn

DBA Porn

In SQL Server Management Studio, when you’re looking at the query editor window, click Query, Display Estimated Execution Plan.  It’ll show a fancy-pants flow chart diagram thingy that gets experienced DBAs all excited.

Right-click anywhere on that diagram and click Save As.  Save it to your local machine somewhere, then upload it to a free file-sharing service like Drop.io or Filedropper.com.  These services let you upload a file, and then share that link with the public.

This diagram does not include the data inside the query (like your customers or sales data), but it does include information about your database schema (tables, indexes, views).  If your schema is vitally secret,well, frankly, you need to hire a DBA, because you’re also probably not encrypting anything.  But I digress.

In your StackOverflow question, include the link to this query plan.

Capture Perfmon data.

Go to my Perfmon tutorial and follow the instructions to capture performance metrics data about your SQL Server.  This will help database administrators find out what parts of your server are the current performance bottleneck.  Capture data for 15 minutes before the query runs, while the query is running, and 15 minutes after the query runs.  Stop the perfmon logging, save the CSV file to your machine, and then upload it to a file sharing service as well.

In your StackOverflow question, include the link to the Perfmon data, and also point out what time the query was executing at.

This file does not include any identifying data about your database other than the server name, so it’s pretty safe to post online.

What happens if…

If a query takes 5 minutes to run, then how long does it take to run if you immediately execute it again with exactly the same parameters?  If it takes 5 minutes again, then we’ve got a different set of problems than if it takes a few seconds the second time.

If the query runs slowly from your application, then try running the exact same query from SQL Server Management Studio.  If you’re running a query with dynamic SQL, like sp_executesql, try running it just by itself.  Copy the string out, paste it into a new query window, and run it.  Is it faster?  Does the execution plan look different?  If so, grab that and upload it as well.

If the query uses parameters, and it’s slow with some parameters but not others (like some customers run fast, but other customers run slow), then it might be a plan problem or a statistics problem.  Run it with both sets of parameters (the fast and the slow) and include both sets of execution plans with your question.

Check your Windows event logs.

On the SQL Server, go into Control Panel, Administrative Tools, Event Viewer, and look at the System and Application logs.  If you see any alerts that aren’t “Informational”, you might have a problem on the server.  Examples include RAID array rebuilds due to a failed hard drive, memory failures, SAN controller errors, or an application that keeps crashing.

Sometimes, even Informational messages point to things that are making your query run slow.  For example, I’ve seen instances where people complained about slow query speeds off and on through the day, and I found out that the antivirus software on the server was doing frequent definition updates.  After each update, Symantec/Norton Antivirus does a scan of memory and a few files, which briefly brings performance to a crawl.  This doesn’t show up as an error, but just as an informational message, but it’s a problem for you.

I wouldn’t post these on the internet, though, because they can include some detailed information about your server.  If you’ve got questions about a specific event, it’s best to take a screen shot of that one event’s details and just post that.

Follow up with your question fast.

After you post the question on StackOverflow, set yourself a one hour timer with your phone, your computer, your microwave, whatever.  After an hour, go back to the site, and answer any and all comments about your question that have popped up since.  People will often need more information to solve your problem, and you want to catch ‘em while you’ve still got their attention.  Continue this for the rest of the first day (revisiting hourly) and then set yourself a to-do item in your task list to go back each morning and follow up again.

I’ve seen so many questions that get answered, but the original questioner never revisits the site to find out what the problem was.  Even if you solve it on your own, at least go in there and make a note of that so that other people aren’t pulling up your question over and over to read through it.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

2 Comments - Leave a comment!

Partition alignment in virtual machines

Written on January 19, 2009 by in SQL Server

Partition alignment is a setup trick for disk-intensive apps like Exchange and SQL Server that can have a 30% or more performance impact on  your server.  The setup information is in my SQL Server Setup Checklist, and Jimmy May has more details on the mechanics of partition alignment.

If you run SQL Server in VMware, this still applies to you.  Today, Duncan Epping of Yellow Bricks (a VMware-focused blog) brings news of a new way to test whether your virtual machines have been set up correctly with partition alignment.  VMware admins can run a script on their VMware host, which scans all of their guests and makes sure their partitions are aligned.

The bad news is that it can’t actually fix the partition alignment – the only way to do that right now is to delete the partition, which means a complete backup and restore with a lot of downtime.  The article hints that NetApp is building a utility that will fix partition alignment on VMware guests on the fly.

For more details, read Duncan’s blog entry about VM partition alignment.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

0 Comments - Leave a comment!

StackOverflow’s SQL 2008 FTS issue solved

Written on November 12, 2008 by in SQL Server

Whew – the Microsoft folks really came through on this one and tracked down the problem pretty quickly.  I want to personally thank Denis Churin, Nikita Shamgunov and Sundaram Narayanan for their detailed investigation into the issue and helping us get it fixed. I’m going to explain the issue to help anybody else get through it in case they run into similar problems.

How SQL 2008′s Full Text Search is Different than 2005

Let’s start with the basics: in SQL Server 2005, the full text data lived outside of SQL Server and wasn’t subject to transactional locking.  If you inserted a gazillion records into a table that had a full text index on it, the indexes were rebuilt without worrying about simultaneous inserts and locking.  It wasn’t like a traditional SQL Server table.

In SQL Server 2008, the full text indexes were moved inside the database server and became subject to transactional locking.  By default, the indexes are updated automatically whenever SQL Server determines they need to be updated.  When that merge process happens, SQL Server needs to obtain some locks on the indexed table.  Ideally, it grabs the locks when there’s a brief period of no load, does its merge work, and lets go of the locks.  The merge process can be quite brief (well under a second) as long as the amount of data hasn’t changed dramatically.

In a heavily transactional environment when there’s a whole lot of inserts/updates/deletes on the indexed table, the DBA may need to restrict those merge activities to only a certain time window of the day.  SQL Server 2008 gives you that ability to do merges manually, but I wanted to avoid that on StackOverflow.com.  Every time I put in a manual job into a solution, it requires manual maintenance, alerting and corrective actions, and I don’t take that lightly.

Where The Problem Comes In: Convoys of Queries

Imagine this scenario:

  1. A full-text select query is issued that looks like it’ll finish extremely quickly
  2. The SQL Server doesn’t see much activity in the full text table (only selects, not inserts/updates/deletes) so it kicks off a merge
  3. The select query doesn’t finish as quickly as the engine expected, and the merge can’t start until it obtains the locks it needs
  4. More full-text queries come in (could be selects, inserts, updates, deletes) that need to obtain locks

Those newly issued queries in step 4 are suddenly delayed while waiting for query #1 to finish.  The impact on full text performance varies depending on how long it takes query #1 to finish – might be milliseconds, might be seconds.

Denis Churin, Nikita Shamgunov and Sundaram Narayanan (the Microsoft heroes) suspected this might be our performance problem at StackOverflow, so they had us grab a memory dump and a database backup at the exact moment we were having performance problems.  They looked at the memory dump and isolated a single particular full-text query that was confusing the engine.  The engine was building an execution plan for it that didn’t work well, and instead of taking milliseconds, it took seconds (as many as 50 seconds).  During that time, performance went into the toilet.

They rewrote the query in a different way the engine would analyze better, and when that query was changed, presto, the performance problems disappeared.

There’s a QFE coming in a few weeks that will let the merge thread run without blocking other queries, but for now, we’re in good shape.

Diagnosing The Problem in Your Environment

Sundaram gave us this query to help troubleshoot when a long-running query is blocking other queries.  This helps identify the issue when a full-text select query suddenly blocks the SQL Server 2008 full text merge thread, and you can look at that query to make it run faster.  I haven’t tested this in depth, but these guys have proven to be much more qualified than me, so I have a hunch it’ll work, heh:

declare @temp int
declare @parent int
declare @final int
set @parent = 0
while (@parent = 0)
BEGIN
select @parent=blocked from sys.sysprocesses where lastwaittype=’LCK_M_SCH_M’ and waittime > 30000
WAITFOR DELAY ’00:00:01′;
END

WHILE (@parent <> 0)
BEGIN
set @final = @parent
select @temp=blocked from sys.sysprocesses where spid = @parent
set @parent = @temp
END
select * from sys.sysprocesses where spid = @final
select * from sys.dm_exec_requests ER
CROSS APPLY sys.dm_exec_sql_text(ER.plan_handle) ST
where ER.session_id = @final
select * from sys.dm_exec_requests ER
CROSS APPLY sys.dm_exec_query_plan(ER.plan_handle) ST
where ER.session_id = @final

And now, finally, I can start performance tuning that server!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

28 Comments - Leave a comment!
« Older Posts
Newer Posts »