The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
1answer
286 views

How to configure postgresql-9.2 to know blk_write_time in pg_stat_database

Interested in the procedure for collecting block write statistics of PostgreSQL 9.2. Steps followed for configuration, could get blk_read time but not blk_write_time CREATE EXTENSION adminpack; and ...
2
votes
1answer
51 views

Recompile not working for DELETE statement

I want to optimize a deletion from my tables. I have a table-variable input for my usp of following type: CREATE TYPE [AddressInfoParts] AS TABLE( ( [TcpInfoId] [BIGINT] NULL INDEX ...
1
vote
0answers
59 views

Logical read when use Offset in SQL

When I execute this script SET STATISTICS IO ON SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] FROM vwTblTest WHERE ([Factor] >= 139501 AND [Factor] < ...
1
vote
1answer
108 views

Why wouldn't statistics update if all rows in a table were replaced?

We have several "reporting" tables in a SQL Server 2012 SP1 instance. Every hour, a job runs procedures that delete all the rows from these tables and then insert current data (involving lots of ...
1
vote
1answer
627 views

How to organize the tables of a MySQL database with several statistics data of several years?

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the ...
3
votes
4answers
1k views

Which version of SQL Server 2016 supports R

Microsoft buying REvolution results (amongst other things) in the integration of R in SQL Server 2016. Looking through the features supported by the SQL Server 2016 I cannot find R-Support. Does ...
5
votes
1answer
55 views

Retain column statistics on paragraph-style columns?

Specific to Microsoft SQL Server... If a query has a search predicate against a paragraph-style text column, it will create a system-created statistic to answer the query. Got it. My question is if ...
8
votes
2answers
2k views

Parallel Statistics Update

In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don'...
4
votes
1answer
843 views

Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans: select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 ...
1
vote
0answers
13 views

NuoDB Statistics

I'm currently doing a research using a TPC-H table and I'm trying to get the running time for 3 queries in NuoDB: SELECT * FROM LINEITEM SELECT * FROM LINEITEM WHERE L_PARTKEY BETWEEN 1 AND 80000 ...
11
votes
1answer
266 views

stats_column_id and index_column_id do not update with physical order of clustered index is changed

Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the ...
7
votes
1answer
86 views

Understanding SQL Server statistics better

I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have ...
3
votes
1answer
59 views

Auto created Statistics vs Manually created Statistics

Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement. Will the manually created statistics get ...
2
votes
1answer
76 views

Disable auto stats in SQL Server

I have couple questions about auto stats in SQL Server: I know I can use the following statement to check if the database auto stats is disabled, but is there a statement to check if a specific ...
2
votes
1answer
84 views

Is it worth to optimize queries with EarlyTermination Timeout?

Right after running Ola's IndexOptimize to rebuild all indexes and statistics, I cleared the data and plan cache and ran a very complex ugly query to get a baseline of it's performance. The query ...
4
votes
2answers
65 views

Statistics for AlwaysOn Availability DBs

Can you please help me understand why there is a need for SQL Server to maintain statistics in tempdb for DBs that are part of an Always On Availability Group? Isn't "everything" that happens in the ...
2
votes
1answer
661 views

Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible

We are using Oracle 11g, 11.2.0.3. We know a record exists in a table but a select is not returning it for some odd reason. The execution plans the general query, not forcing any index, shows that ...
1
vote
1answer
39 views

are overlapping multi column statistics redundant?

I have run a database engine tuning advisor session, DTA, to optimize my database. The DTA has produced some statistics recommendations, however, I noticed some are contained in others for example ...
0
votes
1answer
30 views

How to list the missing multicolumn statistics?

I have used the database engine tuning advisor, DTA, in order to improve the performance of one of my databases. It produced a list of recommendations, mostly multicolumn statistics. These ...
0
votes
1answer
40 views

In what circumstances I am more likely to benefit from auto update stats async on?

I update statistics on a weekly basis using Ola Hallengren's solution. Based on the article below, I am considering enabling the Auto Stats Async and turning on trace flag 2371. Databases With ...
4
votes
1answer
61 views

Verify the importance of statistics? Database with auto-create stats works?

Working on SQL Server 2005, I want to use the database engine tuning advisor to help me tune my webserver. First thing I do is to create a server side profile trace and run it for about an hour. ...
2
votes
2answers
265 views

Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases. I have then passed this .trc trace file as a parameter to the database ...
-1
votes
1answer
30 views

Generate and fetch table statistics [closed]

I am trying to generate statistics for my tables. I am doing it with the following command: exec dbms_stats.gather_table_stats(USER, 'tab_name', cascade => TRUE); Then I am trying to fetch these ...
0
votes
0answers
10 views

outdated stats or missing records?

I'm using the following query go gather index usage statistics: SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS ...
5
votes
3answers
194 views

Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4. After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad ...
0
votes
0answers
15 views

How to derive allowable threshold limits in Netezza

Our vendor sends us a daily extract containing customer data and we load these into our Netezza database daily. Each day, the row counts typically get slightly bigger however it is possible for some ...
0
votes
0answers
22 views

Is there a way to get alerted for stats going stale?

It came to our notice that applications running slow when there is bulk update/insert operation on few tables, randomly on AD-HOC basis. NO fixed date when data will be updated. Thus we see the ...
9
votes
2answers
2k views

Warning for missing statistics in execution plan

I have a situation that I can't understand. My SQL Server execution plan tells me that I have missing statistics on the table, but the statistics are already created: But if we look at the table, ...
9
votes
2answers
232 views

SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column

I'm trying to understand how Statistics sampling works and whether or not the below is expected behaviour on sampled statistics updates. We have a large table partitioned by date with a couple of ...
0
votes
1answer
24 views

Find all stats that were sampled

I am using Ola's scripts for stats updates and for one particular index I want to do full scans when updating stats (to solve for the Ascending Key problem in conjunction with trace flag 2389). We ...
1
vote
2answers
91 views

Partition of a table - how to minimize the time offline? How to check if all the statistics were updated?

As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records. Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were ...
0
votes
0answers
30 views

Group by periods in SQL

Each register is an event in a location with an id, but events take place "in clusters" (e.g zero to two events every day from 31/02/2014 to 10/03/2014, all with the same id). I need to detect the ...
1
vote
0answers
108 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: http://www.sqlservercentral....
5
votes
1answer
42 views

Statistics on filestream column

When I try to create a statistics on a filestream column, I get the following error message: Column 'MyColumn' in table 'MyTable' is of a type that is invalid for use as a key column in an index ...
3
votes
2answers
2k views

SQL Server: Do Statistics Updates cause query plans to be flushed? Should they?

I've found that a big, nasty data-extraction query that runs daily needs updated stats to avoid making horrible query plans based on incorrect rowcount estimates (let's not worry about whether or not ...
0
votes
0answers
27 views

innotop “Command Summary”

We provide DBaaS. This is innotop C output: We have only 8% read workload? The rest is write? I don't understand this is very uncommon workload for DBaaS? Statistics since last restart? What is ...
4
votes
1answer
69 views

Incremental statistics: find out which partitions have been resampled

TL;DR: Is it possible to find out which partition(s) have been resampled and which have not when incremental statistics are used? Platform is SQL Server 2014 Enterprise edition. The long version with ...
3
votes
1answer
72 views

SELECT very slow when no results and a LIMIT is specified

I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified. If the number of results is ...
0
votes
0answers
28 views

Resetting deadlock count statistics

It seems that just restarting the PostgreSQL service doesn't clear / reset the detected deadlock count. How can it be restarted to zero? select version(); PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu,...
3
votes
1answer
102 views

Optimizer gets the wrong number of estimated rows even with updated stats [closed]

I have a procedure running in SQL Server 2008 that contains a query similar to the one below; SELECT /* various columns from table 1 and table 2 */ FROM dbo.tbl_1 AS a INNER JOIN dbo.tbl_2 AS b ON a....
3
votes
2answers
206 views

How inaccurate is sys.dm_db_partition_stats?

I am looking at ways to count rows in several tables on several databases. In this particular case I am looking for unused and abandoned items. Several sources source1, source2 recommend using sys....
9
votes
1answer
146 views

Why can my index seek estimate the right number of rows and the sort operator can't?

I've a query that uses a function on the predicate, something like this: commentType = 'EL' AND commentDateTime >= DATEADD(month,datediff(month,0,getdate()) - 13,0) I have a filtered index on ...
0
votes
1answer
47 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 ...
18
votes
1answer
442 views

Statistics disappear after incremental update

We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the ...
10
votes
1answer
209 views

Why does SQL Server refuse to update these statistics with anything but fullscan?

I noticed a relatively long running (20 min+) auto update statistics operation in a daily datawarehouse build. The table involved is CREATE TABLE [dbo].[factWebAnalytics]( [WebAnalyticsId] [...
6
votes
1answer
158 views

What is more appropriate sp_updatestats or UPDATE STATISTICS?

In a system there is a nightly data load process where all the foreign keys are dropped, all tables are truncated, repopulated from an external source, and the foreign keys are put back in place. At ...
1
vote
3answers
84 views

What procedure can be used to change system statistics in a production environment

System statistics aren't required but highly recommended by Oracle. From my own experience, it produced a huge improvement on the total user IO waits of our production environment when first ...
3
votes
1answer
105 views

PostgreSQL + query planner + amount of entities

I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan. I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
10
votes
2answers
7k 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?
0
votes
0answers
65 views

Large differences between estimated and actual row counts but stats are OK

I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators ...