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 ...
6
votes
1answer
331 views

When do _WA_Sys_ statistics Get Updated?

I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table) The rule of thumb appears to be in ...
8
votes
1answer
591 views

Why is the auto created statistic on this column empty?

Info My question relates to a moderately big table (~40GB data space) that is a heap (Unfortunately, I am not allowed to add a clustered index to the table by the application owners) An auto created ...
1
vote
0answers
29 views

How do I see what update statistics is doing while running?

I have a very large table with 500m rows and 150 columns (I did not design the table, im simply trying to figure out what is going on here). I try to update a single statistic for this table. I run ...
1
vote
1answer
46 views

Does number of rows affect Update Statistics performance if you are sampling with n rows?

For example, I have table A with 80 million rows and table B with 200 million rows. Assume both tables have 30+ stat tables (all manually generated by SQL Server). I randomly take the first 10 stat ...
2
votes
1answer
59 views

Concurrent requests not running in parallel

I am trying to run update statistics in parallel. Say I start with 2 sessions: S1, S2 In S1 I run Update Statistics A with fullscan and time it to take 2 minutes. In S2 I run Update Statistics B ...
3
votes
1answer
408 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 ( ...
1
vote
2answers
152 views

Improving statistics on a large “WHERE IN” SQL Server query

Currently, I'm trying to run the following example query: SELECT [DATA1], [DATA2] FROM TABLE WHERE [DIMENSION0] IN (1, 5, ... (possibly 10s of numbers)) AND [DIMENSION1] IN (5) AND [...
2
votes
0answers
148 views

Howto preserve statistics when switching partitions of partitioned tables

Given are two partitioned tables T1 and T2 with no automatic statistics (their statistics where updated WITH NORECOMPUTE option). T1 has only partition N populated and T2 has partition N empty. Now I ...
3
votes
1answer
226 views

UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies

I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly. Step by step: Assembly: CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH ...
1
vote
3answers
418 views

Stats for usage of columns in queries

I am using MS SQL Server 2014. Have some kind of DWH. Is there some way to find some kind of stats, which columns from database are used in select clause and how many times, and which columns are used ...
5
votes
2answers
1k views

Update statistics on sys.objects

I am working on a query to give me all the database objects that depend directly or indirectly in any level, to a table called dbo.tblborder, which is heavily depended on. However, this question in ...
8
votes
1answer
218 views

Bizarre density results in sampled statistics

A NC-index gets a totally different statistical distribution when estimated with sampling vs fullscan; the sampled one having a bizarre density vector. This results in poor execution plans. I have a ...
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. ...
3
votes
1answer
5k views

Wrong estimated number of rows

I'm trying to identify an issue I sometime experience on a stored procedure. I have to say that the stored procedure uses an UDF in the select clause (perhaps that's the cause of the problem) but ...
1
vote
1answer
240 views

Incorrect Statistics EQ_ROWS Causing Parallel Table Scans and Deadlocks

On one customer's system, one of our database's tables has about 123 million rows. This table is a transaction table that is grouped by a batch key. The clustered index is 4 columns: batch key, ...
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 ...
1
vote
1answer
101 views

Filtered Stats and Incremental Stats on Partitioned table

We have just implemented table partition on a very large table in SQL Server 2014 , we used have filtered stats on the same table before partition not after the table partition we enabled Incremental ...
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 ...
6
votes
3answers
6k views

How to script statistics in Sql Server? (using T-SQL)

there is a very similar question here: Create SQL Script for Statistics But that is not what I want. I want to get back exactly what I have put in. For example: CREATE STATISTICS [...
7
votes
1answer
154 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 ...
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 ...
5
votes
3answers
712 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 ...
4
votes
1answer
707 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 ...
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: --//...
21
votes
1answer
1k 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 ...
23
votes
3answers
19k 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, we ...
4
votes
1answer
210 views

Where has STATMAN gone in SQL Server 2014?

So we've recently upgraded our environment to SQL Server 2014 (Enterprise on CU5) and are having some problems diagnosing performance issues, compounded by the problem that we no longer seem to be ...