Questions tagged [statistics]

Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view.

Filter by
Sorted by
Tagged with
19
votes
2answers
37k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
2
votes
1answer
148 views

Data impact on analyzing mysql table stats

I have a MySQL database that appears to be getting slower over time. After some googling, I determine that the database probably needs to be analyzed. This is because innodb_stats_on_metadata is set ...
7
votes
1answer
468 views

Is there a reliable way to determine how long it took the optimizer to generate a query plan?

I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the ...
0
votes
0answers
19 views

Postgres extremely slow after upgrade from Version 10 to 11

Related to this:PostgreSQL extremely slow after upgrade from 9.3 to 9.4 but trying to understand better... My queries was slow after upgrading to Postgres Version 11.5 from 10.7 I first ran ANALYZE ...
-1
votes
2answers
44 views

Query Execution Details [closed]

We need to find out the list of queries which were executed between a from time and to time, which user had executed the query and what was the execution time of that query.
2
votes
2answers
207 views

Slow select Queries on Log shipping standby server

We have a database in primary DC around 15 TB with partitioned tables with largest one around 7 TB. To avoid users from reporting a LS set up was done for above database in different DC where the LS ...
0
votes
1answer
745 views

Locks caused by async stats update

SQL Server has an option for statistics to autoupdate asynchronously (AUTO_UPDATE_STATISTICS_ASYNC). But, even if this option is ON, from time to time I see locks like this on pic below: say, spid ...
-1
votes
1answer
315 views

How to get user Insert statistics in MySQL

I'm trying to get statistics on the write load on my MySQL servers. Something like what show global status like "Com_%"; gives me. but, here comes the catch - we're using Master-Master replication. ...
0
votes
1answer
91 views

Bad query plan only on first execution, not in SSMS

We have a very customizable report query system where the user can pick and chose which columns to include in the results as well as a lot of filters. I'll be mapping what we're doing onto an example ...
14
votes
1answer
368 views

Cardinality estimate outside the histogram

Setup I'm having some trouble understanding a cardinality estimate. Here's my test setup: the 2010 version of the Stack Overflow database SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2 the new ...
0
votes
0answers
19 views

Frequency of table update in postgresql?

Given postgres 11. I'm looking for statistics on how often a particular table gets something inserted/updated/deleted. There's of course this: https://www.postgresql.org/docs/11/monitoring-stats....
1
vote
2answers
61 views

Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database?

I have a SQL Azure database that's about 500MB in size. Every 2-3 weeks the database will suddenly start to run slow and some queries (generated by LINQ) will timeout. There is no gradual increase in ...
1
vote
1answer
35 views

When are statistics created?

I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we ...
0
votes
1answer
88 views

Postgresql function equivalent to Excel's NORMSINV function?

I need Excel's NORMSINV equivalent function for PostgreSQL, preferably plpgsql function. Can somebody help? For reference, here is Excel's normsinv function explained: https://support.microsoft.com/en-...
0
votes
1answer
26 views

SQL Server. Do reads on Read-Only Replica in Availability Group fire Auto Update Statistics on Primary?

We have MS SQL Server 2014 Enterprise Edition with an Availability Group with Readable Secondary. Our application reads data only from Secondary. As far as I know, auto update statistics starts when ...
10
votes
3answers
3k views

Are SQL Server statistics stored in database or buffer pool?

Just wondering are statistics kept in the database but not in the memory? If I backup/restore the database from a prod server to a development server, would it keep the same statistics so that ...
1
vote
2answers
117 views

Db2 REORGCHK returns the SQL2310N (Error “-668”) message

I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes. I removed a column: db2 "alter table SCHEMA.TABLE drop ...
0
votes
1answer
266 views

When is dm_exec_procedure_stats updated?

I just created a new proc and ran it, but it is not showing up in my query: SELECT schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time ...
9
votes
1answer
168 views

Statistics disappearing/empty randomly throughout the day

I've got a SQL Server 2017 (CU9) database that is exhibiting some performance related issues that I believe have to do with the index statistics. While troubleshooting I discovered that the ...
1
vote
1answer
36 views

Table not analyzed after partition has been split

