InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:38:44 GMT20http://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxjust a wild guess n get +1 for that :)Thu, 11 Oct 2012 01:49:14 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]princa (5/7/2012)[/b][hr]Good question, it's actually the same scenario I am facing right now!!!But I still have some questions:#1 - I am using a partitioned view to union two tables, one is original table, one is backup table. So every time I will need to backup original table to backup table, and use partitioned view to union them then will return the same result as i query only original table. #2 - Then I will delete from original table and then insert all new data. While I am performing this step, eventhough I use with (nolock) on my partitioned view with select statement, it will still be locked until the insert process is done, the same behavior like Truncate. Am I doing something wrong here?[/quote]Whats the wait_type on the spid doing the select? I'd have to reconstruct the scenario to confirm. Also, what version is the server?Tue, 08 May 2012 15:16:59 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxGood question, it's actually the same scenario I am facing right now!!!But I still have some questions:#1 - I am using a partitioned view to union two tables, one is original table, one is backup table. So every time I will need to backup original table to backup table, and use partitioned view to union them then will return the same result as i query only original table. #2 - Then I will delete from original table and then insert all new data. While I am performing this step, eventhough I use with (nolock) on my partitioned view with select statement, it will still be locked until the insert process is done, the same behavior like Truncate. Am I doing something wrong here?Mon, 07 May 2012 12:49:27 GMTprincahttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxStraightforward question and highly detailed explanations!Thank you.Fri, 24 Feb 2012 08:06:26 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxA very very good question Craig... Thank you...Wed, 15 Feb 2012 15:15:39 GMTJagadish Kumar Punnapuhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]mythilimca89 (2/14/2012)[/b][hr]i can't understand the question:w00t:[/quote]I assume you're joking? If not, what in particular can't you understand?Tue, 14 Feb 2012 12:21:05 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]cengland0 (2/14/2012)[/b][hr][quote][b]Cliff Jones (2/13/2012)[/b][hr]Yes, that was the way I always did it also. It was very fast but then you had to deal with all those pesky named constraints and foreign keys.I agree, nicely worded question and answers that required some thought.[/quote]I still do it this way, that's why I asked if there was a better way to do it -- perhaps by using the ALTER VIEW or some other method. So everyone keeps saying that they used to populate tables this way. Which way are you doing it now?I only do this method for my raw data sources. The presentation layer (Not a real name for a data layer) is what has all the constraints and foreign keys. The raw data comes from multiple sources so no contraints are at that level. If they were, then you'd have to be extremely careful on which tables you load first and too many tables get populated simultaneously to have to worry about another problem like that.[/quote]I suspect it is still a valid and efficient way of doing it in most situations; I just don’t personally have much occasion to have to do this anymore.Tue, 14 Feb 2012 09:52:45 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxi can't understand the question:w00t:Tue, 14 Feb 2012 09:24:32 GMTmythilimca89http://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxVery good question Craig.Tue, 14 Feb 2012 08:58:27 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxGreat question, thanks.Tue, 14 Feb 2012 07:48:27 GMTSQLDCHhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxGreat questions and really well laid out. I prefer this type of question to one which just lists code.Thanks.Tue, 14 Feb 2012 06:29:35 GMTskankerhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxI have seen saying that using NOLOCK is good sign of the poor design.In my current work, we use NOLOCK in all SQL statements, (I know its not good)from the given scenario can anyone advice is there a way to bypass the NOLOCK usage and still get the same performance?Tue, 14 Feb 2012 03:17:14 GMTRaghavendra Mudugalhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxGood question and I am feeling good now as I got it right :-)Tue, 14 Feb 2012 02:50:49 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]Cliff Jones (2/13/2012)[/b][hr]Yes, that was the way I always did it also. It was very fast but then you had to deal with all those pesky named constraints and foreign keys.I agree, nicely worded question and answers that required some thought.[/quote]I still do it this way, that's why I asked if there was a better way to do it -- perhaps by using the ALTER VIEW or some other method. So everyone keeps saying that they used to populate tables this way. Which way are you doing it now?I only do this method for my raw data sources. The presentation layer (Not a real name for a data layer) is what has all the constraints and foreign keys. The raw data comes from multiple sources so no contraints are at that level. If they were, then you'd have to be extremely careful on which tables you load first and too many tables get populated simultaneously to have to worry about another problem like that.Tue, 14 Feb 2012 02:35:29 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxGood Question!!Thanks you Kraig.Sandip Panihttp://sqlcommitted.comMon, 13 Feb 2012 22:34:24 GMTsandippanihttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxNice question and great discussion!Mon, 13 Feb 2012 14:47:07 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]L' Eomot Inversé (2/13/2012)[/b][hr]Great question, very well presented.In the bad old days of SQL 2000 I used to solve this sort of problem using sp_rename.Something like [code]if exists (select * from sys.objects where type = 'U' and name = 'old_FD') drop table old_FDbegin tran --code to create new_FD and populate it wit the replacement dataexec sp_rename 'FD', 'old_FD', 'OBJECT'exec sp_rename 'new_FD','FD','OBJECT'commit tran[/code][/quote]Yes, that was the way I always did it also. It was very fast but then you had to deal with all those pesky named constraints and foreign keys.I agree, nicely worded question and answers that required some thought.Mon, 13 Feb 2012 13:37:47 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxGreat question, very well presented.In the bad old days of SQL 2000 I used to solve this sort of problem using sp_rename.Something like [code]if exists (select * from sys.objects where type = 'U' and name = 'old_FD') drop table old_FDbegin tran --code to create new_FD and populate it wit the replacement dataexec sp_rename 'FD', 'old_FD', 'OBJECT'exec sp_rename 'new_FD','FD','OBJECT'commit tran[/code]The SCH_M lock is held only for the duration of the two rename operations.I did it this way because it didn't just work for tables, it worked for databases too - renaming databases is pretty quick; of course in the database, as opposed to table, case the create and populate bit was a transfer in to the server of a database created and validated elsewhere (transferred by DTS or snapshot replication or importing and restoring a backup, depending on the target server and what our access to it was like - internet between UK and 3rd world or developing countries was not always exactly brilliant) not something done in the same transaction as the renaming; some read-only (or read-only except when we wanted to replace them, to be precise) databases were better updated by that method than by updating data piecemeal - take the db describing available media directly off the QA server rather than using a script. And losing the old data only when the current data was out of date was to allow us to roll back to the previous state if the unthinkable happened and we had made an error in one of those releases (a symptom of my paranoia).Mon, 13 Feb 2012 12:40:10 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]Sean Lange (2/13/2012)[/b][hr]Great question and excellent scenario setup. I am quite surprised that at the time I am posting there are 417 answers and 16% of them still think truncate is not logged. :w00t:[/quote]... Especially considering it's only been a week since the last discussion on it for a QotD! :crying:Mon, 13 Feb 2012 12:25:20 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]jdamm (2/13/2012)[/b][hr]I'll add another "Great Question" - I like the questions that make you sit and think about an answer, rather than either run a snippet of code or look up a command to answer (not that we do that before answering ;-) ).J[/quote]These are never bad things, at least to me. They encourage me to do it again and I know not everyone wants to throw bricks at my head for it. :w00t:Mon, 13 Feb 2012 12:24:27 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxTo add for a moment to Paul's well answered posts, the Snapshot Isolation won't save you from the necessary locks for Truncate, but will save you from the necessary dirty reads of the original issue with DELETE FROM, which is why you went NOLOCKing in the first place. :-)Mon, 13 Feb 2012 11:58:45 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]jdamm (2/13/2012)[/b][hr]I'll add another "Great Question" - I like the questions that make you sit and think about an answer, rather than either run a snippet of code or look up a command to answer (not that we do that before answering ;-) ).J[/quote]+1 I couldn't have said it better myself. :-DMon, 13 Feb 2012 10:38:40 GMTDana Medleyhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxI'll add another "Great Question" - I like the questions that make you sit and think about an answer, rather than either run a snippet of code or look up a command to answer (not that we do that before answering ;-) ).JMon, 13 Feb 2012 10:36:33 GMTjdammhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxGreat question!! Thanks for it. I had to deeply analyze the lock modes and the MSDN articles in order to decide which answer was correct, and that helped me learn something new today.Thanks again.El Jerry.Mon, 13 Feb 2012 09:09:37 GMTEL Jerryhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxgreat question and discussion today - tksMon, 13 Feb 2012 09:06:40 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxNice straightforward question with an excellent explanation. Thanks for submitting.Mon, 13 Feb 2012 09:00:32 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxNice oneMon, 13 Feb 2012 08:32:20 GMTSathishKhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxGreat question and excellent scenario setup. I am quite surprised that at the time I am posting there are 417 answers and 16% of them still think truncate is not logged. :w00t:Mon, 13 Feb 2012 07:39:24 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]cengland0 (2/13/2012)[/b][hr]Okay, wait a second. If you have someone running a large query on your view, then you submit the ALTER VIEW statement, it will wait until the large query is over before it runs, right?[/quote]Right. The ALTER VIEW (or most of the alternative strategies) have to acquire Sch-M sometime, the point is to make the time the Sch-M is held as short as possible.[quote]So, what about the queries that came after the ALTER VIEW? Wouldn't those be in queue waiting for the ALTER VIEW to complete but the ALTER VIEW hasn't started yet because it's still waiting for the select statement to complete. So, even though it's quick, due to the way the queue works, you could still have several queries waiting to execute.[/quote]Locking used to work a bit like this (a strict FIFO model) back in SQL Server 2000. Later versions have a much more relaxed form of FIFO where this sort of blocking does not generally occur. New queries that are not incompatible with existing *held* locks (not just requested ones) will get to run without being blocked behind the process waiting for Sch-M. The process needing Sch-M will get in eventually, and so long as this lock is not held for long, the users probably wouldn't even notice.Mon, 13 Feb 2012 07:26:16 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]cengland0 (2/13/2012)[/b][hr]I haven't used the SWITCH in an ALTER TABLE before but according to BOL: If reassigning one partition's data to form a single table, the target table must already be created and it must be empty. That seems you will still have the same problems.[/quote]The idea would be to load the new data into a separate table, then when everything is ready to flip over, truncate the existing table and SWITCH the new data in as one step. Most of the methods require obtaining Sch-M at some stage; the point is to make the changeover almost instantaneous when everything is ready to go. I'll respond to your question about the locks separately.Mon, 13 Feb 2012 07:20:31 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxReally interesting question. Learned something today. Thanks.Mon, 13 Feb 2012 07:05:07 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxReally good question! thanks...Mon, 13 Feb 2012 07:03:04 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]SQL Kiwi (2/13/2012)[/b][hr][quote][b]cengland0 (2/13/2012)[/b][hr]What other ways can you change the entire contents of a table and still have the reports available without dirty data?[/quote]Aside from the VIEW suggestion, similar alternatives include using a SYNONYM, using ALTER TABLE SWITCH (which does not require the table to be explicitly partitioned), a local partitioned view (where only one of the underlying tables is TRUNCATEd) or sp_rename. As Craig mentioned, yet another alternative is to use explicit keys to perform your own 'versioning'.[/quote]I haven't used the SWITCH in an ALTER TABLE before but according to BOL: If reassigning one partition's data to form a single table, the target table must already be created and it must be empty.That seems you will still have the same problems.Regarding SYNONYM, that was an excellent suggestion. Much better than creating a view. However, doesn't that still put a lock on the table during the process and also wait for all previously queued SQL commands to finish before executing?Mon, 13 Feb 2012 06:45:32 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]SQL Kiwi (2/13/2012)[/b][hr][quote][b]Hugo Kornelis (2/13/2012)[/b][hr]I didn't check it, but I fully expect an ALTER VIEW statement to require an exclusive schema lock.[/quote]This is true, but it's not really a problem - the ALTER VIEW statement will eventually get the lock it needs, complete very quickly, and then release that lock. If you don't mind how long the ALTER VIEW blocks for before succeeding, this can work well (though a deadlock might occur).[/quote]Okay, wait a second. If you have someone running a large query on your view, then you submit the ALTER VIEW statement, it will wait until the large query is over before it runs, right? So, what about the queries that came after the ALTER VIEW? Wouldn't those be in queue waiting for the ALTER VIEW to complete but the ALTER VIEW hasn't started yet because it's still waiting for the select statement to complete. So, even though it's quick, due to the way the queue works, you could still have several queries waiting to execute.Mon, 13 Feb 2012 06:38:42 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]cengland0 (2/13/2012)[/b][hr]What other ways can you change the entire contents of a table and still have the reports available without dirty data?[/quote]Aside from the VIEW suggestion, similar alternatives include using a SYNONYM, using ALTER TABLE SWITCH (which does not require the table to be explicitly partitioned), a local partitioned view (where only one of the underlying tables is TRUNCATEd) or sp_rename. As Craig mentioned, yet another alternative is to use explicit keys to perform your own 'versioning'.Mon, 13 Feb 2012 06:33:59 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]Hugo Kornelis (2/13/2012)[/b][hr]I didn't check it, but I fully expect an ALTER VIEW statement to require an exclusive schema lock.[/quote]This is true, but it's not really a problem - the ALTER VIEW statement will eventually get the lock it needs, complete very quickly, and then release that lock. If you don't mind how long the ALTER VIEW blocks for before succeeding, this can work well (though a deadlock might occur).[quote]For the situation depicted by Craig, the best solution would probably be to use one of the SNAPSHOT isolation levels.[/quote]RCSI doesn't help here since it provides versioning at the statement level. An RCSI query that starts after the modification starts will block waiting to acquire Sch-S. Full snapshot isolation doesn't work either: it will also block waiting to acquire Sch-S, and if the DDL change succeeds, the query will then fail with error 3961: [i]"Snapshot isolation transaction failed in database {name} because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation."[/i]Mon, 13 Feb 2012 06:28:07 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxI like the question. I don't like the confilicting documentation.[quote]TRUNCATE TABLE always locks the table and page but not each row. [/quote][url]http://msdn.microsoft.com/en-us/library/ms177570.aspx[/url]Same article also mentions that this is a DDL command, so I guess the Schema M lock is infered like most ddl commands.Mon, 13 Feb 2012 06:06:49 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspx[quote][b]cengland0 (2/13/2012)[/b][hr]I've thought of creating a view that points to the table. Then, you can populate another table with the new information and then update the view to point to the new table. Once all the queries are done running on the old dataset table, the old table can then be deleted. I've never tried this so I'm curious what the experts on this forum have to say about this. Does the view get locked if a query is running it? Any negative sides to my proposed solution?[/quote]In this case, the view and the underlying tables will end up with a Sch-S lock. This will negate any chance you have of either altering the view or performing a table rename while any component is using it.To completely avoid dirty data and locking issues in a case like this, you'll have to do the equivalent of a drainstop, which you can't do in SQL Server directly. This is one of the few cases where I allow for limited Dynamic SQL, because you'll have to actually alter the call to the database, and leave a hanging transaction to 'deactivate' the older version once all the locks have cleared.Hugo is correct, the best solution in cases like this is Snapshot isolation. It's just not always feasible, particularly if you're limited in TempDB. This is one of those 'events' that bit me in the arse once or twice along the way and when I wrote up last week's truncation question this seemed like a good one as well, and an easy way to present it.However, to go back to the root, in most scenarios I come upon this I look into an alternate solution entirely. Flushing a table once an hour is usually because you're reloading off an external feed. Say, current stock ticker prices. There are alternate ways to store this data without stepping on yourself. On of my preferred methods is to use a version key on the new data while allowing the old to continue to exist, and a settings table to tell all views and queries which data is most current.So, you'll import new data into the same structure, assign it a key, and when that's complete you'll update the record in the settings table that indicates to all queries (via WHERE or ON clause) which set of data to use. At the final part of the import you'll clear any data not in the last two versions (or store it, or whatever), so you don't end up with a monster archive table in your active areas.However, you don't always have time to completely rebuild an existing import process when you're looking for 'quick hits' to help optimize the system. Swapping a DELETE FROM with no WHERE clause to a TRUNCATE TABLE clause seems an easy and effective way to reduce a good chunk of logfile load. I find it's good to know the pitfalls of easy fixes. ;-)Mon, 13 Feb 2012 05:56:41 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxNice one, thank youIulianMon, 13 Feb 2012 05:04:49 GMTIulian -207023http://www.sqlservercentral.com/Forums/Topic1250898-2864-1.aspxCraig - interesting question.Thanks.Mon, 13 Feb 2012 05:02:27 GMTpaul s-306273