"Auto Create Statistics" make queries run (really) slower

Hello,

I'm experiencing a strange problem with query performance runing on SQL2005. The database has 10+ tables, but we need to run really specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query is unique (adhoc plan), and not parameterizable. (we cannot optimize this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a duplicate column to optimize some search stuff, like putting everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that table
- the nvarchar content of the table changes only once a day. It means we do all optimization (indexes / stats) just after the update, and there is no change on nvarchar data until the next update (24 hours later)
I found that when "Auto Create Statistics" is enabled on the database, that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats deleted : 7 min to run the same queries

It means that queries are running 8x slower when "Auto Create Statistics" is enabled!
Another interesting point : just after disabling "Auto Create Statistics", the queries continue to perform slowly until I manually delete all statistics created automatically for that table (the one begining with "_WA_Sys_"). It could mean that it's not a stat creation issue, but only the existence of that statistics that could change the query plan. But in both cases, the execution plan for the same query seems to beexactly the same (same aspect, same costs). I also tried to enable the Asyncstats update : no change.

The problem is that for all the other tables in the database, the "Auto Create Statistics" is a good thing and useful. But not for that specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of performance drop?

Thanks.

Thanks for the reply.Yes, it's an upgrade from SQL2000, but all indexes and statistics have been recreated / optimized since. The problem also appear on a fresh install of SQL2005, with newly created table / data / indexes / statistics.
My bet on that issue would be the following :
- As all queries on that table are complex, unique, and using a lot of LIKE statement, anyway each plan is adhoc, and can never be re-used. The plan almost always ends with a table scan.
- When auto create stats is enabled, SQL creates a lot of stats on columns that cannot be useful for the query optimization (especially nvarchar). When a query comes, SQL try to determine the best plan, look to these stats, but can't find any optimization compared to the manually created (indexes). The time trying to optimize the query (and more specifically looking to the stats) is lost, because the query plan won't be used a second time (as all queries use adhoc plans), and auto created stats don't allow any optimization on the query.
I will have a look to your link, and try to run some tests on a dedicated test server.

Ok here are some interesting results : I ran 2 times 10 queries, first time with "Auto Create Statistics" enabled, second time with "Auto Create Statistics" disabled. Between the 2 tests, I deleted all the automatically create statistics, then restarted SQL server service. Here are the results for the query
Select * from sys.dm_exec_query_optimizer_info where counter in ('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It could confirm that all the performance drop goes in optimization time (2.8 sec average vs 0.07), that finally almost doesn't otimize anything in my case. Ok now I need to find a way to disable / reduce that optimization time when "Auto Create Statistics" is enabled. Any idea?

Ok I think I've got an interesting workaround. As we cannot disable autocreate statistics for a specific table, the idea is to update those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate them later

Here is the SQL statement I wrote to do this automatically on SQL 2005 (you just need to set @dbtname correctly). It's just necessary to run it from time to time, to ensure that new autocreated stats are disabled. The first tests shows exactly the same performance compared to queries with "auto create stats" disabled.

DECLARE @dbtname NVARCHAR(255)
SET @dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@dbtname) AND auto_created = 1
DECLARE @statname NVARCHAR(255)

OPEN c
FETCH next FROM c INTO @statname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @statname
EXEC ('UPDATE STATISTICS ' + @dbtname + ' (' + @statname + ') WITH SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @statname
END

CLOSE c
DEALLOCATE c