InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 08:46:09 GMT20http://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxExcellent article. It's likeThe Einstein Theory of relativity. In a place with big numbers rows behave different, Newton's theory does not correspond exactly.Tue, 06 Apr 2010 01:05:31 GMTMatjaz Justinhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]BlackHawk-17 (3/25/2010)[/b][hr]Based on this article from the [url=http://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx][size="2"]CSS SQL Server Engineers[/size][/url] team I think you are on the wrong path.Compact is not defragment. It goes back to the Paul Randall explanation - move pages to open space and trucate the remainder.Regards;Greg[/quote]Thanks for the reference. That is good info.Thu, 25 Mar 2010 13:09:06 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxBased on this article from the [url=http://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx][size="2"]CSS SQL Server Engineers[/size][/url] team I think you are on the wrong path.Compact is not defragment. It goes back to the Paul Randall explanation - move pages to open space and trucate the remainder.Regards;GregThu, 25 Mar 2010 13:01:26 GMTBlackHawk-17http://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxFor giggles, I just checked sp_who2 which also shows the DBCCFilesCompact.Thu, 25 Mar 2010 13:01:26 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[img]http://www.sqlservercentral.com/Forums/Attachment5541.aspx[/img]The script where I was first able to trap this info was designed for SQL 2000, thus needs some updating. I have seen it capture this the FilesCompact in both 2000 and 2005. This happens after the spacereclaim.I have modified the query I found to correct an arithmetic overflow error that occurred in the original script.Original Script Author Info:[code="sql"]/* SP_ACTIVITY *//* Author: Mitch van Huuksloot *//* Date: April 30, 2001 */[/code][code="sql"]set nocount onselect 'Activity on' = convert(char(19), getdate(), 20), Server = @@SERVERNAME/* temp tables to hold more-or-less consistent sysprocesses/syslockinfo snapshots */create table #info( spid bigint, cmd char(16), status char(10), blocked bigint, waittype binary(2), waittime bigint, lastwaittype char(20), waitresource char(25), dbname char(30), loginname char(25), hostname char(15), cpu bigint, physical_io bigint, [memusage] bigint, login_time char(19), last_batch char(19), open_tran bigint, net_address char(12), net_library char(12), )create table #locks( spid int, resource char(32), dbname char(30), indid int, indname char(30), objid integer, objectname char(30), typeid int, type char(3), mode char(12), status char(10), refcnt int, ownertype char(12), transid bigint)/* capture sysprocesses */insert into #info select p.spid, convert(char(16), p.cmd), convert(char(10), p.status), p.blocked, p.waittype, p.waittime, convert(char(20), p.lastwaittype), convert(char(25), p.waitresource), convert(char(30), d.name), convert(char(25), p.loginame), convert(char(15), p.hostname), p.cpu, p.physical_io, p.memusage, convert(char(19), p.login_time, 20), convert(char(19), p.last_batch, 20), p.open_tran, convert(char(12), p.net_address), convert(char(12), p.net_library) from master.dbo.sysprocesses p (nolock), master.dbo.sysdatabases d (nolock) where p.dbid = d.dbid/* capture syslockinfo */insert into #locks select L.req_spid, convert(char(32), L.rsc_text), convert(char(30), d.name), L.rsc_indid, SPACE(30), L.rsc_objid, SPACE(30), L.rsc_type, convert(char(3), v.name), convert(char(12), v2.name), convert(CHAR(10), v3.name), L.req_refcnt smallint, case L.req_ownertype when 1 then 'Transaction' when 2 then 'Cursor' when 3 then 'Session' when 4 then 'ExSession' else cast(L.req_ownertype as char(12)) end, req_transactionID from master..syslockinfo L (nolock), master..sysdatabases d (nolock), master..spt_values v (nolock), master..spt_values v2 (nolock), master..spt_values v3 (nolock) where L.rsc_dbid = d.dbid and l.rsc_type=v.number and v.type='LR' and (l.req_mode+1)=v2.number and v2.type='L' and l.req_status=v3.number and v3.type='LS'/* Show active processes from sysprocesses capture */print ''print 'Active SQL Server Processes'print ''select * from #info order by spid/* Dump out block chain, if there is one */declare @blkd intselect @blkd=count(spid) from #info where blocked = 0 and spid in (select distinct blocked from #info where blocked != 0)if @blkd > 0 begin print '' select 'SPIDs at the head of blocking chains'=spid from #info where blocked = 0 and spid in (select distinct blocked from #info where blocked != 0) print '' end/* Dump inputbuffers for each blocking process */declare @spid smallint, @spidch char(5), @msg varchar(100)declare c1 cursor for select distinct blocked from #info where blocked > 0 FOR READ ONLYopen c1fetch c1 into @spidwhile @@fetch_status >= 0 begin select @spidch = convert(char(5), @spid) print '' select @msg = 'Blocking SPID ' + @spidch + ' input buffer capture' print '' print @msg select @msg = 'dbcc inputbuffer(' + @spidch + ')' execute(@msg) fetch c1 into @spid enddeallocate c1/* Dump inputbuffers for each blocked process */declare c1 cursor for select spid from #info where blocked > 0 FOR READ ONLYopen c1fetch c1 into @spidwhile @@fetch_status >= 0 begin select @spidch = convert(char(5), @spid) print '' select @msg = 'Blocked SPID ' + @spidch + ' input buffer capture' print '' print @msg select @msg = 'dbcc inputbuffer(' + @spidch + ')' execute(@msg) fetch c1 into @spid enddeallocate c1drop table #info -- we are finished with the sysprocesses capture/* Update locks table with tablename, objectname, indexname from the appropriate database */declare @dbname varchar(30), @objid int, @indid int, @idch varchar(20), @indch varchar(20), @objname varchar(30), @indexname varchar(30), @stmt varchar(500)declare c2 cursor for select distinct dbname, objid, indid from #locks where typeid between 4 and 9 for read onlyopen c2fetch c2 into @dbname, @objid, @indidwhile @@fetch_status >= 0 begin select @idch=cast(@objid as varchar(20)) select @indch=cast(@indid as varchar(20)) if @indid <> 0 select @stmt = 'update #locks set objectname = cast(o.name as char(30)), indname=cast(i.name as char(30)) from #locks l, ' + @dbname + '..sysobjects o (nolock), ' + @dbname + '..sysindexes i (nolock) where l.dbname = ' + '''' + @dbname + '''' + ' and l.objid = ' + @idch + ' and l.indid = ' + @indch + ' and o.id = ' + @idch + ' and i.id = ' + @idch + ' and i.indid = ' + @indch else select @stmt = 'update #locks set objectname = cast(o.name as char(30)) from #locks l, ' + @dbname + '..sysobjects o (nolock) where l.dbname = ' + '''' + @dbname + '''' + ' and l.objid = ' + @idch + ' and l.indid = ' + @indch + ' and o.id = ' + @idch execute(@stmt) fetch c2 into @dbname, @objid, @indid enddeallocate c2/* Show lock information from syslocks capture */print ''print 'Locks'print ''select spid, type, mode, status, [database]=dbname, [index]=indname, [object]=objectname, resource, ownertype, "trans #"=transid, refcntfrom #locks order by spid, dbname, object, indname, resource, type, mode, statusdrop table #locks -- drop syslockinfo captureGO[/code]Thu, 25 Mar 2010 12:33:06 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]CirquedeSQLeil (3/25/2010)[/b][hr]It does perform a fileCompact. I have captured it before with a different query - not a trace.Let me find the query and post it.[/quote]Aha! Awesome!!! That would be great - if I can catch it doing that, I will know for sure that auto-shrink compacts pages. Cool.Thu, 25 Mar 2010 12:04:57 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]Paul White NZ (3/25/2010)[/b][hr]I am still trying to catch the DBCC commands in a trace - I get the shrink events and a blank DBCC event, but not the thing I am looking for: whether it runs DBCC FilesCompact (I think that is the correct internal function name) or not.Interestingly, my test database shrank from 2GB to 3MB, and it shrank the log too, since it is in SIMPLE recovery.When I catch the detail, I will post it here.[/quote]It does perform a fileCompact. I have captured it before with a different query - not a trace.Let me find the query and post it.Thu, 25 Mar 2010 12:02:35 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxI am still trying to catch the DBCC commands in a trace - I get the shrink events and a blank DBCC event, but not the thing I am looking for: whether it runs DBCC FilesCompact (I think that is the correct internal function name) or not.Interestingly, my test database shrank from 2GB to 3MB, and it shrank the log too, since it is in SIMPLE recovery.When I catch the detail, I will post it here.Thu, 25 Mar 2010 11:56:54 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]TheSQLGuru (3/23/2010)[/b][hr]Tom, why do you think that autoshrink will make the database more likely to fit into RAM?[/quote]Because some time back someone told me that in cases where a page split had occurred but enough stuff had subsequently been deleted from the two pages that had split that they could be recombined into a single page then autoshrink would do so, and I believed them. I didn't attempt to verify it, because I thought the person concerned was likely to know what he was talking about. From the comments I've seen last night and today I guess I was probably wrong not to try to verify it, and just take it as valid.Thu, 25 Mar 2010 11:51:29 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]Jack Corbett (3/24/2010)[/b][hr]Paul Randal has an older blog post about [url=http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx]autoshrink[/url] on the Storage Engine Blog. It says:[quote]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.[/quote]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.[/quote]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 :doze:Wed, 24 Mar 2010 12:45:02 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxPaul Randal has an older blog post about [url=http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx]autoshrink[/url] on the Storage Engine Blog. It says:[quote]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.[/quote]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.Wed, 24 Mar 2010 08:32:09 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]Paul White NZ (3/24/2010)[/b][hr][quote][b]TheSQLGuru (3/23/2010)[/b][hr]Tom, why do you think that autoshrink will make the database more likely to fit into RAM?[/quote]Possibly because auto-shrink may compact data onto fewer pages?edit: Jack said it better[/quote]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.Wed, 24 Mar 2010 07:56:03 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]TheSQLGuru (3/23/2010)[/b][hr]Tom, why do you think that autoshrink will make the database more likely to fit into RAM?[/quote]Possibly because auto-shrink may compact data onto fewer pages?edit: Jack said it betterWed, 24 Mar 2010 07:21:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxTom,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.Wed, 24 Mar 2010 07:04:05 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxTom, why do you think that autoshrink will make the database more likely to fit into RAM?Tue, 23 Mar 2010 15:38:26 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]GabyYYZ (3/16/2010)[/b][hr]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.[/quote]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)Tue, 23 Mar 2010 15:17:15 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxHey 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 [url=http://www.sqlservercentral.com/Forums/Topic884950-338-1.aspx][b]here[/b][/url].By the way, thanks for your positive comments on the article.Thu, 18 Mar 2010 12:49:00 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxBob,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 [i]it depends [/i]on hardware where that threshold is reached.Or I may be way off. Anyone have some spare cycles to [i]test[/i]?Thu, 18 Mar 2010 08:27:38 GMTBlackHawk-17http://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote]When the developers ask already what's the best solution, then that's a good sign even if the answer is not so easy.[/quote]I couldn't agree more.Thu, 18 Mar 2010 06:09:17 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxHi 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 [url]http://sql-troubles.blogspot.com[/url]Thu, 18 Mar 2010 04:05:10 GMTsql-troubleshttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxAdrian, 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.Wed, 17 Mar 2010 22:15:57 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx>>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!"Wed, 17 Mar 2010 21:53:28 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxHi,Normally for such a query could be considered also the correlated subquery versions: select s.* from dbo.SalesDetail s where exists (select 1 from dbo.SalesDetail c where c.product = s.product group by c.product having max(c.SaleDate) = s.SaleDate)and select s.* from dbo.SalesDetail s where s.SaleDate IN (select max(c.SaleDate) lastDate from dbo.SalesDetail c where c.product = s.product group by c.product)I was wondering if you had any reason for excluding them from your analysis and curious what's the performance for them?!Typically the distinct clause from the second query is replaced by a select from the actual products table, fact that could impact positively query's performance. I understand that the distinct was introduced in order to eliminate the overhead of introducing another table and populating it with data, though I don't think there are many developers attempting to write such a query even if valid. I used the first query mainly with SQL Server 2000, while with the introduction of ranking functions I preferred the 3rd approach which is simpler to use and in addition provides more flexibility. In real-life scenarios there could be multiple transactions having the same date values, fact that shifts the balance toward the 2nd and 3rd approach. If are needed other aggregations (typically are needed) then the 3rd approach seems more appropriate. There are also other considerents: code portability (e.g. to Oracle), flexibility in handling multiple requests.I think users are asking more for some guidelines in writting the queries: best practices, things to avoid, how to analyze the performance of the queries, etc. Best regards,Adrian [url]http://sql-troubles.blogspot.com[/url]Wed, 17 Mar 2010 17:09:10 GMTsql-troubleshttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxFair enoughWed, 17 Mar 2010 16:37:17 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxYou may not.Wed, 17 Mar 2010 16:19:52 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]The Dixie Flatline (3/17/2010)[/b][hr]Jason, I hadn't planned on a sequel. No pun intended. Right now I have another writing commitment to honor before I can do another article. When I get a chance I'll bounce a couple of topics off you.[/quote]Writing commitment? If we may be nosy?Wed, 17 Mar 2010 16:15:16 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxJason, I hadn't planned on a sequel. No pun intended. Right now I have another writing commitment to honor before I can do another article. When I get a chance I'll bounce a couple of topics off you.Wed, 17 Mar 2010 15:10:02 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxSo, BOB, when is part II for this article coming out?Wed, 17 Mar 2010 14:49:08 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxExcellent article that brings several things to mind.1. I remember in college in our ADT course in addition to coding different sort algorithms(Quick, Merge, Hash) we had to do an experiment with what we coded as well. Our hypothesis was based on our code and Big O notation. Multiple data sets were used and read into a list. What we found out was that not only did the data size matter but if the data set was sorted or not sorted and the sort order mattered as well.I am curious what other factors might affect the results.2. This might not be an example of real world since the data is in a controlled environment and in my experience end user and controlled environment can't go in the same sentence :-D.3. This is more of a warning but just because your query runs good in dev you need to test on a machine that is like production. Countless times a fast query in dev timed out in QA and prod because of a single processor in Dev versus multi core system in other environments.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.Wed, 17 Mar 2010 13:56:15 GMTFatal Exception Errorhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxWhups, my apologies to the Guru and to Paul both. I promise to come back when I have time to read through all this more carefully.Wed, 17 Mar 2010 10:14:47 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]Jeff Moden (3/16/2010)[/b][hr]ith the last couple of posts in mind, I hope everyone realizes that the big brother to "It Depends" is "Test it!". ;-)[/quote]or the sequel? "Yes, but ..."Wed, 17 Mar 2010 10:02:05 GMTAlvin Ramardhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]roger.plowman (3/17/2010)[/b][hr][quote][b]TheSQLGuru (3/16/2010)[/b][hr]The optimizer is INCREDIBLY intelligent. Serious rocket science type of stuff. But it only has so much information to go on and (perforce) a limited amount of time/effort it can spend on each optimization required. BTW, as a consultant I would be happy to assist you in the from-scratch rewrite ... :cool:[/quote]Ah, but that would require a *budget*. I've heard of those magical beasts but in the last ten years I have yet to spot one in my neck of the woods. :)I was lucky to be able to get the company to spring for a single copy of SQL Server, much less consulting fees. Reading all these posts from people that actually can afford a *staff*... (laughing) I'm envious![/quote]I've worked in an environment like that before and it really sucked. Worse than not having a budget is the company was always living on the edge. The positive side to that was I learned to wear a lot of hats. That is really how I became a DBA from being an engineer. I have to say that even in larger companies there are still issues getting "Budget" for everything you need. I'm glad that Dev Edition of SQL Server is only around $50.Wed, 17 Mar 2010 09:11:01 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]The Dixie Flatline (3/17/2010)[/b][hr]I absolutely agree with Paul that the optimizer is serious rocket-science type stuff. The fact that execution plans may change or be less efficient at different volumes in no way implies that it is stupid. We just have to understand, work with, and forgive it's imperfections... even as it understands, works with and forgives ours. ;-)[/quote]That quote comes from 'The SQL Guru', not me. I do whole-heartedly agree with it, but that's not quite the same I guess ;-)Wed, 17 Mar 2010 08:26:53 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxI wasn't told there would be a [size="7"]TEST[/size]... :w00t: :w00t: :w00t:But I do recall writing at some point in the article.... 'testing, testing, testing'.I'm surprised (but pleased) to see that there is actually still some discussion going on over this topic. "It depends" is such a truism. I absolutely agree with Paul that the optimizer is serious rocket-science type stuff. The fact that execution plans may change or be less efficient at different volumes in no way implies that it is stupid. We just have to understand, work with, and forgive it's imperfections... even as it understands, works with and forgives ours. ;-)Wed, 17 Mar 2010 08:06:08 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxI always thought that's how things were done, if it worked its production else it was a test. :-PWed, 17 Mar 2010 07:24:14 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]CirquedeSQLeil (3/16/2010)[/b][hr][quote][b]Jeff Moden (3/16/2010)[/b][hr]ith the last couple of posts in mind, I hope everyone realizes that the big brother to "It Depends" is "Test it!". ;-)[/quote]I think this needs a little more emphasis. [size="7"]TEST IT![/size][/quote]I'm not quite sure I understand what you are saying here. :-DWed, 17 Mar 2010 07:06:55 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]TheSQLGuru (3/16/2010)[/b][hr]The optimizer is INCREDIBLY intelligent. Serious rocket science type of stuff. But it only has so much information to go on and (perforce) a limited amount of time/effort it can spend on each optimization required. BTW, as a consultant I would be happy to assist you in the from-scratch rewrite ... :cool:[/quote]Ah, but that would require a *budget*. I've heard of those magical beasts but in the last ten years I have yet to spot one in my neck of the woods. :)I was lucky to be able to get the company to spring for a single copy of SQL Server, much less consulting fees. Reading all these posts from people that actually can afford a *staff*... (laughing) I'm envious!Wed, 17 Mar 2010 07:01:08 GMTroger.plowmanhttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]Dave62 (3/17/2010)[/b][hr]Test it? Isn't putting it into production the way to test things? :hehe:[/quote]Much more fun that way, yes!Wed, 17 Mar 2010 07:00:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspxTest it? Isn't putting it into production the way to test things? :hehe:Wed, 17 Mar 2010 06:58:09 GMTDave62http://www.sqlservercentral.com/Forums/Topic883517-2655-1.aspx[quote][b]Paul White (3/16/2010)[/b][hr][size="7"][font="Arial Black"][color="#FF0000"]T[/color][color="yellow"]E[/color][color="#FF0000"]S[/color][color="#yellow"]T[/color] [color="#FF0000"]I[/color][color="#yellow"]T[/color][color="#FF0000"]?[/color][/font][/size][/quote][size="7"][font="Arial Black"][color="#yellow"]Y[/color][color="#FF0000"]E[/color][color="#yellow"]S[/color][/font][/size]Tue, 16 Mar 2010 22:56:01 GMTSQLRNNR