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

T-SQL: Why “It Depends” Expand / Collapse
Author
Message
Posted Thursday, March 18, 2010 6:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 18, 2015 2:40 PM
Points: 2,801, Visits: 6,137
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 18, 2015 2:40 PM
Points: 2,801, Visits: 6,137
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: 2 days ago @ 4:11 PM
Points: 8,425, Visits: 10,138
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: Yesterday @ 3:38 PM
Points: 4,700, Visits: 6,795
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: Yesterday @ 2:54 PM
Points: 10,470, Visits: 13,778
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


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 7:33 AM
Points: 9,928, Visits: 11,253
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #888940
Posted Wednesday, March 24, 2010 7:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:38 PM
Points: 4,700, Visits: 6,795
Paul White NZ (3/24/2010)
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


I asked the question the way I did precisely because I don't think autoshrink DOES compact the data onto fewer pages and that it just moves them as is to earlier sections of the file(s). I could not find proof of this in my quick search on the topic however.


Best,

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 10,470, Visits: 13,778
Paul Randal has an older blog post about autoshrink on the Storage Engine Blog. It says:

The algorithms that shrink use are basically brute force. It starts at the end of the data file, picks up allocated pages and moves them as near to the front of the data file as it can, fixing up all the links as it goes.


Which confirms that it doesn't reduce the # of pages used to store the data, it just moves them to the "front" of the file and then releases space at the "end" of the file.




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 #888992
Posted Wednesday, March 24, 2010 12:45 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 7:33 AM
Points: 9,928, Visits: 11,253
Jack Corbett (3/24/2010)
Paul Randal has an older blog post about autoshrink on the Storage Engine Blog. It says:

The algorithms that shrink use are basically brute force. It starts at the end of the data file, picks up allocated pages and moves them as near to the front of the data file as it can, fixing up all the links as it goes.


Which confirms that it doesn't reduce the # of pages used to store the data, it just moves them to the "front" of the file and then releases space at the "end" of the file.

Interesting. I wonder if that is still true...probably.
I did set up a test, but am struggling to get auto-shrink to kick in




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #889265
« Prev Topic | Next Topic »

Add to briefcase «««678910»»

Permissions Expand / Collapse