Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

0
votes
0answers
36 views

MySQL 5.6 Performance Tuning Issue

I have read and try lots of things about MySQL optimization and tuning. I tried most of them one by one according to our needs but nearly I got nothing. I am doing my test using some simulation ...
1
vote
1answer
28 views

Does varchar(n) affect the number of records which can be stored per page even if you store less than n?

I'm struggling to find enough documentation to help me answer my own question. The common story seems to be that it doesn't make a difference to actual database size when you use a much higher value ...
32
votes
5answers
4k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
4
votes
3answers
75 views

Make queries and BCP faster on a very large table

This question is somewhat of a continuation to this: Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that ...
1
vote
0answers
15 views

how columns ordering in sql server database table impact performance? [duplicate]

As I read about database design from logical model to physical databases, I noticed that the column ordering may need to be changed the sequence recorded in the logical data model. The DBMS is SQL ...
0
votes
0answers
15 views

Difference between execution times [duplicate]

I managed to make a more efficient my query disabling the sequential scan, but the time in the explain analyze is really different from the real execution of the query, "Execution time: 4854.186 ms" ...
0
votes
0answers
41 views

Reducing the time when retrieving all records from large databases

I have to retrieve most of the records from large table, I'm talking about approximately 3 millions rows. I'm even using this site as a guide for tuning the database. It is not enough, it takes an ...
0
votes
0answers
10 views

Why dropping TokuDB partition so slow

I have defined a simple time series table partitioned by UNIX timestamp (into 32 days). I then loaded ~17K entries, which were distributed among 8 partitions. There were at most 2880 rows in any ...
1
vote
0answers
32 views

SQL Server Trace Flag 834 on VMware

It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware? The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't ...
7
votes
2answers
156 views

How to measure Key Lookups per Second on SQL Server

I am working on reducing the amount of transactions doing key lookups on our SQL Servers. First I want to define a baseline for the normal or tolerable number on that metric and as we constantly ...
-1
votes
4answers
173 views

What MAXDOP setting should be used for MS SQL Server 2012? [closed]

I am getting a performance degradation error in one of my cluster production server. The error is like that A significant part of sql server process memory has been paged out. This may result in ...
4
votes
1answer
38 views

Is it possible to connect MS SQL Server Express 2012 through DAC Connection?

Is it possible to Connect MS SQL Server 2012 Express Through DAC. After moving from SQL Server 2012 Enterprise to MS SQL Server 2012 Express, some performance problem were there. When i have checked ...
2
votes
1answer
67 views

Stack Dumping and performance degradation of sql server process [closed]

I would like to say that today i got first time SQL Server log message in My SQL Server 2012 Database. ***Stack Dump being sent to D:\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0012.txt * ...
0
votes
2answers
53 views

MySQL Change in execution plan after upgrade

We upgraded the MySQL 5.0 to MySQL 5.6 last week. After the upgrade most of the DMLs are very slow. Especially the inserts and updates using JOINs. There is a difference in execution plan of these ...
3
votes
2answers
92 views

Release MySQL Memory and Performance Tunning

I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file. The server is a dedicated server for MySQL database and the ...
0
votes
2answers
38 views

Optimal database structure for fast inserts

