SQL Server 2017 introduces a very helpful enhancement to the showplan to see which statistics were used to generate a plan: https://learn.microsoft.com/en-nz/archive/blogs/sql_server_team/sql-server-2017-showplan-enhancements However, I can't find it in my execution plan. I have the following query on the StackOverflow database:

Use StackOverflow2010;

DROP TABLE IF EXISTS #tempPosts;
CREATE TABLE #tempPosts(
	Id int
)

INSERT INTO #tempPosts
SELECT ID FROM dbo.Posts
WHERE OwnerUserId = 26837

SELECT Title, u.DisplayName, pt.Type FROM dbo.Posts p
INNER JOIN #tempPosts temp
ON p.Id = temp.Id
INNER JOIN dbo.Users u
ON p.OwnerUserId = u.Id
INNER JOIN dbo.PostTypes pt
ON p.PostTypeId = pt.Id
OPTION(RECOMPILE)

I turned on the Include Actual Execution Plan to capture the plan and could not find OptimizerStatsUsage field in the plan: What could be the reason for OptimizerStatsUsage not showing up in my execution plan? Is there any additional configuration or step needed to see this property? Thank you for any insights!

I am troubleshooting a performance issue on a job that is taking 5h longer to complete on SQL Server 2019 compared to when it ran on 2012. One of the problematic steps of the job is during a batch load into a Operational Data Store type of database (ODS) which gets data from different sources and inserts into a table that is truncated every night and reloaded, the indexes are disabled before the load and they are rebuilt in the end one by one and by name, not with rebuild all. Even though there are four disabled indexes in the table, I saw that their associated statistics are still getting updated daily: Statistic | Statistic Type | is_disabled | Stats Last Updated | Rows | Rows Sampled | Unfiltered Rows | Row Modifications | Histogram Steps --------- | -------------- | ----------- | ------------------ | ---- | ------------ | ----------------- | ----------------- | --------------- IX_1|Index Statistic|Yes|2023-07-27 03:07:00.4566667|238634988|617299|238634988|4963|3 IX_2|Index Statistic|Yes|2023-07-27 03:07:24.8866667|238634988|617299|238634988|4963|200 IX_3|Index Statistic|Yes|2023-07-27 08:29:01.5266667|238634988|617290|238634988|4963|199 IX_4|Index Statistic|Yes|2023-07-27 03:07:34.0233333|238634988|617299|238634988|4963|192 Furthermore I also found out the execution plan of some queries consider these statistics from the disabled indexes, is this normal?

Due to nature of this database which truncates and loads tables every day should I turn off the AUTO_UPDATE_STATISTICS either for the whole database or for some of the tables ? When I query the sys.dm_db_stats_properties there are many auto-created statistics with more than 200 million row modifications. Any inputs will be appreciated, thanks!

I have a statistic on a Primary Key column in a table. When I update the statistic with the default options: UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350 I get a histogram as follows (abridged) RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ----------------------------------------------------------------------------------------------------------------------------------------------------------------- 3400002201 0 1 0 1 3400009992 18103.04 1 7790 2.323882 3400040033 26083.68 1 26080 1.000144 3400050456 13029.09 1 10422 1.250153 3400087676 26083.68 1 26080 1.000144 3400103858 19556.38 1 16181 1.208602 3400126866 13029.09 1 13029 1 3400162832 39138.27 1 35965 1.088232 3400213115 45665.56 1 45641 1.000547 3400238444 26083.68 1 25328 1.029836 3400242626 13029.09 1 4181 3.116262 3400262174 19556.38 1 19547 1.00048 3400283983 26083.68 1 21808 1.19606 3400304837 19556.38 1 19556 1 3400316046 13029.09 1 11208 1.162481 3400346666 13029.09 1 13029 1 3400368443 19556.38 1 19556 1 3400385634 26083.68 1 17190 1.517375 3400390548 13029.09 1 4913 2.651962 3400398297 13029.09 1 7748 1.681607 3400417467 13029.09 1 13029 1 3400428728 13029.09 1 11260 1.157113 3400462206 32610.97 1 32600 1.000332 3400477978 13029.09 1 13029 1 3400492969 19556.38 1 14990 1.304629 3400507579 13029.09 1 13029 1 3400529627 32610.97 1 22047 1.479157 3400535909 13029.09 1 6281 2.074366 3400556632 26083.68 1 20722 1.258743 3400576037 19556.38 1 19404 1.007853 3400588565 19556.38 1 12527 1.561139 3400630507 39138.27 1 39120 1.000457 3400655236 19556.38 1 19556 1 3400670940 19556.38 1 15703 1.245392 3400691760 19556.38 1 19556 1 3400701959 19556.38 1 10198 1.917668 3400718913 19556.38 1 16953 1.153565 3400745176 19556.38 1 19556 1 If we look at the Hi Key 3400009992, the histogram tells us: There is one row equal to this value There are 18,103 rows where the value > 3400002201 and < 3400009992, however, of these 18,103, only 7,790 are distinct. How can this be? A primary key must be unique If I update the statistic with FULLSCAN, I get the histogram below (complete) which seems to accurately represent the data RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 3400000000 0 1 0 1 3401474769 1474758 1 1474758 1 600004687218 16383 1 16383 1 600005089447 65535 1 65535 1 600005665352 98303 1 98303 1 600006294532 81919 1 81919 1 600008729190 294911 1 294911 1 600012125564 425983 1 425983 1 600014952842 376831 1 376831 1 600017609236 344063 1 344063 1 600017776836 24575 1 24575 1 600022385710 598015 1 598015 1 600022698873 38234 1 38234 1 600022698878 0 1 0 1 Why doesn't SQL server's sampled histogram represent the uniqueness of the primary key?

