September 14, 2011 at 11:27 am
Roy Ernest (9/14/2011)
I would also add not setting the min and max for memory.
Well, not setting max yes. Min can be left at 0 on a dedicated SQL box.
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
September 14, 2011 at 11:31 am
Gail, I stand corrected. 🙂
I forgot the fact that the default value is 0.
On another note, I am attending your Bad plan Sit!. 🙂
-Roy
September 14, 2011 at 11:37 am
Roy Ernest (9/14/2011)
I would also add not setting the min and max for memory.Not caring about the IO Subsystem.
Agreed, but this is getting more on "advanced" tuning.
I am under the impression that Grant is more looking more for stuff a CEO could do himself on a small mom & pop shop that could really get him in trouble.
I'm not sure this falls in there (ymmv).
September 14, 2011 at 11:39 am
Another request:
I'm bunking tonight and starting the rewrite of my rather old (and second-ever) article Managing Transaction Logs[/url]. Other than when and when not to shrink (which definitely has to go in there), what else do people think I need to add or expand on?
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
September 14, 2011 at 11:52 am
Grant Fritchey (9/14/2011)
Changing the topic for a moment, I'd like to beg some ideas from the group, again.I'm tasked with writing another article. This one is all about the scary, crazy stuff that the uninformed can do because of how SQL Server is setup and documented. As the one HUGE glaring example, the ability to just run DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS. Not just the ability, but the active encouragement. Another one that immediately comes to mind is the use of the NO_LOCK hint as the RUN_FASTER hint. What are a few others?
A couple come to mind:
1) Encouraging people to believe that the default isolation level (read committed) guarantees them ACID properties, so that they don't look at their application to see whether it needs to use snapshot isolation or serializable isolation or repeatable read instead for some or all of its transactions (or serializable isolation or repeatable read isolation for some of the statements within some of its transactions) or use holdlock hints sometimes to improve isolation for particular accesses to particular data. This is particularly pernicious since the default isolation guarantees neither atomicity nor isolation, and only offers durability if you stretch its definition to what might well be though of as being beyond its breaking point.
2) Encouraging people to believe that the using exact numerics instead of floats eliminates the need to look carefully at issues about rounding errors (so that they will lose out two ways: on performance for cases where float would be perfectly OK and on accuracy for cases where they pick too little precision for their exact numerics). This one is really bad, because it has been thoroughly taken on board by many otherwise competent people, so that extremely bad advice ("never use floats") instead of correct advice ("use your application's requirements to determine what the appropriate types are for its various non-integer numeric data domains") is available almost everywhere.
I'm sure there are more, if I remember any and remember you're collecting I'll post again.
Tom
September 14, 2011 at 11:57 am
Grant Fritchey (9/14/2011)
Changing the topic for a moment, I'd like to beg some ideas from the group, again.I'm tasked with writing another article. This one is all about the scary, crazy stuff that the uninformed can do because of how SQL Server is setup and documented. As the one HUGE glaring example, the ability to just run DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS. Not just the ability, but the active encouragement. Another one that immediately comes to mind is the use of the NO_LOCK hint as the RUN_FASTER hint. What are a few others?
The default settings when creating a database. Usually too small and autogrowth is specified with percentages, so the database can grow too rapidly.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 14, 2011 at 11:58 am
mtillman-921105 (9/14/2011)
Grant Fritchey (9/14/2011)
You combined Jeff & FLOAT. You're going to get the lecture now about the evils of the DECIMAL data type. I'm sorry, but you've brought this on yourself.Oh, DECIMAL's all I ever use in its stead just about... Then, well maybe I need the lecture. I'm primarily a student here anyway. :laugh:
No lecture from me, but you'll see that my reply to Gus' included advice to use float never and exact numerics always (an idea encouraged by MS documentation) is one of the things I think he should include in his collection of idiocies. You might get one from someone else though.
Tom
September 14, 2011 at 11:59 am
Congratulations, Jeff !!
And good job to everybody at SSC for voting the man in...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 14, 2011 at 12:24 pm
Congratz Jeff, Float's needed for microscopic percentages and the like (money in particular is problematic), I personally prefer decimal/numeric to avoid rounding issues as I usually deal with financial data and LIKE knowing my significant digits, and sorry about the car, Gail. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 14, 2011 at 12:26 pm
Tom.Thomson (9/14/2011)
mtillman-921105 (9/14/2011)
Grant Fritchey (9/14/2011)
You combined Jeff & FLOAT. You're going to get the lecture now about the evils of the DECIMAL data type. I'm sorry, but you've brought this on yourself.Oh, DECIMAL's all I ever use in its stead just about... Then, well maybe I need the lecture. I'm primarily a student here anyway. :laugh:
No lecture from me, but you'll see that my reply to Gus' included advice to use float never and exact numerics always (an idea encouraged by MS documentation) is one of the things I think he should include in his collection of idiocies. You might get one from someone else though.
For my purposes, using DECIMAL(20,4) has never let me down. I'm not doing quantum physics or anything that requires more decimal places. But I have worked with legacy code that used FLOAT and it did trip me up - just the other day as a matter of fact.
At least with DECIMAL, I know what I'm getting and if it isn't precise enough, I can always increase the decimal places. If you're just dealing with hours and money like I am, I think it's precise enough.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
September 14, 2011 at 12:37 pm
Grant,
Defaulting all character columns to varchar or, even worse, nvarchar and then compounding it by using 255.
No statistics or index maintenance.
Then echoing just about everything else everyone else said.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 14, 2011 at 12:40 pm
Grant Fritchey (9/14/2011)
Changing the topic for a moment, I'd like to beg some ideas from the group, again.I'm tasked with writing another article. This one is all about the scary, crazy stuff that the uninformed can do because of how SQL Server is setup and documented. As the one HUGE glaring example, the ability to just run DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS. Not just the ability, but the active encouragement. Another one that immediately comes to mind is the use of the NO_LOCK hint as the RUN_FASTER hint. What are a few others?
How about leaving the default settings as C:\ for both the data and the transaction log?
Or giving everyone permissions to create databases?
Not as technical, but both can crash the server pretty quickly.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 14, 2011 at 1:08 pm
ALZDBA (9/14/2011)
We get to hear "Ho, ho, hoo" at SQLPASS 2011 !!!!http://www.sqlservercentral.com/articles/Exceptional+DBA+Awards/75988/
Congratulations Jeff !!
+1
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
September 14, 2011 at 1:13 pm
Grant Fritchey (9/14/2011)
Changing the topic for a moment, I'd like to beg some ideas from the group, again.I'm tasked with writing another article. This one is all about the scary, crazy stuff that the uninformed can do because of how SQL Server is setup and documented. As the one HUGE glaring example, the ability to just run DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS. Not just the ability, but the active encouragement. Another one that immediately comes to mind is the use of the NO_LOCK hint as the RUN_FASTER hint. What are a few others?
Total lack of security planning (sa or db_owner access for everyone - even worse with just a single login). In other words: not using the Windows Authentication Mode.
September 14, 2011 at 1:14 pm
Grant Fritchey (9/14/2011)
Changing the topic for a moment, I'd like to beg some ideas from the group, again.I'm tasked with writing another article. This one is all about the scary, crazy stuff that the uninformed can do because of how SQL Server is setup and documented. As the one HUGE glaring example, the ability to just run DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS. Not just the ability, but the active encouragement. Another one that immediately comes to mind is the use of the NO_LOCK hint as the RUN_FASTER hint. What are a few others?
Pick your trace flag, undocumented or documented, that may be found on the internet.
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
Viewing 15 posts - 29,851 through 29,865 (of 66,742 total)
You must be logged in to reply to this topic. Login to reply