Questions tagged [sql-server-2016]

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
6
votes
5answers
1k views

Is OPTION (RECOMPILE) used in production environments?

Is OPTION (RECOMPILE) used in production? This option seems to get a lot of bad press. Is it deserved? I have a DBA who, so far, refuses to entertain the idea of OPTION (RECOMPILE) within the meat of ...
1
vote
2answers
49 views

How can parse column names using ordinal position (not name) from XML data in SQL Server?

I have several hundred tables in a database that have the same structure: SomeId, Pos, Varying Number of Other Fields So, for example, one table may look like this: PersonId, Pos, Hobby, Degree 12345, ...
2
votes
1answer
39 views

Use CTE to fetch most recent row for each ID

I just recently started learning about the wonders of CTEs and how they can be used, instead of WHILE loops, to optimize performance. For context, I am automating the process of updating firmware on ...
4
votes
4answers
597 views

Why changing the column in the ORDER BY section of window function “MAX() OVER()” affects the final result?

I have a table with below structure and it's data : create table test_table ( Item_index int, Item_name varchar(50) ) insert into test_table (Item_index,Item_name) values (0,'A') insert into ...
1
vote
1answer
28 views

Does SQL Server upgrade to table locks at total locks or session locks?

I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so ...
1
vote
1answer
24 views

Invalid Column Name on CONCAT_WS

Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue. I have two tables cases and legal_entity the data on the two tables looks like this legal_entity ...
0
votes
2answers
39 views

Online rebuild consuming lot of log space