We've noticed on one of our servers, that after some time queries are falling to full table scan, seems due the outdated statistics. SQL Server is quite busy, receiving hundreds of queries/row modifications per minute, can it be a reason, why statistics are not updated? We noticed, that if we stop the app, statistics get updated, hence wondering if there might be a case that SQL Server does not find a window or something to update statistics, or that is not possible and there's another reason hiding? Manually triggering statistics update works, but I would like to avoid scheduling periodic statistics update if possible, as I did not find information that statistics update would be prevented because of stream of selects/inserts/updates. Update: Auto statistics update is enabled

Postgres collects index usage statistics in pg_catalog.pg_stat_all_indexes. Does anyone know if those statistics are identical across master and replica nodes when using “physical” wal replication?

In my tables, I have many **statistics** with -1 and I want to know how can I enforce DB2 to calculate these values.

Select FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD
    FROM SYSCAT.INDEXES
    WHERE (TABSCHEMA = SCHEMA_NAME) AND
          (TABNAME   = TABLE_NAME)

My collection statistics look like Automatic maintenance (AUTO_MAINT) = OFF Automatic database backup (AUTO_DB_BACKUP) = OFF Automatic table maintenance (AUTO_TBL_MAINT) = OFF Automatic runstats (AUTO_RUNSTATS) = OFF Real-time statistics (AUTO_STMT_STATS) = OFF Statistical views (AUTO_STATS_VIEWS) = OFF Automatic sampling (AUTO_SAMPLING) = OFF Automatic column group statistics (AUTO_CG_STATS) = OFF Automatic reorganization (AUTO_REORG) = OFF

Postgresql 9.3 Debian 7 I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they are not used at all. Is there any usage statistics somewhere or some trick query to do that?

Microsoft describes the [user_lookup] counter in dm_db_index_usage_stats as the 'Number of bookmark lookups by user queries.' It describes the [singleton_lookup_count] in dm_db_index_operational_stats as the 'Cumulative count of single row retrievals from the index or heap.' This is not at all clarifying to me. Could someone provide a clearer definition/differentiation of what these two values are tracking? The question stems from an index observation (800m row table) today where the current [user_lookup] value on the Cx was around 800 and the [singleton_lookup_count] was over 1 billion. They're obviously counting much different things and I need to understand what they are and whether a difference of that magnitude could be indicative of a problem.

Index statistics are utilized by sql server to derive the best plan for query execution. Suppose I update the index statistics then can existing queries use the effect of updated statistics or does sql server need to be restarted so that the cached plans are reset?

