All Questions
Tagged with sql-server-2017 performance
51 questions
0
votes
0
answers
251
views
High number of Latch Waits, is it a problem, and how to investigate?
We have reports of performance issues on one of our applications. I tried to have a look for anything obvious, but I'm an Oracle DBA, and my SQL Server knowledge is pretty limited - we don't have any ...
- 123
0
votes
1
answer
60
views
Storing segmented time series - performance issue caused by triangle relationship / maximum search
I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a ...
- 3
3
votes
1
answer
483
views
Performance problems in SQL Server. Parameter Sniffing or not in my scenario?
Here I am facing a performance problem that occurs with particular statements in a stored procedure, that has many statements, on occasion
SP is executed once a second, normally completes less than 50 ...
- 5,964
3
votes
1
answer
2k
views
How much resources does a sleeping connection require in SQL Server?
One of our DBAs complained to our team that he noticed about a dozen sleeping connections are virtually permanent. Each of them indicates a very short query (a single record from a single record was ...
- 1,171
0
votes
2
answers
73
views
SQLCONNECTIONPOOL growing very huge
One of my db server SQLCONNECTIONPOOL growing very huge,it consumed 55GB , out of 65Gb (sql MAX) memory)
Environment: Microsoft SQL Server 2017 (RTM-CU25) Standard Edition
How can i reduce the ...
3
votes
2
answers
740
views
How can Temp Table creation time be reduced in terms of CPU time?
Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact
These are ...
- 5,964
0
votes
1
answer
473
views
Insert Into Table performance questions
I am writing a stored procedure, main purpose of it will be the insertion into table
Procedure will be signed by certificate, and sending email notifications
Procedure will accept user-defined table-...
- 5,964
4
votes
4
answers
2k
views
Query Store takes a very long time to load duration
I'm running Query Store to make a DB perform better.
It's been working fine until now..
When i try to load the Top durations of last day it took me 26 min to load the screen.
The SQL version im ...
- 53
0
votes
3
answers
776
views
How Important is the buffer pool really?
I have been doing some testing of a new (virtual) server which is to replace an existing production server. We suspect the current production server is overspec'ed and therefore are tweaking the ...
- 2,083
0
votes
1
answer
342
views
Table Lock Escalation - Performance Questions
I have few questions on table lock escalations, please confirm or disprove:
Index Scan operator in a query results in Table Lock Escalation ? always or not (small tables) ?
When Shared lock is ...
- 5,964
0
votes
1
answer
576
views
SQL Server high SQLCONNECTIONPOOL consumption
SQL Server Version : SQL Server 2017(RTM-CU21) on Linux
Runing environment : kernel 4.19,Docker 18.09
Docker Image : docker pull mcr.microsoft.com/mssql/server:2017-CU21-ubuntu-16.04
Problem ...
1
vote
1
answer
1k
views
Identify the line of a procedure of a spid/session
In SQL Server 2017, how can I identify or pinpoint at which line of a stored procedure a spid (session) is currently at and what the execution plan of the current statement looks like?
- 11
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
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
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
2
answers
2k
views
Multiple disks vs single disk on virtualized environment
We're planning to install a SQL Server on this physical architecture:
A hyper-v cluster based on windows server 2019
A cluster shared volume where all VMs live
A full-flash SAN that contains the CSV
...
- 602
0
votes
0
answers
454
views
Why SQL Server's MERGE has such a huge Excessive grant when compared to INSERT + DELETE?
I am trying to optimize a security computation process for a Web application by trying to making it synchronous (compute on the fly for an entity) instead of asynchronous (large computation for all ...
- 1,171
5
votes
2
answers
7k
views
log send queue size and redo queue size in AG
I am trying to figure out the way to monitor these 2 events
log send queue size - I can see this in perfmon
Redo queue size - I can see in dmv but not in perfmon counter
Is there any way of using ...
- 2,210
0
votes
3
answers
289
views
How to efficiently recompute the data in a rather large X table?
I have recently noticed performance issues for the main application I am developing right now (it is a legacy one). A job triggers a stored procedure that computes the security for all the documents ...
- 1,171
2
votes
1
answer
220
views
CHECKDB with default settings and parallelism involved [closed]
This would be my follow-up question because I am trying to understand the CHECKDB behaviors
While running CHECKDB without any MAXDOP specified on below 2 servers:-
Server with 10 Vcores, MAXDOP at ...
- 794
5
votes
1
answer
14k
views
Best practices for SQL Server block size while formatting disks for data and log files
What is the current best practice or general guidance on formatting the filesystem for SQL server database files.
Currently we have SQL Server file system, NTFS formatted with 64KB block size based on ...
- 2,210
0
votes
1
answer
3k
views
TempDB Log File Growth - questions
Problem:
On one of the production boxes, TempDB transaction LOG file grew unexpectedly, filling the drive
It was 32 GB but now it is 240 GB
Questions:
What is the mechanism of TempDB log file usage ?
...
- 5,964
1
vote
0
answers
925
views
SOS_CACHESTORE_CLOCK Spin Lock
We recently moved from SQL Server 2012 (SP3) to SQL Server 2017 via restored backup. When we were running on 2012, performance was adequate. Since moving to 2017 we have noticed a degradation in ...
- 2,540
1
vote
1
answer
1k
views
Composite index performance with varchar columns
I have large fact tables with composite non-clustered indexes, something like this:
NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey)
The columns ending with "Key" are varchar(...
- 265
0
votes
1
answer
2k
views
Is filtering on int better than filtering on date [closed]
I have a system where most of our table has the current datetime as primary key and we filter most of the data based on the datetime.
The question, if I move this datetime to another new table and ...
- 9
5
votes
2
answers
186
views
Query Execution oddities between SQL Server 2008r2, 2017 and 2019
I have a very basic query that was running completely fine with index seeks across the board on SQL Server 2008r2. When we migrated to SQL Server 2017, it started performing worse, and now does a ...
- 379
6
votes
4
answers
6k
views
What is the best SQL server backup strategy for large DB with few tables and lots of immutable data?
We have a large DB (500GB and expanding). 95%+ of the data is stored in 3 tables (one table has 2bn+ rows). The data is largely immutable - i.e. once it's added it's only then read afterwards. We can'...
- 899
7
votes
2
answers
554
views
Force SQL Server to use fragmented indexes?
I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index ...
- 173
1
vote
3
answers
2k
views
Slow query in production, bad execution plan or bad index?
I've just fixed a production performance issue by dropping an index and recreate it.
I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
Arguments ...
- 111
0
votes
0
answers
41
views
How can I obtain a list of our applications' SQL statements that do not make use of indexes?
We have a system that processes thousands of statements a second, all running quickly. With so many statements we are looking to fine tune with small improvements to have a knock on large improvement ...
- 305
22
votes
4
answers
33k
views
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
On Production SQL Server, we have following config:
3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to a single Dell SAN storage unit which is a RAID array
From ...
- 5,964
10
votes
3
answers
3k
views
Long Running Query on Read-Only Replica that takes moments on the Primary
I've got a 4 node AG setup as follows:
VM Hardware Configuration of all nodes:
Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710)
16 vCPUs
356 GB RAM (long story to this one...)
max ...
- 9,487
7
votes
2
answers
636
views
can anyone help me with this awful query plan?
The query:
SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5,
Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9,
Object1.Column10, Object1.Column11,...
2
votes
1
answer
2k
views
How can you have a Buffer cache hit ratio of 100% with PLE of 103? [duplicate]
I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE ...
- 6,298
-2
votes
2
answers
184
views
First query waits for loading the whole table in memory
I'm testing SQL Server 2017 performance. I've create a table like the following one:
CREATE TABLE [dbo].[AssessmentResponses](
[ResponseID] [int] IDENTITY(1,1) NOT NULL,
[AssessmentID] [int] ...
- 231
0
votes
1
answer
68
views
Different manifestations of the same logic yield very different execution times [closed]
Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested.
For every widget in the ...
- 409
2
votes
3
answers
505
views
Struggling to create an efficient index for this query
I am struggling to create an index for a query and unfortunately I cannot change the query at all as it part of an ERP system. The problem is this query has over 1m reads and sometimes a duration of ...
- 41
12
votes
2
answers
3k
views
Why does a DELETE query run in one format much longer than in another?
I have specific cleanup code that tries to remove some duplicates.
This runs perfectly on many customer sites. The logs tell me that at least 1 sec up to 45 sec is consumed by this query:
DELETE ...
- 230
1
vote
1
answer
1k
views
When to Disable and Recreate Indexes in Data Warehouse ETL?
We are moving data from Staging Database into Kimball Data Warehouse DB.
I am hearing different ideas regarding when to disable and rebuild indexes.
Stage tables are truncated every day for each new ...
user162943
0
votes
1
answer
2k
views
TSQL or SSIS for Direct Insert Speed Performance
I am moving data from Table A into Table B. Tables have the exact same DDL, table structure, and are straight inserts. We are Not transforming or modifying data.
If TSQL Server and SSIS have the ...
user162943
3
votes
1
answer
1k
views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
- 465
2
votes
1
answer
755
views
Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows
We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered ...
- 136
3
votes
1
answer
122
views
I've got 60 million sales of 800 different liquid products whose price change daily
..and I'm having a bit of a battle getting to customer stats like who's bought the most, and when was their last purchase.
I've no problem writing the query, it's just really slow
Sales
CustID, ...
- 344
1
vote
1
answer
4k
views
Inner join with greater or equal VS where condition using a variable
Suppose I have a table with a unique column and this columns has only integer numbers.
Eg: ID column with rows from 1 to 999.
My intention is to return only rows when my ID is greater than 500, for ...
3
votes
1
answer
4k
views
Hash Match Spill
I've got a hash match spill going on here.
I've updated statistics with FULLSCAN on the tables involved so it isn't that.
Any pointers much appreciated.
https://www.brentozar.com/pastetheplan/?id=...
- 85
0
votes
1
answer
136
views
Even out disk i/o
We noticed that when a few of our Microsoft SQL server jobs run disk I/O goes from a background of 1-5 disk queue length to 100-700 disk queue length. The thing is the jobs only run for 1-2 minutes so ...
- 191
6
votes
3
answers
4k
views
Using CROSS APPLY OPENJSON causes Azure to hang
I have a table with around 8 million rows with a schema of:
CREATE TABLE [dbo].[Documents](
[Id] [uniqueidentifier] NOT NULL,
[RemoteId] [int] NOT NULL,
[Json] [nvarchar](max) NULL,
[...
- 175
10
votes
1
answer
4k
views
Unbelievably slow and unusable Query Store
I'm beginning with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It just keeps saying "Waiting" for hours and hours. Query on the ...
- 945
1
vote
2
answers
3k
views
Slow query performance when searching for a particular value, but fast with a different value on the same column
I have a SQL Server 2017 select statement that is executed by the front end application. The query has three joins and UNION. Each join statement has a clause where NAME LIKE '%ibm%'.
When I change %...
- 179