Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
0
votes
1
answer
81
views
When should I Switch In from Stage Table to a SQL OLTP Partitioned Table
I have an OLTP Database with a big table (30M records a day / 400TPS). I want to implement Partitioning & Sliding Window on that table with Monthly Partitions plus a temp Stage-Table.
The time to ...
- 9
4
votes
1
answer
864
views
ALTER INDEX REORGANIZE questions
I have few tables with indexes that are highly fragmented (over 90% fragmentation) and are big enough (tens of gigabytes) in size. Goal of defragmenting those is to reduce space consumption by above ...
- 5,964
2
votes
1
answer
54
views
Speed the response time of the Query
So I have this query that I am using on an API endpoint, the query takes between 3-4 seconds to run, returning a small OFFSET of 10 rows. Is there a way I can put indexes somewhere or improve on ...
- 21
0
votes
1
answer
39
views
SQL drop connection is similar with set single user command?
Would like to ask when i detach a database , i select the drop connection properties in the box. Is this drop connection properties is similar function as "set single user with rollback immediate&...
- 3
1
vote
2
answers
2k
views
Do the windows local admin group or domain admin group members automatically get the sysadmin permissions?
https://learn.microsoft.com/en-us/sql/ssms/agent/select-an-account-for-the-sql-server-agent-service?view=sql-server-ver15#windows-domain-account-permissions
This link says that:
member of the Windows ...
- 3,499
-2
votes
1
answer
91
views
Delete millions of rows on a badly designed table [duplicate]
I have identified Millions of duplicated rows on a table I have inherited by using this query:
SELECT COUNT(*) AS NumRecords, AccessID, LEFT(SQLTEXT, 5000)
FROM Table
WHERE AccessID=5012
GROUP BY ...
- 447
1
vote
1
answer
519
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 ...
- 2,540
1
vote
1
answer
737
views
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(...
- 195
1
vote
1
answer
718
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 ...
- 11
0
votes
3
answers
369
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
I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue and theDate. Now assume I have a large table, called ...
- 249
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 ...
- 113
-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
...
- 265
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 ...
1
vote
1
answer
550
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 (
...
- 11
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
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 ...
- 5,964
2
votes
1
answer
546
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 ...
- 213
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 ...
- 1,063
0
votes
2
answers
363
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
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 ...
- 794
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
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 ...
- 407
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
125
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 ...
- 301
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 ...
- 2,210
-2
votes
1
answer
70
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 ...
- 794
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?
- 13
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 ...
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 ...
- 5,964
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 ...
- 794
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 ...
- 101
1
vote
1
answer
658
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 ...
- 1,063
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 ...
- 742
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 ...
- 5,964
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 ...
- 103
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 ...
- 103
1
vote
1
answer
834
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
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 ...
- 63
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 ...
- 794
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
3
votes
2
answers
2k
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 ...
- 794
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
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 ...
- 1,106
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
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
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
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 ...