SQL Server Performance

Timeouts problems

Discussion in 'Performance Tuning for DBAs' started by petercor, Dec 18, 2002.

  1. petercor New Member

    We purchased a new 2GHz dual processor server to handle some large datasets (10 off 2 million records). Initially we had problems importing all tables into a single filegroup and needed to create a secondary filegroup. This was not required on our old machine.

    That done, we are now trying to run queries on tables with about 2 million records. Generally run fine, but we have timeout problems on complex queries, that run OK without a WHERE clause, but timeout when one is added. I created yet another filegroup and set that to default and can get one of the tables with just a single 'where like' clause, but no more without getting the timeout.

    Any ideas?
  2. satya Moderator

    HOw about indexes and timeout settings on server and for query?
    Have you seen any errors during these filegroups allocations.

    Satya SKJ
  3. sqljunkie New Member

    Do you know where the lock timeouts are coming from?
    The SQLServer:Locks object in Performance Monitor has instances that can help you identify where the lock timeouts are coming from. Profiler also has these capabilities. Just out of curiosity, are you doing any GROUP BY or ORDER BY operations?
  4. bradmcgehee New Member

    The usage of filegroups should not affect the performance in such a way a to cause a timeout. You need to be looking in other areas. Have you examined the execution plans of the "complex" queries to see what is happening internally. Your issue of the WHERE clause causing problems is very strange. Generally, the opposite is true. With a proper index, a WHERE clause should significantly reduce resource use and speed use, which prevents timeouts. As rortloff has said, you need to more clearly identify the source of the timeouts.

    Brad M. McGehee
  5. petercor New Member

    I must admit that I am not a SQL Admin guru, so some of the questions I can't answer clearly.

    How do I look at 'execution plans' or the Performance monitor to see what is going on?

    I've not seen any other errors, apart from the timeout which suggests it is an ODBC timeout.
  6. sqljunkie New Member

  7. satya Moderator

    One thing I suggest you to keep yourself informative about SQL Server administration is go thru this website about articles and other tips. Also first hand help is to go thru Books online which is installed along with SQL Server, which I prescribe always in the newsgroups.


    Satya SKJ
  8. royv New Member

    I personally feel it would be better to look at indexes first before profiling, only because indexes in my opinion are a necessity, and they are much easier to set up then profiling. Profiling is also a necessity, don't get me wrong, I would start with indexes first. Excellent tips on this website, never let me down so far.

    "How do you expect to beat me when I am forever?"

Share This Page