SQL Server Performance Forum – Threads Archive

Poor performance – might be related to indexes

Hi, I am working on performance issues of an bussiness application on the DB performance issues. Some of the pages gets about 30seconds to be loaded. It was noted that the DB contains tables on which there were duplicate Indexes and some tables which are used in extensive querying were with no indexes for the pimary key or the firld being used in the query.
So a script was written to delete those duplicate indexes and to introduced new indexes. So once this script was run on the DB ,the application performance was enhance quite noticebly and pages were loaded within seconds.
However from the next week the perfomance reduced back to old level. The DBA run a statistis job weekly and I would like to know if this can rollback or mess the newly introduced indexes? Also I learnt through your forum that if the queries include a WHERE clause with "<>" or "is null" or "or", or "like", or if you have any columns that are not highly selective then even though there are indexes this type of queries cause problems preventing any current indexes from being used. So could anyone tell me if that too could be a reason to this problem? I think my problem is bit similar to the discussion "poor index performance – mssql 2005" by MrRobot. I would highly appreciate if anyone can tell me if there is a workaound for the problems arising from "WHERE clause with "<>" or "is null" or "or", or "like", or if you have any columns" thanks

First of all check if you have fragementation which will degrade the peformance.. Check the following some of them are for sql 2000 but they are good for 2005 too.. http://www.sql-server-performance.com/rd_index_fragmentation.asp
http://www.sqlservercentral.com/columnists/chedgate/fragmentation101.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx For Query tuning…
Transact-SQL Query SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/transact_sql.asp
An Introduction to SQL Server Query Tuning
http://www.sql-server-performance.com/rd_query_tuning.asp http://download.microsoft.com/downl…-d92cc12deccc/sqlhol120 – database tuning.doc MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>