Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
4
votes
2
answers
807
views
Multiple volumes vs single volumes for SQL Server CHECKDB performance
We have a ~10TB database with dozens of data files on SQL Server 2017 Enterprise CU21. We're seeing slower CHECKDB performance when all of the files are on a single volume as opposed to multiple ...
- 90.3k
1
vote
1
answer
3k
views
Update Rows From Table If Table Exists Without Using sp_executesql
I have a situation where I need to run a query on a large number of similar databases. Most of them will have a table that I need to update some rows in but some of them will not have the table.
I ...
- 90.3k
-1
votes
1
answer
446
views
Efficient dimension and fact joining
I have large fact table, and a much smaller dimension table in a simple star schema:
--1.
CREATE TABLE dbo.Dim
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(2000)
)
--index
...
- 47.6k
6
votes
2
answers
3k
views
SSMA for Access - Migration Assistant Wizard fails at link tables
When using SQL Server Migration Assistant for Access with the Migration Wizard, all goes well converting an Access (2016) database to SQL Server Express (2017) until I get to the Link Tables step. ...
- 2,071
10
votes
3
answers
2k
views
DB backups have suddenly gotten smaller
About 8 GB has been trimmed off of the size of our main database. This is reflected in backups and when we look at free vs available space.
Everything seems to be working and we can't find any sign ...
- 90.3k
1
vote
1
answer
551
views
LISTENER_IP = ALL not working
I try to create an endpoint specifying to listen on any IP:
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
...
- 47.6k
1
vote
0
answers
636
views
Speed up reading data from a table
Problem
We have a table that has just 44 rows, and one of the columns is varbinary(max).
This column stores XML files converted to varbinary(max).
Average length of data in this particular column is ...
- 90.3k
0
votes
0
answers
44
views
Database Page Compression and ColumnStore Archive - is data compressed when in buffer pool and when sent to client? [duplicate]
One of benefits of Page Compression or ColumnStore Archive is reduced storage space requirements
Here I have two questions:
When compressed data is read from disk to buffer pool, in the buffer pool ...
- 5,964
0
votes
0
answers
40
views
Searching text using LIKE, and Included Column
SELECT * FROM Table WHERE (Column1 = 'ED69K')
SELECT * FROM Table WHERE (Column1 LIKE '% ED69K%')
I have a wide table with 25 columns on the table. The above queries do need to do key lookup ...
- 23.2k
0
votes
2
answers
365
views
Remove Query Tuning Assistant (msqta) schema and tables
When running the Query Tuning Assistant (using the New Database Upgrade Session) on SQL Server 2017 I have a new schema msqta created in the corresponding database as supposed, along with six tables ...
- 90.3k
0
votes
1
answer
328
views
How to reduce the maximum degree of parallelism at session level?
I have inherited a legacy application that includes a job that creates performance issue at instance level. The DBA told us about this and our team needs to fix the stored procedure that is the most ...
- 1,171
1
vote
1
answer
1k
views
Estimated recovery time and REDO queue size related to DB up time
Can someone help me figure out if my understanding is correct on the below:
On the AG Dashboard for my readable secondary replica I see:
Estimated recovery time (secs) - 4598
REDO queue - almost 24 ...
- 25.1k
0
votes
0
answers
359
views
Clustered Columnstore Index alignment on multiple columns
I have a fact table which is a clustered columnstore index.
It is also:
-partitioned by a single data date column.
-has a nonclustered, aligned, composite unique index (PK) on 6 INT columns, plus the ...
- 265
3
votes
5
answers
14k
views
Windows Logs Application is full of "Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ****]"
I have Windows Server 2016, with SQL Server 2017, and found Windows Log Application is full of Login failed messages (as follows) :
Login failed for user 'sa'. Reason: Password did not match that ...
0
votes
1
answer
1k
views
SQL Server 2017 and TLS - client requirements
I am testing something around SSL\TLS and SQL Server 2017. I have enabled TLS on a test SQL Server instance by creating a self-signed cert and doing the required steps to add it to the Windows ...
- 4,685
6
votes
1
answer
1k
views
Memory-optimized table index size 10X bigger than data
On one of our production servers, I found that index size is 10X times bigger than data size.
These servers and schemas are identical with a similar workload.
CREATE TABLE [dbo].[...
- 90.3k
0
votes
0
answers
202
views
Troubleshooting why I need to frequently run sp_refreshview
I'm an accidental dba for our ERP database (SQL Server 2017). ERP had become unresponsive for a user and I determined that a query running against a view was running very slowly. I thought it might ...
- 1
0
votes
1
answer
126
views
Replication reverted partitioned table to primary filegroup, can I re-link to the partitions?
We have 2 servers:
Server A. production (no partitions)
Server B. New Server (partitions and compression)
We restored a backup to the new server, partitioned a large table by datetime and turned on ...
- 90.3k
3
votes
1
answer
659
views
Availability Group- Redo Rate displayed on AG Dashboard vs Perfmon counter
I am bit confused on checking the metrics for REDO Rate KB/Sec from Always on AG dashboard, which for some scenarios matches with perfmon counter Database replica : Redone Bytes/Sec ( hopefully it's ...
- 90.3k
1
vote
3
answers
7k
views
Are there any things to consider before granting VIEW SERVER STATE and VIEW DEFINITION to Developers?
I would like to grant below to permissions to Developers group, on Production SQL Server:
VIEW SERVER STATE
VIEW DEFINITION (server level)
This is done to make them able to query some of dynamic ...
- 69.5k
5
votes
3
answers
994
views
SQL Server Logon Trigger Implications - are there any side effects possible in my scenario?
Some of developers on my team know passwords from SQL accounts that have extended permissions
We would like to track and be alerted whenever any of developers are using any of those SQL accounts to ...
- 26
4
votes
1
answer
1k
views
Stuck REDO Thread of an availability group
We are facing issues with REDO queue fluctuating as we have readable secondary configuration.
Based on my understanding in newer versions of SQL Server, redo threads have been made parallel. Current ...
- 16.5k
-2
votes
1
answer
71
views
Database scoped configuration applicable to Secondary
I saw some new database scoped configurations like MAXDOP and legacy cardinality for secondary options and have few questions or doubts i am not able to understand
MAXDOP-
How do i setup MAXDOP, say ...
- 90.3k
0
votes
2
answers
48
views
does SQL automatic backup runs even the machine/server is offline
I am wondering if the SQL jobs like automating the database backup runs even the machine/server is offline?
- 1,629
0
votes
1
answer
75
views
Peak load upgrade and downgrade SQL Server 2017 Standard <-> SQL Server 2017 Enterprise
We are currently running on SQL Server 2017 standard edition and for higher traffic weekends like Mothers day we want to go on SQL Server 2017 enterprise version and during non peak hours we want to ...
- 4,160
0
votes
2
answers
1k
views
Best option to index an application log table for quick insertion and retrieval in date order?
Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).
The primary key (clustered) is on [id]. I need to be able to ...
- 47.6k
2
votes
0
answers
260
views
Unhandled Deadlocks with SQL Server 2017 [closed]
We’re running into issues with unresolved deadlocks on the most recent version of SQL-Server 2017, has anybody seen a problem like this before?
Detailed explanation: We migrated our database from ...
- 21
0
votes
1
answer
120
views
SQL Always On automatic failover incase of multiple secondary
I have three node setup for SQL Always on in SQL 2017 enterprise edition. All three are in same subnet and in synchronous commit mode. Now when there is a failover how do I make sure it should always ...
- 10.4k
0
votes
1
answer
31
views
Reaching a Microsoft SQL Server 2017 cluster from another device
Let say I have a python service in one instance. This service needs to reach a MSSQL cluster of 5 nodes that is in an other location, with different IP addresses. Cluster creds:
user: my_user
password:...
- 30.6k
0
votes
1
answer
75
views
Is there a way to tell optimizer not to use index for reads?
Of course I can just drop and recreate or disable and rebuild it.
But for large tables it can take time, and for critical tables it can cause issues.
Is there any "more sophisticated" way to ...
- 47.6k
15
votes
1
answer
22k
views
Clone a database from SQL Server 2019 to 2017 [duplicate]
I try to clone a database from an Sql Server on my local machine v15 (2019) to a server machine v14(2017). I did a .bak file and when I tried to import on the server side it says:
System.Data....
- 451
3
votes
1
answer
365
views
What's the difference between equality (=) and inequality (<>) on BIT field in SQL Server?
Is there difference between WHERE [BitField] = 1 and WHERE [BitField] <> 0?
They seem logically equivalent.
SQL Server, however, disagrees and creates slightly different execution plans. (Which ...
- 47.6k
0
votes
1
answer
2k
views
How to test user permissions
I have two server users, lets say admin and notadmin. I need to test notadmin's permissions. I need set and test VIEW SERVER STATE permission on notadmin, but usually I have access only to database ...
- 39.5k
1
vote
1
answer
835
views
Reduce transaction log file utilisation
I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which ...
- 171
3
votes
1
answer
1k
views
SQL Server High CPU usage and RESOURCE_SEMAPHORE waits
Last week had an issue on one of SQL Servers, CPU started burning over 80 % (normal is 10-30 %)
This lasted for about 2 hours until I manually failed over to secondary replica in AG (and this has ...
- 5,964
3
votes
1
answer
1k
views
Turn off Error AND Usage Reporting SQL Server
Unfortunately, SQL Server usage internet bandwidth and us activity in SQL, for R&D. You can find in your server, this service is called: SQL Server error and usage.
This service is active without ...
- 166
0
votes
1
answer
206
views
Upgrading Operating System only. OS 2016 - 2019
Currently we have a number of Servers that are using Windows 2016 with SQL Server 2017. The support team want to do an in-place upgrade of the OS to 2019 due to 2016 Mainstream support ending in Jan ...
- 423
0
votes
1
answer
112
views
How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs
We are currently running into this issue for one of our availability group design:-
On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours
On Secondary which is configured ...
- 4,685
0
votes
0
answers
201
views
Readers blocking reader on readable secondaries
We recently migrated to AG setup for one of our VLDB's around 20+TB.
Current setup is Multi subnet AG with primary accepting mix workload while heavy reporting offloaded to readable secondary replicas....
- 794
4
votes
3
answers
6k
views
to change collation for system database SQL Server 2017
We are using fresh SQL Server 2017 CU23 Standard Edition database with "SQL_Latin1_General_CP1_CI_AS". Now, I want to change all system databases (master , model , temp, msdb) to another ...
- 69.5k
5
votes
2
answers
825
views
Why does SQL Server not perform constant (UNION ALL) branch elimination with OPTION(RECOMPILE) when selecting the result into a scalar variable?
We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). This normally ...
- 90.3k
4
votes
1
answer
1k
views
How to migrate Unicode UTF-8 CLOB data from Oracle to SQL Server 2017 UTF-? [closed]
I am currently assisting in a migration effort of this application from Oracle 12c to SQL Server 2017. Initially I just performed table inserts using openquery to Oracle. I discovered that the tables ...
CommunityBot
- 1
2
votes
1
answer
247
views
Invalid object name 'ApexSQL.ApexSQL_SourceControl.Objects'
I was trialing Apex source control - trial ended. Uninstalled Apex (well I thought I did). Today I deleted the Apex database. Now I get
Msg 208, Level 16, State 1, Procedure ...
CommunityBot
- 1
0
votes
0
answers
32
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?
- 1
1
vote
0
answers
122
views
Does dm_os_memory_clerks show all SQL Server memory?
We are running SQL Server 2017 on a machine with 256GB Ram. Noticed a dramatic shift in some performance counters (e.g. page life expectancy usually very high, but had started fluctuating a lot the ...
- 113
1
vote
1
answer
488
views
Set the Deadlock Priority to Lowest possible value, but my script is still NOT always the victim in deadlock situations
I have below two queries on Production:
Query # 1: Script that deletes older historical data from core table "Table1" based on "Date" column
There are ~20 tables that point to &...
- 5,964
2
votes
0
answers
576
views
Telemetry_xevents session and auto_stats
I've noticed on a number of instances that the telemetry_xevents session is full of auto_stats events.
The statistic is not being updated. I've even disabled auto update statistics and auto update ...
- 180
0
votes
0
answers
90
views
SQL Cluster (Always On) - Database table replication
I have the following scenario at this moment.
Two old sql servers - they have setup some kind of publisher subscriber replication from one database to another but only some of the tables at a 12 hour ...
0
votes
0
answers
26
views
Aborting long running query under AG configuration [duplicate]
Currently we have a process where any query running over 2 mins (only select) in primary database participating in db mirroring is killed, running every 5 mins.
As we have moved to Availability groups ...
- 2,210
0
votes
2
answers
2k
views
How do you configure a 4-node AlwaysOn cluster to have two listeners?
Looking to build a 4-node (servers A, B, C & D) AlwaysOn Windows 2016 failover cluster with 2 nodes in the primary datacenter for HA, and 2 other replica nodes in the DR datacenter for disaster/...
CommunityBot
- 1