Networks·41,398 discussions

Sql-Sever 2000 DBA Help

Locked

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.

Topic

Reply To: Sql-Sever 2000 DBA Help

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.

Reply To: Sql-Sever 2000 DBA Help

In reply to Reply To: Sql-Sever 2000 DBA Help

Poster rated this answer.

Reply To: Sql-Sever 2000 DBA Help

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.

Reply To: Sql-Sever 2000 DBA Help

In reply to Reply To: Sql-Sever 2000 DBA Help

Poster rated this answer.

Reply To: Sql-Sever 2000 DBA Help

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.

[Author]

Replies