I have a table like this: create TABLE dbo.MyTable ( TMyTableID bigint NOT NULL IDENTITY(1, 1), LogArgumentID bigint NOT null, LogID bigint NOT NULL, LogTextID int NOT NULL, FloatValue float NULL, Filler varchar(500) null, EXTExtractJobID bigint not null ) ON Standard WITH ( DATA_COMPRESSION = PAGE ) GO CREATE UNIQUE CLUSTERED INDEX ix_LogID_LogArgumentID ON dbo.MyTable (LogID, LogArgumentID, TMyTableID DESC) WITH (DATA_COMPRESSION = PAGE) ON Standard GO ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (LogArgumentID) WITH (DATA_COMPRESSION = PAGE) ON Standard GO CREATE NONCLUSTERED INDEX EXTExtractJobID ON dbo.MyTable (EXTExtractJobID, LogArgumentID) WITH (DATA_COMPRESSION = PAGE) ON Standard GO with about 75 billion rows. When I run a query similar to this: select logid, max(case when logTextID =1 then floatvalue end) as MaxFloatValue from dbo.MyTable as M where TMyTableID > cast(12300000000 as bigint) and TMyTableID > cast(12350000000 as bigint) group by M.logid I get a plan like this: The estimates out of the Clustered Index Seek is about right, but the estimate from the Stream Aggregate is 100x off the real data.

I'm using MySQL 5.7 How can I run ANALYZE TABLE for all tables in a database. It should work if new tables are added to the database in future. This is to refresh the statistics of table indexes.

I've just inherited about 20 instances of SQL Server, as part of a wider acquisition project. I'm in the process of assessing performance and I don't like the way maintenance plans have been implemented. I'm seeing daily blanket index rebuilds (I can deal with this one) and also daily manual updating of statistics. Around half of the databases have been set to Auto Update Statistics = False, for reasons which are not clear other than I am told it is to reduce 'Performance Issues'... I always thought, and worked to, best practice of setting this to True and felt the Manual Update was not necessary if this setting was True. Am I wrong? Can anyone explain what the benefit would be in having this set as False, but doing a daily manual update instead? I should mention that some of the databases are highly transactional (millions of Inserts, Deletes, Updates per day) Others are low in terms of transaction rates, and some are all but read-only. There is no rhyme or reason though as to which have the Auto Update setting set to False. It appears to be a lottery.

I just learned that a client company I work for has decided to keep the auto update statistics options off for some of their SQL Servers, and the DBAs manually troubleshooting performance issues when they arise. However, this kind of does of not make sense to me. Why would you want to prevent the statistics from being updated?

We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the outcomes of the incident was that we were to consider disabling AUTO_UPDATE_STATISTICS, allowing for the the overnight maintenance jobs to handle statistics updates, and keep stats static during the day. The arguement for disabling statistics updates is that it will ensure that we retain the same consistent plans. By default my preference is not to do that, and more proactively monitor memory grants, and memory used, using query store and/or extended events, and to alert when significant issues occur. I had looked around and couldn't really find any instances where people had blogged/posted about disabling this option and the sucesses of it. My question is are the any noted instances that people have of success of disabling statistics/ please can people relay successful implementations of doing so? SQL Version: 2016 Standard EE

I was looking at the tables (**pg_stat_user_indexes** and **pg_stat_user_tables**) and discovered many indices that are not being used. But before I think about doing any operations to remove these indices, I need to understand what period was the analysis of this data (*idx_scan*), has it been since the database was created? In the **pg_stat_database** (*stats_reset*) table is there a date that normally is today or up to 15 days ago, but does this process interfere with the tables I mentioned above? * No command of% reset () was executed. Do the% reset () commands clear the tables (**pg_stat_user_indexes** and **pg_stat_user_tables**)? My goal is to understand the period of data collected so that I can make a decision.

If I have frequent DML(insert,update,delete) operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where condition on index columns, Or will the performance remain same as when there are no DML operations? This Link says UpdateStatistics does block Select queries. So will DML operations cause UpdateStatistics. It says " The only time you wouldn’t be in control of this is if AUTO_UPDATE_STATISTICS is enabled on your database and AUTO_UPDATE_STATISTICS_ASYNC was disabled.". So is changing these values the best solution? **Note:**  **I will be using isolation level (Read Uncommitted) for the SELECT query which should not be blocked by locks.**

