SQL Server Performance Forum – Threads Archive

Slow query response after changing WHERE values

Hi, Initially I have a fast running query, but right after changing the datetime values in the WHERE clause, it’ll take forever to get the results. After reindexing the clustered index of the table, it’ll be fast again.
It’s happening quite often.
What’s happening? Need help! Raymond

Then ensure to update stats on this table frequently and also compile involved stored procedures. If the table has frequent updates and inserts its better to reschedule the DBCC DBREINDEX in order to gain the performance. Can take help of PROFILER for the slow running queries and help of index tuning wizard. 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.

Using QA to see the query execution plan, i see a lot of clustered index scans, even though i’ve created non-clustered indexes on the columns specified in the WHERE clause. Is this a good thing? Raymond

How often you rebuild the indexes?
http://www.sql-server-performance.com/mr_indexing.asp &http://www.sql-server-performance.com/nonclustered_indexes.asp for information. 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.

Clustered index scan is actually a table scan. You would usualy want to avoid it. It seems that optimizer is choosing it over the use of the non clustered index for some reason. rebuilding indexes and updating statistics could help solve this issue. you could schedule a job to do it on a regular basis. otherwise post some code we might be able to help more.

Also see if there is any hyphoteticals Indexs. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The tables with the clustered index scans, how many rows do they contain?

I’ve checked, there are no hypothetical indexes. The tables which has clustered index scans, typically has a range from a few hundred thousand rows to about 2 million. It’s a fairly new production database, but it’s already grown to 20GB in 2 months… hard for me to obtain downtime to rebuild the indexes but production runs 24×7… The thing is, sometimes this query takes less than 2 minutes to generate the results,
and when i change the date values in the WHERE clause, it’ll take more than 30 minutes! Raymond

Can check the execution plans before and after applying UPDATE STATS on the involved tables, for assesment. If you cannot obtain window for DBCC DBREINDEX then atleast run UPDATE STATS during less hour traffic on the database, and is there any chance of archiving rows from the database for the case of performance and historic data queries can be held on the other database. HTH 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.

Will updating the stats cause blocking on select/update/insert queries? Raymond

Not really but its better to run during less traffic hours on the database. http://www.sql-server-performance.com/statistics.asp for information. 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.

Depends what kind of update you will use. If option will With Fullscan or 100% via job, performance will down may be with blocking. If you use standard options (10%) no problem, but allways is better in less traffic hours.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>