SQL Server Performance

SQL2005_DB_Performance_Slow

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Sandeep_Das05, Aug 1, 2009.

  1. Sandeep_Das05 New Member

    hi,
    in sql server 2005 test server one of database running slow, what are options i need to check..??
    thanks
  2. RickNZ New Member

    That's like saying "I don't feel well, what might be wrong?" There are too many possibilities for a short response on a forum to be useful. You could write a book to answer that question.
    If you can provide more details, perhaps a more useful response would be possible:
    What is your schema (table and index definitions)?
    How many rows?
    Which queries are slow?
    What do you mean by "slow"?
    How are your disks/filegroups organized?
  3. rohit2900 Member

    some addition also provide database size, server configuration etc.
  4. Sandeep_Das05 New Member

    hi rick,
    Pls find my answers for your queries.
    What is your schema (table and index definitions)? - its default "dbo"
    How many rows? - well i am asking about whole database not to a particular table average table row is 10,000 and more than aprox 250 tables presents
    Which queries are slow? - all queries
    What do you mean by "slow"? - I mean to say if the same database working fine in my local machine then why it’s not performing well in clients test box and i am unsure about other database performance in the same box.
    How are your disks/filegroups organized? - As its test box, we have default configuration as windows server 2003 no any raid models used and we have 3 data files and 1 log files which placed in same drive.(E:)
    Pls advice,
    hi rohit,
    Pls find my answer to your question additionally
    some addition also provide database size, server configuration etc. - Database size is 10 GB and no special configuration for the test server.
    Pls advice on the same,
    thanks.
  5. rohit2900 Member

    Hello Sandeep
    [quote user="Sandeep_Das05"]
    What do you mean by "slow"? - I mean to say if the same database working fine in my local machine then why it’s not performing well in clients test box and i am unsure about other database performance in the same box.
    [/quote]
    Both the db's are same..!!!
    Is one db copy of other if yes then.. no need to check the constraints etc.... as they should be same... What is the difference between the configuration of both the machines. (Although its not a big database but trying to understand the issue). You test box is a normal P4 system.. I wanted to know the physical configuration of the machine in terms or CPU & RAM etc.
  6. Sandeep_Das05 New Member

    hi rohit
    Both databases are same in all respect...Yes, the machine configuration are not same. In test server we have 4 GB ram and P4 Core2deo 2.80 GHz CPU. where in production it’s much higher but in local system it’s less configuration that test/dev server.
    hope you are clear now
    thanks
  7. RickNZ New Member

    The fact that you have log files on the same drive as data files will explain why write operations are slow.
    Regarding read operations, you might try executing the following commands on both machines:
    checkpoint
    dbcc dropcleanbuffers
    set statistics time on
    Then run one of the slow queries, and compare the results.
    If the results are similar, there are two factors that might explain performance differences:
    1. Different workload on the two machines. Maybe one of them is running a bunch of services or apps that the other one isn't? If the E: drive is being used by one of the other apps, it could result in a massive slowdown.
    2. Data caching. It might be that the systems are actually performing about the same once the data needed by the queries is in RAM. The initial cache warm-up time on some systems can be lengthy.
    You might also want to defragment the disk of the slower machine. Fragmented filesystems are another cause of order-of-magnitude performance differences.
  8. Sandeep_Das05 New Member

    hi rick
    i can give a try to your points...as per my knowledge i think we have not more loads in test server...can you just roughly tell me one thing may be sounds not good. what system configuration needs to setup sql server 2005/2008 server to give best performance..
    thanks
  9. RickNZ New Member

    As I said, one thing that will affect performance in a very significant way is to move your database log onto its own disk. Having the log on the same drive as your data is a sure-fire recipe for poor performance.
    After that, make sure your system has enough RAM.
    Next, make sure that your database data file is defragmented. Ideally, both it and the log should be on separate disks, by themselves.
    Make sure that both the data file and the log are set with sizes large enough that they aren't frequently auto-growing.
    Then, have a look at your queries and your indexes. Look at some query plans. Add indexes to help avoid table and index scans. Avoid constructs such as LIKE '%keyword%' that force a table scan.
    Use batched commands to minimize round trips. Execute multiple CRUD batches in a single transaction when you can.
    Hopefully that's enough to get you started....
  10. Sandeep_Das05 New Member

    thank you rick....i need to check the above option too....

Share This Page