Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
865
questions
1
vote
1answer
55 views
Create a Sequence with the calling user as the owner
Background
I have a SQL User that has the following permission:
GRANT CREATE SEQUENCE ON SCHEMA::seq TO SequenceCreator
GO
I would like to be able to have this user create a sequence that is owned ...
0
votes
0answers
48 views
For SQLServer, does the order of index creation (not the column ordering) affect SQLServer's decisions for the execution plan?
I found an interesting behavior, where the execution plan is affected by which index gets created first. Both indexes have have different key and include columns. The difference in the execution plan ...
1
vote
1answer
180 views
+50
Why is Index causing bad estimation and execution plan
SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) -
ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0)
FROM [Credit] M WITH(NOLOCK)
INNER JOIN [Transaction] T4 WITH(...
1
vote
1answer
56 views
SQL Server Always ON - LOGPOOL_CONSUMER wait and replication issue
I'm facing a strange issue in one of my customers AG. There're 4 instances under this AG.
Staring a few days ago, my customer complains about some query (SELECT) timeouts issues. When I faced this ...
0
votes
0answers
34 views
Database Restore on two different servers but index fragmentation differs
We have 2 SQL servers in our test ENV, both with the same database on. There are some tables that are replicated from the DB on the one server to the DB on the other server. While we're testing we ...
0
votes
1answer
27 views
Calling function that takes user defined table type as input, in a select query, with data in the table type from the table selected from
Hopefully the title makes some amount of sense. Basically I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue ...
1
vote
1answer
143 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 ...
-1
votes
1answer
26 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
...
10
votes
3answers
1k 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 ...
1
vote
1answer
42 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 (
...
0
votes
0answers
43 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 ...
1
vote
0answers
122 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 ...
2
votes
1answer
66 views
SQL Agent stopped recording history (SQL Server 2017)
I have a Dev server that has been running without issue for at least 2 years. This dev machine was a VM Clone of the Production server.
On 2021-06-05 something happened on this server that altered ...
0
votes
0answers
30 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 ...
0
votes
0answers
25 views
Can we track historically or see queries on server is landing on which schedulers
The ask may sound weird but we are seeing sort of awkward behavior. Or may be we do not have historical data to confirm if it is true.
For our 4 sockets 16 core processor SQL server with total of 64 ...
0
votes
2answers
29 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 ...
0
votes
1answer
51 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
vote
1answer
72 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 ...
0
votes
0answers
41 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 ...
0
votes
0answers
27 views
Failover an Availability Group during a backup
This is a scenario I hope won't happen, but if I was to perform a manual failover on an availability group halfway through a database backup will this cause the backup to fail or would it continue?
I'...
0
votes
1answer
43 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 ...
0
votes
0answers
23 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 ...
0
votes
1answer
34 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 ...
3
votes
1answer
57 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 ...
0
votes
0answers
59 views
Azure App Service connect to SQL Server using Active Directory principal (Windows / AD authentication)
Can Azure App Service connect to SQL Server (not Azure SQL DB, not Azure SQL MI) using Active Directory service principal (domain\user.name) or Azure AD Managed Identity ?
Tried creating login for ...
-2
votes
1answer
35 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 ...
0
votes
2answers
30 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?
0
votes
1answer
39 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 ...
5
votes
3answers
356 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 ...
3
votes
1answer
89 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 ...
2
votes
0answers
87 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 ...
0
votes
1answer
20 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 ...
0
votes
0answers
38 views
DBCC Check DB fails due to operating system error 665
We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665.
The fragmentation level of the disk drive is 0% as it is automatically ...
0
votes
1answer
53 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 ...
1
vote
3answers
231 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 ...
3
votes
1answer
214 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 ...
0
votes
1answer
32 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 ...
1
vote
1answer
39 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 ...
0
votes
1answer
21 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 ...
0
votes
1answer
52 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 ...
0
votes
0answers
48 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....
3
votes
2answers
125 views
Blocking on readable secondary replica
We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries.
Generally on old setup we have select queries running for longer duration as the ...
3
votes
1answer
1k 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....
3
votes
3answers
264 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 ...
0
votes
0answers
22 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?
3
votes
1answer
197 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 ...
0
votes
0answers
40 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 ...
0
votes
0answers
16 views
TDE State Changes in Error Logs
I am experiencing some messages in our Error logs on a Development database relating to TDE. This is running on SQL Server 2017 CU21.
[TDE] SetDbeState on DEK 0x0000000000000000: Old State: None (0), ...
0
votes
0answers
22 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 ...
0
votes
0answers
28 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 ...
