Post reply

Commercial Software with All Tables as Heaps and Terrible Performance

  • October 4, 2015 at 9:30 am

    #1831267

    Robin,

    Did you check the power saving settings that Jason mentioned just a couple of posts ago?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 5, 2015 at 5:18 am

    #1831333

    Eric M Russell (10/3/2015)

    898 million page reads? Each page is 8'000 bytes... :w00t:

    The final 42 million reads is pretty bad, as well, even for a batch run. That's like doing a table scan from a 344GB table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 7, 2015 at 3:21 pm

    #1832155

    Jeff Moden (10/5/2015)

    Eric M Russell (10/3/2015)

    898 million page reads? Each page is 8'000 bytes... :w00t:

    The final 42 million reads is pretty bad, as well, even for a batch run. That's like doing a table scan from a 344GB table.

    Hello Jeff

    I think the problem is that I have to have a sub-query which compares and ranks data from two different systems and I have to repeat this 10 times. Table scans unfortunately have to occur to some extent as I am not always joining by the primary key (I didn't design these systems!).

    If I can get it down to 30 seconds I can live with it though!:cool:

  • October 7, 2015 at 3:36 pm

    #1832162

    Hello All

    I just wanted to say thanks for the help everyone has given.

    The software supplier has now agreed to implement primary keys and clustered indexes across their software in the next update so this should benefit all customers who use (suffer) it:

    The evidence that you have gathered is compelling and we accept that the solution that you are proposing follows SQLServer database design best practices.

    We are therefore planning to implement clustered primary keys throughout the [redacted] system in the next 4.32 release (December) subject to successful completion of platform regression tests.

    Your script utilizes a neat naming convention for the keys and we have tested it with a number of key processes in [redacted] and found no detrimental effect so far.

    Looking forward to a performance boost at Christmas 😎

  • October 7, 2015 at 3:37 pm

    #1832164

    WayneS (10/4/2015)

    Robin,

    Did you check the power saving settings that Jason mentioned just a couple of posts ago?

    Hello Wayne

    Sorry I forgot to reply but this was not the issue and it looks like it was the primary keys after all.

    Thanks though.

    Robin

  • October 8, 2015 at 4:09 am

    #1832241

    robinwilson (10/7/2015)

    Jeff Moden (10/5/2015)

    The final 42 million reads is pretty bad, as well, even for a batch run. That's like doing a table scan from a 344GB table.

    I think the problem is that I have to have a sub-query which compares and ranks data from two different systems and I have to repeat this 10 times. Table scans unfortunately have to occur to some extent as I am not always joining by the primary key

    Would it be worth trying a COVERING non-clustered index on the sub-queries?

  • October 10, 2015 at 12:23 pm

    #1832687

    Kristen-173977 (10/8/2015)

    robinwilson (10/7/2015)

    Jeff Moden (10/5/2015)

    The final 42 million reads is pretty bad, as well, even for a batch run. That's like doing a table scan from a 344GB table.

    I think the problem is that I have to have a sub-query which compares and ranks data from two different systems and I have to repeat this 10 times. Table scans unfortunately have to occur to some extent as I am not always joining by the primary key

    Would it be worth trying a COVERING non-clustered index on the sub-queries?

    Hello Kristen

    Thanks for the suggestion.

    I will look at this once I have the primary keys on the live system.

    They have sent me a supported workaround (which is just my primary key script copied to include all tables) and I will apply this at half term just in case there are any unintended side effects.

    Thanks

    Robin

  • October 27, 2015 at 11:47 am

    #1835867

    robinwilson (9/28/2015)[/b

    Virtualisation

    The server is virtualised on a VMWare host and I do wonder if something is wrong there (and I have exhausted other avenues I think). I did discuss moving to a physical server with IT but they decided against it as they can quickly fall it over to another physical host if things go wrong when virtualised and don't have the spare hardware for an SQL Server cluster.

    Is there anything else I can check such as:

    * If the throughput from the SAN is insufficient

    * If something is not configured correctly on VMWare which could be causing issues

    I need to do further testing with the indexes still as well on the test server.

    Thanks

    Robin

    Several things VMWare bods like to do to kill SQL Server because they think it's a file server.

    1) Ballooning. There's a thing called a balloon driver in VMWare. It likes to steal back memory from SQL Server boxes where it thinks the memory's not being used much and hand it out somewhere else. The way SQL Server uses buffer cache makes it look like it's not being used to the visor. Things like vCop measure memory use in a way that's basically meaningless on a SQL Server VM.

    You should turn off the balloon driver on all SQL Server VM's.

    2) Overprovisioning of VCPU's. You want to keep your VCPU's nice and busy - here, less really is more. One - you're effectively throwing all your nice expensive cache out of the window (figuratively speaking) as with the vCPU's not being busy, they 'hop' about the pCPUs meaning anything loaded onto the on board cache memory's now gone. Also, if you've allocated 8 vCPUs (because the vendor gave you the same spec as a VM as they did for a physical server, which is totally random anyway because they based that on what you can get for about £2500 in a server off the Dell website <- trust me, that's how most of them do it I swear) - the visor will give you NOTHING until it can give you ALL 8 vCPUs. It neither knows nor cares you only need a tiny few clock cycles - you get nothing unless you get the full 8. If you've al lot of overprovisioning on your VM's this is really going to hurt you - and a lot more than the file servers they actually care about.

    3) Thin provisioning. Just NO.

    4) SAN. It depends what you use to some degree, but basically the rules still apply (unless you're using cached storage like Nimble) - many fast spindles, put logs, tempdb and data on seperate spindles with fast raid. Chances are your SAN Admin has you in a huge raid 5/6 swamp with everything in the same pool with LUNS carved from that as required - and I'm talking EVERYTHING, not just the SQL Server stuff - fighting like cats in a sack and making everything suffer. Don't blame them, they did this because that's what the vendor said when they set it up that way. Because billable:actual work ratio's good with that config

    SQL Server on seperate disks. Your SAN admin will tell you the magic bits in the SAN fix the laws of physics - but no, they don't.

    I'll hunt down some links (I don't expect you to belive me at this point, let alone your SAN admin), but basically Brent Ozar's good here and there are some good coursed on Pluralsight.

    Deepest apologies to any of the hard working regulars here I should have plugged here and didn't.

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • October 30, 2015 at 6:40 am

    #1836652

    Hello Andrew

    Thanks for all the information you have given and the things to check.

    I have now added primary keys and clustered indexes to all tables in the database and this has improved the speed significantly but I still feel there is something else that is not quite right as it hasn't entirely solved the timeouts and random performance issues.

    I will get the IT department to look over your points and check the settings.

    Thanks for the help.

    Robin

  • October 30, 2015 at 7:01 am

    #1836658

    Eirikur Eiriksson (9/14/2015)

    Wayne West (9/14/2015)

    Matt Miller (#4) (9/14/2015)

    Eirikur Eiriksson (9/14/2015)

    Jeff Moden (9/14/2015)

    Eirikur Eiriksson (9/13/2015)

    Using heap to speed the inserts is like a very bad Credit Card deal, no transaction fees but X000% Annual interest rate:pinch:

    😎

    Jeff Moden (9/12/2015)

    It's a really good thing that I don't live within driving distance of that, ummm... person.

    Jeff, what happened to the PoIP (Porkchop over IP) you were working on?:-D

    I decided to go whole hog on a new porkchop launcher project, instead 😀

    That's quite "striking":hehe:

    😎

    That's not bad but still - it's a medium range delivery system. You might consider thinking about adapting "cruise porkchops" onto the upcoming Navy platform (I think this would be dubbed LRASM-P given the current funding from defense dept). cruising speed is 2.8M. and effective range in the 3-5K miles.

    There is a Titan II silo south of Tucson in Green Valley. I don't think it would be too difficult to retrofit it to deliver a porcine package. Of course fueling and launching it, much less making sure that all world powers know that you're only delivering a porkchop, albeit in a rather aggressive manner, and that you're not lobbing a nuke. Though there are probably times when the nuke might be the appropriate response: after all, if you can throw it from orbit, it's the only way to be sure. 😀

    Someone has leaked the details

    😎

    Peppa Pig jigsaw. Nice 😉

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 10 posts - 61 through 69 (of 69 total)

You must be logged in to reply to this topic. Login to reply