Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: SQL hang SELECT * (bit OT)

    Hi Everyone,
    I'm having a bit of a dispute with one of my clients' software developers.
    This client has a server, installed by us (Windows 2003 Std. SP1/ SQL 2000 SP3a) (Dual Xeon 2.8, 4GB RAM)
    They are having performance issues with the software developers application, which is based on SQL.
    I'm having trouble believing the issues are caused by the server, but the software dev's blaming us for the problems.

    Yesterday i got a mail from them that a specific SQL query "hangs" upon execution and this is likely caused by a bad install of the server or SQL (yeah right). This query is: select * from table, wich hangs the server upon running. Theyt say a select row(item) from table does work (duh).
    Now i know a bit of SQL and i'm thinking this select all from that table query locking up the server is most likely caused by a table that has grown so big that it just locks up.

    Am i right thinking this has nothing to do with the hardware/software (sbs/sql), but is merely caused by bad programming and/or lack of SQL knowledge at the dev's side? Especially since last year the DB grew to about 40 GB and i had to tell them to think about putting it in simple mode to make it smaller. Something they'd never heared about...

    Any ideas would be very welcome.
    I don't want to go throw mud around with us blaming them and them blaming us and the client being the one with the problems.
    I just want to make a well thought out statement to the software dev. and then try to find the right solution.

    Thanks,
    Dennes

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Oh, I love these. Are you familiar with profiler? Run a trace for all queries over say 1,000 milliseconds, and let it run. When you see a query go by that runs for a long time, copy it out to Query Analyzer, and get a look at the graphical query plan. With this, you can show that the cost of the query (hover over the icon that reads "SELECT") is very high. Either that, or run the Index Tuning wizard, and "suggest" a few indexes that may improve performance.

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    Thanks!
    But i'm not too familiar with Profiler.
    How can i do this for a trace of queries on one specific table?

    Dennes

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I probably would not. You can show a pattern if there are many queries that run long. The one you are after (if it generates a timeout error) should run for just over 30,000 milliseconds.

Posting Permissions

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