New answers tagged

1

What are the benefits of hosting your own database? If you mean make your database in cloud hosting, the benefit is you don't have to install configure your database system (like DBA did) Is there any benefits of hosting your database on a separate server from your web server, e.g.MySQL hosting on Scale Grid then eventually a web server on BlueHost? easier ...


0

I ended up having to solve this by creating a table variable to hold the results of OPENJSON and joining on that variable. This forces SQL Server to actually create a temp table containing the query data rather than repeatedly running OPENJSON. Apparently a CTE doesn't do that, even though I thought it did. Presumably this applies to any table-valued ...


0

If performance is restored after a VACUUM ANALYZE, I would question whether the table(s) in question are being auto_vacuumed and auto_analyzed. If you look at the pg_stat_user_tables view, do the values for last_autovacuum and last_autoanalyze seem reasonable? If tables are constantly being locked then auto vacuum might not be able to run.


3

Firstly: Is the CPU use really more for the same amount of work? As performance is 50% faster 33% more work is being done on average in any given period of time, so using 30% more CPU resource cancels down to using the same amount of CPU for the same amount of work, just over a shorter period of time. The fewer page accesses could explain this: the CPU is ...


1

Try using the TempDBInfo procedure, it shows what currently is inside TempDB's files and who is using it most https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/TempDB%20-%20TempDBInfo.sql Procedure is not fully finished, I am going to rework the way it shows summary information, and also I am planning to add logging capabilities But ...


2

What you are you seeing is by design for tempdb Transaction Log file. I wish Microsoft had better documentation on this. How is the transaction log growth increasing upto 80% and then lowering down back to 0 and then increasing and growing and so on , with reaching almost max 90-95%. From the First reference article: Tempdb is not recovered in the event ...


0

Interesting that you don't see any running transactions but it continues to grow, as there has to be an underlying cause. Have you explored the last piece of advice Aaron mentions in that answer you linked? You may also consider that your tempdb log usage may be caused by internal processes that you have little or no control over - for example database mail,...


2

Using intersect almost always means that you should add a composite index. where `product`.`available` > 0 and `product`.`is_bought_copy` is null and `product`.`deleted_at` is null needs INDEX(is_bought_copy, deleted_at, -- in either order, first since "=" available) -- last, since "range" That index ...


0

