Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
625
questions
-1
votes
0
answers
60
views
SQL Server Stored Procedure performance slows from 30min to 22 hours overnight
We are running SQL Server 2019 web edition on AWS RDS (t3.xlarge which is 4vcpu x 16gb). We have been running a SQL Job Agent scheduled job that executes approx 50 Stored Procedures for flattening ...
0
votes
0
answers
41
views
What's the best practices / recommendations for paginating data from different tables?
We are trying to break up a table called Transactions into more specific tables where each table is essentially inheriting from an abstract database modal called Transaction
(which holds all the ...
- 1
1
vote
0
answers
10
views
Wrong query plan for GIN index
I have the following repeatable case.
I have an uuid[] column with a gin index, which stores all unqiue uuids, without doubles and repeats.
Test table:
CREATE TABLE test
(
id serial not null, ...
- 11
0
votes
0
answers
14
views
Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan
I have the following query:
SELECT 1
-- my unique text to find this query in query store views
FROM MyTableA
INNER JOIN MyTableB Alias
ON Alias.Column3 = 'value'
...
- 1,600
-1
votes
1
answer
43
views
queries with significant difference of estimated and actual number of rows - skew data - in the query plan
I am using dbcc show_statistics looking for skew data in my histogram and improving the quality of my statistics.
OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.
If ...
2
votes
2
answers
71
views
Can a redundant index improve SELECT performance?
Is it possible that adding a redundant index improves the performance of a query? I encountered a case like that several days ago and since then I've been scratching my head: how?
The MariaDB 10.4 ...
- 143
0
votes
0
answers
38
views
MariaDB SELECT query keeps stuck in one system but not in another
This is the reduced query showing problem:
SELECT
t.*
FROM
transactions t
LEFT JOIN assignments a ON a.transaction_id = t.id
LEFT JOIN users u2 ON u2.id = a.user_id
WHERE (
u2....
- 103
4
votes
2
answers
183
views
Poorly performing stored procedure using cached data while testing
I have a stored procedure that takes about 15 seconds to run the first time, and the subsequent runs take 1 to 2 seconds. If I wait for an hour and run it again then it takes 15 seconds again.
I'm ...
- 135
0
votes
0
answers
41
views
Optimizer Occasionally chooses a bad plan - how to tune query to ensure it is consistent
I have a query in my environment which occasionally gets a "bad" plan and takes a long time to run.
We can see from the query store output below that the query normally uses plan 25493651 ...
- 1,600
0
votes
1
answer
31
views
Partitioning vs Archiving Data for a specific use case
I'm not going to ask the difference between partitioning and archiving as there are countless of information out there on the net discussing them, and I have read the details of partitioning as ...
- 1
0
votes
1
answer
45
views
Bad PostgreSQL planner decisions on column with uneven distribution
Facts
I have following simple table that represents financial transactions:
CREATE TABLE transaction (
"user_id" text not null,
"offset" serial not null,
"tx_data&...
- 3
0
votes
1
answer
38
views
Parallel Insert is not showing in MSSQL Stored Procedure execution Plan
As you can see in the above screenshot, this query provides a parallel insert:
INSERT INTO #StateAllocationData WITH (TABLOCK)
(ProjectID,StateId,StateLineDescriptionId,PartnerID,Value)
SELECT @...
- 11
3
votes
0
answers
43
views
Evaluating Success of CTFP Value Change
We have recently made a change to the CTFP setting on our test server with a view to rolling this out to live.
Our current CTFP is 15 so we tried setting it to 50 which is often seen as a better value....
- 1,600
0
votes
1
answer
127
views
Why UPDATEs are faster when WHERE key is sorted in SQLite? How to improve performance?
I have a large database and a file with ID-Name pairs in each line, where ID corresponds to the primary key of the table in the database.
I need to write all the names from the file into the database ...
- 3
0
votes
1
answer
17
views
How to make the first match in a group a different group
I need to group by a certain column and to make the first occurrence into another group.
select * from table;
id
currency
amount
1
USD
1
2
USD
2
3
EUR
3
4
IL
4
5
EUR
5
6
USD
6
So something ...
- 437
1
vote
0
answers
36
views
Slow Query Log Only Logs Few Queries
Slow query is enabled but only logs few queries when you retrieve the slow log. It’s been working ok all last week, suddenly this week, it only logs just 2 or 3 queries.
Long query time = 0.01 and I ...
- 11
-1
votes
1
answer
104
views
LIKE query with leading wildcards in large MySQL production database
According to insights in Amazon RDS the following query in a large MySQL production database is causing high loads ( ~ 50.000.000 entries):
SELECT * FROM entities WHERE status='ready' AND user_id='...
- 215
-4
votes
1
answer
40
views
slow postgres performance with simple querys
after alot of reading i found multiple sources saying postgres should be able to handle ~100 million rows
i set up a simple table with:
CREATE TABLE trade
(
id INT PRIMARY KEY NOT NULL,
symbol ...
2
votes
2
answers
47
views
Find Which Queries will be affected by Proposed Change to CTFP
The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming ...
- 1,600
0
votes
1
answer
120
views
SQL Query, how to know if is resource intensive? I/O
I am not a SQL guru, but I am troubleshooting an issue with our marketing platform as its become slugish and slow, I've requested the top 50 heavy/resource intensive queries executed on our sql server ...
- 357
0
votes
0
answers
28
views
Database design for fixed records
Potentially trivial database design which I'm trying to overthink.
Let's say that I have:
A fixed number of records (on the order of the 1000s, you can think about a static product catalog).
The ...
- 1
2
votes
2
answers
344
views
Need help tuning a complicated query
I've inherited a rather complex stored procedure that is causing timeouts in our Production environment. I've used SQL Sentry Plan Explorer to help me look at some of the issues. I've identified a ...
- 121
0
votes
1
answer
150
views
mysql cpanel database using too much memory! [duplicate]
I've been trying to figure out why mysqltuner is reporting such dangerous amounts of maximum possible memory usage 63.2G (404.10% of installed RAM).
I'm an amature at this and could do with some help ...
1
vote
1
answer
192
views
More columns vs. more rows to optimize storage and performance?
I have a table with this structure:
ticker VARCHAR NOT NULL,
interval VARCHAR NOT NULL,
ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
price ...
- 217
0
votes
3
answers
523
views
MySQL's maximum memory usage is dangerously high
I'm trying to understand what is not working in my configuration.
When I run the
[root@server ~]# ./mysqltuner.pl
>> MySQLTuner 1.9.8
* Jean-Marie Renouard <jmrenouard@gmail....
2
votes
1
answer
94
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 ...
- 765
0
votes
0
answers
82
views
When is the mysql performance schema event "wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done" fired?
I am using Aurora Mysql 5.7.mysql_aurora.2.07.2 and am facing bottlenecks on load testing with a write-heavy workload. On enabling performance insights I noticed that a large number of sessions were ...
- 101
0
votes
0
answers
58
views
PostgreSQL Database Performance issue
I have performance issue with PostgreSQL Database. These are excerpted related tables:
CREATE TABLE public.patient (
patient_id serial4 NOT NULL,
user_id int4 NULL,
cid varchar(13) NULL,
...
1
vote
0
answers
65
views
Potential causes for unusually long queries (postgres)
Recently we've noticed some SELECT queries taking an unusually long time to complete and are a bit stumped on what the reason could be.
Looking at DB (and server) CPU and IO during the time the ...
- 11
0
votes
1
answer
85
views
How do I move PostgreSQL temp_files to a different tablespace/disk for CURSORs
While working on performance improvements on our PostgreSQL database (Ubuntu Focal, PostgreSQL 13.3), I created a "temporary" tablespace on a fast local NVME drive. This works well and both ...
- 103
0
votes
0
answers
101
views
What could cause "spikes" of slow queries in MongoDB?
I operate a fairly busy HTTP backend over a MongoDB cluster consisting of a primary instance and two secondary nodes. Most commands and queries are served by the primary DB node, and on the surface, ...
- 101
0
votes
0
answers
72
views
Left join performing very badly
Consider the following situation, where the two tables below need to be joined. The query is currently performing very badly. There are no indices defined nor are the ids marked as PK.
I was thinking ...
- 153
0
votes
0
answers
82
views
SQL Server 2014 consuming more memory than max server memory setting
For the past month, I have noticed our SQL Production server has been consuming more memory than we allocated for it. This is the current structure:
Total OS Ram: 31Gb
SQL Server currently using ...
- 63
1
vote
2
answers
357
views
Memory utilization by SQL Server 2016 [duplicate]
I am running a SQL Server 2016 Enterprise Edition on Windows Server, with 128 GB of memory. During installation, I assigned 124 GB to the SQL server, since the server is dedicated to SQL only, leaving ...
- 11
-2
votes
1
answer
121
views
LEFT JOIN Performance Issue
I have a view, which has a column that comes from query
Without this query my count(*) against that view executes in less than 2 seconds, whereas it takes 2 days for the view with above extra column ...
6
votes
1
answer
882
views
SQL Server table query with pagination performance tuning, understand the current solution
as stated in the title i start a performance tuning of a table query with pagination generated by a legacy program that use Linq To SQL as ORM.
I have found this resource in which is highly ...
- 287
0
votes
0
answers
38
views
Proceeds JOIN only if condition met on the Owner table
I have many hasOne relationships like this:
components
component_headings
component_images
Here one Component can only have one hasOne to any of [headings, images].
Logically when a Component.type =...
- 101
0
votes
1
answer
169
views
Getting warnings while generating query Execution plan in Oracle , PLSQL developer
These are the steps I follow to generate a real Execution Plan in PLSQL developer :
1)Using this hint in my main query /+gather_plan_statistics/
select /*+gather_plan_statistics*/ from mi_factcustomer
...
- 1,366
0
votes
0
answers
27
views
Oracle Index Clustering Factor
My question is about Clustering Factor (CF) of Indexes in oracle
I have created a table as follows :
Create table T2 as select c1,c2,c3,c4,c5,c6 from T1 order by c1,c2,c3,c4,c5,c6;
Create Index IDX ...
- 141
1
vote
2
answers
703
views
SQL Server how to upload big json into column, performance issue
I am experiencing poor performance on a single row insert/update statement over a table when a nvarchar(max) column has few MB of data.
Here my table structure :
CREATE TABLE [dbo].[tbl_set_Cart](
...
- 287
-1
votes
1
answer
60
views
Database Tuning for 3rd Party Software Database [closed]
I'm starting out as a SQL Server consultant trying to focus on maintenance and performance improvements. I'm using Brent Ozar's first responder's kit and tools. I've had good success with one client ...
- 101
1
vote
2
answers
522
views
Query Performance improvement in Cassandra
I have a table in my Cassandra db.
CREATE TABLE table (
pk uuid,
status int,
location text,
type text,
id text,
updatedtimestamp timestamp,
PRIMARY KEY (pk)
);
CREATE ...
- 13
0
votes
2
answers
135
views
Sql Server Disk I/O Troughput In Performance monitor
i use sql server 2019 and trace flag 1117(Grow All Files in a FileGroup Equally) is on in my DataBase.
I need to consider the correct number of data files for primary file group according to my system ...
- 11
-1
votes
1
answer
139
views
Postgres IO bottleneck [closed]
I'm getting consistent 100% IO utilisation with quite low overall throughput, which is causing system slow down, I'm unsure whether this is a 'real' IO limit or the result of configuration and ...
- 11
1
vote
2
answers
68
views
DBCC shrink file history
I ran shrink file(.mdf) through GUI by selecting Task -> Shrink -> Files and selected second option i.e Reorganize pages before releasing unused space. Now, I would like to know the history of ...
- 79
2
votes
0
answers
153
views
How to determine the Tipping Point of which Physical Join Operator gets used to process a query?
I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
- 26.7k
3
votes
1
answer
125
views
sp_HumanEvents @event_type = N'blocking' and logging job help needed
Those who use excellent sp_HumanEvents, maybe the author himself, please help me understand something I am missing (cause of my stupidity).
#1
When monitoring blocking, blocked process threshold must ...
- 935
0
votes
1
answer
51
views
Could hardware RAM disks (with battery) have any benefit in database servers?
For database servers with many writes, I am wondering why can't these ramdisk/ramdrive be a solution to avoid wearing out SSDs and to avoid losing data if too much is kept in normal ram.
Can these ...
- 133
0
votes
0
answers
30
views
Sql id and plan creation pattern
Oracle database.
I've got this customer who is complaining about some ODI application taking longer than before (meh!).
Currently, it is not clear what the app does (I'm asking..) but he told me that ...
- 19
1
vote
2
answers
421
views
I am burning trough NVME SSDs, 3x 60GB MySQL databases (web crawler)
I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 3000-4000 queries per second (each server) non-stop, with short spikes to 12000-14000.
I am ...
- 133