Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
464
questions
1
vote
1answer
31 views
how to speed up / fix this Ubuntu Apache MySQL Server Configuration?
I got new to the project, my predecessor is far gone and I'm left on my own.
Currently the MySQL-database seems super slow and after checking some variables and values I cannot identify the problem on ...
0
votes
0answers
33 views
Slow on searching duplicate records in millions of records
I've below three tables
CREATE TABLE [dbo].[RegisteredUser] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[UserCode] char(9) NOT NULL,
[Email] [varbinary](5000) NULL,
...
0
votes
0answers
18 views
Sampled rows is very less for auto update stats in SQL Server 2016 [duplicate]
I have SQL Server 2016. One of the table is having 9.24 million records. One of the stats (no filter on stats) on it is getting auto updated by SQL Server with sampling rate of 1 % (120023 rows).
As ...
0
votes
0answers
7 views
Postgres tracking count of recent items in count table or by selecting count of recent records from history table
Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has ...
-2
votes
1answer
22 views
What are some good SQL Server Performance Metrics from System Views?
I am working on a project where I need to collect as many SQL performance metrics as possible, all from system views.
I currently have collected, and in parenthesis the system view from which I have ...
0
votes
1answer
29 views
Mysql 8 - Count(*) - performance issue
I have this table with 2 million records
CREATE TABLE `my_table` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ID_USER` int(11) DEFAULT NULL,
`BODY` mediumtext CHARACTER SET utf8 COLLATE ...
0
votes
0answers
43 views
Index usage for database: where are the sys tables located
I have seen a couple of scripts that query some sys.someName tables to find usage of indexes in a database. My guess is that these tables are tied to a database e.g. there is a sys.indexes table in ...
-2
votes
1answer
36 views
Is Buffer Pool Extension (BPE) needed if my database is already on SSD?
Here is my simplified scenario, let's say I have one (and only one) database of 500GB on SQL Server 2016 EE, this database is on SSD drives already (both data and log files), and my server has 64GB ...
2
votes
0answers
31 views
Sql server partitioned table: what happens when an update of a record changes the destination partition?
I my company we are facing performance issues on a big Sql server DB (running on Azure, total size > 150GB).
We have one big table (1,5GB, 3 millions records) that has several quite slow "...
3
votes
3answers
435 views
Need help to understand tuning up a slow SQL server query [closed]
For one of our databases the query like below is extremely slow.
Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below
SELECT [...
0
votes
1answer
35 views
MySQL Clustered Index or Non Clustered index? (Database Design)
I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB.
I am creating a custom app where the user will be ...
1
vote
2answers
22 views
MySQL index size doesn't include all values
I have a table with ~1.4M rows and an id field as primary key and not all primary keys are included in index:
mysql> SELECT DISTINCT(COUNT(id)) FROM my_table;
+-------------+
| (COUNT(id)) |
+------...
0
votes
0answers
30 views
Slow query using SQL Server difference (soundex) function
We've got a nasty query that is always doing a clustered index scan even if I add in a non-clustered index with the Forename and Surname columns.
Query takes 2-3 seconds on a 2 million row table and ...
0
votes
1answer
494 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
105 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
61 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
29 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
131 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
113 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
125 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
46 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
226 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
169 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
141 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
113 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 ...
3
votes
0answers
77 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
239 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
106 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
55 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
140 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
54 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
93 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
160 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
118 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
773 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
43 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
88 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
265 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
165 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
75 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
41 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 ...
