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

T-SQL: Why “It Depends” Expand / Collapse
Author
Message
Posted Wednesday, March 17, 2010 9:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 1,945, Visits: 2,891
>>4. Forcing a plan is dangerous. I remember in an article somewhere Joe Celko mentioned that the difference between .NET and SQL developement is that .NET for the most part does not have mutable characteristics. Over time SQL data changes so the profiler will change how it chooses to do things based on these changes. <<

I never talk aobut about .NET -- I think the quote you wanted was about hints/pragmas/other names in SQL that circumvent the optimizer. They are permanent, just like "temporary patches" in assembly language programs. Nobody will dare remove them, ever. Must be magic.

Thre is a classic joke about a husban asign his wife why she cuts the ham bone flush with the meat. Because her mother taught her to do it that way. He then asks the mother-in-law; she said because grandmother did it that way. He finally asks his grandmother-in-law. Answer: "When Grandfather and I were first married we had this little tiny oven in our apartment. It was sooo small I had to cut off the bone on the Christmas ham to make it fit!"


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #885205
Posted Wednesday, March 17, 2010 10:15 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
Adrian, I have nothing against those two approaches you listed. At a glance, they appear perfectly workable. Please understand that no techniques were deliberately excluded, other than by my desire to keep the article focused. Frankly, turning the topic into a comprehensive exploration of all possible coding techniques, or all potential variables is an effort I'm not prepared to make at this time.

I believe I mentioned in the article that other approaches were possible and that the intent was not to exhaustively compare all the possible solutions to one particular problem. Anyone seeking a "best" way to solve a particular problem can simply pose the problem in one of the forums and may stir up some lively debate.

The three approaches in the article illustrated that relative performance can vary with volume. Performance also varies due to a great many other factors such as indexing, partitioning, and parallelism; but longer articles than this one have been written that address those topics. I chose three techniques that told the story I felt new SQL developers needed to understand. The point was that SQL peformance is more than simply a matter of using one particular coding technique. Evidently I failed to communicate that as well as I hoped.

One thing though. I am learning to avoid the use of phrases like "normally", or "most people use", because that suggests that somewhere out there someone has taken an industry-wide survey about how people code in SQL. I know I haven't taken or even heard of such a survey. If anything, this article should encourage newcomers to test for themselves any technique they read or hear about. After all, confusion over unexpected results is something that drives us to learn and grow and deepen our understanding.

-------------------------------

Joe, your hambone story reminds me of something I read about legacy code containing undocumented quirks that were temporary workarounds for problems that were long since solved. No one ever goes back to clean them out and so they are perpetuated.

In another thread today, the OP finally found that the reason he wasn't getting the expected results from an index over a temporary table was that the DBA had shut off statistics for tempdb. He solved his problem by using a hint, but we encouraged him to go back and question the DBA's decision. Without statistics, he will be forever doomed to use hints, and even if they turn the statistics back on, those hints are liable to remain in place.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #885207
Posted Thursday, March 18, 2010 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 5:49 AM
Points: 9, Visits: 141
Hi Dixie,
thank you for your detailed answer. I understood the reasons. Sorry, I haven't seen the fragment where was mentioned that other solutions might exist!

In several (many) cases I observed that developers were interested mainly in a SQL solution to a problem rather then using the best solution. Learning to optimize comes in time, either when the user is a "perfectionist" or when it becomes imperative to do that - when (perceived) query's performance is (really) poor. When the developers ask already what's the best solution, then that's a good sign even if the answer is not so easy.

Thank you!
Best regards,
Adrian
http://sql-troubles.blogspot.com
Post #885338
Posted Thursday, March 18, 2010 6:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
When the developers ask already what's the best solution, then that's a good sign even if the answer is not so easy.


I couldn't agree more.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #885416
Posted Thursday, March 18, 2010 8:27 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 7:34 AM
Points: 42, Visits: 398
Bob,

What was the usage on tempdb and memory while running the various tests? That may have impacted performance.

When the optimizer picks an execution plan it doesn't necessarily take tempdb into account. Based on that, a plan with more in-memory operations will out-perform until a threshold where spillage to tempdb arises. At this point a tempdb-based plan may be more efficient. Put another way, maybe the second two examples are optimized to take advantage of tempdb whereas the first doesn't and suffers as load increases and disk access is now required.

I guess it depends on hardware where that threshold is reached.

Or I may be way off. Anyone have some spare cycles to test?
Post #885554
Posted Thursday, March 18, 2010 12:49 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
Hey Blackhawk:

The problem he was running into was that the optimizer had no statistics on an indexed temporary table he was joining and so it was making bad choices. It was very similar to the performance problems with using table variables. The entire thread is located here.


By the way, thanks for your positive comments on the article.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #885821
Posted Tuesday, March 23, 2010 3:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 8,684, Visits: 9,212
GabyYYZ (3/16/2010)
That being said, there are a few absolutes you can give for SQL Server:

1. Do NOT turn autoshrink on.
2. ummm...
3. ummm...

Come to think of it, as far as I can remember, # 1 seems to be the only answer about SQL Server that all experts seem to agree on...I can't think of any others. Please tell me if I'm wrong and there are some other obviously bad practices that are not much open to debate (or is someone willing to defend keeping Autoshrink on?)

Cheers.
Well, I'll risk being called a nutcase and suggest that even for Autoshrink the answer is "it depends".

Suppose you have a fairly small database, with occassional large insertions and large deletions that leave the total data size the same but the space occupied much greater (page splits all over the place); now suppose also that most of the time this database is read only; add to that the idea that if you leave autoshrink on the database will be small enough that it all fits into SQL Server's RAM on the server you ar running on, so that disc accesses are non-existent/negligible except during the occassional big updates, but if you leave autoshrink off the database will not fit into RAM unless you do some explicit shrinking. No-one has ever managed to explain to me just what benefit turning auto-shrink off in those circumstances will deliver, and how this benefit outweighs the obvious disbenefits.

So, as for pretty well everything else in the DB world, the answer is "it depends". Almost always it is right to have autoshrink off. In some very rare circumstances it is right to have it on. It depends on the shape of your workload, the size of your DB, whether or not the size of the DB can be regarded as effectively constant, what else is on the server, and so on.

There is one thing you really can be certain of though. If someone has a maintenance job that goes around rebuilding the indexes in the db every night and also has autshrink on in that DB then they really did get it wrong (I think all the experts agree on that one). Only slightly less certain is that all experts would agree that using autoshrink to save disc space is always wrong (I believe that all experts agree on that because because I think that anyone who doesn't can't be an expert; but I have to accept that I could be wrong)


Tom
Post #888547
Posted Tuesday, March 23, 2010 3:38 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 4,343, Visits: 6,150
Tom, why do you think that autoshrink will make the database more likely to fit into RAM?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #888554
Posted Wednesday, March 24, 2010 7:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 11,219, Visits: 12,976
Tom,

You don't have to autoshrink to get the data into ram. Rebuilding/reorganizing indexes will reduce the # of pages that hold data and allow SQL Server to load the accessed pages into memory.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #888923
Posted Wednesday, March 24, 2010 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 11,194, Visits: 11,136
TheSQLGuru (3/23/2010)
Tom, why do you think that autoshrink will make the database more likely to fit into RAM?

Possibly because auto-shrink may compact data onto fewer pages?

edit: Jack said it better




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #888940
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»»

Permissions Expand / Collapse