SQL Server Performance

Where do I start - Performance Quest

Discussion in 'T-SQL Performance Tuning for Developers' started by exBK, Mar 8, 2004.

  1. exBK New Member

    Sometimes, the server on which my DB resides starts slowing down. At that point, the CPU is at constant 100%. After a while (usually 2-4 hours later), it all start working great.
    The application we have is a DB intense app. Here are a few observations I have:

    * When I look at the number of users connected (when server is slow and fast) is around 100 (sp_who 'app name').
    * Some of the same queries that take about 1-3 seconds take about 3 minutes when everything is slow.
    * I have checked the indexes and they all seem to be OK (I run dbcc ShowContig and then dbcc reIndex if needed)
    * I update the table statistics of a couple of tables regularly that are heavily accessed (insert/updates).
    * The DB has 2 processors and 2GB ram and the current DB size is 1.5GB

    I am not sure what else I should be looking when things slow down? Any tips are greatly appreciated. Thanks for your time.
  2. bambola New Member

    Sounds like you are having lock problems. Try run sp_lock with sp_who2 when everything slow down and see if the problem is there.
    if so there are different things you can do to improve performance (different choice of indexes, using lock hints), depending on the query you are running.
  3. bradmcgehee New Member

    Also, get a Profiler trace of this activity, and see what specific queries are causing the problem, and take a look at each one in depth, seeing what you can do to speed them up.

    Brad M. McGehee, MVP
  4. satya Moderator

  5. ravism New Member

    We had similar problems for about two weeks. And it used to happen everyday between 11AM and 1PM. And perfectly normal behaviour before 11AM and after 1PM. And during the two hours all queries used to take 10 times longer than what it used to during other times. So even with the profiler trace I could not tell which one(s) were causing the problem. Luckly for me I started looking at the releases that were done in the last month and found out that a program was with a query that was changed recently

    this query is a very commonly run query (atleast 10 times a minute) and was accessing 300,000 records with a composite index. But unfortunately it was doing a scan on the index. And the reason why it used to go down between 11 and 1 is becuase of the high usage.

    Hope this helps!!
  6. satya Moderator

    You should also trace from network point of view and see any other process is running during that time.

    Event viewer logs, sQL server error log will be starting point to view for any information.
    What is the significance of that query?

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page