New answers tagged

0

I had a very similar issue in Oracle 12c. In experimenting, the PRECOMPUTE_SUBQUERY optimizer hint 'forced' the subquery to execute first, thus making use of the defined indexes. In your snippet, that would look like this: ... where t.ObjectId = 12345 or t.Id in ( select /*+ PRECOMPUTE_SUBQUERY */ TaskId from ObjectAffectingTasks where ...


10

I wonder if this imbalance of the number of rows maybe a result of the Timeout Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent optimizer transformations may require new estimates to be computed. There is no general rule to say which of the estimates will be more "accurate". See this ...


6

They're not redundant, no. Each statistics object includes a histogram of the values stored in the first listed column. For the remaining columns, only density information is kept at each level (no histogram). For example, the proposed statistic on (ClassID, SegmentID, GroupID, Tier1, LanguageID, Active) would create: A histogram for ClassID values A ...


2

Other than the DTA, is there any other way to find out the missing multicolumn statistics? Nope ! query optimizer assumes that columns within the same table are independent. Joe Sack talks about it here The query optimizer assumes that columns within the same table are independent. For example, if you have a city and state column, we may ...


3

I think you will be much better off testing your actual workload under sync and async, and see if you observe any differences. Coming up with a sweet spot to suit a particular feature is kind of backwards. Generally, async is primarily beneficial when you don't care if the query that triggered the stats update still runs that last time with the old stats ...


0

Balazs Papp was right. I needed to specify table name in upper case although create command contained only lower case.


4

Please note that a computer generating a list of "missing indexes" should not be swallowed whole. You will still need to decide which indexes to create, which recommended indexes are near duplicates of existing indexes, and how you should want to handle those issues. It still requires you making a decision since the generated recommendations need some ...


7

Check the View Tuning Output: If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu. As always review and test the recommendations before blindly applying them to your PROD environment. I would highly suggest to look ...


6

How come I have so many statistics to create, since my database has auto create stats on? Automatic statistics can only create single column statistics objects. The statistics suggested by DTA are all multi-column statistics that capture potentially-useful (though limited) correlation information. For details see the Statistics documentation. How ...


3

Isn't "everything" that happens in Primary replica, replicated to the Secondaries? Yes, stats created on Primary replica will replicate to secondaries. The secondary is a strict replica (copy) of primary database. So, the stats are created in tempdb linked with the readonly database. SQL server will maintain statistics of read-only secondary databases ...


3

Isn't "everything" that happens in Primary replica, replicated to the Secondaries? If so, statistics changes that happen in Primary would be replicated into secondary isn't it? You are correct. Can you please help me understand why there is a need for SQL Server to maintain statistics for DBs that are part of Always On availability group in tempdb? ...



Top 50 recent answers are included