March 21, 2013 at 11:42 pm
Comments posted to this topic are about the item Toggle Switches
March 22, 2013 at 1:45 am
Interesting post. At work, I deal with SQL Server, Oracle, and Sybase.
For what it's worth, I like to think of the multitude of database options on different DBMS as like an airplane cockpit or buttons on an SLR camera. The number of switches you have have two effects: (1) it overwhelms the uninitiated (2) higher potential for incorrectly (or forgotten!) flipped switches.
If you must know how important #2 is, an SLR camera usually has a "reset" button combination to be always assured that you don't miss any switches toggled -- it brings all switches back to default!
Now, DBAs come and go and systems get updated. Switches are good. But sometimes more importantly is you know what and more importantly why you want to toggle a switch.
A wise man once said that the role of a camera is to "get out of the way of taking the picture". I feel very the same about a DBMS.
If I could take a good picture by flipping the fewest if not no switches, the better; If there was a better way to configure my database and it'd run fine for most of the days, why not?
But don't get me wrong, switches are good for the small fraction of work you have to do, where you know best what you want. For everyday work, worrying about switches too much is just a pain.
Cheers,
Ivan R.
March 22, 2013 at 2:37 am
It depends. At low to medium scale its a case of keeping your eye on things and let the system sort itself out.
At high scale you do want to get under the hood and start playing. However we should always strive for automation. If you can determine the decision making points and processes a DBA uses to decide a course of action then you can automate that.
Remember when clustering involved a lot of hacking the registry and other under the hood voodoo? Well it doesn't anymore and some of the things we have to do as configuration options today will vanish tomorrow.
Remember DBCC PINTABLE? Gone now because memory management is so much better.
March 22, 2013 at 7:17 am
For me less is definitely more. I understand that more toggles gives you more control but why do I want to control my data-store? I just want it to operate in the most optimal manner for me.
My ideal database would have just one button which allowed me to select an "operating mode" (Transactional DB, Reporting Data Warehouse etc.) and it would interrogate it's enviroment to determine all the nitty-gritty details it needs to decide how to optimise it's own performance for that function.
An impossible goal, I'm sure, but just imagine the blissful nirvana of a world without DBA's (speaking as a developer that is:-P)
March 22, 2013 at 7:41 am
There are some settings that arn't just tweaks; they fundamentally change the behaviour of SQL Server and should be chosen based on the case usage of the database:
Optimize for Ad hoc Workloads, Full vs. Bulk-logged recovers model, and Threshold and Maximum Degree of Parallelism, etc.
How an online transaction database should be optimally configured is different from what's optimal for a data warehouse. The problem is that these settings must be configured after installation and are often not entirely understood by the DBA.
It would be great if the SQL Server install process would present a handful of well described profiles from which the DBA can choose based on the intended useage pattern. Then based on the chosen profile, various server or default database level settings are automatically configured.
For example:
OLTP:
transactionally inserted,
write many / read many,
full transactional recovery,
low number of ad-hoc workloads,
high number of concurrent sessions,
normalized table design,
small - medium sized tables
Staging:
- bulk inserted,
- write once / read once,
- disabled transactional recovery,
- low number of ad-hoc workloads,
- low number of concurrent users,
- normalized table design,
- very large tables
Data Warehouse:
- bulk inserted,
- write once / read many,
- simple transactional recovery,
- high number of ad-hoc workloads,
- low number of concurrent sessions,
- star-schema table design,
- very large tables
OLAP:
- bulk inserted,
- write many / read many,
- disabled transactional recovery,
- low number of ad-hoc workloads,
- high number of concurrent sessions,
- star-schema table design,
- small / medium sized tables
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 22, 2013 at 8:40 am
I wish developers in general would stop using the us vs them paradigm to consider the customer. Assuming the user is an idiot newb who can't be trusted with settings is wrong. Assuming the user has somehow managed to become an expert at internals and can be trusted with no safety net is also wrong.
I assume that if I asked any of the professionals in this SQLServerCentral community if I should change setting X, the most accurate answer would be "it depends" - and the ensuing discussion of pro/con and if-this-then-that would prove their understanding (and mine) of the how/why of deviating from the default.
If the UI for affecting changes to the system captured this expert advise and took a more conversational approach, I know I'd feel more confident investigating switches/dials because the UI itself would remind me "be sure you consider..." or "this will impact..."
I'm not suggesting to go so far as a hand-holding "clippy" virtual DBA, but if I'm attempting to tune SQL for high performance and the interface can provide me with a sanity-check about observed daily requirements being within normal expectations for default settings I'd like to see a message like "Are you sure you want to risk your current A-OK status for some perceived benefit you just read about online?" Yeah, I think that's a good idea. 🙂
March 22, 2013 at 9:25 am
It would be great if the SQL Server install process would present a handful of well described profiles from which the DBA can choose based on the intended useage pattern.
That's kinda what I'm saying (although I envisaged it as a "dial" after installation but the two are close enough). I think I'm taking a slightly more abstract view though. In my eyes a database is a data repository, nothing more. I don't even care that it's a DB as opposed to a file system or some bloke sat in an office furiously shuffling postit notes around... it's just data. I want it to store data and feed it back to me when I want it. I don't care how it does that but I would like to know it's doing it in the most efficient way possible (which admittedly probably rules the postit notes out). Come to think of it in my absolute utopia even the dial would be redundant. The repository would simply select the best aproach to handling whatever operation was being thrown at it right now.
Now obviously that's an unrealistic ideal but is, none the less, the direction I'd like to be shooting for. I'll never get all the way there but the closer the better.
I wish developers in general would stop using the us vs them paradigm
Just to be clear I was only joking about a world without DBAs. Not sure if that came across or not but I hope it did. Come to think of it, you guys currently are my "dial" and I'm grateful for it (well, most of the time anyway).
March 22, 2013 at 9:40 am
I've been bitten by configuration settings in the past, but now find myself regularly executing sp_configure on unfamiliar servers. The one I haven't drilled into my thick head yet is startup parameters. Startup parameters are relatively harder to find and investigate what they do. I wish startup parameters were more readily visible and self-explanatory like configuration settings.
If I could have one toggle-switch... it would be for an override of Kerberos Windows Authentication double-hop "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"...argh! I get it, but can you please, just please forward this account?
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
March 22, 2013 at 9:48 am
Mike Dougherty-384281 (3/22/2013)
I assume that if I asked any of the professionals in this SQLServerCentral community if I should change setting X, the most accurate answer would be "it depends"🙂
Mike hit it on the head with this statement. The general configuration and default is for a general installation and data collection. We have the normal configuration for the normal data collection and business activity. If your data collection or process is vastly different you will want to configure things to optimize that processing or requirement.
Do we need more switches? "Yes depending on" the level of control we really need or feel we need for our specific process/data/user activity.
The challenge is that with more options we introduce a potential for more confusion by these new or unfamiliar with the process, theory, and strategies of data could become. By having say 14 new dials we may find some DBA's thinking that they should use some of them just because they are there and they may "test drive" a few just to see. In doing so they may introduce a significant amount of work with little or no gain.
Mike is right,, it really does depend on ...
Not all gray hairs are Dinosaurs!
March 22, 2013 at 12:28 pm
I agree with Mike as well, that in many cases, it really does depend..However, that said, you have to be very careful how you throw that term around and who you use it in front of. Because, in my experience, many people (mostly executives) regard that statement as the quickest way out. So, it can be viewed by some as a "cop out". So, you must be careful in the context you use it and who you are talking to at the time. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 22, 2013 at 11:57 pm
ijtroquim (3/22/2013)
Now, DBAs come and go and systems get updated. Switches are good. But sometimes more importantly is you know what and more importantly why you want to toggle a switch.A wise man once said that the role of a camera is to "get out of the way of taking the picture". I feel very the same about a DBMS.
If I could take a good picture by flipping the fewest if not no switches, the better; If there was a better way to configure my database and it'd run fine for most of the days, why not?
But don't get me wrong, switches are good for the small fraction of work you have to do, where you know best what you want. For everyday work, worrying about switches too much is just a pain.
Cheers,
Ivan R.
+1
Too many decisions to make can make for overload and confusion. Sometimes having the option available or more decision points available is a good thing.
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
March 27, 2013 at 12:44 pm
Does the photo for this editorial show a toggle switch that toggles between "ON" and "ON," between "NO" and "NO," or between "ON" and "NO"?
I expect that someone will answer: "Yes."
😛
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply