SQL Server - Auto Update Statistics

If I disable this option database wide, do I have to manually disable this
option on tables?

Thanks in advance

  Linchi Shea replied to RG

No.

Linchi

Server Management Studio I open the Connect to Server box with these values: Server Type: "Database Engine"; Server name: "ICHIBAN \ SQLEXPRESS" which is the correct name for the computer and the No user action is required. 2008-12-27 11:28:20.90 spid7s Starting up database 'master'. 2008-12-27 11:28:21.09 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required. 00000003 2008 was started by login "sa". 2008-12-27 11:28:21.24 spid7s Starting up database 'mssqlsystemresource'. 2008-12-27 11:28:21.28 spid7s The resource database build version is 10. 00.1600. This is an informational message only. No user action is required. 2008-12-27 11:28:21.57 spid15s Starting up database 'model'. 2008-12-27 11:28:21.57 spid7s Server name is 'ICHIBAN \ SQLEXPRESS'. This functionality will not be available. 2008-12-27 11:28:21.81 spid7s Starting up database 'msdb'. 2008-12-27 11:28:21.87 spid15s Clearing tempdb database. 2008-12-27 11:28:22.23 Server A self-generated certificate was successfully loaded

NULL THEN @PeriodEnd ELSE [Austritt_Kostenstelle] END GROUP BY Kostenstelle.Abteilung_ID - -- --SNIP- -- -- This works perfectly on database servers 2000 and 2005! Okay, let's execute this command with sp_executesql: - -- --SNIP- -- -- declare @PeriodBegin sp_executesql structure. So, I'm running into heavy performance issues which break down our current database solution. :( Sounds like you might have a bad plan cached. Can you try adding OPTION QueryPlan CachedPlanSize = "94" CompileTime = "321" CompileCPU = "306" CompileMemory = "1384"> <MissingIndexes> <MissingIndexGroup Impact = "8.43038"> <MissingIndex Database = "[KVP_DB]" Schema = "[dbo]" Table = "[Sitzung]"> <ColumnGroup Usage = "INEQUALITY"> <Column Name = "[Dauer]" ColumnId = "7" / > < / ColumnGroup> <ColumnGroup 0.0399058" AvgRowSize = "17" EstimatedTotalSubtreeCost = "0.76535" Parallel = "0" EstimateRebinds = "0" EstimateRewinds = "0"> <OutputList> <ColumnReference Database = "[KVP_DB]" Schema = "[dbo]" Table = "[Kostenstelle]" Column = "Abteilung_ID" / > <ColumnReference Column = "Expr1018" / > < / OutputList> <MemoryFractions Input = "0" Output ScalarOperator ScalarString = "SUM([KVP_DB].[dbo].[Sitzung].[Dauer])"> <Aggregate Distinct = "0" AggType = "SUM"> <ScalarOperator> <Identifier> <ColumnReference Database = "[KVP_DB]" Schema = "[dbo]" Table = "[Sitzung]" Column = "Dauer" / > < / Identifier> < / ScalarOperator> < / Aggregate> < / ScalarOperator> < / DefinedValue> < / DefinedValues> <HashKeysBuild> <ColumnReference Database = "[KVP_DB]" Schema = "[dbo]" Table = "[Kostenstelle]" Column = "Abteilung_ID" / > < / HashKeysBuild> <RelOp NodeId = "1" PhysicalOp = "Hash Match" LogicalOp 0.242801" AvgRowSize = "17" EstimatedTotalSubtreeCost = "0.725445" Parallel = "0" EstimateRebinds = "0" EstimateRewinds = "0"> <OutputList> <ColumnReference Database = "[KVP_DB]" Schema = "[dbo]" Table = "[Sitzung]" Column = "Dauer" / > <ColumnReference Database = "[KVP_DB]" Schema = "[dbo]" Table = "[Kostenstelle]" Column = "Abteilung_ID" / > < / OutputList> <MemoryFractions Input = "0.746269" Output = "0.746269

to initialize Distributed Transaction Coordinator. 2007-11-15 03:13:24.89 spid4 Starting up database 'master'. 2007-11-15 03:13:26.89 server Using 'SSNETLIB.DLL' version '8.0.2039'. 2007-11-15 03:13:26.89 spid5 Starting up database 'model'. 2007-11-15 03:13:27.06 spid4 Server name is 'BUSSERVER'. 2007-11 15 03:13:27.10 spid8 Starting up database 'msdb'. 2007-11-15 03:13:27.23 spid9 Starting up database 'pubs'. 2007-11-15 03:13:27.23 spid10 Starting up database 'Northwind'. 2007-11-15 03:13:27.23 spid11 Starting up database 'BUS'. 2007-11-15 03:13:27.40 spid12 Starting up database 'LogDB_Main'. 2007-11-15 03:13:27.53 spid13 Starting up database 'POI_Polet'. 2007-11-15 03:13:27.67 spid14 Starting up database 'FleetAdminDB_Main'. 2007-11

be greatly appreciated. Thanks in advance. Server: Msg 8999, Level 16, State 1, Line 1 Database tempdb allocation errors prevent further CHECKDB processing. Server: Msg 8906, Level 16, State 1, Line 1 Page (1:87) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was ALLOCATED 0_PCT_FULL'. Server: Msg 8906, Level 16, State 1, Line 1 Page (1:103) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was ALLOCATED 0_PCT_FULL'. Server: Msg 8906, Level 16, State 1, Line 1 Page (1:104) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was ALLOCATED 0_PCT_FULL'. Server: Msg 8906, Level 16, State 1, Line 1 Page (1:107) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was ALLOCATED 0_PCT_FULL'. Server: Msg 8906, Level 16, State 1, Line 1 Page (1:109) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was ALLOCATED 0_PCT_FULL'. Server: Msg 8906, Level 16, State 1, Line 1 Page (1:111) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was ALLOCATED 0_PCT_FULL'. Server: Msg 8906, Level 16, State 1, Line 1 Page (1:114) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was

example in the article. I then reproduced the experiment of your article in the AdventureWorks database and ran the query Q1, making sure that all indices for the xml columns had Schema = 3D"[dbo]" Table = 3D"[AttributesNameValue]" Alias = 3D"[ANV]" Column = 3D"AttributeId" / > NoExpandHint = 3D"0"> Database = 3D"[AdventureWorks]" Schema = 3D"[dbo]" Table = 3D"[AttributesNameValue]" Alias = 3D"[ANV]" Column = 3D"AttributeId" / > Schema 3D"14" EstimatedTotalSubtreeCost = 3D"31.7678" Parallel = 3D"1" EstimateRebinds = 3D"19971" EstimateRewinds = 3D"0"> Database = 3D"[AdventureWorks]" Schema = 3D"[sys]" Table = 3D"[xml_index_nodes_2091154495_32000]" Alias = 3D"[attribute:1]" Column = 3D"id" / > Database = 3D"[AdventureWorks]" Schema = 3D"[sys]" Table = 3D"[xml_index_nodes_2091154495_32000]" Alias = 3D"[value:4]" Column = 3D"id Database = 3D"[AdventureWorks]" Schema = 3D"[sys]" Table = 3D"[xml_index_nodes_2091154495_32000]" Alias = 3D"[value:4]" Column = 3D"pk1 3D"23" EstimatedTotalSubtreeCost = 3D"24.5263" Parallel = 3D"1" EstimateRebinds = 3D"19971" EstimateRewinds = 3D"0"> Database = 3D"[AdventureWorks]" Schema = 3D"[sys]" Table = 3D"[xml_index_nodes_2091154495_32000]" Alias = 3D"[attribute:1]" Column = 3D"id" / > Database = 3D"[AdventureWorks]" Schema = 3D"[sys]" Table = 3D"[xml_index_nodes_2091154495_32000]" Alias = 3D"[value:4]" Column = 3D"id Database = 3D"[AdventureWorks]" Schema = 3D"[sys]" Table = 3D"[xml_index_nodes_2091154495_32000]" Alias = 3D"[value:4]" Column = 3D"pk1