General discussion

  • Creator
    Topic
  • #2176828

    Sql-Sever 2000 DBA Help

    Locked

    by singh_surjit ·

    Their is a specific problem in my ms-sqlsever 2000
    database as i am having 3 Big tables A,B,C and each table contains around 1 million record and all 3 tables contains clustered index which is always incremented and apart of that their are unclustered index also on each tables. My system is OLTP where lot of insertions and selections of data through asp page take place. Each single entry of record adds records in all the 3 tables.
    But at times their are lot of timeout errors in select queries and even in the insert queries. On running sp_who2 i had found that some select queries are blocking insert operation. And Insert
    queries are blocking other select queries thus
    resulting in lot of timeouts. Earlier the clustered index was not incremental and thus sometimes i had to face chainlinkage error then i changed the clusted index in all the three tables
    to other key but then queries start executing very slow and so i revert back my clustered index
    to the previous key with the change that clustered index will be always incremental. Can
    anyone suggest what need to be done to improve the situation.

All Comments

  • Author
    Replies
    • #3330293

      Reply To: Sql-Sever 2000 DBA Help

      by jaqui ·

      In reply to Sql-Sever 2000 DBA Help

      renormalise the structure.
      the rebild the entire db and site to fit new db structure.

      an index for entire system in one table.
      data tables have a link field that contains the index data key for the actual index table.

      most common problem with indexes and speed is the normalisation of the data isn’t as effective as it needs to be.

    • #3233607

      Reply To: Sql-Sever 2000 DBA Help

      by faith_michele ·

      In reply to Sql-Sever 2000 DBA Help

      I might be wrong but it seems like you do not have a middle tier in place. Check out these tips for optimizing the application design.

      http://www.databasejournal.com/features/mssql/article.php/10894_1459801_6

      Here are some locking tips.

      http://www.sql-server-performance.com/reducing_locks_hints.asp

      I cannot attest to ASP because I learned with ASP.NET. What I remember was that there should be a way for the data to be checked prior to sending it to the database. Also, the user should have a means of canceling a transaction.

      If you analyze the data for reporting, something as simple as Data Transformation Services (DTS) can be used to schedule the updates and deletions to the database during low usage periods. That will free up resources for ETL.

      http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dts_overview.asp

      You might try setting up a test copy to work on and troubleshoot prior to implementing any changes on a large scale.

      I hope that this helps or at least gives you some other options to consider. There are many articles and books that cover SQL Server optimization.

    • #3233595

      Reply To: Sql-Sever 2000 DBA Help

      by singh_surjit ·

      In reply to Sql-Sever 2000 DBA Help

      Please my database is normalise and problem is that inserts are getting blocked due to select statements. And all the columns have appropriate indexes where ever required.
      i had used readpast in select statements but that is also causing inserts to blocks. Please I Replay which is Practical and logical. And searching is done and it is orderby createddate desc. I want what need to be done in case any optimizer hint which does not block my inserts. Their is nolock hint but it will read dirty buffer and can corrupt data or indexes.

Viewing 2 reply threads