Blog

Are you frustrated by third party applications that you can’t change, but you have to support? Tired of beating your head against the wall when your users complain about things you can’t fix? In this 30-minute session, Brent Ozar will show you his favorite tricks to get the most performance without losing support. He’ll show you how to interact with vendors and get what you want – without getting heartburn:

Like that video? We’ve got half a dozen more scheduled for upcoming Tuesday lunches. Click the boxes you want and sign up for free.

↑ Back to top
  1. As a vendor, the first 5 minutes of this session was brilliant, not that the rest was rubbish by any means.

    Vendors encourage their customers to muck with their databases by not providing ample (any!) documentation, a data model, and the foresight to realize that the most valuable thing is the data, not the application. So of course folks want to get at that data, and sometimes that requires new views, tables, etc…

    I think vendors hate it most when you open a support ticket and fail to start with every change you’ve made to the system. Pretending everything’s vanilla when it’s more like a gummy bear Neapolitan blizzard with sprinkles isn’t going to win you any favors. So be sure to document your changes as you make them – for your own benefit as well as the vendor so they can better help you when you need it.

    • Thanks sir!

      I work with one ISV who provides an API to access the most valuable data. Problem is, most folks don’t want to get the data out via a web API – they just want to run a query. It’s a tough line to walk. If you give users a set of views and ask them to query through that, then it gives you a little abstraction, but major releases can still break the bejeezus out of that. I’m glad I’m not an ISV anymore – it’s tough work.

      • But SQL is the best API :)

        Vendors also aren’t fans of having their databases touched because a majority of their customers aren’t database professionals. Not anyone reading this post of course…

  2. i usually avoid messing with any vendor’s databases except to add a missing index
    my GP databases have lots of unused indexes, but i dare not touch them. most times the databases are pretty small so its not a big deal. any issues we call support

  3. Nice webcast! Can I suggest that, when designing your slides, you keep in mind where the video window will be so it isn’t overlaid on slide text?

    On to my real question: What if you have forced parameterization and optimize for ad hoc queries enabled on the same database? Do they play nice together?

    • Shaun – thanks, glad you like the webcast! We actually design our slides for long-term use. If you join the webcast live, you see the video in a separate window, and we also use the same slide decks at user groups and conferences. We give away the recordings on YouTube just to make it easier for people who would otherwise never see the slides at all. It’s the best compromise we’ve got short term. Thanks though.

      About forced parameterization and optimize for ad hoc – sure, you can use both, although what pain are you trying to solve with each?

      • No pains now. It was more of a thought exercise. I typically turn on optimize for ad hoc queries by default, so I was wondering what would happen if I had to also turn on forced parameterization. Was just wondering if forced parameterization would make SQL create a general plan, but the optimize for ad hoc would mean SQL would just store a stub for it until the query was run again. I’m guessing this is how it would work. Just wasn’t sure if that would cause performance issues or if it was dumb to have both on at the same time.

        P.S. This was a great topic for me. I’ve got a 1.2 TB vendor database that is mainly heaps. I can’t stand it. We’re moving from 2005 to 2008 R2 so I can implement compression (and compressing heaps isn’t fun either).

  4. I’ve yet to understand why some vendors do not offer index tuning as extra service when a company buys their software. No two companies uses their app the same way but both companies will complain on the performance of the system soon enough.
    Then vendor says “but you are using vmware and our little database must have its own monster server” and then fight is on..

    Instead if the vendor sold index tuning as a “must have” service for their db/app, provided that service a couple of times the first year and then continue to do it occasionally. Their product would be beloved instead of disliked and rumour alone could sell some copies.

    Two of my horror stories:
    #1 One vendor told a customer of mine that run all their SQL servers in vmware to buy a separate server, and oh the database did not perform that much better. Customer still hates the software.
    I just ran sp_blitzindex and found 96,7% of the NC indexes were not used.(and there were a looot of them)

    #2 Vendors says to customer, in order to fix your performance issue you must upgrade to Enterprise Edition and then you will pay us a brazillion pesos to implement partitioning which will make our app go lightning fast. Oh yeah and then sp_blitzindex stepped in and the heaps were just raining from the sky, duplicate/borderline duplicate indexes where just everywhere and the database was suffering from all the phobias..

    So I do understand all vendors do not want or need to sell performance tuning as an extra service, but some of you out there should think about it, your software might actually be liked even after that first year..

    • I think this is because many vendors do not have the knowledge in house to provide index tuning services. This is especially true for products that support several different database platforms as their back end.

      • Yup, I agree. That is what I tell my customers too.

        But in certain situations (one platform support, proved sql performance issue and even having their own DBAs) it blows my mind that they have not thought of it
        So I’ve told my customers to always ask for this service when buying 3rd party software.

        Some vendors are actually listening and taking the plane over to us to sit down for a couple of days and tune the database for the customers needs. Customer is happily paying for it and the vendor is happy cause the customer is happy and I am happy if everybody is happy.

  5. Oh and thanks for a great webcast, really got something out of it.

  6. Dear Brent

    Adding a computed column into a table structure doesn’t break the application, it only brings down the table due to restructuring. I have done it before. and for boosting up the performance it must be PERSISTED with nonclustered index on top of that for Index Seek operation.

    Best Regards
    Hamid J. Fard
    Data Platform Specialist

    • Hamid – and what happens when you try to do an insert statement without naming the fields explicitly?

      For example, say you have a table called dbo.Sales with 4 fields, and one of them is a computed field. Try this:

      INSERT INTO dbo.Sales VALUES (‘Field1′, ‘Field2′, ‘Field3′)

      Does that work successfully?

      • Dear Brent,

        Sorry for my late reply.

        Of course nothing, please try below statements

        use tempdb;
        go
        create table dbo.tt (id int, ss char(80), ComputeCol as 2-3 persisted);
        go
        insert into dbo.tt values (1,’MCM’);

        Best Regards
        Hamid J. Fard
        Data Platform Specialist

      • Dear Brent,

        Please try below statements as well,

        use tempdb;
        go
        create table dbo.tt (id int, ss char(80), ComputeCol as 2-3, kk char(40) );
        go
        insert into dbo.tt values (1,’MCM’,’78’);
        go
        select * from dbo.tt;

        As what I understand, Computed columns are stored physically at the end of the record in the page, it means the developer needs to take care of non-computed columns order during insertion. Otherwise SQL Server fails the insertion due to data type conflict.

        Best Regards
        Hamid J. Fard
        Data Platform Specialist

  7. I would love to watch this video, but it says it is not availalbe?

    • Hi Debbie,

      Are you still getting the message? I’m seeing the video as available. Are you reading it via a web browser, or are you possibly getting to it via an RSS reader? (It should work, just trying to track down any more info that can help us figure out why.)

      The video is published via YouTube, so you might also just be able to get to it via our YouTube channel if all else fails: http://www.youtube.com/user/BrentOzar/videos

      • I had the same problem on youtube site, but only this video. I tried all the steps they had for resolving the problem. I finally tried viewing it in Chrome (instaned of IE) and it worked for me then.

        Thanks!

  8. Great presentation Brent,

    Listen to the man people!

    For Best Results…
    As an ISV, I just wanted to chime in on some comments here. In our case, we’re always walking the line between relentless improvement and avoiding the risks that come with any change.

    This sometimes means avoiding improvements for the sake of improvements: For example, fixing heaps or unused indexes or missing indexes is not necessarily an end in itself. (e.g. an unused index may be benign simply because it’s for a feature you don’t use)

    It also means that support calls go better if you focus on the business need or business pain your feeling. (This is Brent’s “Identify the End User Pain”) That means end-user-impact instead of DBA-impact. I’m really lucky to work with some awesome, talented DBAs who run our software. The relationship has never felt like us vs. them.

  9. If you draw a matrix scheme and add:

    DBA – focused on dba-impact
    DBA – focused on end-user-impact
    DBA – Dont care
    DBA – Dont know
    ISV – Bad database – No skills, blames customer.
    ISV – Bad database – willing to help but no skills
    ISV – Bad database – willing to help, got skills but no time.
    ISV – Bad database – willing to help, got skills.
    Customer – Willing to pay for improvent
    Customer – Got no money – asks DBA to solve problem
    Customer – Great relationship with ISV
    Customer – No relationship with ISV
    And so on..

    If you mix and match above, thats what I am faced with as a consultant. So sometimes its just happy times and somtimes it is impossible not to have a we and them relationship because some people just don’t care or do not want to listen to customer problems.

    So this webcast will help with some of the scenarios for sure.

  10. One tactic that’s worked well for me with a vendor app: much like your suggestion for naming indexes with your org name, do the same for any other objects you add. Our vendor only uses dbo, so any procs, functions, etc I add go into an org name schema that clearly tells my team that it’s in-house work, and tells the vendor that it’s not their problem.

    Just make sure the schema you pick isn’t one the vendor might reasonably ever want to use!

  11. Thanks Brent,

    Relevant as usual. As far as I am concerned, the post is a new proof of IT industry customer service expectation immaturity.

    I like to use the following analogy regarding application vendors and support: Suppose you decide to rent a 1000 dollars large apartment. In the beginning you are happy with your family but after few months, your kitchen floor goes down. Because you can not cook anymore, you bring in a contract builder who explains that the reason the floor went down is because the cement used was poorly prepared. You immediately call your landlord to explain the situation and he tells you that accepting your monthly rent money does not give him any responsibility over the quality of the cement and that trying to rebuild this kitchen by yourself will be placing that responsibility on your shoulder for any other room’s floor that may go down.

    While you think whether you should take that responsibility and you and your family keep on eating sandwiches, the landlord proposes you to move to a new 2000 dollars appartement to fix the problem. Problem is: you don’t have 2000 dollars to send on putting a roof over your family. And to make it better, your neighbor down under threatens to sue you if you don’t fix this issue.

    As you guessed:

    > Initial Rental= Initial Licensing Cost
    > New Rental Cost= Upgrade to the new version of the software
    > Loss of Product Support= Acceptance to fix any other room’s floor going down
    > Landlord = Application Vendor
    > Bad quality cement= You name it= poor design, excessive locking, bugs
    > Contract Builder= Some independent database consultant
    > Neighbor=Supplier, Client impacted by the design flaw

    While you wait:
    > You pay 1000 dollars a month but you keep on eating sandwiches.
    > You heating bill keep on increasing since the winter is here and you have a big black hole in your kitchen.
    > Your neighbor down under decides to sue you because he is sick of hearing you cursing at your landlord.
    > 3 month later, your bathroom’s floor goes down for the same reasons. Now you became a dirty sandwich eater.
    > Your 2 year old kid may fall through the kitchen floor.

    Your probable attitude toward that landlord would be to sue him because he rented to you a dangerous apartment and call him a hustler because he tries to take advantage of the situation to rent you a more expensive apartment. But, by some kind of miracle, that does not seem to apply to IT industry.

    IT is the only business where one would pay for a product and pay for the extra cost for fixing a flaw in the product due to faulty design. By some tour de force, many application vendors have convinced their customers that database application is not a part of the application and that they (the customers) should not only pay expensive money for licensing and upgrades but also for fixing the design flaws of the products.

    Regards,

    Racim

    • Racim – Wow, you put a lot of work into that! I disagree with some basic concepts here though.

      When a software vendor sells you something, it relies on a few things. It’s more like the vendor is selling you a manufactured home.

      *You* actually build the foundation (implement the hardware), not the vendor. The vendor can give you advice on how much concrete to pour for the house you just bought from them, but they won’t pour it for you.

      *You* have to tell the vendor how much space you need, what kinds of rooms, and so forth. If you give the vendor bad specs, then you’ll get the wrong kind of house, and that’s not the vendor’s fault.

      The vendor will come by from time to time to give you housing upgrades *if* you paid for support. If you changed the house, they may not be able to give you upgrades, and you might lose support.

      • Well, let’s say that debunking poor application vendors is a hobby of mine. :)

        I see your point regarding the bad specs as well as the implementation by the customer and I agree that most application vendors are all right But you also need to take into consideration the following:

        > Application Vendors do also sell consulting set up fees and impose specific standard configurations which boils down to having them pour down the concrete and hardware that is often standard.
        > Application Vendors Companies are not necessarily Application Development Companies. Application Vendors often sell the same application basis developed in the past and try to adapt it to all situations
        > Customer Support and Upgrade are independent products . Many companies sell them separately. Would you imagine your TAM’s reaction if you claim a cross version upgrade when purchasing you extended support for your SQL 2005 SP3 boxes ?

        Legally, an software application license usage is like a right to rent the usage of something but not owning the thing, which in this case would be intellectual property. When an application vendor sells you a license he sells the right to use and bears the responsibility of having that application software behave a certain way if you respect his configurations. My basic point is that, customers would not have to modify any schema if the application behaved the way it was supposed or the way it was sold. I guess it is a matter of perspective and experience.

        Regards

  12. love your articles keep them coming and your tumbler site is so my life in support.

  13. I work for what you refer to as a “vendor,” so I have a little different perspective. I would gladly welcome input from any customer that followed all of the steps that Brent outlined. Thougtful, well-documented feedback is one of the best ways for us to improve our product, make all of our customers happier, and ultimately make more money for my company and ultimately for me.

    I do have to disagree with Brent’s suggestion for granting vendors SA access. We can only do so much in our development and testing environments to guess how you might use our product. Even with the best testing that we can develop, we are still guessing how customers are actually going to use the product and which features they use more heavily. Our products are highly configurable and we can’t possibly do a full feature and load test on every possible combination of configuration settings. We may occasionally need to have some pretty broad access to our product as it is installed in your environment to allow us to find and correct problems.

    I can’t agree with the idea of simply letting the business people be the bad guys and say “no” when a vendor requests an elevated access level. Of course they are going to say no when you tell them that I have access to freely query and change all of your HR or financial data. My question is why you would even consider putting your HR data or financial data on a server that every user can access. Simply denying users access to a specific database means that you are placing all of your trust in one layer of security that could be accidentally changed very easily.

    When a support issue is escalated beyond our support team to me or my fellow developers I usually plan to spend at least half of my time trying to figure out how to get the information that I need within whatever scope of access the customer (who normally knows nothing about how our product actually works) has decided is appropriate for me to have. Quite often these support issues arise from a specific feature or functionality that only that customer has requested. A large number of the remaining issues are a result of a change that a customer has made with all of the best intentions but we are unable to detect due to our level of access.

    When I talk about “change that a customer has made” I am not necessarily talking about customers just changing the application that we have provided. Our application serves as an interface between users and other third-party systems, so I need full access to the third-party system as well to find the reason that angry users are lined up outside your office.

    Bottom line, if you don’t trust your vendors with the data within the vendors’ applications you need to need to find some different vendors that you do trust. If giving vendors full access to their own application’s data also grants access to data that is none of the vendor’s business you need to ask yourself if the biggest threat to this secured data is really from the vendor or from internal sources.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php