Muhammad, yes I am running this using osql as well a through our application. Unfortunately this is SS 2000. It runs fast after the initial call.

Jeffrey, this is as I expected. Would you have some SQL I could use to check the buffer cache hit ratio, page life expectancy and whatever is necessary? ...

Jeffrey, what I mean is I am running it for the first time logging in. The server has been up for over 35 days and remains up. I...

Sorry, I thought it was a syntax error as I stated above, but it was really a sqlcmd -v usage error. It now works. Thank you all for...

Okay, this could be the answer if I can get the correct syntax. Here is the table DDL but it is giving an error:

create table outlet

(

...

Gail, in Oracle it is built into SQL*Plus. I guess SQLCMD does not provide that functionality.

Here is the DDL. Thank you.

Lynn, this query is actually generated dynamically through a stored procedure based on input from the application. I have attached the query plan.

Mr. Kincaid,

there are over 10 million rows in the tables. When forced to use the correct indexes, the LIKE clause poses no problems. The results return immediately.

The current collation is Latin1_General_BIN. Would there be any benefit in changing this possibly atthe column level? If so, to what? Also, you will notice in the...

Sorry for the delay in providing this. I have attached the DDL.

Would collation setting have anything to do with this type of performance issues?

Good point! I did re-write the query but it made no difference. Here is the query with the index hints which works well. It still needs the...

What would cause the SS optimizer to choose the wrong plan. The same query with index hints runs far better that the path taken by SS. Here are...

The zipped query plans are attached. The query with the like in it work well sporatically but the query with the substring works well all the time. Thank...