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»»

Toggle Switches Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 11:42 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
Comments posted to this topic are about the item Toggle Switches






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1434136
Posted Friday, March 22, 2013 1:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 21, 2014 2:49 AM
Points: 5, Visits: 242
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.
Post #1434149
Posted Friday, March 22, 2013 2:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 2,916, Visits: 1,854
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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1434156
Posted Friday, March 22, 2013 7:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 128, Visits: 694
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)
Post #1434252
Posted Friday, March 22, 2013 7:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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
Post #1434271
Posted Friday, March 22, 2013 8:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 6:19 AM
Points: 262, Visits: 919
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. :)
Post #1434306
Posted Friday, March 22, 2013 9:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 128, Visits: 694
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).
Post #1434333
Posted Friday, March 22, 2013 9:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:27 AM
Points: 1,411, Visits: 818
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)

Post #1434339
Posted Friday, March 22, 2013 9:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:15 AM
Points: 2,464, Visits: 1,551
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!
Post #1434343
Posted Friday, March 22, 2013 12:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,334, Visits: 3,069
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. ..."
Post #1434470
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse