All Questions
Tagged with sql-server-2017 query-performance
27 questions
1
vote
1
answer
63
views
Get All Windows between Start and End Dates
Here is the SQL Fiddle for my question: https://sqlfiddle.com/sql-server/online-compiler?id=ab1634d7-fec7-4918-ac1c-3f4fcac8dc92
I have the following sample data:
DROP TABLE IF EXISTS #Price
CREATE ...
- 2,540
0
votes
1
answer
382
views
Wait time on server replies is sometimes very long when running the query over and over
As seen in this screen shot, I'm running the same query over and over again, comes back with 0 rows (as expected). For some reason I'm seeing very large wait times on server replies while running this ...
- 53
4
votes
1
answer
437
views
Selecting from sys.dm_tran_locks takes 100+ ms
Our application uses SQL Server application locks and we have cases where we need to see whether a given lock exists or not. To do this, we retrieve and filter information from sys.dm_tran_locks.
...
- 263
0
votes
1
answer
192
views
Will update statistics fix wait type IO_COMPLETION issue in Microsoft SQL Server?
I am using Microsoft SQL Server 2017 and recently we have come up with a situation where we see there is Wait Type IO_COMPLETION is contributing 60%. After performing update statistics this issue got ...
- 251
0
votes
1
answer
1k
views
Filtering a recursive CTE as view
i'm using MSSQL Server 2017 and have a CTE used in a view to get the root element of a hirarchical tree linked to one specific element.
If the CTE is used directly and the base elements are allready ...
- 3
2
votes
0
answers
381
views
Service Broker performance issues after upgrading from 2012 to 2017 - tons of LCK_M_IX
Last night we upgraded a SQL Server 2012 server to SQL Server 2017 + CU 29. This is one of a matched set - we send the same message to both and each processes it. No, this is not AlwaysOn; we're ...
- 433
2
votes
1
answer
577
views
Avoiding Hash match to improve CPU usage for a specific query
We are trying to resolve or figure out change in plan for one of the top sql queries by CPU.
This query we have found (it's a vendor-based product) which unfortunately I do not have access to extract ...
- 794
-2
votes
1
answer
819
views
when update large table it take too much time to update
I work on sql server 2019
when update table Z2DataCore.parts.SourcingNotMappedParts i found slow and long time when update
estimated execution plan
actual execution plan
Update statement takes 26.5 ...
- 147
-3
votes
1
answer
3k
views
Select records based on priority
I have data stored as:
TestId | Priority |
--------+-----+-----
test103 | 2 |
test104 | 2 |
test105 | 1 |
test106 | 0 |
test107 | 1 |
test108 | 2 |
...
user244947
5
votes
3
answers
1k
views
Query runs indefinitely - what is the root cause ? (not asking how to fix)
I have a simple query with a grouping, which worked fine, until I added one more join:
select
[ca].Value,
[c].cID,
[c].Name
from ReportingDB..Table1 [t1]
join MainDB..Companies [c] on
...
- 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
63
views
Monitoring Query which executes thousand of run/minute and is generally fast
I am looking for some advise here on one of our SQL database server with below behavior-
Queries running for this databases are generally considered good with avg run time of 20 ms.
Suddenly on some ...
- 794
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
3
votes
2
answers
2k
views
Why is it faster to create index and run query than run query without it?
I have table with few million rows. It contains logs from external service, so I've decided not to index it (lot of inserts, sparse reads).
When I run query that reads from the table without index, it ...
- 103
2
votes
1
answer
747
views
What can cause delay in SELECT statement execution and high ASYNC_NETWORK_IO even with "disregard results after execution"
We are facing performance problems on our SQL Servers.
7 servers, all running the same application, all have the same problem.
SELECT @@VERSION
Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0....
- 2,845
0
votes
1
answer
77
views
User info retrieval takes too long - ASP.NET Membership
I have an application that has been taken from a sharepoint user control and converted to plain web pages. Everything is working fine - I can sign up and retrieve my password, etc. However, when I try ...
- 1
8
votes
1
answer
454
views
Improve the poor performance of the sys.dm_db_stats_properties DMV
We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL ...
- 253
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
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
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
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
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
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
5
votes
1
answer
471
views
Empty result takes longer with all columns included
I'm executing a query every 1 second and most of the time it returns no results (SQL generated by Entity Framework):
SELECT TOP (5)
[Extent1].[ID] AS [ID],
***30 more columns***
FROM ...
- 153