Some of you might remember that prior to 7.0 (i.e., the "old architecture"), the NC indexes did indeed had the "rowid" instead of the clustering key as row locators. For fun, I tested a batch with a bunch of row modifications on a table with some nc indexes. The difference between 6.5 and 7.0 was a few minutes vs an hour to do the modifications. I believe that a large part of the difference came from this...
Thanks for answering my question (msdb). To be honest, I agree, that it is unlikely that I really need it up to date, but it costs me almost nothing to do log backups on the msdb and WHEN I need it but haven't, it could cost me tons of more time to manually check the last known state of the job history. And to be honest: does every of your clients regularly export / save their current job configuration? It can be terrifiying if you have 50 jobs and have to rebuild them manually without forgetting a step / setting etc. (or even if it are the 3 ones that were changed since the last full backup) Regarding TDE: - it takes about 5 % extra CPU following to the documentation - as far I remember it is only encrypted on rest, not in the memory, so it needs only to encrypt everything that is written and decrypt everything that is read from disc - most impact should be on backups, where it reads the whole database, decrypt it, compresses it and encrypts it again (at least on newer versions, in an older version it couldn't compress TDE encrypted databases) - I had to disable TDE on my new fancy SAN, which provides build-in block deduplication, compression and encryption, because it otherwise could not compress the data (there are usually a lot of empty pages in a database plus duplicated pages (e.g. from index rebuilds) plus half empty pages)
The challenge is that people often don't back up the keys, and then they'd be faced with a database they can't restore. (sigh) This is why we can't have nice things.
Lol, answer to Ryan's question was awesome 😂(Someone has to pay this cruise's cost)
Another office hours and another amazing video!
Thanks Brent!
My pleasure!
Some of you might remember that prior to 7.0 (i.e., the "old architecture"), the NC indexes did indeed had the "rowid" instead of the clustering key as row locators. For fun, I tested a batch with a bunch of row modifications on a table with some nc indexes. The difference between 6.5 and 7.0 was a few minutes vs an hour to do the modifications. I believe that a large part of the difference came from this...
What a life Brent, have fun man! Enjoying every office hours, especially the views . . . oops I should have said Knowledge
Cheers!
Thanks for answering my question (msdb). To be honest, I agree, that it is unlikely that I really need it up to date, but it costs me almost nothing to do log backups on the msdb and WHEN I need it but haven't, it could cost me tons of more time to manually check the last known state of the job history. And to be honest: does every of your clients regularly export / save their current job configuration? It can be terrifiying if you have 50 jobs and have to rebuild them manually without forgetting a step / setting etc. (or even if it are the 3 ones that were changed since the last full backup)
Regarding TDE:
- it takes about 5 % extra CPU following to the documentation
- as far I remember it is only encrypted on rest, not in the memory, so it needs only to encrypt everything that is written and decrypt everything that is read from disc
- most impact should be on backups, where it reads the whole database, decrypt it, compresses it and encrypts it again (at least on newer versions, in an older version it couldn't compress TDE encrypted databases)
- I had to disable TDE on my new fancy SAN, which provides build-in block deduplication, compression and encryption, because it otherwise could not compress the data (there are usually a lot of empty pages in a database plus duplicated pages (e.g. from index rebuilds) plus half empty pages)
Azure SQL Database already has TDE enabled by default. Microsoft should consider doing that with the "box product".
The challenge is that people often don't back up the keys, and then they'd be faced with a database they can't restore. (sigh) This is why we can't have nice things.
I hope your ship wasn't the one grounded on a sandbar within the arctic circle!
Heh heh heh, no.