why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched. Details: We have a table with primary key clustered index (...
2
votes
1answer
45 views

Key lock triggered by batch update

I'm doing a batch update operation,but it appears key lock I think I'm in trouble. I don't understand why the deadlock is happening. Environment: SQL Server 2016 deadlock graph detailed: <deadlock-...
2
votes
0answers
30 views

Researching Performance Degradation on AO Secondary using MultipleActiveResultSets (MARS)

Environment: SQL Server 2016 Enterprise Edition on Windows 6.3 9600 two node cluster with Always-On. We have an application that executes SELECTs on the only secondary node of a SQL Server 2016 always-...
2
votes
3answers
94 views

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's ...
0
votes
1answer
42 views

sp_Blitz - RegOpenKeyEx() returned error 2

I'm running the latest sp_Blitz script on a SQL 2016 Std server on a Windows Server 2016 physical box. I'm getting the following error. What could be causing this and how do I troubleshoot it? ...
0
votes
1answer
41 views

alter compatibility level

I plan to change the compatibility level on the databases in an sql server 2016 sp1 instance from 100 to 130. When upgraded from sql 2008 to sql 2016 the compatibility level was changed from 80 to 100....
0
votes
0answers
21 views

How To Grant A Specific Login Under Acitve Directory Group To View Server State In SQL Server [duplicate]

I want to grant only a login who is under an active directory user group to view server state in sql server. How can i do it?
0
votes
1answer
21 views

Missing Target Local Server in Job

SQL Server 2016 Enterprise, CU15 + GDR. Client recently started using OPCON in production and I noticed that when OPCON creates jobs, it doesn't add the Local Server as the target. This prevents the ...
1
vote
1answer
176 views

Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero. From the DBA I ...
0
votes
1answer
34 views

sql server 2016 sp1 to sp2 with latest cumulative

I inherited an SQL Server 2016 SP1. I am not a DBA and work at a small company where we do not have a test environment. I know that I need to install the SP2 first. Do I then install the last ...
0
votes
1answer
30 views

One-directional Transactional Replication is causing one table's Clustered Index to be copied over as a Nonclustered Index in the subscriber database?

This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before. I'm using One-Directional Transactional Replication and I have ServerA ...
3
votes
1answer
329 views

Can shrink database improve performance

Yesterday I've got a mail from one of our suppliers mentioning that there were performance issues with their application. I don't know yet what the issues are but the solution they propose is to ...
0
votes
0answers
19 views

There are currently no logon servers available to service the logon request

I am facing this particular issue in one of my server (SQL 2016 std on Win 2016 std) , where SQL server does not come online post OS reboot. Event viewer has below error. After I login to server and ...
0
votes
0answers
21 views

Non-unique clustering vs partitioning [closed]

I have large table with billions of rows. It has no DATE column, its kind of like a dimension table (very poor design i know). The table is clustered on a non-unique decimal(18,0) column called TaskId,...
0
votes
0answers
16 views

Get real error from backup job failure instead of backup terminating abnormaly

I've had log backup jobs fail for a few different reasons lately. When I go to the job it tells me backup terminated abnormally. However, when I run the backup script in SSMS it will tell me the ...
0
votes
2answers
43 views

Script execution order

I did Generate Script on a database and got over 1700 scripts. I've sorted them into folders below: Each folder contains .sql files of that type, for example 06 UserDefinedFunction contains many ....
0
votes
3answers
50 views

Index Maintenance on busy OLTP database

I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition. There is no budget for enterprise nor ...
1
vote
1answer
26 views

Running Excel addin for MDS2019 vs MDS2016

We are upgrading master data services (MDS) of SQL2016 to SQL2019.Is there a way to run excel 2019 addin for connecting to SQL 2019 MDS along side(parallely) with excel 2016 addin for connecting to ...
0
votes
2answers
27 views

Simple Backup Plan Question

I'm using Microsoft SQL Server 2016 Standard (64-bit). I have the following backup plan (9 steps long) ... What I want is just one full (simple) backup everyday. Do I need to do step 8 (Backup ...
6
votes
5answers
289 views

Linked Server connection started failing with “Cannot generate SSPI context … SQL Server Network Interfaces: The logon attempt failed” error?

UPDATE 10: The original question and previous updates follow this update below (and will soon be cleaned up into one current update). Last week be broke the clusters, and now everything is truly a ...
0
votes
1answer
100 views

Is this an excessively large 'Lock' time and is it indicative of a problem?

Given: Production environment Application Servers using Hibernate SQL Server Studio Manager v17.5 SQL Server 2016 in a clustered HAG setup SQL Servers do NOT have the Query Store feature enabled The ...
1
vote
1answer
50 views

Stale Statistics and View performance on SQL Server 2016

I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily. So my doubt essentially is - What could be the reason that even with ...
2
votes
1answer
65 views

SELECT query in a SP in AlwaysOn Availability Group Read Only Routing

There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn ...
1
vote
0answers
46 views

Is it possible to create a Linked Server from SQL Server 2000 to SQL Server 2016?

I am trying to create a Linked Server from SQL Server 2000 to SQL Server 2016 R2. However, while creating I am getting the below error when trying to create Linked server from SQL Server 2000. TCP ...
1
vote
4answers
59 views

How to enforce SQL Server policies in order to avoid users enabling bad practices

I have some rogue Windows Administrators who give themselves SA and then grant end-users SA. These users then end up enabling auto-shrink, auto-growth percentage and all kinds of other poor practices -...
0
votes
2answers
40 views

T SQL, Get new customer added or deleted from historical table

I have a Table containing historical data. Every day the same staff list will be added with the new dates (between 1200-12500 staff each day however only a few staff might be newly added todaysDate or ...
0
votes
1answer
42 views

SQL Server License Migration [closed]

We need to move our SQL Server 2016 standard License. Our plan is to install SQL Server on the new box with the same license key , test the application / databases and then delete the old SQL server ...
0
votes
1answer
39 views

Will an underpowered mirror host cause: The query processor could not start the necessary thread resources for parallel query execution [duplicate]

We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine. Our main database server has 16 physical cores with hyper threading and 2 numa nodes. We have set the ...
1
vote
1answer
35 views

Is it possible to “bind” sp_executesql to the calling procedure, as recorded in the Query Store?

Statements executed with sp_executesql appear to be generally "unbound" from the procedure in which they appear. By "bind", I simply mean "associated to the calling object&...
2
votes
3answers
113 views

Slow Parallel SQL Server query, almost instant in serial

I have a SQL Server query as follows (obfuscated): UPDATE [TABLE1] SET [COLUMN1] = CAST('N' AS CHAR(1)) FROM [TABLE1] WHERE (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR ...
1
vote
0answers
41 views

MS SQL Server Enterprise 2016 is truncating the audited select statements, how do we fix it?

TL;DR; I am getting SELECT as the audit output instead of the actual (suspected) query SELECT DISTINCT CONVERT(varchar, DecryptByPassphrase(getkey(N'key1'), FIELD1)) as DECRYPTED_FIELD1 FROM view1 ...
0
votes
1answer
62 views

Recursive CTE to get Grandparent, parent, children [duplicate]

I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents. Here's an example of how things can look in the tree: ...
0
votes
0answers
87 views

Slow query SQL Server 2016

I have a query that is taking more than 7 hours and doesn’t finish, as it doesn’t finish I can’t generate the actual execution plan. Here's brentozar's Paste The Plan It has a temporary table, and ...
0
votes
0answers
12 views

Enable query logging for single user on SQL server 2008 and SQL server 2016

I need to monitor queries executed by a specific set of users on a database. I need to check them on a weekly base. Is that possible on SQL server 2008r2? And 2016?
2
votes
3answers
52 views

Preserving full refresh data source

I have a SQL Server database that gets updated every week with a completely new set of data. I am currently using two staging databases. A series of view databases are connecting to these and one week ...
-1
votes
2answers
24 views

Tool to Execute SQL Server Scripts and Automatically Recognize and Prompt for Scripting Variables [closed]

I have a folder of scripts that contain multiple objects and jobs that I roll out every time I deploy a new SQL Server Instance. The scripts utilize scripting variables, as an example, here is an ...
4
votes
2answers
74 views

In-Memory OLTP SQL Server 2016 - insufficient system memory

I am trying to place an In-Memory OLTP table, Here is the memory estimate you will need: But when the migration starts, the following error message is displayed: The size of my table is ...
0
votes
1answer
82 views

High Latch Wait time with Logon trigger enabled while full backups are running

I have 14 production servers with SQL 2016 standard version, 128 gb of RAM, 16 CPU and SSD drives. Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 ...
1
vote
1answer
49 views

In SQL Server, what's the best way to split multiple delimited strings into rows without a function?

I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What's the best way to split multiple delimited strings without using a ...
-1
votes
1answer
55 views

TSQL throw weird error when commenting a line of text [duplicate]

Here is a strange situation. Following code works perfectly if I run it as is, but if I comment the first CHARINDEX line, it throws an error. Msg 536, Level 16, State 4, Line 1 Invalid length ...
3
votes
1answer
40 views

Procedures using TVPs are slower when the TVP numeric value gets larger?

A legacy application has a nightly job that repeatedly calls some store procedure using a TVP and passes in batches of 10,000 ids that are in sequential order that it needs to process. Now that the ...
1
vote
3answers
55 views

Microsoft SQL Server 2016 Restore command many files

I have a question regarding proper syntax for restoring a 2016 SQL Server database. One database 1 mdf no issue. But I need to restore a database that has 3 mdf files and 73 ndf files and then apply 2 ...
0
votes
2answers
51 views

What could be the reason for shrinking the transaction log file before and after the database backup?

I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who ...
0
votes
1answer
271 views

Cannot fetch a row from OLE DB provider “SQLNCLI11” for linked server

I have two SQL Servers on two different machines somewhere in the cloud (maybe Azure). One is Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64) Jul 8 2017 16:43:40 Copyright (c) ...

1
2 3 4 5
49