June 7, 2017 at 9:04 pm
Comments posted to this topic are about the item SQL Server is Getting Smarter
June 8, 2017 at 8:49 am
As a counterpoint to being excited about the new features: https://www.brentozar.com/archive/2017/06/automated-tuning-and-the-future-of-performance-tuners/
My nickels worth on the topic of auto-tuning databases and the like is probably going to sound like a "in my day we tuned indexes by writing the entire b-tree out by hand on green bar paper then typing it in" but...
I don't see it working as well as MS seems to be thinking, certainly not initially and almost certainly not for a long, long time, if ever. Every database has a different usage pattern, even when used by two different customers in the same industry. Everyone already knows how bad the Database Tuning Advisor recommendations can be, or the "missing index" suggestions in query plans, so (in the short term) how could anyone expect any better from this?
POSSIBLY, I'll grant you, in Azure SQL it might get better faster, presuming MS collates / aggregates / smushes / spindles / and folds data from across a large portion of the customer base. Or at least the ones that are OK with agreeing to MS monitoring their DBs to generate said data.
As for automatically setting up backups at install / creation, I'm on the fence on that one. I could see it being useful for the accidental DBA (such as I started as,) but even then it could cause as many problems as it fixes. Oops, AD didn't specify a different backup location, so now their data drive (which just happens to also be their C drive) is full and they need to figure out why. Or it engenders the false sense of security, because after all, the SQL Server itself is already doing backups so we're protected from data loss. Wait, the hard drive with the backups failed as did the drive with the databases and we can't recover because we never thought to take the backups OFF the server?
I guess, I'm largely on the fence, although leaning towards the "it's not going to be this great shining future of DBA-lessness" people might think it will be.
June 8, 2017 at 9:35 am
jasona.work - Thursday, June 8, 2017 8:49 AMAs for automatically setting up backups at install / creation, I'm on the fence on that one. I could see it being useful for the accidental DBA (such as I started as,) but even then it could cause as many problems as it fixes. Oops, AD didn't specify a different backup location, so now their data drive (which just happens to also be their C drive) is full and they need to figure out why. Or it engenders the false sense of security, because after all, the SQL Server itself is already doing backups so we're protected from data loss. Wait, the hard drive with the backups failed as did the drive with the databases and we can't recover because we never thought to take the backups OFF the server?
There's definitely more to backups than just running them. As many wise people frequently say, it's not the backups that are important, but the restores.
Perhaps if SSMS had a big coloured banner warning whenever you were connected to a database whose "last backup date" was more than 7 days old. Experienced folks can switch it off, but that initial big coloured warning would alert the accidental DBAs. Plus anyone using SSMS for the first time would see it - extending the visibility beyond the DBA and encouraging the DBA to address it.
Leonard
Madison, WI
June 8, 2017 at 9:50 am
On backups.
Certainly some automated default would cause issues in places. Absolutely having some ideas on how manage backups and restores is important.
However.
I've had no shortage of customers and clients, including developers, that just never turn them on. Having some basic default, like keep 2 fulls and logs every 4 hours, is better than nothing. Putting this as an option isn't a bad idea. Keep in mind, this is mainly for the small customers, the ones that don't really think about this stuff. Anyone with a 10GB or larger database likely has some staff to think about and manage things.
You can't fix everything, and the idea isn't to be perfect. It's to help people be a bit better. I can't imagine for a second that having no backups because of no defaults is better than having some once a day or week backup.
June 8, 2017 at 10:01 am
On tuning.
The DTA and missing indexes were quick features, rapidly built to provide basic assistance. These were the type of simplistic logic that I'd employ if I needed to whip out this feature in a couple weeks. These were good first attempts, but didn't include enough details analysis. Part of that was less understanding, part is less hardware capability.
As times change, and as we learn more about how to setup machine learning that adapts to the data, I have higher hopes for things like indexing. It's a good fit for machine learning. A known type of problem in a fairly narrow space. Having some basic rules around knowing that we want 5-6 indexes, not on every column, and being able to capture and analyze workloads over time, I'd expect that index tuning fades from our jobs for the most part as DBAs. We still want to know how to seed indexes, and certainly include DRI, but deciding if I need to include firstname with lastname, and is gender a good third column or would city be better? That type of decision is perfect for a machine learning system that isn't looking at a few queries, or dealing with a single irate customer, but can analyze the workload constantly over time.
I like Mr. Darling, but I think he's got a bit of the consultant hat on there. I do think over time that parts of our job will be taken by machines. Not all of it, and maybe not most, but deciding on which indexes to choose is one I think they'll figure out over time. Especially as the space used by plans becomes a small % of the db, hardware gets faster, and machine learning models become better trained.
June 8, 2017 at 12:31 pm
...to manage a lot of the trivial, but important, ...
So many people don't know the definition of trivial. Trivial does not mean easy.
June 8, 2017 at 4:20 pm
I wasn't implying it's easy. If it were easy, the DTA or missing index recommendations would do just fine. It's tedious and monotonous. It's complex, but a job that is bound into a niche box. This is exactly what a computer can do well.
Managing indexes is of little value from a DBA. There are much better things that they can do with their time, like fixing poor code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply