May 15, 2010 at 11:00 am
Comments posted to this topic are about the item Five Things to Trim From SQL Server
May 15, 2010 at 6:22 pm
I posted the following on Paul's good blog...
The problem is that we keep putting bandaids on stab wounds. Instead of deprecating features, the features should be repaired to provide incredible value. Removing auto-shrink would be doing nothing more than covering up a symptom of the real problem on a sometimes very useful tool. Instead of removing auto-shrink, the database shrink method should be rewritten to work correctly. What the hell... Peter Norton has been doing it right since the early versions of DOS.
Instead of doing a shrink as it's currently done, here's how it should work at a 120,000 foot level...
1. Prescan the structure of the file to determine what work needs to be done to shrink the file so that all pages for all tables will be left in a contiguous sequential order in the fewest number of steps.
2. Starting with the "first" out of order page in the file, move it to the first available blank page in the file. This could, of course, be a new single page/extent allocation at the "end" of the file.
3. Find the next correct page for the file and move it to the space opened up by step #2. This, of course, opens up a new blank page for repeats of step 2.
4. I don't know the exact algorithm that Norton used, but let's pretend that step 2 and 3 are it... continue steps two and three until complete.
5. Leave space at the end of the file for additional growth according to a user definable setting which, of course, should have a reasonable default based on the size of the data/index space assigned already.
When the run is complete, you'll have the same effect as what Norton does to a hard drive. Not only will the database be shrunk to the smallest possible size, but all table data will be sequentially contiguous and require comparatively very little disk head movement to traverse the rows of a table even if a table scan occurs. The logical side effect would be the equivalent of having rebuilt all clustered indexes and non-clustered indexes without any table interleaving what-so-ever. Even heaps would become contiguous according to their internal row numbering.
Some options that should be included are (with separate options for non-clustered indexes and clustered indexes/data)...
1. Compact all data and indexes to remove all page and extent splits for maximum compaction and disk space recovery.
2. The opposite setting of 1 would be "Do not compact page or extent splits." Just put them in sequential order. This would be a "self indicating" fill factor for tables and indexes that suffer a lot of inserts/modifications and would prevent a lot of page/extent splits in the near future.
3. Fill factor at the database level. This setting could have a couple of options:
a. No fill factor. Rely on existing page/extent splits.
b. Use original fill factor as originally assigned by index.
c. Fill factor for static tables/indexes (those that have not been written to for xx days/months)
d. Fill factor for frequently inserted tables/indexes).
e. Fill factor for frequently modified tables/indexes).
f. "By table" override for all the above settings.
4. Have the option for all of these actions to run in a "crawler" mode during "quiet times" like most of the disk defragmentation programs have.
Like I said... let's stop trying to cover up stab wounds with bandaids. Let's fix the bloody underlying problem and turn things from a "fault" to and incredibly useful feature. If Peter Norton and the folks and DiskKeeper can do it, the boys in Redmond should be able to do it.
... and here's what he posted back...
Hi Jeff - you're absolutely right. Problem is, I designed a comprehensive replacement for shrink in early 2001 (when I owned all of DBCC) that did everything you say above and more. There wasn't enough time and it wasn't a high enough priority so it didn't happen. That was the window of opportunity for doing something about it, when someone who cared was in control. Now that's not likely to happen, I'd like to see auto-shrink removed.
Along with all the real garbage they pulled in the GUI of 2k5 along with the deprecation and removal of some really nice features and the addition of features that just don't make any bloody sense, I have to agree with Paul. I'll transpose what he said into my own thoughts... some people driving the bus either don't care who's riding the bus or don't know how to drive.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2010 at 8:34 am
SSMS is probably the weakest link in the entire SQL tool chain.
May 16, 2010 at 11:10 am
sjsubscribe (5/16/2010)
SSMS is probably the weakest link in the entire SQL tool chain.
How so?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2010 at 12:30 pm
I'm not sure it's the weakest link, but it's a weak one. Brent had a good rant on things being buried, not clean, some dialogs getting lost (not in the taskbar), etc. It's kind of a mess to me, but lower priority than other things.
I agree with you, Jeff, it could be fixed. I'd like to see things fixed, but I understand that there are other priorities, mostly from sales, that prevent things from being fixed. There's also the "what" needs to be fixed. We all have different priorities.
May 16, 2010 at 4:30 pm
Jeff Moden (5/16/2010)
sjsubscribe (5/16/2010)
SSMS is probably the weakest link in the entire SQL tool chain.How so?
Compared to the stability and usability of Enterprise Manager it replaced, there's no contest. There are tons of posts elsewhere that can enumerate all the deficiencies, so I won't. Of course a lot of people have bought into the marketing hype and defend it. Even simple things like sorting on results columns is missing. Searching the results grid? No. Scripting the UI, forget it. Add-ins, a pain. Add-ins make an already unstable product practically unusable. I can't think of anything else that comes with MS SQL Server more flaky than SSMS. Recent fixes have probably arrested the downward spiral, but I'd still like to see it improved and make it worthy of MS SQL Server.
May 16, 2010 at 5:50 pm
sjsubscribe (5/16/2010)
Jeff Moden (5/16/2010)
sjsubscribe (5/16/2010)
SSMS is probably the weakest link in the entire SQL tool chain.How so?
Compared to the stability and usability of Enterprise Manager it replaced, there's no contest. There are tons of posts elsewhere that can enumerate all the deficiencies, so I won't. Of course a lot of people have bought into the marketing hype and defend it. Even simple things like sorting on results columns is missing. Searching the results grid? No. Scripting the UI, forget it. Add-ins, a pain. Add-ins make an already unstable product practically unusable. I can't think of anything else that comes with MS SQL Server more flaky than SSMS. Recent fixes have probably arrested the downward spiral, but I'd still like to see it improved and make it worthy of MS SQL Server.
Heh... pardon my previous caution. I'm glad to see someone actually feels pretty much the same way I do. 🙂 I'll add to the list you started... They took away the "f4" key functionality... they took away the simple method of selecting more than one table to delete a bunch of test tables in one shot... you have to open new edit windows to get a lot of the setting changes to take effect... you can't do anything in SSMS once you open a query designer window and when you get out and go back in on a given query, it has once again rearranged all the bloody tables... you still can't print the diagram it makes... a lot of the properties pages require that you expand the pulldown only to find out there's only one property to be modified... you can't script certain objects by right clicking on them but you can still use sp_helptext on them so where's the sense in that?... as you say, the list goes on and on. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2010 at 5:52 pm
Steve Jones - Editor (5/16/2010)
I'm not sure it's the weakest link, but it's a weak one. Brent had a good rant on things being buried, not clean, some dialogs getting lost (not in the taskbar), etc. It's kind of a mess to me, but lower priority than other things.I agree with you, Jeff, it could be fixed. I'd like to see things fixed, but I understand that there are other priorities, mostly from sales, that prevent things from being fixed. There's also the "what" needs to be fixed. We all have different priorities.
Heh... understood on the priorities thing. Too bad that the folks in sales don't know what would really sell and really put the product ahead of the competition. Actually, some of it would be just to catch up to the competition. 😛
The sales folks should talk to some of us "frequent posters" because we've seen it all and pretty much know what folks are going to ask. Heh... I mean, c'mon... what's so hard about making a PIVOT command that actually works well? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2010 at 8:00 am
remove autoshrink? OMG!! To many times have I seen databases paticularly the log files) without autoshrink turned on expand until every single byte or storage on the server was filled. There are far too many people out there running databases that have no conception on how to shrink files, or even how to set up a simple recovery plan. Until SQL server is taken out of the hands of barely developers and managers (i.e. never) then so mechanism will be needed to deal with this unlimited growth.
May 17, 2010 at 8:31 am
Not remove shrink. Remove auto-shrink.
If something expands to fill the disk, autoshrink won't prevent that. you will still need to, and can shrink the dbs.
May 17, 2010 at 10:49 am
Thanks, I totally agree with your editorial.
The one little thing that I miss from enterprise manager was being able to right-click on a table name -> all tasks -> import/emport data.
In SSMS you have to go back to the database name, right click -> tasks -> then either import or export data.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
May 17, 2010 at 11:02 am
I can understand those who are used to the command line not liking SSMS, I sure like the command line in Windows.
BUT being relatively new to SQL Server, I don't know what I'd do without SSMS.
May 17, 2010 at 11:28 am
Nice list Steve. I think those are some good options to change, remove, or improve.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 17, 2010 at 12:05 pm
Re: "not Shrink remove autoshrink". I understood that. But on too many occasions databases used by developers will run some ridiculously intensive process that will generatea ridiculous amount of log file entries. In addition, they run processes that fill up and expand the database with temporary data to the extend that the dbf and log files use up all the room on the server. Without an autoshrink everything comes to a grinding halt until they can generate a funding request for me to come in and run dbcc shrinkfiles.
I like SSMS. Not as convenient as the combination of enterprise mgr and query analyzer but not bad and there are new capabilities that I have yet to really utilize. My main problem was that after 10 years of using enterprise manager it was really hard to make the mental shift required to utilize the new tools, and to find the locations of the old tools. However "Nothing is permanent except change." It would be easier if more of my contracts would actually give me a chance to work with SQL Server. Recently I have been spending far too much time futzing with DB2 and Oracle.
May 17, 2010 at 12:07 pm
They'll also learn not to do that, or how to shrink it themselves.
Auto shrink causes more problems, performance problems, and isn't worth it. This is one of those cases where I don't think SQL Server should be protecting people's ignorance at the expense of other issues.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply