Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
456
questions
0
votes
0answers
7 views
MariaDB game server config optimization
I have a FiveM game server that accesses a local database in MariaDB 10.3.16, this base is 400mb, 200mb being usable and the rest logs that are accessed only by external query when necessary. I have a ...
-1
votes
0answers
25 views
Different scan count and logical read, which one is best? [closed]
I have a SQL Server table, I created an exact copy of the table (including indexes, Pk, data, etc.)
In one of the table I have created partition.
I ran the same query in both the table and the IO ...
0
votes
1answer
33 views
MongoDB - Replication Oplog Window has gone below 1 hours
I'm working with MongoDB Atlas and I have a cluster of 3 with M30 and 100gb of storage.
My current usecase is the following:
- An user dispatches one search in my platform
- The platform dispatches ...
-3
votes
2answers
66 views
Why does IO Statistics show a lot of LOB Logical Reads on the Worktable when none of the tables in my query use any LOB datatype fields?
I have a procedure that runs a query dynamically across a few hundred databases that all have the same exact schema and aggregates the results to a temp table.
The query only involves 3 tables (each ...
1
vote
2answers
60 views
Very large table makes the application performance too slow
We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.
I found one table referenced in this app with 1,332,730,...
0
votes
1answer
26 views
Which query has better performance? [closed]
I have 3 tables as follow:
class
id:bigint(PK)
name:varchar (unique)
principal:
id: bigint(PK)
pid:bigint
role:boolean
uniqueConstraint(pid, role)
entry:
cid: (FK, reference class)
...
2
votes
2answers
49 views
Increasing WAL Segment Size?
Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes?
The reason for wanting to increase it from 16 MB ...
2
votes
1answer
60 views
ZFS Tunings for PostgreSQL
What are the recommended ZFS tunings for PostgreSQL 11 (with TimescaleDB 1.6 extension) running on striped mirrored vdevs of SATA3 SSDs?
I am considering:
ashift=12
atime=off
relatime=on (or off?)
...
0
votes
3answers
116 views
When is it ok to use an Index Hint on a query in production code?
I've recently been working with decently sized tables (between 100s of million of rows to 10s of billions), and have ran into a few cases where I was forced into a corner to use query hints, which I ...
1
vote
1answer
44 views
Batch - Update vs Insert then Delete [closed]
There is a plan to create a new ETL job to do a particular task. It will retrieve data from database1, and from that data, it would update equivalent records in database2. Both database1 and database2 ...
6
votes
2answers
212 views
Disable top (n) Sort Optimization for a specific view
I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan.
As the purpose of the view is to provide some business ...
1
vote
2answers
82 views
Mariadb Delete with Join query stuck sending data
I am using Mariadb (10.4.10-MariaDB-log) with Galera Cluster (3 Nodes).
I am running a large data queries with joins , but
No idea why delete with join is not working.
DROP TEMPORARY TABLE IF ...
-2
votes
2answers
77 views
T-SQL Query performance
We are using SSIS package to execute the following statement.
SQL statement
delete from ABC
OUTPUT DELETED.[ABC_Id], 5
into XYZ (OId, OCode)
where ([ABC_Id] = ?...
1
vote
1answer
128 views
Funky cardinality estimate skews, high memory grants and missing statistics
I have a select statement within a procedure that is causing me some grief at the moment. The query estimates about 100 million rows and that in turn grants large chunks of memory (about 8 gigs per ...
0
votes
1answer
78 views
in what all scenarios SQL Server update statistics
I know SQL Server update stats when 20% of data is changed in a table. It keeps tracks of it by using modification counter which we can see in [modification_counter] column of sys.stats table.
But I ...
0
votes
0answers
28 views
SQL Access Advisor with big Select query
I need to get recommendations from SQL Access Advisor for big Select query (> 10000 simbols).
I use DBMS_Advisor, but one procedure i need support only 4000 symbols.
How can I use this package with ...
2
votes
0answers
76 views
SQL Server, what is the optimal Processor & Core configuration for VMs? [duplicate]
In my day job I deal with alot of SQL Servers...and lately I've had a rash of clients running 16 cores but configured as 1 core per processor and their SQL Standard edition runs like a dog, as ...
1
vote
1answer
27 views
Optimizing a query that gets the opening balance
I have a query like this
select org, `Balance` from oss_collection_history och2
where och2.`DateFrom` =
(select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org)
It ...
0
votes
1answer
46 views
Best strategy to correctly index complex join query
This is the query running on MySQL 5.7 with INNODB:
SELECT SQL_CALC_FOUND_ROWS
calendar.id_calendar AS calendar_id,
calendar.event_datetime AS calendar_event_datetime
FROM
users
INNER ...
2
votes
1answer
55 views
Why should a SET TRANSACTION command show a long Duration when the CPU is low?
I got enlisted to troubleshoot a performance issue on a SQL Server 2016 Enterprise Edition at a customer site and have been handed a SQL Profiler trace captured during a period of "poor" performance. ...
0
votes
1answer
111 views
Same session ID(with same SQL text) but 3 different connection ids
I was listing out all sessions open in my sql server 2016 production server.
I have issues with slow performance and i have been investigating it.
I see that i have several same sessions(session_id) ...
2
votes
1answer
69 views
Breaking point for SQL server database based on connections from app to SQL server [closed]
We have been asked to confirm on some capacity test for one of our SQL server.
Application team is building their new app servers with better infrastructure to connect to SQL server.
Currently with ...
0
votes
0answers
93 views
SQL query tune to replace UNION - Oracle
SELECT FN_ENQDATA_STRING (col1)
FROM tab1
WHERE tab1.col2 = tab2.col2
AND tab1.col3 = ?
I also have tab2 which has same columns as tab1 and i need to call function for that too. What I can do is
( ...
0
votes
1answer
54 views
Can I drop an overlapping index if the larger index is unique
I have 2 nonclustered indexes:
IX1 = [col1]
IX2 = [col1],[col2] (unique).
I want to drop the IX1 index as the optimizer can stop use IX2 for the first column and stop using when it has satisfied ...
2
votes
0answers
93 views
MySQL performance degradation with heavy inserts
I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
0
votes
1answer
50 views
Performance issues on a MySQL database with 150 million rows
Current setup:
A managed vps server
InnoDB storage for big tables
10.0.36-MariaDB
18gb ram
8 x 2.40 GHz CPU.
I have a pretty big MariaDB database table which is used for Business intelligence ...
1
vote
1answer
76 views
Alternative of JOIN with BETWEEN
I have two tables to JOIN based on BETWEEN condition.
Table 1 is small is small around 1500 records and Table 2 is of 40 millions records. Table1 is having only one column with the datatype bigint ...
-1
votes
1answer
113 views
How to know needed UNDO_RETENTION for a specified query of long duration? [closed]
I'd like to know whether there is some way to know the undo_retention needed for a specified query with long duration that need keep the consistence.
In other words, I'd like to know how long my ...
1
vote
0answers
38 views
updates in posgres taking a longer time
I have tables as follows:
Source - 5 million records
Target - 5 million + daily incremental records
Both the source and target table have composite binary index on id and timestamp.
Basically a ...
4
votes
2answers
117 views
Which Plan is Better? WHERE EXISTS Instead of INNER JOIN
Did a switch to a WHERE EXISTS in temp table (Object13 in the plans) from an INNER JOIN to said temp table in final select in procedure.
Logical reads plummeted, but plan cost and memory ...
8
votes
2answers
750 views
Why is it not recommended to use RAID 5 for a log file? [duplicate]
While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: avoid RAID 5 for t-logs because, for every write request, RAID&...
0
votes
0answers
33 views
How do I troubleshoot FILESTREAM_WORKITEM_QUEUE waits in SQL Server 2014?
Env: SQL Server 2014 SP2 on Windows Server 2012 R2.
I am seeing our top wait as FILESTREAM_WORKITEM_QUEUE, with average waits near 1s and over 5000 waits during a 2-hour period of time.
I haven't ...
1
vote
1answer
83 views
Indexing Strategy for the query performance
I have a question on indexing strategy as i am trying to do indexing for a database.
I understand the basic indexing strategy which are:
Clustered index on PrimaryKey
Add Non Clustered index for ...
1
vote
1answer
164 views
How To Fix High DTU Usage on Specific Query
I have a query that runs every few seconds on a Microsoft SQL Azure Database. It is from a web job that checks if there are actions that need to be done based on rows in a specific table (its a ...
0
votes
2answers
124 views
Worse query performance even after improving the query by making it to run in parallel mode
I have a SQL Query which gets generated from application using EFF.
It uses a view and this view has joins to multiple tables in there.
The query runs slow and when i looked at the execution plan,i ...
2
votes
1answer
73 views
Understanding when to remove Order By or Sort operator from Query/plan
While i was reading and per understanding one needs to avoid unwanted ORDER BY in SQL queries if matching index to support query has key columns sorted the same way.
For below DB test schema--
...
0
votes
0answers
56 views
Truncate causing poor performance on subsequent Select/Insert
Question
Why is a Truncate-Table statement causing poor Insert/Select performance on subsequent calls, and is there any harm based on my use-case (described below) in leaving it as a heap?
...
0
votes
0answers
32 views
How to control dirty pages eviction threshold percentage in mongodb server settings?
I have a scenario in which I only insert documents to the MongoDB database.
I'm aware that by default, once 5% of WiredTiger memory pages are dirty, MongoDB would start evicting the dirty pages.
In ...
2
votes
0answers
104 views
How can i tune query without creating index
I am just starting to learn about tuning the queries and have found this very simple query which takes over 6 hours to run because of no supporting indexes. As its vendor product and they are not ...
-1
votes
1answer
88 views
Mysql keep high cpu usage and I cannot tune it down
I have an asp.net application which use Mysql as database server. In my application have some threading which will query mysql database. It always caused the Mysql database server CPU usage up to 99%. ...
0
votes
0answers
46 views
Comparing Performance impact between 2 Scalar UDF's
I have my developer team who uses the data from Change data capture from SQL Server to extract data and load to a different database system.
To convert the LSN format within CDC they are using the ...
-1
votes
1answer
141 views
Query performance question
Using Demo from here to reproduce my issue with changes in table structure as below and modifying demo partition function to datetime
CREATE TABLE [dbo].[DemoPartitionedTable](
[DemoID] [int] ...
1
vote
1answer
178 views
SQL Server : Memory pressure,Spiked user connections and CPU usage
I have the below sql server environment.
SQL Server 2016 standard edition.
128 GB RAM
96 GB alllocated to SQL Server
8 tempdb's with 1 GB each.
Have a major database with 2.5 TB size.
Reporting also ...
0
votes
1answer
134 views
How to deal with different badges in Postgres Explain Visualizer?
I've been working with several DBMS during my career, but always Oracle/Sql Server/MySql. Now I have to deal with PostgreSQL and my first task is to tune several queries to speed them, as our DB is ...
0
votes
0answers
54 views
Partitions for table that hold only ids in PostgreSQL 9.6
I am working in query tuning and decided to go for partitioning my tables. There are 3 tables in the base query and only one table makes the query slower. I tried to create partitions but that table ...
-1
votes
2answers
77 views
Table with 80 foreign key
I have a table with 120 columns and most of them (around 80) are foreign key . We are refactor some of main table in our system . I was thinking of change this table to divide in small table because ...
0
votes
1answer
38 views
How to implement complex search functionality?
Below is my table structure:
CREATE TABLE `post` (
`postId` bigint(20) NOT NULL AUTO_INCREMENT,
`userId` mediumint(8) unsigned NOT NULL,
`areaId` smallint(5) unsigned DEFAULT NULL,
`postTitle`...
1
vote
0answers
53 views
high-water mark of number of sessions or connections
I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information. The ...
0
votes
0answers
15 views
IO scheduler for Oracle RAC on Linux
All the documentation I have seen says that Oracle recommends the deadline scheduler. This seems very counter-intuitive when using a mini-SAN (with persistent write caching) - in such a scenario, and ...
1
vote
1answer
376 views
Slow SQL query from app, fast from SQL Studio
I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS.
Here is the query :
SELECT T0.pk
FROM products T0
WHERE (( ( T0.pk IN (...
