All Questions

Filter by
Sorted by
Tagged with
1
vote
0answers
29 views

Slight change of query causes CONVERT of illegal values to be done first, causing a conversion error [duplicate]

I have a table MyTable which has columns OldVal, NewVal, Nme which are all VARCHAR(MAX) Wherever Nme = MyValue, the OldVal and NewVal values will be "integers" IE a numeric value value stored as a ...
7
votes
1answer
470 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 ...
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 ...
3
votes
2answers
211 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 ...
0
votes
1answer
91 views

what is more important on a query execution plan, cardinality or selectivity?

The paper "How good are query optimizers, really?" says that cardinality is the thing that most influence to find a better query execution plan in RDBMS. Summary of this paper can be found here. ...
1
vote
1answer
914 views

incorrect Estimated Number of Rows vs Actual number of rows

I'm seeing a huge difference in the estimated number of rows vs actual number of rows. I'm not sure if i'm reading this right, so does this mean that sql server estimated the number of rows returned ...
1
vote
1answer
63 views

Oracle Optimizer refuses to use my index

I have a relatively simple query that SELECT data in a table. SELECT l.columnRecovered FROM schema.TABLE l WHERE (column1 = :a or (:a is null AND column1 is null)) AND (column2 = :b or (:b is null ...
2
votes
1answer
64 views

Optimizer failing to load statistic when creating plan in SQL Server 2017 “Calculator failed. Replanning.”

This morning, the statistics on one of our filtered indexes updated, and when a plan that uses that statistics recompiled it stopped using that index (and started scanning the entire clustered index). ...
0
votes
1answer
109 views

Logical Reads vs. Records Returned

I have a very basic query against a large table that roughly follows this form: SELECT [Extent1].[A] AS [A], [Extent1].[B] AS [B] FROM [dbo].[SomeTable] AS [Extent1] WHERE ([Extent1].[A] &...
7
votes
1answer
2k views

Bad execution plan after stats update due to temp table

A stored procedure query sometimes gets a bad plan after stats update on one of the tables, but can be recompiled to the good plan right after. Same compiled parameters. Issue seems to come from a ...
1
vote
0answers
49 views

PostgreSQL: optimize Rarity Score calculation

I need to populate a value called "Rarity Score" for collectible items by one query. Here is the data I currently have: Items table +-----+--------+-------+ | id | name | score | +-----+--------+-...
2
votes
0answers
72 views

Index statistics are requiring constant updates due to timeouts

We have been experiencing an odd behavior in our application where various modules will begin to timeout in SQL Server 2012. Each time we stumble across this issue, we find that the statistics require ...
0
votes
2answers
152 views

How to manipulate the query plan so that I can have more control on memory grants?

Let's say I have a complex query and for this query there are huge Differences between the estimated and actual execution plans. These differences are causing spillage into tempDb as described here. ...
0
votes
2answers
102 views

Statistics impact on db2 optimizer plan

Has the creation of statistics any impact on the estimated costs and the optimizer plan given by db2 optimizer. Or will (in the best case) only the execution time of a query change? I ran something ...
2
votes
1answer
89 views

SQL Server - Do automatically created column statistics get dropped automatically after time?

The retired SQL Server 2000 documentation https://www.microsoft.com/en-us/download/details.aspx?id=51958 states: "The statistics generated for a column can be deleted if you no longer want to ...
24
votes
2answers
2k views

Cardinality Estimate for LIKE operator (Local Variables)

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and ...
2
votes
1answer
298 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
1answer
143 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 ...
1
vote
1answer
314 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 ...
0
votes
1answer
427 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 ...
2
votes
2answers
855 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 ...
3
votes
1answer
426 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....
2
votes
1answer
32 views

what monitor before applying changes on the server? - looking for a basic answer - starting point only

these settings were decision of our development team: there are 2 settings: 1 - max degree of parallelism 2 - optimize for ad hoc workloads On OUR_DEV_SERVER: 1. Run the following script: --//...
1
vote
0answers
67 views

Oracle tuning strategy for columns with long tails

We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small ...
2
votes
2answers
2k views

How to improve row estimate of 1 row in join for newly inserted data

A custom statistic exists for the CacheId column of a table. After an overnight statistics update: Statistics for INDEX 'ST_TableName_CacheId'. -------------------------------------------------------...
6
votes
1answer
1k views

New query slower even if it has less logical reads than the old one

I have a query in a stored procedure that must list all dates in a range and JOIN on it date from a table, if it exists for that day. The procedure was created before I was employed as the DBA, so I ...
3
votes
1answer
2k views

Check which statistics were used in query optimization

Is it possible to tell when statistics were last accessed by a query using T-SQL? I'm trying to ascertain what statistics are involved in a query, I know I can check the visual query plan but I ...
2
votes
1answer
825 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 ...
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. ...
3
votes
1answer
135 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 ...
1
vote
3answers
302 views

PostgreSQL and query planner

I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to ...