Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Location
    Kansas
    Posts
    11

    Unanswered: Performance Drop on small table

    I'm running sql server 2000 enterprise edition on windows 2003 enterprise edition. The server is a dual 3Ghz cpu with 3.75Gb of Ram. The DB runs alone on this machine. No IIS or other client-server apps run on it

    We are a medium sized nonprofit serving at-risk families and their kids.

    We have a small intranet that we've run for about 2 years for our +-300 employees. One table, our largest so far at 142,904 rows and 16 columns, has suddenly slowed considerably in the last few days. It is our Timesheet details table and both employees and their supervisors run queries against. All queries are in Stored Procedures. It's slow enough that supervisors call in and I've noticed timeouts occurring.

    The table has one Primary key and an index on a foreign key to the main timesheet table....am i saying that right?

    It seems pretty odd to me that such a small table would be timing out but I'm not that knowledgable about dbs (yet).

    I first noticed the issue because I make it a habit of checking the event viewer on our web server. Yesterday there was about 10 occurrences and today its doubled...already. We only have 4 pages that query the Timesheet and Timesheet Details tables and provide different functionalities and all of the pages have slowed down considerably.

    We reindexed our tables, a job that runs weekly, and that sped things up considerably and stopped our errors, so far, for today.

    I am hoping that some folks on dbForum who are familiar with troubleshooting and optimization could help me gain some insight into why this would be happening? Is some setting in our db not right that might be causing this kind of fragmentation? Could our indexes be insufficient?

    I'm happy to share info on how these 2 tables are setup, I just don't know how to make much sense of it myself at the moment.

    Thank you for anyhelp you can provide.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I would check if there are any concurrent updates that are blocking the select on that table. SQL Server 2000 will not allow concurrent read and write access to the same row (might even be on page level, but I'm not sure about that)

    I have also seen locks being held on system objects e.g. by doing a CREATE VIEW that would block SELECTs on other tables just because the system tables are blocked.

    If you are subject to blocking can easily be seen with sp_who2 which will list if a session is blocked by another.

    You can also use the SQL Server Profiler to pinpoint the queries that are running slow and then go from there to analyze why they are slow.

  3. #3
    Join Date
    Mar 2005
    Location
    Kansas
    Posts
    11
    Thanks Shammat. I'll try the sp_who2 and see what I come up with.
    Is SQL Profiler something that can be run on a production database during the working day or would it be too big of a performance hit?

    Thanks for the info...good stuff to start my inquiry with!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Kayaker411
    TIs SQL Profiler something that can be run on a production database during the working day or would it be too big of a performance hit?
    Definitely.
    It simply logs the statements that are currently sent to the database. You can filter based on various conditions such as database affected, user and (which is interesting for you) the execution time of the query.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    If you see blocking, just try implementing some with (NOLOCK) hints, or you may need to review your indexing strategy.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by PMASchmed
    If you see blocking, just try implementing some with (NOLOCK) hints
    I wouldn't just recommend that. Blocks are not a bad thing per se that should simply be ignored.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by PMASchmed
    If you see blocking, just try implementing some with (NOLOCK) hints
    If you do, be aware that your transaction will then see non-committed data from other transactions.
    If that is OK with your application, that it might improve the situation

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by shammat
    If you do, be aware that your transaction will then see non-committed data from other transactions.
    If that is OK with your application, that it might improve the situation
    Or in other words "If it is ok that your application might display incorrect data, go ahead!"
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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