Questions tagged [performance]

An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.

Filter by
Sorted by
Tagged with
1
vote
0answers
10 views

Optimizing cloud server (php and mysql)

I am running a vBuletin forum as a hobby and I was using a shared hosting service until a couple of days ago. Because the amount of visitors grew and the forum seemed to be slower and slower every day,...
0
votes
0answers
10 views

When no index is faster than having a clustered index?

I ran the same query, which involves joining two tables A and B, when B had no index at all and then added a PK clustered index on the ID column used for joining to it. The time elapsed when there was ...
1
vote
1answer
14 views

log send queue size and redo queue size in AG

I am trying to figure out the way to monitor these 2 events log send queue size - I can see this in perfmon Redo queue size - I can see in dmv but not in perfmon counter Is there a way using ...
0
votes
1answer
46 views

How to fix below blocking problem on SQL server 2014

I am having a strange blocking problem which i am not able to understand. During a slighter more volume due to some app pooling issues we see sleeping spids with open transactions, so those SP's tend ...
0
votes
0answers
23 views

Strategies to get more performance in a specific scenario

Introduction Hi Guys, my name's Joao, i'm currently working as backend and that's my first post. I'll appreciate any help! My team are working on a new feature. During the process of development we ...
1
vote
0answers
28 views

Anomalous performance of mass INSERT in Oracle

Can you please help me diagnose, explain, and possibly solve the problem of unstable peformance of mass insertions in Oracle? I need periodically to load large amounts of data into an Oracle database. ...
0
votes
0answers
16 views

Prepared statement with parameters repeated multiple times throughout the query [closed]

I have a complex query issued via JDBC consisting of 2 string parameters and multiple SELECT statements, some of which nested. The same DB TABLE is queried multiple times and each query parameter is ...
1
vote
1answer
25 views

Why index on low selectivity column hurts query performance