A quick background of the issue: We have an application, and we have many instances of this application running for clients. While they may be on slightly different versions, they are fundamentally the same. Yesterday, one client had an issue with SQL timeouts. Looking at the query we identified an issue with certain tables, and the use of OUTER APPLY and re-wrote it to circumvent the issue. Inspecting the query plan today, I can clearly see that the stats are bad, as it is expecting circa 2.5 million rows, which is incorrect. I updated the stats and it has resolved the issue, and now expects 30 rows. My confusion comes from when I inspect the query plan for other clients' databases, and the stats seem off, but, the query is returned in around 1 second, not the 45 seconds seen in the issue that was faced. Both databases have auto stats turned on. Does this suggest a problem with auto stats on the problem database? Whilst testing, I did clear the cache DBCC FREEPROCCACHE so the engine had to generate a plan each time. I have not done this on a database that was returning the data in a timely fashion however. Sorry for the vagueness, I unfortunately cannot share the query plan, due to sensitive information. Currently, we run only automatic stats updates (no scheduled statistics / index maintenance). This will change; the databases have been neglected somewhat. I should also mention, these databases are in Azure. I am not sure if that changes anything?

I would like to know if we can update the statistics of an index that has been reorganized, using the IndexOptimize script. Indeed, an index that is reorganized needs to have its statistics updated. Is there a way to use IndexOptimize script to update the statistics of this reorganized index only? For instance, is there a flag to recognize the reorganized index in order to update the statistics on this flagged index? Thanks.

I’ve created a dump of all statistics on all tables in a Production database (based on the three sets returned by DBCC SHOW_STATISTICS). The database has been up and running for several months, and is actively used around the clock. Reviewing it all, I find that several statistics are not actually set. That is, the statistics *entry* is there, but “LastUpdated” is null, and there are no steps in the histogram table. I am not able to find a consistent reason for this—some are for indexes, unique indexes, filtered, indexes, filtered unique indexes, and auto-generated stats. Why is this? I can see a handful of remotely possibly reasons. Many are for where there is no data in the table (this makes sense), but there are many that do have rows (up to 10s of millions), and for any reason that might barely explain it ( “first-column duplicates” of other stats/indexes, unique indexes, filtered indexes, unlikely datatypes such as bit or large nvarchars), I can find something similar in another table/index where statistics are present. I’m mostly convinced it’s not just one single thing. What are the possible reasons that statistics might not be present for tables under these conditions? How worried should I be about this?

I guess this is a very basic question but I can't get my head around it.. I have perfect statistics in place for a column but while using a TOP condition the estimated number of rows are always a number very close to the number used for the TOP operator giving me a complete wrong number. Is there any easy explanation for this knowing that the statistics for the InterviewerID on the application table know exactly that theID 12868 rows have more than 3K rows ? Why is telling me 13.83? Version being used: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Thank you all! How I call the SP ? EXEC agy.sp_searchTest3 @interviewerId = 12868,@searchTerm = 'aar' Query causing the issues : ALTER PROCEDURE [agy].[sp_searchTest3] ( @interviewerId INT ,@searchTerm VARCHAR(50) = NULL ,@searchApplicationId INT = NULL ) AS BEGIN IF @searchApplicationId IS NOT NULL SELECT TOP 10 u.userId ,u.firstName + ' ' + u.lastName AS 'fullname' ,app.applicationId ,u.loginId AS email ,adi.URI AS imageurl FROM [app].[application] AS app INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId INNER JOIN [usr].[user] AS u ON u.userId = a.userId LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId AND ud.documentTypeId = 5 -- (Portrait pic) AND ud.documentStateId NOT IN ( 3 ,5 ) -- (Rejected, Deleted) LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId WHERE app.interviewerId = @interviewerId AND u.isActive = 1 AND app.applicationId = @searchApplicationId IF @searchTerm IS NOT NULL SELECT TOP 15 -- everytime I change the top value the estimated number of rows change as well u.userId ,u.firstName + ' ' + u.lastName AS 'fullname' ,app.applicationId ,u.loginId AS email ,adi.URI AS imageurl FROM [app].[application] AS app INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId INNER JOIN [usr].[user] AS u ON u.userId = a.userId LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId AND ud.documentTypeId = 5 -- (Portrait pic) AND ud.documentStateId NOT IN ( 3 ,5 ) -- (Rejected, Deleted) LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId WHERE app.interviewerId = @interviewerId AND u.isActive = 1 AND ( (u.firstName LIKE @searchTerm) OR (u.lastName LIKE @searchTerm) OR (u.loginId LIKE @searchTerm) ) END