SQL Server Performance Forum – Threads Archive
Wierd index behavior
Hi, I have an SQL 2000 database containing geographic data. The data is imported from a big file, and is only updated with small changes each month. So the data in this database very rarely updated. The performance of this database is very critical to the usability of the application beeing developed (lots of LIKE searches on varchar fields of 255 chars or less) I tuned my queries and indexes, and have reached a very acceptable level of performance, but this performance is of course very dependent on the indexes. I am now experiencing a strange problem : last friday everything went fast, but today (monday bloody monday) performance has gone down by a factor 10. No data had changed in the database so i saw no real need to execute some "update statistics" commands, but i was in paranoia mode so i did it anyway. And to my surprise, the performance was back at its previous level. How can this be? I thought updating the statistics of the indexes would only seem appropriate after alot of data had changed on a database with "Auto update statistics" disabled. But in my case, the auto-update option is enabled, and there have been no changes made to the database. I would really really like to know why this happens and how to fix it. Any help on this is welcome … DavyHow about db maint. plan against this database?
How often you schedule DBREINDEX?
Are there any recent changes to SQL & OS, such as service pack etc.? Can an index be created that will represent the majority of data you will be pulling?
How do you process the queries against the database?
Any triggers are involved? During this process if any of the queries are taking time then take help from PROFILER and capture the trace. SUbmit the trace to Index Tuning Wizard for any recommendation Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
>How about db maint. plan against this database?<br /><br />I’m not sure i understand this.<br /><br />>How often you schedule DBREINDEX?<br /><br />No operations are beeing scheduled atm. But it might be a solution.<br /><br />>Are there any recent changes to SQL & OS, such as service pack etc.?<br /><br />I’m quite sure that there were no such updates, but i’ll check just to be sure.<br /><br />>Can an index be created that will represent the majority of data you will be pulling? <br /><br />This is an overview of the fields that are indexed : <br /><br />The primary key index is a compound key on 2 fields : an identifier (int), and a language id (int)<br /><br />The clustered index is on an nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> field that is the name of the geographic entity. I chose to make this field the clustered index because i think the LIKE searches on this field would have the most negative impact on performance.<br /><br />>How do you process the queries against the database?<br /><br />I am executing stored procs using a .Net SqlCommand object, using parameters.<br /><br />>Any triggers are involved?<br /><br />No, no triggers on this DB<br /><br />>During this process if any of the queries are taking time then take help from PROFILER <br />>and capture the trace. SUbmit the trace to Index Tuning Wizard for any recommendation<br /><br />I’ll give this a try when the problem occurs again. The "problem" is that the queries are currently running very fast again, and i don’t know of any way to reproduce the problem of the indexes slowing down.<br /><br />
Database maintenance plans are available to optimize and check data integrity for any database. There is a wizard available from Enterprise Manager to take you forward. Depending upon the query pattern and timing you would gain if DBCC DBREINDEX scheduled atleast once a week or depending upon your investigation. This will take care of updating stats for the tables, but it may be better if you can update stats manually as long as it doesn’t slowup the performance during any live query. At the moment of slow running queries, initiate the PROFILER to get to know the details. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
can you show the query statement used to pull out the data In fact , I had the same problem yesterday, but solved by using a different index, we got 100 times more traffic at that moment, before that the stored procedure runs ok,
also set the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" on the top of stored procedure.
]]>