"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 be exactly the same (same aspect, same costs). I also tried
to enable the Async stats 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.
We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> 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 be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats 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.
|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and 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 created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following 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 confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?
|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
... WITH NORECOMPUTE.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and 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 created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following 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 confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?
|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.
|||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