I am using MySQL Percona server. This table needs to do fast bulk inserts using 'INSERT INTO ... VALUES' for smaller requests (about 100k records) and using 'LOAD DATA INFILE' for larger (up to 10kk ...
0
votes
2answers
46 views

More Efficient Use of Union

Looking for a more efficient way to achieve what my view is doing. This view joins tables to themselves to show where each part in the database is used. In other words if I am a part of a part list ...
1
vote
0answers
89 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
0
votes
0answers
335 views

MySQL 5.7 InnoDB is extremely slow on spatial data compared to MySQL 5.6

I have a table area which contains ~300,000 rows. It is defined as follows: CREATE TABLE `area` ( `area_id` bigint(20) NOT NULL AUTO_INCREMENT, `boundary` geometry NOT NULL, `name` varchar(255) ...
0
votes
0answers
14 views

server response is getting slow day by day

MySQL server 5.5 > running in Linux Box is getting slow. MySQL server is being used by Java Web application, please help me to figure out the problem.
0
votes
1answer
37 views

Tuning read-intensive queries - Oracle

Now this might be very vague but Im sure I can provide more info on them.. my query is that is it recommended to use tablespaces if different block size because I have a table in which number of reads ...
3
votes
3answers
253 views

Can I find out which database is queried on the most?

We have a number of database which all have the same schema for each customer. I was wondering if there was a query which could select count(*),db_name from somesysview group by db_name; From a ...
3
votes
1answer
151 views

What does “cleaning up” mean in MySQL InnoDB transaction status and how to prevent it?

In our database (MySQL 5.6.22 on RDS) we have a lot of transactions (~500) in a state "cleaning up" under the load: > SHOW ENGINE innodb STATUS; ... ------------ TRANSACTIONS ------------ ... ...
0
votes
0answers
47 views

Optimizing DB for performance by archiving/partitioning

I have a table in my Database which would be loaded with huge amounts of data each day. These are basically status of the orders customers has placed. The UI in my application which calls the DB for ...
0
votes
1answer
59 views

Azure TempDB and Log files

I'm setting up an Azure D11 SQL Server to be used for a data warehouse. I can only attach 4 data disks and have done so, now I need to determine how to configure them and split the DB files. I'm ...
1
vote
0answers
73 views

Mongodb Huge Number of Open Connections and Write Locks

We are seeing around 800 open connections to our Mongodb. To clarify our issue: We have 5 different clients who write the data into Mongodb. Each of these clients - write four different type of ...
3
votes
3answers
190 views

Simple yet problematic update query

I have a rather simple update/query, that has been causing me a lot of grief over the years. in simplest form it is : update VillageSemaphore set TimeStamp = getdate() where VillageID in ...
0
votes
0answers
29 views

Minimum Oracle table size making IOT advisable?

I have inherited an IOT that doesn't seem to merit it because: only 25k rows fairly pure join table, non-index fields rarely queried total index width only ~125 bytes total row width only ~160 bytes ...
0
votes
0answers
90 views

Why my mariadb slow when I select all from empty table?

I try to find why my database slow when I select all table . That's empty table... MariaDB [kohadata]> select count(*) from biblio; +----------+ | count(*) | +----------+ | 0 | +----------+ ...
2
votes
2answers
58 views

Can moving tables to another MySQL database help performance?

I want to know if I using different databases can help performance. I have one big table support_chat with more than 5 million rows. I need to scan this all the time (3s with ajax) to check new ...
1
vote
0answers
52 views

Mariadb improve performance while import dumpfile

I have a dumpfile with millions data inside. I want to decrease time because I waited too long. I imported 10 million data (3 GB) and I waited 1:30 hours. I will import nearly 100 million data (30 GB) ...
4
votes
1answer
559 views

Do I have a memory issue?

I ran sp_Blitz that pointed me to a memory issue saying: Memory Dangerously Low The server has 32755 megabytes of physical memory, but only 235 megabytes are available. As the server runs ...
0
votes
1answer
46 views

Sql Server 2012 Failover Replica in Synchronous Mode between Primary-Secondary on VPN is very slow

I have installed SQL Server 2012 Clustered HA with Replica in Sync mode between two nodes connected by an high-speed VPN. The DB is encrypted. Everything works fine, but to do the replication, the HA ...
11
votes
2answers
281 views

Why do SQL Server row estimates change when I add a join hint?

I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query ...
0
votes
0answers
49 views

SQL Server File Latency

I've a Database which is in SQL Server 2012. For the last couple of weeks it's very slow and some times not responding to the queries. So I checked the read/write latency and the result is - Read ...
2
votes
1answer
74 views

How to tune the query on all_constraints in Oracle 12c?

How to tune the following query, as it's taking around 8 seconds: select constraint_name,table_name from all_constraints where r_constraint_name in ...
2
votes
0answers
58 views

PAGEIOLATCH_SH and WRITELOG waits

We have a slow overnight process which I was asked to look at with the top waits being PAGEIOLATCH_SH and WRITELOG I have been collecting information from sys.dm_io_virtual_file_stats and can see ...
8
votes
1answer
201 views

Does RSS Profile have an effect on SQL Server with NUMA setup?

I'm curious whether the RSS Profile which is default NUMAStatic on Windows 2012 R2 could/should be changed for SQL Server with a NUMA setup. Has anyone done performance benchmarks on high load SQL ...
0
votes
1answer
86 views

Identifying root cause of high cpu load

I need to research what caused a high load in our Oracle database some days ago. I wish to see a list of of high-load SQL statements. We don't use any Oracle tools like ADDM or AWR. I tried to ...
2
votes
1answer
55 views

Associate SQL Server Queries with Databases

Is there a way via T-SQL and DMVs to show which database a query was run against? The goal is to look at CPU and I/O by query by database so I can redistribute the databases as I reconfigure the ...
1
vote
0answers
61 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...
1
vote
0answers
17 views

Several Data Importation Illogical Latency in MySQL [closed]

I am used to importing a 4GB of data from an .sql file towards a testing server (hardware). I tweaked the database to speed up inserts, and, so far, the script takes 6 hours, more or less. We were ...
1
vote
3answers
42 views

Speeding Up MySQL Full Text Search

I am doing a complex full text search on a table with 130k+ records. It takes .5 seconds to complete. I was hoping it would be a bit faster. Is there anything I can do to make it faster? Below is ...
1
vote
0answers
41 views

Howto measure improvement after create and move new filegroups in SQL Server [closed]

How can one measure the (hopefully) positive effect of moving couples of indexes to a different filegroup, so to say seperate them from the primary data filegroup, and move this filegroup to another ...
3
votes
1answer
162 views

PostgreSQL doesn't use all the RAM it could

I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config: # <snip> the default config ...
0
votes
1answer
62 views

Oracle 11g PGA advice view is empty when it shouldn't

I have the following situation in an Oracle 11g server (it's part of a RAC). PGA_AGGREGATE_TARGET is set, STATISTICS_LEVEL is TYPICAL, and yet V$PGA_TARGET_ADVICE shows up empty. Why does this happen? ...
5
votes
2answers
2k views

Latin1_General_BIN performance impact when changing the database default collation

I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the ...
1
vote
0answers
144 views

DB CPU parameter in AWR - Oracle

What is the significance of DB CPU paramater in Automatic Workload Repository? Say in my case it's always above 100. Does it have benchmark metric as such to say that so and so number is the right ...
1
vote
0answers
33 views

Why do Statistic recommendations disappear from DB Tuning Advisor after implementing recommended indexes?

Why is it, when analyzing a query through Database Engine Tuning Advisor, statistic recommendations disappear after some or all of the suggested indexes are implemented? For example, Query X suggests ...
2
votes
1answer
503 views

Can I exchange partition between two table with partition with same name?

I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table ...