Questions tagged [sql-server-2014]

SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
41
votes
2answers
1k views

Why does changing the declared join column order introduce a sort?

I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique. The test setup Setup script, including some ...
40
votes
2answers
31k views

SQL Server: How to track progress of CREATE INDEX command?

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the ...
32
votes
1answer
5k views

DBA first day in a new job - check backups and security - How? what else should be checked?

Generally when I start in a new environment, I tend to check where are the backups, when the last full was taken, when was the last restore applied and I check the security too. The way I do this is ...
28
votes
4answers
586 views

Why does the estimated cost of (the same) 1000 seeks on a unique index differ in these plans?

In the queries below both execution plans are estimated to perform 1,000 seeks on a unique index. The seeks are driven by an ordered scan on the same source table so seemingly should end up seeking ...
28
votes
1answer
667 views

Is the eager spool operator useful for this delete from a clustered columnstore?

I'm testing deleting data from a clustered columnstore index. I noticed that there is a large eager spool operator in the execution plan: This completes with the following characteristics: 60 ...
27
votes
1answer
694 views

SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

Consider the following query plan in SQL Server 2014: In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 ...
26
votes
2answers
1k views

Why does LEN() function badly underestimate cardinality in SQL Server 2014?

I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is ...
26
votes
4answers
17k views

The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'

I have a question about XTP_CHECKPOINT. I'm using SQL Server 2014. I have a database that is in SIMPLE recovery model mode. It is also being replicated. There are no open transactions. I've run DBCC ...
25
votes
3answers
21k 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 ...
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 ...
23
votes
1answer
1k views

Why Do My Nonclustered Indexes Use More Space When I Delete Rows?

I have a large table with 7.5 billion rows and 5 indexes. When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on. I ...
23
votes
1answer
966 views

Are unfixable spatial index corruptions considered normal?

I have a spatial index for which DBCC CHECKDB reports corruptions: DBCC CHECKDB(MyDB) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS The spatial index, XML ...
23
votes
1answer
897 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...
22
votes
1answer
60k views

Where is SQLCMD.EXE in SQL Server 2014 Express?

Using "SQLCMD.EXE" to back up my SQL Server Express databases for years I just discovered that after installing the 2014 version, I found no SQLCMD.EXE anymore. In previous versions it was located at ...
22
votes
2answers
822 views

Is it possible to force the optimizer to eliminate irrelevant tables in this partitioned view?

I'm testing different architectures for large tables and one suggestion that I've seen is to use a partitioned view, whereby a large table is broken into a series of smaller, "partitioned" tables. 1, ...
21
votes
1answer
4k views

What exactly can SQL Server 2014 execute in batch mode?

When a columnstore index is being used in a query SQL Server is able to use batch mode. Documentation is thin on what can run in batch mode and what can't. Please look at the following (motivating) ...
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 ...
20
votes
2answers
742 views

Why does Concatenation operator estimate fewer rows than its inputs?

In the following query plan snippet, it seems obvious that the row estimate for the Concatenation operator should be ~4.3 billion rows, or the sum of the row estimates for its two inputs. However, an ...
20
votes
2answers
37k views

Select all records, join with table A if join exists, table B if not

So here's my scenario: I'm working on Localization for a project of mine, and typically I would go about doing this in the C# code, however I want to do this in SQL a bit more since I am trying to ...
20
votes
3answers
14k views

Huge data and performance in SQL Server

I've written an application with a SQL Server backend that collects and stores and extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in ...
20
votes
1answer
2k views

Restore Database using GUI - Wrong file to restore

I'm just messing around with the SSMS graphic interface and studying the options of the "restore" task. One thing that I noticed is when I click on "generate script", the first line of the query is: ...
19
votes
3answers
723 views

Forcing Flow Distinct

I have a table like this: CREATE TABLE Updates ( UpdateId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ObjectId INT NOT NULL ) Essentially tracking updates to objects with an increasing ID. The ...
19
votes
3answers
5k views

Clustered columnstore indexes and foreign keys

I am performance tuning a data warehouse using indexes. I am fairly new to SQL Server 2014.Microsoft describes the following: "We view the clustered columnstore index as the standard for storing ...
18
votes
13answers
70k views

Capitalize only the first letter of each word of each sentence in SQL Server

I want to capitalize only the first letter of each word of each sentence in a SQL column. For example, if the sentence is: 'I like movies' then I need the output: 'I Like Movies' Query: ...
18
votes
5answers
3k views

Unable to create a Filtered Index on a Computed Column

In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column: ALTER TABLE dbo.tblBGiftVoucherItem ADD ...
18
votes
6answers
3k views

What can be the downside of always having a single integer column as primary key?

Within one Web application I am working on, all database operations are abstracted using some generic repositories defined over Entity Framework ORM. However, in order to have a simple design for the ...
18
votes
5answers
7k views

How to recursively find gaps where 90 days passed, between rows

This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this. SELECT ...
18
votes
5answers
37k views

Using DISTINCT in window function with OVER

