11
Lets first get the concept of reader / writer threads out of the way
During a backup, SQL Server creates one reader thread for each volume
that the database files reside on. The reader thread simply reads the
contents of the files. Each time that it reads a portion of the file,
it stores them in a buffer. There are multiple buffers in use, so the
...
5
Don't delete more than 1000 at a time. All the rows being deleted are saved in case of a crash (or reboot) so that they can be restored. (cf Atomicity.) This also explains why the table was non-responsive after the reboot.
Index updates are delayed (cf Change Buffering). This may explain why subsequent deletes got slower -- the updates to the indexes ...
5
What is currently happening
When running your query, the table scan, stream agg & compute scalar operators are not evaluated at runtime.
Why is it happening
The apply NL join means that for each row in #Docs, return a row from #Docsitems that matches the predicate. This predicate should be WHERE IDDocs = D.ID
But the compute scalar operator (EXPR1007)...
5
BACKUP DATABASE successfully processed 19696388 pages in 1945.648
seconds (79.088 MB/sec).
The speed you see here is a result of simple division of the whole backup duration per volume of data processed.
In your case backup duration is 1945.648 s, data volume processed is 19696388 pages * 8Kb / 1024 = 153.878,03125 Mb
The speed = 153.878,03125 Mb / ...
3
if I have to access same table multiple time in the stored procedure is it a good idea to load the data in temp table and access it rather than the original table?
It sometimes is. It is a common performance optimization to "materialize" or "spool" intermediate results into a temp table, if putting the logic for returning the intermediate results in the ...
3
What is the best SQL server backup strategy for large DB with few tables and lots of immutable data?
I think you should look into log shipping once you get it setup your 10 minutes logs can be shipped to the server, so only changes are updated.
Assuming the log shipping destination is also where you want your backups kept, you can do backups there. Other then the initial backup, you can run for years and only ship the logs.
You could take full backups ...
2
Analysis of VARIABLEs and STATUS:
Observations:
Version: 8.0.15
16 GB of RAM
Uptime = 61d 02:53:06
Are you sure this was a SHOW GLOBAL STATUS ?
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
How much RAM? (This analysis assumes 16G.)
Your machine is generally quite ...
2
How many of the tables are not connected to any other table by a FOREIGN KEY relationship? You can do this by checking out the answer here.
It is possible to have tables with 0 records - if you ran a nuclear power plant for example, you'd want the table catastropic_failure to have 0 records.
Some reference tables could only have 1 or 2 reference codes - ...
2
The index on (cube_id, period_type) cannot be used for the ORDER BY. But it can implement the AND period_type = '1min' very efficiently.
The index on (cube_id, end_time, period_type) can be used for the ORDER BY, but cannot be used efficiently for the AND period_type = '1min'. It can be used, just not efficiently. It can filter the rows in the index ...
2
For a general-purpose or OLTP design, the initial index design should be more conservative:
Clustered index on Primary Key.
Unique non-clustered index any other unique keys.
Index supporting each Foreign Key (where not already covered above).
Then, for very large tables, consider changing to a Nonclustered Primary Key and a Clustered Columnstore.
Then ...
1
Is this an application that was developed in house? Or was it a packaged application that the company purchased (and potentially customized)? I'd generally expect that a "database for an eshop" would be purchased rather than being built internally but that's far from a guarantee. If this is a packaged application, this is quite normal.
Generally, ...
1
You will need to define an index on the voteup_count column for the query to be performant. Without this index, this query will be scanning all the rows until it reaches the first value of the Range condition.
Assuming that the data tree is in such a way that rows with low voteup_count values come first (basically, in ascending order). In that scenario, ...
1
No, I think you are doing the Right ThingTM.
Performing a bigger query to get all the data you need in one round trip almost always beats fetching the data points one by one (“nested join implemented in the application”).
Your normalized database design is perfect for a transactional application that performs data modifications and small ...
1
Please try this:
DELETE FROM journal
WHERE id NOT IN (
SELECT
j.id
FROM
journal j
WHERE j.created_at >= 636742944000000000
ORDER BY j.created_at DESC
)
Please make sure to create the index on created_at and id.
In additional, I think id in a table should be unique/primary key.
1
How I personally would do this:
Rename TableA into TableA_Old and create a view named TableA. That way your users can keep on working.
Create a new TableB, create your indexes on it and start copying data from TableA_Old into TableB (or first copy data and then create indexes; at least I would create the clustered index first and all other indexes after ...
Only top voted, non community-wiki answers of a minimum length are eligible
