New answers tagged

0

Late answer but could be of use to other readers. Please, have in mind that there are lots of maintenance or reporting tasks, you can create, that carry unseen risks associated with them. What would happen when a drive gets filled up during differential backups performed daily? And what if an index rebuild job runs unusually long? How about if a data load ...


0

In general, always run your T-log backups in serial; many of my instances have a couple of dozen databases, and several that are very active, and the transaction log backups only take a few seconds total; up to half a minute or so when it's particularly busy. Running backups in parallel only really would be beneficial if all of the following conditions are ...


0

Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I would suggest to setup one job that would backup the transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time. What ...


7

You've addressed a boatload of items in your question. Thanks for being so thorough! Just a couple of things I notice off hand: How various hardware / load factors influence what should be done. Are you running a 24x7 instance? What is the load around the clock? I notice you have backup compression disabled; is that by design for the test, or ...


2

If (post_id, user_id) is unique, then jettison the id and make that pair the PRIMARY KEY. I specifically suggest that order because I assume this query is frequently used? SELECT COUNT(*) FROM tbl WHERE post_id = ? AND visible; By having the PK start with post_id, you get the added efficiency of "clustering", thereby drastically reducing ...


2

You should consider the balance between how often your data will be accessed vs how often your data will be updated. Adding another condition on your SELECT just to display your content may well create more load than deleting rows in the infrequent occasion that a user un-likes. Note that you will need an index on visible to efficiently query against it.


1

I suggest to use sequence instead of max(pk), the reason is serialization. Also sequence increases manageability. Let your client try 'select max(pk)' in multi user environment and scale up the number of concurrent users then he understands himself. For more please Sequence vs max primary key value + 1


3

The presentation of the data should not be the determining factor in how the data is stored - especially in the case of a primary key. That would be like saying that all dates need to be stored in the database as strings so that a particular date format could be displayed. To make the select max(pk) + 1 work in a system with more than one user is to ...


9

Putting a condition in the WHERE clause and the ON clause are two very different things. The ON clause is part of the JOIN. Now with an INNER JOIN you won't see any difference in the output (unless also using GROUP BY ALL). But an OUTER JOIN is a different story. If you moved that particular condition into the WHERE clause it would (among other things), ...



Top 50 recent answers are included