Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
3
votes
2answers
119 views
Performance Improvement went wrong in Production, worked fine in Test
We have an application supported by vendor and there was a piece of code which was doing very heavy logical reads and time consuming inside a procedure, hence I proposed them to tweak the query a bit ...
1
vote
1answer
50 views
Assign Permissions to a SQL User
I have active directory on my company, I have some login users created at the level of the SQL Server (2014) that I need to propagate to all my databases on that server. I want to create a read only ...
2
votes
1answer
205 views
Fragmentation Level for Heaps
I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and ...
0
votes
1answer
55 views
Loading Data from CSV file or Staging Table to Multiple tables - SQL Server 2014
Hi i am trying to load data from CSV file to multiple sql server tables and some of the tables having Reference to parent tables so i can get the FK column value but trying to load via INSERT INTO ...
2
votes
1answer
28 views
Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan
Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan
-- create objects
CREATE DATABASE ConvertTest
GO
USE ConvertTest
GO
CREATE ...
2
votes
1answer
264 views
How to determine the cost of index creation?
I have a table that I want to create an index on to improve the performance of a couple of SELECT queries that use the table.
How can I test whether the index has had any significant detrimental ...
0
votes
0answers
22 views
Find when tables were deleted historically
We've had an issue where data tables have been deleted from a client applications SQL Server Database (Dynamics Navision). Unfortunately we had an outage and the SQL Servers were affected by this, ...
1
vote
1answer
27 views
JSON_VALUE in SQL-server 2014 [closed]
I am looking for an equivalent functionalities of JSON_VALUE in SQL Server 2014. Can any of you post an alternate of the below code which is supported in SQL Server 2014
SELECT JSON_VALUE(ERROR_MSG, '...
0
votes
0answers
24 views
Slow connection to SQL Server both locally and by client
Yesterday I was experiencing extremely slow connectivity to the SQL Server with connections via management studio taking 5+ seconds where normally connecting to the server occurs within seconds. This ...
0
votes
0answers
11 views
Encrypting File Sytem for FileStream Folders _ Using existing free space for EFS Processing
Can any one help me on below case related to EFS for File Stream folders.
I am with SQL Server 2014 SP2 and Availability groups on Windows Server 2012 R2 Standard.
I have 800 GB of FileStream ...
7
votes
2answers
387 views
SQL Server: Covering indexes including all columns?
Our team has inherited an application and associated database. The previous developers appear to have enforced a rule where every index, on every table, has an INCLUDE clause to always add every ...
0
votes
1answer
29 views
Problem with Script to Synchronize Login on AlwaysON
I'm trying to resolve an issue. I've a two nodes with Always On enabled, I want to synchronize user over the nodes. I see that fantastic script.
https://maq.guru/synchronizing-sql-server-logins-in-an-...
0
votes
0answers
19 views
Cluster Access point [closed]
Cluster newbie here.
This might a very stupid question. I am trying to configure a three node cluster. Two nodes in 1 subnet other is in a different subnet. In the Access Point For Administering the ...
0
votes
1answer
27 views
Remove Windows FCI completely
When I built the cluster I forgot to uncheck the storage. It's now giving me all sorts of errors. How can completely remove the cluster so I can rebuild it?
This is a 3 node cluster and I evicted two ...
6
votes
1answer
186 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 ...
-2
votes
2answers
42 views
Collecting query performance metrics without impacting production servers [closed]
How can I capture the query or performance metrics over a period of time, say a month, so that later they can be analysed for performance? I'd like to use DMVs or extended events.
I am trying to ...
0
votes
0answers
35 views
Frequent Cluster node failure
I experience this node failures frequently for specific SQL servers which are on a VMs, anytime I investigate we get the errors below in the failover cluster manager logs but are unable to identify ...
0
votes
0answers
24 views
Allowing a user to grant SELECT and VIEW DEFINITION permissions only on objects they create
How do I give access to a user so that they can grant SELECT and VIEW DEFINITION on any objects they create in a database.
For example, I have a user MyUser in the roles
db_datareader
db_datawriter
...
0
votes
1answer
26 views
Measuring Availability Group synchronization lag - Clarifications
Can I get some help on below
As I have started and learning about the Measuring of Availability Group synchronization lag between replicas inclusive of BCP , I want to understand and learn that does ...
1
vote
1answer
20 views
Query to find the subtract of each column from the following column
Hi everyone hope you are doing well
I have a table with this structure
Table 1:
[dbo].[ValueTest] (Date , PorojectCode , Value)
It has this data
('1397/04/01' , 'A' , 100) , ('1397/04/03' , ...
5
votes
2answers
229 views
Process to change collation on a database
I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.
So my research shows that in order change ...
8
votes
0answers
95 views
Incorrect process utilization from System health extended events?
I have been recently working on understanding the data or metrics being stored in system health extended events event file.
We are trying to implement the data collection of performance metrics using ...
5
votes
1answer
153 views
Strange blocking on readable secondary after reboot
We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the ...
2
votes
1answer
37 views
Excessive memory grants from indexed foreign key cascade delete
Each day, stores can enter sales information into our OLTP application. The app calls a stored procedure in SQL to save this information. Based on the users's activity, the application sends a code ...
0
votes
1answer
25 views
File growth and location change for system health extended events
I am continuing the questions over system health extended event from my previous question How are the system health extended events files rolling over?
I see system health file location by default ...
1
vote
2answers
31 views
Stored Procedure on a read-only AlwaysOn Availability Group replica
We have a read-only reporting server that is part of the AlwaysOn availability group. Our developer wants to create some stored procedures. My question is where should the stored procedure be ...
1
vote
1answer
34 views
SQL Server TDE - Database backups and post restore behaviour
Below is my SQL Server details:
SQL Server 2014 Enterprise SP2 GDR with Availability Group configured.
List item
NOTE: The Databases are FileStream Enabled.
Below are the series of actions I did ...
13
votes
1answer
306 views
SET NOCOUNT Error in handling SQL call after upgrade
We are upgrading our test environment with a new server and updated version of Microsoft SQL Server and have run into an issue.
On the new server, our old code will get "operation is not allowed ...
2
votes
1answer
30 views
Unable to search between IPv6 range when converting string IP Address to VARBINARY(16)
I am attempting to search for IPGeolocation data, which is presented by the data vendor as a start and end range for both IPv4 and IPv6. However, when I convert the IP that is provided, I am getting ...
1
vote
1answer
38 views
Set up new publication on SQL Server 2014 database which is part of an availability group
I like to setup a publication (transactional replication) on a SQL Server 2014 database which is part of an availability group. We have a primary and one secondary (non-readable) replica.
On the ...
5
votes
1answer
111 views
How are the system health extended events files rolling over?
I see the default system health session events within SQL Server are 5 MB each with 4 files in total.
We are trying to fetch data from the system health in historical events but for some critical ...
0
votes
0answers
19 views
Multiple Ad-Hoc Statements taking longer than previous days [duplicate]
We have a Data Warehouse product which runs a number of ad-hoc (IE SQL Text, not stored procs or dynamic SQL) every night.
The build took significantly longer last night than previous nights and ...
2
votes
1answer
41 views
SQL Server 2014 AOAG + Replication?
I have an existing SQL Server (2014) setup using Always On Availability Groups. All the documentation says I should not try to install replication (aka distributor role) on any of the servers ...
0
votes
0answers
30 views
CheckDB Failure on most databases in Dev instance
I have a maintenance plan on one of our infrequently used dev boxes which keeps failing on CheckDB on all user databases and most system databases. When trying to run CheckDB manually, I get the ...
3
votes
2answers
98 views
Why is this column order in my non-clustered index better for my query?
I am working on a query for a table that contains movie tickets. The database holds 380k rows. A rows represents a showing of the movie (which cinema, when, how many tickets and at what price among ...
3
votes
2answers
106 views
SQL Server CPU Issues
We've inherited some SQL server 2014 databases, they are running on Azure IaaS. We're getting complaints about performance and trying to get a grip on it. We're starting to look at CPU performance, ...
0
votes
0answers
36 views
sys.dm_db_index_operational_stats not giving proper statistics in sql server?
While working on Index Analysis in SQL Server 2017,
I observed that sys.dm_db_index_operational_stats not showing index is getting utilized(column name range_scan_count, however when i checked same ...
2
votes
1answer
102 views
How to clear ad hoc queries from plan cache?
As the title implies, I'm going to remove just ad hoc queries (not prepared queries) from plan cache in sql server 2014/2016 because it occupies more than 50% percent of my main memory. Do you have ...
-2
votes
1answer
45 views
SQL Server Database flatten records
Sorry for the confusion. I have below attached table sql server database table holding continuous and non continuous records. When i say continuous that means EP of first record if Beginning Point(BP)....
0
votes
2answers
23 views
Three tables with some matching data and some not - want to data from all three
I have three tables
Costs (47 rows)
Budgets (60 rows)
Committed Costs (12 rows)
All three have the same 2 fields and than a number
JobNumber, CostCode, (either costs, budget, commitedcosts)
They ...
1
vote
1answer
44 views
DBCC check on Always On Secondary Replica
What is the best way to schedule DBCC check on all the nodes in Always On availability group. Can we just schedule the same job on all the nodes at the same time including all the secondaries. In my ...
5
votes
1answer
305 views
SQL Server Service does not start automatically after system restart
I have a SQL Server system that reboots weekly (after installing Windows Updates).
The last three weeks I have seen that the SQL Server service does not start when the server comes back up (...
1
vote
1answer
68 views
Simple SELECT taking too long to run
I am running this simple query:
SELECT *
FROM ABC_TABLE
WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18'
There are 64 columns and we need all of them.
There are a total of 2,066,381 ...
0
votes
1answer
42 views
How to collapse into a single row from multiple rows
Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest ...
3
votes
1answer
38 views
Select/Join multiple row values from Column A if dates in Column B are within 'x' seconds of each other
I have the following Database structure and need a query to work in SQL Server 2008 and also SQL Server 2014.
Database: ASSETS
Table: COMPUTERS
PK (GUID) (nvarchar) (nvarchar)
| ...
0
votes
1answer
37 views
How To get value of column when it exist in content value of column by sql server 2016
I want To get value of column when it exist in content value of column by sql server 2016
For Example:
Table1:
Title1
----
Jack
Benz
Volvoo
Table2:
Description Names
---- ...
1
vote
1answer
36 views
Listener IP not changing after failover
We have a multi-subnet AlwaysOn availability group. There are 2 subnets and those are listed on the cluster and also the listener. Whenever I fail over and ping by the listener name it still shows ...
1
vote
1answer
56 views
Clarifications on Transparent Data Encryption
This is the first time we are applying TDE on production databases that are critical and I have the questions below.
While TDE is in progress:
Can we still access the application that is working on ...
0
votes
3answers
101 views
Creating Windows login/user for Mixed Always on Environment+ Instance without Always ON
How can i use by making changes to below script to run in the environments with Always ON enabled/and not enbaled. Below seems to be working after some really helpful suggestions from Kin. However ...
22
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 ...