I'm trying to migrate a query from Oracle to SQL Server 2014. Here is my query which works great in Oracle: select count(distinct A) over (partition by B) / count(*) over() as A_B from MyTable ...
18
votes
4answers
4k views

Memory Optimized Tables - can they really be so difficult to maintain?

I'm investigating the benefits of upgrading from MS SQL 2012 to 2014. One of the big selling points of SQL 2014 is the memory optimized tables, which apparently make queries super-fast. I've found ...
18
votes
3answers
49k views

Error message - view server state permission was denied - when connecting to SQL Server 2014 using SQL Server 2012 Management Studio

When right-clicking on a table and selecting "select top 1000 rows", I get this error: Is there any workaround, other than the obvious upgrade to SQL Server 2014?
17
votes
3answers
3k views

Is a weekly rebuild of indexes a good idea? [duplicate]

We had a SQL expert suggest rebuilding our table indexes weekly. We're currently doing this Saturday night and it causes lots of timeouts during the rebuild process. I'd like to avoid the timeouts ...
17
votes
3answers
18k views

How to write a query in SQL Server to find nearest values

Let's say that I have the following integer values in a table 32 11 15 123 55 54 23 43 44 44 56 23 OK, the list can go on; it doesn't matter. Now I want to query this table and I want to return a ...
17
votes
1answer
3k views

SELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order (“desc”)

We have a large database, about 1TB, running SQL Server 2014 on a powerful server. Everything worked fine for a few years. About 2 weeks ago, we did a full maintenance, which included: Install all ...
17
votes
2answers
3k views

SQL Server UniqueIdentifier / GUID internal representation

A colleague of mine sent me an interesting question, that I can't entirely explain. He ran some code (included below) and got somewhat unexpected results from it. Essentially, when converting a ...
17
votes
1answer
3k views

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large ...
16
votes
2answers
2k views

What happens when there is no available physical memory left for SQL Server?

While googling I found some conflicting information. Some sites state that when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB (see: SQL Server:...
16
votes
1answer
609 views

SQL Server 2014 COUNT(DISTINCT x) ignores statistics density vector for column x

For a COUNT(DISTINCT) that has ~1 billion distinct values, I'm getting a query plan with a hash aggregate estimated to have only ~3 million rows. Why is this happening? SQL Server 2012 produces a ...
16
votes
2answers
488 views

Is it legal for SQL Server to fill PERSISTED columns with data that does not match the definition?

I'm following up on this question about strange values in a PERSISTED computed column. The answer there makes a few guesses about how this behavior came to be. I'm asking the following: Is this not ...
16
votes
1answer
33k views

There is insufficient system memory in resource pool default to run this query

I'm using SQL Server 2014 - 12.0.2269.0 (X64) on Windows Server 2012 and I'm experiencing some memory issues. When I run a stored procedure that is doing a quite "heavy" calculation, I get an error ...
15
votes
4answers
53k views

how to query SSISDB to find out the errors in the packages?

I have seen this question SSIS 2012 - How to Query Currently Running Packages in T-SQL? It gives me the following script: SELECT E.execution_id , E.folder_name , E.project_name , E....
15
votes
3answers
2k views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT o....
15
votes
2answers
11k views

Alternative way to compress NVARCHAR(MAX)?

I am trying to compress some tables that have NVARCHAR(MAX) fields. Unfortunately, the row and the page compression do not have the desire impact (only ~100/200 MB saved for 20 GB table). Also, I am ...
15
votes
2answers
3k views

How can SQL Delete using a sub query

The following code was added by one of our developers to delete duplicate records from the table: DELETE SubQuery FROM ( SELECT ID ,FK1 ,FK2 ,...
15
votes
2answers
3k views

Can SQL Server system tables be defragmented?

We have several databases in which a large number of tables are created and dropped. From what we can tell, SQL Server does not conduct any internal maintenance on the system base tables, meaning that ...
15
votes
2answers
7k views

Newer Version of SQL Server Less Stable?

Our database architect told a client of ours that SQL Server 2014 is a poor choice over 2012 because it lacks the performance and stability advantages that 2012 has. Everything I've read contradicts ...
15
votes
1answer
9k views

Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job ...
14
votes
3answers
19k views

Creating a Linked Server that Points to Itself

I am attempting to create a linked server on SQL Server 2014 instance servername\instancename using the following call: EXEC master.dbo.sp_addlinkedserver @server = N'servername\instancename', ...
14
votes
2answers
793 views

Can SQL Server create collisions in system generated constraint names?

I have an application which creates millions of tables in a SQL Server 2008 database (non clustered). I am looking to upgrade to SQL Server 2014 (clustered), but am hitting an error message when under ...
14
votes
4answers
8k views

Progress of SELECT INTO statement

Our ETL flow has a long-running SELECT INTO-statement, that's creating a table on the fly, and populating it with several hundred million records. The statement looks something like SELECT ... INTO ...
14
votes
3answers
2k views

Concatenate all values of the same XML element using XPath/XQuery

I have an XML value like this: <R> <I>A</I> <I>B</I> <I>C</I> ... </R> I want to concatenate all I values and return them as a single string: ...

1
2 3 4 5
51