Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

Filter by
Sorted by
Tagged with
-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 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, ...
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 ...
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 ...
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 ...
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&...
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 @...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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

1
2 3 4 5
13