November 11, 2008 at 9:20 pm
Comments posted to this topic are about the item Fix the Little Things
November 11, 2008 at 10:31 pm
Heh... you already know the answer to this, Steve... flash and time to market drive everything. No time to think about such useful things... there's a shedule to meet! 😉
What would be even better is a defrag "crawler" that operated in the background, constantly seeking the biggest bang for the buck to defrag because the server already knows what the longest running queries are and, therefor, which indexes are used and need to be kept sparkling. Spare clock cycles would be used in a manner similar to DiskKeeper's disk defragger. When the big bang items are taken care of, then it would move on to less items. A couple of parameterized settings would allow it to be tweaked.
Of course, DBA's don't have to write squat to do this... all they have to do is lookup DBCC SHOWCONTIG and there's code already written for them. It's not a crawler, but it does get the job done. If the database is properly maintained, there's hardly ever a need to invoke a defrag or index rebuild manually. If there is the need, typing a simple DBCC command is almost as fast as finding the table, right clicking on it, and selecting something like "Defrag me now".
I agree... some of the little things are very important... Like the idea of deprecating the very useful SELECT ColumnAlias = ColumnName... what the hell is up with that? Why don't they leave that alone? Seems like they actually have to go out of their way to get rid of that. And it's VERY useful... it's one of the easiest ways to write a SELECT to test for what will be returned before turning it into an UPDATE which uses the SET ColumnName = x.ColumnName format.
And why did they get rid of the very usefile xp_getfiledetails extended stored procedure? They should have expanded on it's use!!!
You're right... the little things matter... these little things, and more, have been very useful and it would have been such a little thing to just leave them the hell alone! Then to do like they did in 2k5 and not have the simplicity of pressing F4 to find something... or being able to press Ctrl-Shift-C to comment out blocks of lines without having to shift to the 2k keyboard. Removing these incredibly useful things is "useful" or "improved"? They must want people to buy Toad or AQT... :hehe:
In case you couldn't tell, you've kinda hit a sore spot with me about Microsoft and the little things they've removed. What's next? Removing the FROM clause from UPDATE, being able to overload a variable in a single Select, or maybe remove the ability to SET @variable = column = expression in the UPDATE statement just because they aren't ANSI??? They've gotta stop messing with the little things before they start thinking of new little things.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2008 at 6:10 am
I wonder how much of it not being in the box is playing to the DBA market - add too many 'instead of' DBA features and we might start to worry that we won't be needed. Probably not the reason, but would be interesting to see the overall reaction.
I've never understood why the installer didn't include an option to set up a default maintenance plan, or just do an interview type approach to build one. Plans may not work for all, but compared to doing no maintenance, this would be a really good start.
It often seems that MS doesn't pay much attention to the 'average' DBA, or to companies that can't afford a DBA. Good to have knobs to turn, also good to have an auto-pilot.
November 12, 2008 at 6:23 am
Thing is, MS don't need to do this kind of stuff. If there's a market, someone will write a utility that does all these "little things". If there's sufficient demand, MS will then buy the company and integrate the product with the next SQL Server version.
Look back at all MS's acquisitions and tell me I'm wrong....
Semper in excretia, suus solum profundum variat
November 12, 2008 at 6:28 am
majorbloodnock (11/12/2008)
Thing is, MS don't need to do this kind of stuff. If there's a market, someone will write a utility that does all these "little things". If there's sufficient demand, MS will then buy the company and integrate the product with the next SQL Server version.Look back at all MS's acquisitions and tell me I'm wrong....
I think that's pretty much spot on... but I sure do wish they'd stop removing stuff that already works... and yes, some of it is "undocumented", but a lot of it is very well documented. Of course, planned obsolescence through little jabs is probably a part of the plan... either that or just thoughtless arrogance.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2008 at 8:45 am
I'm not sure about the removing features, but there are quite a few little things that would make life easier, and they're not usually important enough for someone to write a utility for. Or profitable enough.
Microsoft buys small companies they think will generate sales, they don't necessarily look to improve the lives of DBAs by fixing simple things. I know they want to prove an ROI, and SS2K8 includes things to help the DBA, but not some simple, low level things that occur constantly, like fragmentation, like cleaning up the backup process to prevent people from inadvertently striping backups, breaking chains, etc. It seems that only those things that marketing things will "sell" the product are added.
My point is this is not necessarily the best approach. This has led them to have a reputation of not cleaning up the product and not fixing things. Look how many people still run SS2K and don't want to move. The fancy new features aren't impressive or needed, and the lack of polish on little things has people concerned about making changes.
I think they could get a lot of benefits from fixing things rather than focusing so much on new features.
November 12, 2008 at 12:00 pm
I dream about the day of right clicking a table and choosing "defragment." This wonderful built in utility would then return my table to it's initial fill factor and clean up all the allocations of extents to ensure that they were contiguous.
I'm not sure I'd want it that accessible, although it's already an option in the right-click menu of the index so it's already fairly accessible.
I heard of a case at my former company where one of the DBAs took down the production system for 3 hours after he was playing around in object explorer, right-clicked the index and selected rebuild.
Offline rebuild of the clustered index of a 52 million row table in peak business hours. :crying:
Call me a cynic, but I think if that option was there we'd still get lots of questions about fragmentation, plus a few more reading
"I selected the defragment option and now all the queries are timing out. Please help."
😉 :hehe:
Personally I'd like to see a bit more said about what operations will do (in the graphical wizards anyway) so that people can't say that they didn't know what it was going to do.
One other thing that would be great (and maybe I should just write it) would be a built-in tool to check for good maintenance and admin practices and would show a report that said something like:
Database X has not been backed up in 167 days.
Database Y is in full recovery model but has no log backups. Log drive will fill up in approximately 24.2 minutes at current rate of growth.
Database Z has been shrunk 42 times in the last week and has grown 178 times in the same period. A defragment of indexes and drive is recommended
CheckDB has not been run on Database K in 127 days
... etc, etc...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 3:38 pm
Ah, but the problem is the way the option is there. It shouldn't be an offline operation. Making this an enterprise only feature was ridiculous. I understand most of them, but not this one.
The system should handle this without interrupting users. We write stuff to get around it, so should MS.
November 12, 2008 at 3:55 pm
how ironic. I just barely got to read your editorial today. What was i doing instead? Scheming a system that would reindex my prod server. I'm resurrecting a series of procs and tables i wrote up years ago at another location to perform showcontig (SQL2000) and tell me historically the status of tables. Then i can proceed to reindex or defrag them.
Silly, after all these years, that there isnt a built in config to take care of this for me.
How often have i implemented this type of system? almost at each db shop i have worked.
November 12, 2008 at 4:00 pm
And on every instance. Usually I have things scripted out, but still I shouldn't have to implement this every time.
November 14, 2008 at 3:15 am
Database X has not been backed up in 167 days.
Database Y is in full recovery model but has no log backups. Log drive will fill up in approximately 24.2 minutes at current rate of growth.
Database Z has been shrunk 42 times in the last week and has grown 178 times in the same period. A defragment of indexes and drive is recommended
CheckDB has not been run on Database K in 127 days
... etc, etc...
Hi Gail,
I'm not sure If I read the details incorrectly, but the new policy manager for SQL 2008 should supply just this. You setup the policies it should check on, and you can get a report over the policies that are not being followed.
Another sure bet would be SQLresponse from Redgate. It has a bunch of recommendations based on exactly what you mentioned - you were beaten to the punch on that 😉
p.s I don't work for Redgate, I just love their products 😀
Regards
GermanDBA
Regards,
WilliamD
November 14, 2008 at 8:40 am
GermanDBA (11/14/2008)
I'm not sure If I read the details incorrectly, but the new policy manager for SQL 2008 should supply just this. You setup the policies it should check on, and you can get a report over the policies that are not being followed.
The policies work, but only if the admin knows enough to setup and enable policies. If the person titled DBA doesn't know enough to do backups, what's the chance he's going to put policies in place or buy a 3rd party tool?
I'm talking about something for the situation where the person fulfilling the DBA role is completely clueless about what needs doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2008 at 1:05 am
Hi Gail,
I get what you mean. But then again, where does it stop? At some point the person responsible has to have some knowledge of the system he/she has been burdoned with.
There are plenty of accidental DBAs, but would you want them to act blindly on suggestions from the SQL server? You said yourself that someone you knew brought down a production server by doing something that was "too easy".
Some things should not be dumbed down, otherwise I would be out of a job! 😉
Regards,
WilliamD
February 21, 2013 at 7:01 am
But I'm somewhat amazed how many fragmentation questions, posts, articles, and more that I still see on a regular basis. Shouldn't this be a low level feature of SQL Server that just works? In 2008, or I guess SQL 11 now, should there be any reason for a DBA to monitor this and write custom code to ensure that it's fixed on a regular basis? Should this not be something built into the system?
It's sounds like what we need is something like an 'AUTO REORGANIZE INDEXES' database setting, and then something similar to the Lazy Writer process that sits in the background and performs online incremental reorganization of index pages (a handful at a time) during periods of relatively low resource utilization.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 21, 2013 at 8:41 am
Steve, FYI...
Not sure if this is me or the email but I clicked the "Join the discussion" link in the email this morning but was brought to following page:
http://www.sqlservercentral.com/FindForumThread/97198
Link in the email was:
http://www.sqlservercentral.com/links/746380/293894
I had to search the forums for "Fix the little things" to find this thread.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply