So if your primary access path is by question, then the unique clustered index that makes the most sense will be (QuestionId, EventId).
Adding a second index to EventId may not be useful as the index might not be selective enough and the query engine will decide it's just faster to read the entire table instead of doing a lot of work to read a large portion ...
You have two options:
redesign/tune the procedure that is doing too much disk IO (or may too much use of cpu or ram)
increase hardware power (more MIOps, more ram, more logic cores)
But before doing so you have to deep dive into the issue to find what is the root cause of it.
There is no inherent performance difference between a SQL (relational) database system vs a NoSQL database system. Your biggest performance impacts are going to be how you design & architect the database, how you design your queries, and the hardware you put behind it.
Both types of systems could easily handle inserting millions of records a second, let ...
While all the things mentioned above for general performance will help alleviate bottle necks, the main issue that you'll run into with many parallel requests that are not point requests is due to the standard mySQL thread pool scheduler...which is...not great for a large number of concurrent threads. If you find this behavior alot, try pool-of-threads ...
Apparently changing the compatibility level from 2019 to 2012 has solved the issue. I don't know if I have any disadvantages by doing this. Anyone knows what this change exactly does or means?
For now the described issue is gone. Thanks for your help: Vladislav Zalesak. Dan Guzman. J.D. SQLpro. Charlieface.
what are the actual benefits of doing so on the above described architecture (if any)?
There are some management benefits, particularly:
You get separate disk performance counters in the VM for Data, Log, and TempDb
You can migrate any one of them to a different storage device without reconfiguring the VM or SQL Server.
You can manage Storage Quality of ...
Originally the benefits were increased I/O performance by separating concerns across multiple physical disks. This is because concurrent changes can be (and typically will be) occuring to your data file, log file, and even TempDB file. The disk is the slowest hardware component that is used in a SQL Server environment, so optimizing it is important.
As disk ...
@user1673665 ok, this definitelly looks like cardinality issue, prolly statistics.
You have SQL Server expecting few row, ie for scMat_MaterialText 4, but you get 62000. Thats way off.
Try to run a select on this table, then review estimates vs actual rows read. And try to update statistics. I would say thats the culprint
The planner doesn't understand how the restriction on s.subscription_id interacts with the merge join. The index scan on "s" gets to quickly skip about half the tuples by jumping to the middle of the index, while the index scan on "si" then will read through all the tuples that correspond to the quickly-skipped ones from table "s&...
You should use pt-online-schema-change.
This will allow for no downtime and you can pause it in the middle of a production period
Provided you have twice as much space free (at least 248GB)
STEP 01 : Select Folder
For this example, let's choose /home/whoeveriam/online_change
mkdir -p /home/whoeveriam/online_change
STEP 02 : ...
Assuming you're using Microsoft SQL Server (as your tag implies), if you're using JMeter, ensure your tests run directly on SQL Server and don't involve any middleman that can affect results. I'm not personally familiar with JMeter but the biggest difference you have between your two tests is about 57 milliseconds which is little enough to be affected by any ...
The reason for that messages is the client's inactivity. If no actions from the client for wait_timeout seconds then connection will be closed with a message.
That is the normal behaviour and that message was hidden by default log_warnings = 1 before mariadb 10.2.24. All newer releases has log_warnings = 2 and logfile now is filled by "(Got timeout ...
A FOREIGN KEY:
Includes an index, which may help performance during JOINs, sometimes significantly.
Includes a constraint that guarantees the existence of a matching row in another table.
Some cases of adding an index require copying the table over. Hence the "copy". Apparently, you have such a situation. For further discussion, please provide ...
Might a hardware perspective explain the differences?
The steps of disk I/O are something like:
buffering (for writes)
send data to the device
"Seek" the desired cylinder on the device
Wait for the read/write head to be over the sector that needs read/written
perform the I/O
buffering (for reads)
Steps 3 and 4 are the dominant time consumers for ...
After reading the post of Sean Gallardy I setup StorPort tracing using Perfmon.
How to configure the tracing you find here and used StorPort-Trace-Reader to analyze the results.
The results showed that there was indeed some latency.
Next I used Windows Performance Recorder to trace the "Minifilter I/O activity". Using the Windows Performance ...
It's 2021 and I decided to add one more answer.
Recent versions of SQL Server come with a very handy new feature called Query Store
After you enable it for a database, you can review the most "expensive" queries (CPU wise or I/O wise), the "longest running" queries, etc. - for a period of time. And, most importantly, examine their ...
For me the trick was to drop the fk constraint from another referencing table. This referencing table was huge. But be careful, I knew that that constraint for the records I had to delete was not relevant. Therefore I could temporarily drop the constraint to add it afterwards (during which I was sure there was no other database activity).
Consider creating this composite index,
CREATE INDEX some_table_indx_col3_col2 ON some_table(col3,col2);
You should remove the col3 index to avoid index redundancy and conserve storage space.
Considering these existing details,
less Qcache_free_memory 2,555,753,592
Your index (well, both of them, it is not clear why you have two of them which differ only in UNIQUE) is on (id, created_at), but your ORDER BY is on id, created_at DESC.
PostgreSQL can follow an index forward, and follow it backwards, but it won't follow one inside out and sideways. Make an index on (id, created_at DESC) if you want to have the best hope of ...
This sounds like a prime candidate for a Microsoft Product Support engineer. - Max Vernon
We experienced the same issue and our final decision was to use DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') on a daily basis. We consider the solution as more predictable and managed than trace flags using. Unfortunely, MS wasn't ...
Your costs are on your WHERE predicate for ts.status.
You can see in the explain it's doing a Seq Scan for 400,951 rows with a cost of 10,628,176.10.
While having an index that is based on the ORDER BY fields in a query can help performance with the sorting, generally you should focus more on indexing based on your predicates (JOIN, WHERE, and HAVING clauses)...
It seems you are too much concerned about fragmentation, As long as you keep updating statistics regularly, fragmentation shouldn't bother you much for performance. You may read more details about this on a video shared by Mr. Brent Ozar and also another page here. Let me try answering your question one by one:
Doesn't an Index with included columns have ...
Doesn't an Index with included columns have the exact same problem?
Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?
Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?
I would, yes.
How to prevent fragmentation?
There are a couple of ...