On my table I split a subpartition successfully. After that I rebuild the indexes and gather statistics. ALTER TABLE T_MO_DPI SPLIT SUBPARTITION ACTIVE_RELATIONS VALUES (24) INTO ( SUBPARTITION ...
3
votes
1answer
398 views

Query with conditional JOIN running slow

I have a query the first part of which looks like this - With CTE AS ( select DISTINCT A.col1, A.col2, . . . from tableA A JOIN tableB B ON ( ...
3
votes
2answers
221 views

How often should you update your stats?

How often should you update your stats? What is "too seldom"? How often is "too often"? The answer is "it depends" on your database, users, data, etc. So I've tried to log what our stats look like ...
2
votes
1answer
133 views

Auto generated statistics by query optimiser

Can we consider removing the auto generated statistics if the update statistic within a given database is taking too long? We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but often ...
2
votes
0answers
93 views

Incremental Schema Stats (With partitioned/Subpartition table) gathering is taking too much time in oracle version 12.2.0.1

When I am gathering schema stats with below options, it is taking too much time: EXEC dbms_stats.gather_schema_stats ( ownname => 'SCHEMA', granularity => 'AUTO', CASCADE=> true, ...
1
vote
1answer
39 views

how to override a table's cardinality or statistics histogram

I remember reading once that it is possible to force SQL Server to assume a table is a certain size, or perhaps even override a statistics histogram, when compiling a query. Can someone explain how ...
1
vote
0answers
79 views

Why are my statistics getting out of date so quickly?

I've got a large table (300 million rows), with about 1 million rows added per day. This is time-series data, so I have the following setup: CREATE TABLE Readings ( Id INT PRIMARY KEY, ...
0
votes
1answer
20 views

MySQL 5.7 statistics - Change db

I have a MySQL 5.7 database. I was looking in statistics in server status that shows type of queries / commands / tasks executed in the database. It shows that the query command "change db" by far was ...
0
votes
0answers
20 views

SharePoint changing statistics options daily

SharePoint switches OFF and ON options like AUTO_UPDATE_STATISTICS, AUTO_UPDATE_STATISTICS_ASYNC, AUTO_CREATE_STATISTICS, INCREMENTAL on SharePoint databases daily at 12 AM and that generates lots of ...
2
votes
4answers
227 views

What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx?

Can anyone point me to the correct documentation or additional permissions I need besides SA to do the following? When I run it, I receive the following error: Cannot find the object "#test" because ...
1
vote
1answer
183 views

Histogram Skew on Clustered Index

Problem I've been attempting to optimize a query generated from Entity Framework, and noticed that the execution plan shows wildly inaccurate estimations. After a little digging, I noticed that the ...
6
votes
4answers
21k views

sp_updatestats vs Update statistics

Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE ...
3
votes
2answers
181 views

why it is doing index seek

I created a sample table as below CREATE TABLE [dbo].[StatisticsDemo]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL ) ON [PRIMARY] Then I inserted below data as below: ...
0
votes
1answer
19 views

Return results of two MySQL query in only one

I am running two seperate queries to get statistics from 3 different tables: contacts campaigns sent_messages The first query counts all contacts that are marked as "stop" in the contacts table (...
2
votes
1answer
84 views

Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException

We have an Excel table that gather data from our datawarehouse db on a SQL Server 2016. It queries a relative simple view to find the top customers of the last few days (including some aggregated ...
7
votes
3answers
1k views

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?
1
vote
1answer
60 views

Are idx_scan statistics reset automatically (default)?

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, ...
5
votes
4answers
2k views

Composing the “ultimate database maintenance script”

I'm trying to compose T-SQL script that will: A) Shrink database size to the minimum possible size; B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a "...
19
votes
3answers
4k views

Is there a reason to update statistics manually?

In SQL Server, statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?
1
vote
1answer
1k views

How do I get Azure SQL query history/statistics/performance?

I'm migrating away from Azure SQL and my new cloud hosting service wants stats like "estimated inserts per hour on average", traffic/bandwidth usage per month, or "average read/writes on table x per ...
2
votes
1answer
119 views

SQL User with only access to maintain indexes and statistics

I need to have an AD user account with access to all databases on a specific instance of SQL Server 2008 R2, and the only thing that user will be doing is; Reorganize indexes Database integrity ...
-1
votes
1answer
105 views

How much Table Statistics Change before Query Execution Plan Update?

If I have table in an environment, and it has not undergone any DDL changes or sp_recompile. How much statistics data has to be inserted/updated before SQL Server 2016 Engine Recompiles a query ...
4
votes
1answer
127 views

How can I find out which statistics are evaluated by the optimizer?

My enterprise has a farm of many nearly-identical, isolated production environments. Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas. ...
27
votes
1answer
4k views

Where are Statistics physically stored in SQL Server?

Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool? More specifically, is there a way to figure out the pages used by ...
12
votes
1answer
1k views

statistics are up to date, but estimate is incorrect

When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698: For this query: SELECT * FROM Reports_Documents WHERE ReportID = 18698 ...
10
votes
3answers
10k views

Why set Auto Update Statistics to False?

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 ...
1
vote
2answers
107 views

sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way?

I have this table in one of my databases: IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL DROP TABLE [dbo].[repl_Gender_Type] GO CREATE TABLE [dbo].[repl_Gender_Type] ( [Gender_TypeID] CHAR(...
3
votes
2answers
178 views

updating statistics using STATS_STREAM or WITH FULLSCAN?

the syntax for creating statistics is: -- Syntax for SQL Server and Azure SQL Database UPDATE STATISTICS table_or_indexed_view_name [ { { index_or_statistics__name ...
7
votes
1answer
671 views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/...
0
votes
1answer
56 views

Execution plan for staging In-Memory tables estimate row count =1

We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process. the nature of these staging tables is empty most of the time. The ETL process executed ...
2
votes
1answer
169 views

Stored Procedure: table-Value parameters

We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and ...