The table is simple: CREATE TABLE `t1` ( `ID` int NOT NULL AUTO_INCREMENT, `name` int DEFAULT '1', `LastName` int DEFAULT '0', UNIQUE KEY `idx_ID` (`ID`) USING BTREE, KEY `idx_name` (`name`) ...
1
vote
2answers
20 views

Why MySQL tends to use more and more swap while there is enough RAM?

I have a server with 64GB RAM and 64 GB swap. It's running MySQL 8.0.19 and few php scripts. Here is my config: innodb_buffer_pool_size=40G innodb_buffer_pool_instances=4 innodb_buffer_pool_chunk_size=...
1
vote
2answers
36 views

Does MySQL use its index metadata to optimize COUNT(*) queries?

Let's consider a user_actions table with a standard indexed primary key user_action_id. I want to know the number of user actions up to a certain key (say 100 millions): SELECT COUNT(*) AS `...
0
votes
2answers
73 views

PAGEIOLATCH wait and Page Life Expectancy very low

Training for SQL Server, has following scenario: SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM Server has below performance conditions: - very high PAGELATCH_IO waits - average Page ...
1
vote
2answers
57 views

Why is my database still fragmented after I reorganized and rebuilt it, and updated the statistics in SQL Server?

I created a Maintenace Plan, first i reorganize the indexes, then rebuild, and final update the statistics every Saturday. I ran this script for verification: SELECT S.name as 'Schema', T....
0
votes
0answers
24 views

Will the query be quicker if adding more WHERE conditions?

Assume I have a table t1 and two queries q1 and q2 on t1: SELECT xxx FROM t1 WHERE <condition-1> and SELECT xxx FROM t1 WHERE <condition-2>, where <condition-1> logically ...
0
votes
1answer
35 views

How are huge relational models kept fast? [closed]

I have a bit of experience with relational data models, but just with a few thousand entries. In these cases performance was of no concern, because every query felt instantaneous anyway. But I thought ...
0
votes
0answers
9 views

Can Redshift process CTE / with chains without making interim tables?

If you are careful about structuring a CTE chain, is Redshift capable of analyzing it and determining that interim results are being used in a linear fashion? If so, it could drop any interim tables ...
0
votes
1answer
62 views

Turning “Set Statistics IO” for stored procedure doesn't show all tables used in stored procedure - why?

I'm reviewing a stored procedure that's been a performance problem. And I just realized that some of the tables which are used in the stored procedure don't even show up AT ALL in the results from Set ...
0
votes
0answers
17 views

Analysis of Conditions with no results and high offset number - PostgreSQL JSONB [duplicate]

I am asked to record down the analysis on PostgreSQL JSONB query having Conditions with high offset number and query that provide no results considering the below. select * from sub_test df, ...
0
votes
0answers
30 views

How to write a complex logic in SQL that should work two main cases (unfiltered and filtered by one dimension)?

This question is slightly related to this one. I am dealing with a legacy business logic that computes several DocumentXUser security tables. This logic is very complex (a single query with lots of ...
1
vote
0answers
53 views
+50

Reduce file header block

I'm in the middle of my first Statspack analysis, and one of the most time consuming events is Buffer Busy Waits (around 30% time). I checked out the Buffer Wait statistics, and it seems like the most ...
0
votes
2answers
36 views

Performace of SELECT query based on primary key vs. indexed column

I have a scenario where a row in a table can be obtained by using either primary key or another column which is indexed: SELECT * FROM table_name WHERE primary_key = ? vs. SELECT * FROM table_name ...
-1
votes
1answer
51 views

Optimising SQL Query

Currently I have a query to calculate the averages of auctions that are present within a game, in order to do this I have the following query: SELECT item_name, tier , CAST(SUM(highest_bid) AS ...
0
votes
3answers
137 views

How to efficiently recompute the data in a rather large X table?

I have recently noticed performance issues for the main application I am developing right now (it is a legacy one). A job triggers a stored procedure that computes the security for all the documents ...
0
votes
1answer
30 views

How to capture execution plan from sql profiler for filtered queries only

Using sql profiler, we trace all slow queries (filter on duration/reads) to see where we can optimise. Events used: RPC:Completed SQL:BatchCompleted Filter on Duration. If I add the event ShowPlan ...
4
votes
1answer
144 views

What is the difference between ASYNC_IO_COMPLETION and IO_COMPLETION wait types?

Could someone explain what is the main difference between these two wait types?
4
votes
1answer
85 views

Suddenly SQL server restarted with pending task count increased

We are trying to understand the issue where SQL server was suddenly restarted on its own- Its a SQL server 2012 SP4 -GDR applied with 40 CPu's HT enabled , thus total of 80 logical processors- MAXDOP=...
0
votes
0answers
12 views

Monetdb - Performance of two node distributed query processing using TPC-H data is much slower than a single node case

We ran the following query on two-node setup loaded with TPCH-20 data where all tables except nation and region are generated with two splits - each loaded into one node. Nation and region are ...
-1
votes
1answer
27 views

Is InfluxDB faster than PostgreSQL [closed]

According to this article the only real benefit influx has over postgres (using time-based indices) is space used. PostgreSQL is more performant, with time indices. Why would one use influx-db over ...
0
votes
1answer
61 views

What is the best way to store optional values in table?

I have a large table that will be partitioned (about 300 million rows). There are currently 57 columns in the table. At the moment we also have an 'Option' column, which is to hold values ​​that would ...
0
votes
1answer
54 views

Clustered Index during data load in MS SQL Server

Until today, I was under impression that it's been considered as a best practice to disable the indexes in sql tables during the data load. And with the fact that one cannot disable the clustered ...
1
vote
0answers
45 views

High CPU on Unloaded Server — Performance Monitor

We recently upgraded a very lightly used SQL server (running < 5% CPU 99% of the time) from 2012 to 2016. In conjunction, we moved the server from an AWS t2 to a t3 instance and installed the new ...
0
votes
0answers
40 views

Postgresql Performance Diagnostic

I am trying to activate Performance Diagnostic on a Postgresql 12 advanced server from PEM on a windows 10 machine. I am trying to install EDB Wait States module by changing the postresql.conf and ...
2
votes
2answers
40 views

Should I in general partition tables by tenant IDs in a multi-tenant system?

We're building a system where the data in 10 tables is associated with Accounts. A typical table looks like this: create table Things( accountId varchar(64) not null, internalId varchar(64) ...
0
votes
1answer
72 views

SQL Server storage - evaluate the number and types of read write operations for the existing deployment

Training for SQL Server, one of the questions in Learning material says: Before migrating on-premise SQL Server deployment (single server) to Azure VM, you need to evaluate "number and types of ...
1
vote
1answer
78 views

best practice with RAID0 configuration - to keep all of them in separate drives i.e. tempdb, mdf, ldf, backups [duplicate]

Database size - 145GB Currently our database mdf file, tempdb, backups are in D drive and log files are in C drive. Can we go for the below configuration: D drive - tempdb, E drive - mdf, f drive - ...
0
votes
0answers
20 views

Calculating space required before a query is run

I have a Postgres database table that occupies about 20GB. I want run a simple aggregation query against the table (by simple I mean that the query selects from the one table only, doesn't involve any ...
0
votes
1answer
27 views

Covering Index Not Being Used Effectively

I have a unique composite index on three fields (hash, is_customer, user_id) in a table a. And queries that go like this: select `a`.`id`, `hash`, `is_customer`, `user_id` from `a` where `hash` = ...
1
vote
0answers
39 views

Bad write log efficiency, strange broken relationships

I recently switched galera synchronization method from rsync to mariabackup which greatly reduced CPU usage on our DB servers and also increased the innodb_log_file_size to 2G from about 600M or so. ...
2
votes
2answers
68 views

Use BIGINT and twice the storage space or INTEGER but with extra operations?

Suppose I've a table named agency with some columns: internal_id(integer, unique) , external_id(bigint, unique) , name, location, created_at, ... internal_id and external_id are each one unique and ...
0
votes
1answer
37 views

MySQL - Force index degraded performance over time

Last year we migrated data from our old product environment into a new environment with our new product. Since the new product was still fresh, we found that some of the new queries were very slow ...
1
vote
1answer
54 views

My query is too slow

Please excuse my bad english. I am really frustrated and do not know what to do next. My problem is a sql query. The problem with the query is that the query takes too long and I think that this is my ...
0
votes
0answers
48 views

Clear performance schema on read-only MySQL instance

I am using AWS Aurora MySQL with Writer and reader instances in replication. Both instance are used by two separate applications. I want to clear the performance schema on the read replica server but ...
0
votes
1answer
26 views

AWR dbms_workload_repository ash_global_report_text extract text from output

I want to build a scheduled job in Enterprise Manager to trigger every hour the following query: SELECT output FROM TABLE(dbms_workload_repository.ash_global_report_text(584958394, Null, SYSDATE-1/24, ...
2
votes
0answers
63 views

CHECKDB with default settings and parallelism involved

This would be my follow-up question because I am trying to understand the CHECKDB behaviors While running CHECKDB without any MAXDOP specified on below 2 servers:- Server with 10 Vcores, MAXDOP at ...
5
votes
1answer
324 views

Best practices for SQL Server block size while formatting disks for data and log files

What is the current best practice or general guidance on formatting the filesystem for SQL server database files. Currently we have SQL Server file system, NTFS formatted with 64KB block size based on ...
0
votes
0answers
37 views

Scale out methods with high availability [duplicate]

We have a two site AlwaysOn Cluster and looking to do a High Available multi-site write solution. We have looked at Replication, Sharding, and SQL Elastic options for having multi-site write for a ...
0
votes
1answer
36 views

How to benchmark Switchable Optimizations in MySQL

I want to benchmark the performance of Switchable Optimizations in MySQL, i.e., comparing the query latency and the result of EXPLAIN with/without using these optimization switches. I have already ...
0
votes
1answer
30 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 ...
5
votes
2answers
62 views

Foreign key part of a composite primary key

I have a table Room with primkey on column room_id and another table Items with primkey item_id. Each Item has a foreign integer key to room_id. When my program runs, it only looks at items in a ...
0
votes
1answer
111 views

TempDB Log File Growth - questions

Problem: On one of the production boxes, TempDB transaction LOG file grew unexpectedly, filling the drive It was 32 GB but now it is 240 GB Questions: What is the mechanism of TempDB log file usage ? ...
0
votes
2answers
45 views

How to properly and effective rename a vaue (column) of a foreign key in both tables?

I have 2 tables: users and user_statuses. A user status is of type varchar. It's varchar for the purpose of readability and I don't consider using id/int in this case. CREATE TABLE users( --.............

1
2 3 4 5
120