Page 1 of 2 12 LastLast
Results 1 to 16 of 27

Thread: So where can I hire an MSSQL expert on short notice???

  1. #1
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    So where can I hire an MSSQL expert on short notice???

    I'm about ready to cry here!

    We were sold an SQL based system, called Opera II Enterprise, on the grounds that it'd perform better for our company than the FoxPro based system we'd be using (of the same product) since we performed about 40,000 transactions a month. Nothing we have tried can get this system to perform anything better than the current system and in many cases, it actually runs slower.

    Our initial setup was an SBS box running Exchange, plus the FoxPro based system and the SQL version simply wouldn't run on this without heavily corrupting data. So, roll out test 2, nice new thin HP Server, Quad core and 4Gb of RAM, way in excess of the software vendor's recomendations, and yes, it runs now, but there's unexplainable pauses, during data entry you can physically see a letter go in at a time and end up as many as 3-4 lines ahead. The FoxPro system simply doesnt' suffer from that


    I'm at a loss to diagnose this, has anyone got suggestions on things I can check or try to improve system performance? Thus far we've tried some fairly obvious things like disabling the virus scanner on the PC, upgraded the network switch to 1GB and the individual PC's are fitted with GB cards and the Database Tuning Wizard suggests that we create another 40 odd indexes, all centred around the stock databases (where the slowdowns are occuring). Is there anyone who can suggest something else I can try?

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  2. #2
    Treasure Hunter extraordinaire herulach's Avatar
    Join Date
    Apr 2005
    Location
    Bolton
    Posts
    5,618
    Thanks
    18
    Thanked
    172 times in 159 posts
    • herulach's system
      • Motherboard:
      • MSI Z97 MPower
      • CPU:
      • i7 4790K
      • Memory:
      • 8GB Vengeance LP
      • Storage:
      • 1TB WD Blue + 250GB 840 EVo
      • Graphics card(s):
      • 2* Palit GTX 970 Jetstream
      • PSU:
      • EVGA Supernova G2 850W
      • Case:
      • CM HAF Stacker 935, 2*360 Rad WC Loop w/EK blocks.
      • Operating System:
      • Windows 8.1
      • Monitor(s):
      • Crossover 290HD & LG L1980Q
      • Internet:
      • 120mb Virgin Media

    Re: So where can I hire an MSSQL expert on short notice???

    Is no support available from the vendor? I'd take them to task over it if I were you

  3. #3
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: So where can I hire an MSSQL expert on short notice???

    Quote Originally Posted by herulach View Post
    Is no support available from the vendor? I'd take them to task over it if I were you
    I would love if there was vendor support, but quite frankly they're as sick of this project as I am and to be honest, it appears I know as much about SQL as they do. It's one of these annoying products that is sold via a reseller and thus, the experience is limited. None of the original developers are left on the original team either so it's a case I don;t think anyone really knows how this program works anymore


    Still, I've added a few more indexes to see if it helps improve matters.

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  4. #4
    Senior Member
    Join Date
    Jan 2004
    Location
    Leicestershire
    Posts
    1,212
    Thanks
    7
    Thanked
    31 times in 30 posts
    • madman045's system
      • Motherboard:
      • P9X79 Pro
      • CPU:
      • I7-3820
      • Memory:
      • 32GB
      • Storage:
      • Not enough!
      • Graphics card(s):
      • HD7970
      • PSU:
      • 850w Corsair
      • Case:
      • Corsair Carbide 300R
      • Operating System:
      • Win 7 Ultimate X64
      • Monitor(s):
      • Dell U2713HM & 2007WFP
      • Internet:
      • Plusnet FTTC - 30mbit/7mbit

    Re: So where can I hire an MSSQL expert on short notice???

    What disks does this box have in it, raid controller? raid configuration?

    Sounds to me like an IO bottleneck, what OS are you now running on the HP Server and which version of SQL?

    Lucio is it this edition you have?

    Opera II Enterprise SQL

    Andy

  5. #5
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: So where can I hire an MSSQL expert on short notice???

    Quote Originally Posted by madman045 View Post
    What disks does this box have in it, raid controller? raid configuration?

    Sounds to me like an IO bottleneck, what OS are you now running on the HP Server and which version of SQL?

    Lucio is it this edition you have?

    Opera II Enterprise SQL

    Andy
    That's the bunny, we're running gigabit switch and cards inthe PC, the server is a Proliant ML370 with Xeon 2.0GHz Quad core, with 4GB ram running Server 2003 and Enterprise Edition MS SQL 2005. The HDD are a pair of 73GB 10K HDD, with a pair of 73GB 15K rpm HDD which only store the LDF file (logs)

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  6. #6
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: So where can I hire an MSSQL expert on short notice???

    you've seperated the transaction and log drivers, running 64bit OS and MS SQL.

    Odds are the performance increase should of been hudge.

    Could it be that the product is just plain crap?

    You shouldn't be setting indexes, the vendor install scripts should set those up.

    the only decent MS SQL contracter i've come across (professionally) charges 1,200 a day. For contracts that are as long as 6 months. But he was truely great at his optomisation
    throw new ArgumentException (String, String, Exception)

  7. #7
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts

    Re: So where can I hire an MSSQL expert on short notice???

    too many indexes as be as bad as too few , especially when you are doing large inserts
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  8. #8
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: So where can I hire an MSSQL expert on short notice???

    i should clarify, depending on the complexity of the app, you shouldnt be messing about with its databases.

    an example is, i insert about 850,000 rows, for a morning batch job. When doing this, i drop the indexes, do a bulk insert (with only the clusetered index) then build the other indexes.

    As moby says, its not as simple as adding more indexes (not too mention you can end up with a hudge amount of space allocated to the indexes, one of my systems has about 3 times the space for the index, than it does for the table... this is however by design).

    Contact the vendor, nag the vendor, mabye look at another product if its not good enough.
    throw new ArgumentException (String, String, Exception)

  9. #9
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: So where can I hire an MSSQL expert on short notice???

    Thanks for the advice people, if I still have a job at the end of the day, I'll see what I can do with it.

    I'm inclined to say that the product is just plain crap, because we simply aren't doing *that* much with this product that it should be this slow and unresponsive. Given the sizes you guys are talking about, this is a tiny 9GB database, with at most a couple of million records in the largest table. We will process about 200 orders a day which typically have 10 lines on (which for some arcane reason have to go in twice)

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  10. #10
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: So where can I hire an MSSQL expert on short notice???

    Ok, this question should be fairly straight forward. I've tried capturing a trace on the TSQL statements, and one of them puzzles me.

    When I update an order in the program, one of the commands is an UPDATE for the sales table which refreshes the order balance. My question is it normal coding practice to update all the unrelated fields with the data that hasn't been changed? For example, one of these is a memo field with a few thousand characters in, yes you can change the memo field but not at the same time as posting a new order.

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  11. #11
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: So where can I hire an MSSQL expert on short notice???

    depends how clever they're system is.

    If you've got the standard boiler plate Data Accessor type layer, then yes often you will update the whole object via its key.

    This just makes for simpler code, and the performance hit of one single UPDATE command should be minimal. (if your looking at the trace log, you should be able to see it take virtually no time at all).
    throw new ArgumentException (String, String, Exception)

  12. Received thanks from:

    Lucio (24-01-2008)

  13. #12
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts

    Re: So where can I hire an MSSQL expert on short notice???

    one of the neat things you can do with SQL 2005 profiler is that you can merge is with perfmon data and see what the server was doing at the exact time an instruction was excecuted.

    check the usual things like disk queue length , but might be worth looking at some of the more SQL specific counters like buffer cache hit ratio. You might also want to check on the page split counters.

    If the database doesn't have any fill factor in it , you'll have to grow the files and shuffle data around every time you insert something. Make sure you dont have any of the auto shrink options on. If you've created indexes , make sure they are backed up with the appropriate statistics ( and that they are updated ).
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  14. Received thanks from:

    Lucio (24-01-2008)

  15. #13
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: So where can I hire an MSSQL expert on short notice???

    Quote Originally Posted by Moby-Dick View Post
    one of the neat things you can do with SQL 2005 profiler is that you can merge is with perfmon data and see what the server was doing at the exact time an instruction was excecuted.

    check the usual things like disk queue length , but might be worth looking at some of the more SQL specific counters like buffer cache hit ratio. You might also want to check on the page split counters.

    If the database doesn't have any fill factor in it , you'll have to grow the files and shuffle data around every time you insert something. Make sure you dont have any of the auto shrink options on. If you've created indexes , make sure they are backed up with the appropriate statistics ( and that they are updated ).
    Thanks a lot Moby-Dick, that's the kind of thing I've been wondering whether it was possible to do. Out of interest, if I created a perfmon trace on one or more of the local machines, could I then compare the files against the SQL trace or would it be out of sync because of differences between the system clocks?

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  16. #14
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts

    Re: So where can I hire an MSSQL expert on short notice???

    it wouldn't sync really - its used for looking at whats happening on the server rather than what is happening clientside.
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  17. #15
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: So where can I hire an MSSQL expert on short notice???

    Ok, I've ran a few traces and there's not any particularly large spikes on the data.

    This may be a silly question, but is it possible for the basic cabling structure of the building to be the problem? For example, we have a big problem with static electricity and light bulbs tend to blow more than I'd expect and given that most people go "but it shouldn't do that" do make me wonder if we have a simple issue of dodgy wiring.

    Is there a simple (ish) tool I can check this idea with or do I just run a constant 1s ping of the server?

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  18. #16
    Anthropomorphic Personification shaithis's Avatar
    Join Date
    Apr 2004
    Location
    The Last Aerie
    Posts
    10,857
    Thanks
    645
    Thanked
    872 times in 736 posts
    • shaithis's system
      • Motherboard:
      • Asus P8Z77 WS
      • CPU:
      • i7 3770k @ 4.5GHz
      • Memory:
      • 32GB HyperX 1866
      • Storage:
      • Lots!
      • Graphics card(s):
      • Sapphire Fury X
      • PSU:
      • Corsair HX850
      • Case:
      • Corsair 600T (White)
      • Operating System:
      • Windows 10 x64
      • Monitor(s):
      • 2 x Dell 3007
      • Internet:
      • Zen 80Mb Fibre

    Re: So where can I hire an MSSQL expert on short notice???

    To me the performance seems so bad for such a simple system, that either something is horribly mis-configured, or the application is truly awful.
    Main PC: Asus Rampage IV Extreme / 3960X@4.5GHz / Antec H1200 Pro / 32GB DDR3-1866 Quad Channel / Sapphire Fury X / Areca 1680 / 850W EVGA SuperNOVA Gold 2 / Corsair 600T / 2x Dell 3007 / 4 x 250GB SSD + 2 x 80GB SSD / 4 x 1TB HDD (RAID 10) / Windows 10 Pro, Yosemite & Ubuntu
    HTPC: AsRock Z77 Pro 4 / 3770K@4.2GHz / 24GB / GTX 1080 / SST-LC20 / Antec TP-550 / Hisense 65k5510 4K TV / HTC Vive / 2 x 240GB SSD + 12TB HDD Space / Race Seat / Logitech G29 / Win 10 Pro
    HTPC2: Asus AM1I-A / 5150 / 4GB / Corsair Force 3 240GB / Silverstone SST-ML05B + ST30SF / Samsung UE60H6200 TV / Windows 10 Pro
    Spare/Loaner: Gigabyte EX58-UD5 / i950 / 12GB / HD7870 / Corsair 300R / Silverpower 700W modular
    NAS 1: HP N40L / 12GB ECC RAM / 2 x 3TB Arrays || NAS 2: Dell PowerEdge T110 II / 24GB ECC RAM / 2 x 3TB Hybrid arrays || Network:Buffalo WZR-1166DHP w/DD-WRT + HP ProCurve 1800-24G
    Laptop: Dell Precision 5510 Printer: HP CP1515n || Phone: Huawei P30 || Other: Samsung Galaxy Tab 4 Pro 10.1 CM14 / Playstation 4 + G29 + 2TB Hybrid drive

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Its a bit short notice but....
    By Moby-Dick in forum General Discussion
    Replies: 16
    Last Post: 06-11-2007, 04:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •