Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sql-server-2014]

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

Major releases:

  1. 12.00.2000 - RTM, April 2014
  2. 12.00.4100 - Service Pack 1 (SP1), released May 2015

SQL Server 2014 will leave mainstream support on 2019-07-09 and extended support on 2024-07-09.

SQL Server 2014 contains the following major new features:

Database Engine

  • Memory limit for Standard Edition increased from 64GB to 128GB
  • Clustered columnstore indexes & archival data compression
  • Memory-optimized tables (aka In-Memory OLTP, codename Hekaton)
  • Buffer pool extension to SSD
  • New cardinality estimation model
  • AlwaysOn replicas increased from 4 to 8
  • Delayed durability
  • Parallel SELECT INTO
  • Inline index specification for CREATE TABLE
  • Rebuild individual partitions of a partitioned table online
  • Lock priority for online operations
  • Backup Encryption
  • Incremental statistics update
  • Database data files hosted on Windows Azure
  • Deploy a SQL Server Database to a Windows Azure Virtual Machine Wizard
  • Backup to URL / Managed backup to Windows Azure
  • Resource governor physical IO control

See Features Supported by the Editions of SQL Server 2014

Analysis Services & Business Intelligence

  • PowerView for multidimensional models
  • BIDS becomes SQL Server Data Tools for Business Intelligence (SSDT-BI)

No major changes to the following feature sets in SQL Server 2014:

  • SSIS (Integration Services)
  • SSRS (Reporting Services)
  • Replication

Note: Windows Vista is not a supported operating system for SQL Server 2014.

2763 questions
60
votes
4 answers

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 method below, which at least shows me movement…
Jonesome Reinstate Monica
  • 3,319
  • 9
  • 36
  • 55
43
votes
2 answers

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 realistic statistics: DROP TABLE IF EXISTS…
Daniel Hutmacher
  • 8,955
  • 1
  • 22
  • 52
38
votes
3 answers

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 will see that there is a statistic that has been…
Artashes Khachatryan
  • 1,393
  • 1
  • 10
  • 21
35
votes
2 answers

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 via T-SQL. Check the backups ;with Radhe…
Marcello Miorelli
  • 15,417
  • 50
  • 140
  • 271
28
votes
4 answers

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 OPENTRAN and it returns: "No active open…
user3469285
  • 661
  • 2
  • 8
  • 19
28
votes
4 answers

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 the same values in the same order. Both nested…
Martin Smith
  • 80,333
  • 15
  • 230
  • 323
28
votes
1 answer

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 rows and just one distinct value of fId (accurately…
Geoff Patterson
  • 8,237
  • 2
  • 26
  • 53
28
votes
1 answer

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 million rows deleted 1.9 GiB TempDB used 14 minutes…
James Lupolt
  • 4,238
  • 5
  • 26
  • 44
26
votes
1 answer

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 wrote a query against dm_db_partition_stats to report…
Michael J Swart
  • 1,935
  • 5
  • 20
  • 30
26
votes
2 answers

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 yielding very poor plans because there are…
Geoff Patterson
  • 8,237
  • 2
  • 26
  • 53
25
votes
2 answers

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 the query optimiser doesn't have to resort to…
Fza
  • 640
  • 1
  • 7
  • 18
24
votes
1 answer

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) query plan where a surprising number of things…
usr
  • 7,240
  • 5
  • 30
  • 58
24
votes
1 answer

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 C:\Program Files\Microsoft SQL…
Uwe Keim
  • 887
  • 2
  • 13
  • 24
24
votes
1 answer

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" FROM @p1 AS d JOIN "MySchema"."Region"…
World Wide DBA
  • 12,897
  • 4
  • 35
  • 54
24
votes
1 answer

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 index or indexed view 'sys.extended_index_xxx_384000'…
boot4life
  • 1,279
  • 1
  • 11
  • 19
1
2 3
…
99 100