CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008
Oleg
Oleg

Reputation: 1458

Disable auto creating statistics on table

Is there any possibility to disable auto creating statistics on specific table in database, without disabling auto creating statistics for entire database?

I have a procedure wich written as follow

create proc 
as
create table #someTempTable(many columns, more than 100)
inserting into #someTempTable **always one or two row** 

exec proc1
exec proc2
etc.

proc1, proc2 .. coontains many selects and updates like this:

select .. 
from #someTempTable t
join someOrdinaryTable t2 on ...

update #someTempTable set col1 = somevalue

Profiler shows that before each select server starts collecting stats in #someTempTable, and it takes more than quarter of entire execution of proc. Proc is using in OLPT processing and should works very fast. I want to change this temporary table to table variable(because for table variables server doesn't collect stats) but can't because it lead me to rewrite all this procedures to passing variables between them and all of this legacy code should be retests. I'm searching alternative way how to force server to behave temporary table like table variables in part of collecting stats.

P.S. I'm know that stats is useful thing but in this case it's useless because table alway contains small amount of records.

Upvotes: 4

Views: 8852

Answers (1)

Code Different
Code Different

Reputation: 93161

I assume you know what you are doing. Disabling a statistics is generally a bad idea. Anyhow:

EXEC sp_autostats 'table_name', 'OFF'

More documentation here: https://msdn.microsoft.com/en-us/library/ms188775.aspx.

Edit: OP clarified that he wants to disable statistics for a temp table. Try this:

CREATE TABLE #someTempTable
(
    ID int PRIMARY KEY WITH (STATISTICS_NORECOMPUTE = ON),
    ...other columns...
)

If you don't have a primary key already, use an identity column for a PK.

Upvotes: 5

Related Questions