Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Fix the Little Things Expand / Collapse
Author
Message
Posted Tuesday, November 11, 2008 9:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:23 PM
Points: 33,278, Visits: 15,449
Comments posted to this topic are about the item Fix the Little Things






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #601103
Posted Tuesday, November 11, 2008 10:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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...

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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601135
Posted Wednesday, November 12, 2008 6:10 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
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.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #601287
Posted Wednesday, November 12, 2008 6:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:14 AM
Points: 1,049, Visits: 3,007
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, sumus solum profundum variat
Post #601295
Posted Wednesday, November 12, 2008 6:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601299
Posted Wednesday, November 12, 2008 8:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:23 PM
Points: 33,278, Visits: 15,449
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #601444
Posted Wednesday, November 12, 2008 12:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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.

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."

;)

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #601615
Posted Wednesday, November 12, 2008 3:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:23 PM
Points: 33,278, Visits: 15,449
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #601746
Posted Wednesday, November 12, 2008 3:55 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:44 PM
Points: 615, Visits: 443
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.



Post #601755
Posted Wednesday, November 12, 2008 4:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:23 PM
Points: 33,278, Visits: 15,449
And on every instance. Usually I have things scripted out, but still I shouldn't have to implement this every time.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #601758
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse