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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pinformaticien@yahoo.fr - 17 Mar 2008 18:59 GMT

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.

Reply to this Message

Jason Massie - 17 Mar 2008 19:45 GMT

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.

Signature

Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

> Hello,
>
[quoted text clipped - 48 lines]
>
> Thanks.

Reply to this Message

pinformaticien@yahoo.fr - 17 Mar 2008 21:10 GMT

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?

Reply to this Message

jason - 18 Mar 2008 03:50 GMT

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.

Signature

Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio

> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
[quoted text clipped - 23 lines]
> Is it possible to disable "Auto Create Statistics" on a specific
> table?

Reply to this Message

pinformaticien@yahoo.fr - 18 Mar 2008 11:12 GMT

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.

Reply to this Message

pinformaticien@yahoo.fr - 18 Mar 2008 16:34 GMT

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

Reply to this Message

Gert-Jan Strik - 23 Mar 2008 13:01 GMT

> Ok I think I've got an interesting workaround. As we cannot disable
> autocreate statistics for a specific table, the idea is to update
[quoted text clipped - 30 lines]
> CLOSE c
> DEALLOCATE c

Auto create statistics is only invoked if there are no existing
statistics for that column. So another solution to prevent automatic
statistics creation for a particular column is to manually create
statistics before the query is run that triggers the statistics
creation.

It could be as simple as:

create statistics ST_Test on dbo.test(my_column) with sample 0 rows,
norecompute

before running a query like

select * from dbo.test where my_column = 5

Signature

Gert-Jan

Reply to this Message