disable read on secondary: USE [master] GO LTER AVAILABILITY GROUP [AG-Something] MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)) GO do your ddll workload on primary re enable read on secondary USE [master] GO ALTER AVAILABILITY GROUP [AG-Something] MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(...


0

What will you do if the dataset is too big? Assuming you need to read all the data, but not blow out RAM in the process, simply plan on reading in small chunks. Perhaps 10 rows at a time. Don't use OFFSET to step through the table, it will get slower and slower, leading to a terribly slow overall experience. Instead, ORDER BY the primary key and "...


1

If you're trying to store key-value pairs of data you should use a key-value store database instead of SQLite, since SQLite is meant to store relational data. Where is your database going to live?...mobile device or dedicated server? If you do stick to SQLite though, file size is not much of a factor in terms of read and write speeds. Rather you're probably ...


0

Rate Per Second = RPS From the available (very incomplete) global status and global variables posted, please consider the following suggestions to improve performance. innodb_log_file_size=512M # from ~ 50M for log rotation reduction from 6 minutes to about 30 minutes. innodb_log_buffer_size=256M # from 16M for ~ 30 minutes RAM storage before write ...


5

What are your thoughts and experience on above ? Why would a statement inside SP that usually completes within 10 ms for months, starts to experience RESOURCE_SEMAPHORE wait and complete in 4000-8000 ms ? CPU pressure caused by bad plans. You should track and manage plan stability with Query Store, as well as investigating bad plans and remediating with ...


4

Consider storing your xml in a more usual format if you can. This might require a change at an earlier stage of the process, or some pre-processing when you import the data, but it could well be worth it. The key observation is that encoding information in element names is quite unusual. Using xml with a predictable structure (ideally conforming to a schema) ...


2

Using a window aggregate would presumably be substantially faster than the self-join that you currently have. This is available from version 3.25 Unfortunately, in SQLite, DISTINCT cannot be used in an OVER aggregate, but we can simulate it with MAX of DENSE_RANK (if file_id is nullable then it can be a little more complex) The calculation is quite simple: ...


9

XML index in SQL Server is implemented as an internal table that is a persisted version of the node table that is much the same as the XML shredding functions produce. One of the columns in the internal table is called hid and that column contains the value used in the seek for a path expression. The value is an ordpath value. When you create a path xml ...


2

Likely the majority of the performance issue you're seeing is just the normal constraints of the data limitations of SQLite. Your query is of sound mind, and I don't believe there's much you can do to optimize it other than re-write the WHERE predicate to something more efficiently relational with an INNER JOIN like this: WITH file_id_counts AS ( SELECT ...


1

(Disclaimer: I work at QuasarDB) Because you are using the newer gp3 EBS volume types, and you are reporting a system-wide instability of the system (rather than just the QuasarDB process), I believe this is related to the EBS volume types, rather than something related to QuasarDB. We have observed stability issues with gp3 volumes under high pressure ...


9

The strong general advice is to never shrink of course. Still, the question here is: Can shrinking ever improve performance? Well, maybe. Consolidating all the used pages at the start of each file might have benefits for ramp-up reads, and have favourable cache effects at multiple levels, including at the storage layer. In addition, shrink will remove any ...


1

What you are considering is "premature optimization". You can't usually tell how much of an overhead those extra few bytes of "Birth date" and "Birth place" will bring, and whether it will have any material effect on performance. (I also hope that "Birth place" is actually a foreign key reference to a "Place" ...


5

In most database management systems, data is is stored as pages, not blocks. Pages are normally 4 or 8 KB, depending on the database and how its been configured. All else being equal, smaller row size will equate to better reuse of cached pages and less page reads on queries that require a large number of rows - so less I/O and faster read performance. ...


5

The big issue when upgrading across SQL Server 2014, meaning, going from anything less than 2014 to anything 2014 or greater, is the fact that the 2014 release of SQL Server included a new Cardinality Estimation Engine. For the majority of queries, the new CE won't affect them in any way. Some queries will run faster. A few queries, usually queries that were ...


2

One potential cause for this is then you now have different execution plans for some queries, compared to what you had earlier. When you see significant performance differences, the reason is frequently plan related. Compatibility level affects the optimizer. Running with the same compat level as your old SQL Server had is likely to give you higher degree of ...


0

I would not trust the "relevance" provided by two different tables to be comparable. So, I would work on putting all the Fulltext data into a single table. Why have 2 similar subtables? If you had a column in your super-table (if you have such?), then the relative relevance would be possible and the query would run about twice as fast. I cannot ...


0

I tested it myself and the "array method" is faster for the purpose of finding the sum of all the binary data in the array. Finding key:value pairs is faster using the "many variable method" if you need to find the key:value pair, but I really only need the value once the cursor selected the correct document.


2

CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index ON sensor_values (sensor_id, ts); CREATE INDEX sensor_values_sensor_id_timestamp_value_index ON sensor_values (sensor_id, ts, value); You can do instead: CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index ON sensor_values (sensor_id, ts) INCLUDE (value); This will create your unique ...


0

In the end I created a function create or replace function last_sensor_values_days_day_updated() returns TIMESTAMPTZ AS $$ SELECT (MAX(day) + INTERVAL '1 day')::TIMESTAMPTZ FROM sensor_values_days_private LIMIT 1; $$ LANGUAGE SQL IMMUTABLE STRICT; AND use it like SELECT sensor_id, ts::date, MIN(ts), MAX(ts), MIN(value), MAX(value) FROM sensor_values WHERE ...


Top 50 recent answers are included