September 19, 2011 at 8:26 am
The log doesn't have pages (it has VLFs, log blocks and log records). Checkpoint will cause the log buffer to be flushed to disk (the current set of un-hardened log records).
The dirty data pages I referred to are data pages. Data pages aren't written to the log (log records are), data pages are written to the data file (by lazy writer or checkpoint) after their associated log records have been hardened in the log file.
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 19, 2011 at 8:27 am
Yes it is. If you take the DB offline or stop SQL you can edit it. Great for documenting the DB.
Editing the text file will not harm the DB in any way.
Make it an HTML file instead of TXT, and you can do nicely formatted documentation in it.
Make it an XML file, and you can create even more confusion, especially if you put the table structures in there in XML format, and include some sample data in them, as "documentation". Just wait to see some poor dev try to understand how SQL Server is using XML data stores.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2011 at 9:12 am
Tom.Thomson (9/18/2011)
Posting is easy (3 or 4 lines of code using CDO) and scheduling posts using say SQLAgent is also easy, but a good content generator is more difficult - how did you do that bit?
I can't remember what of the shelf class I used to do the client end of web conversations, but it certainly wasn't CDO and it certainly required more than 3 or 4 lines of code to get the dialogue right. I guess my mind was stuck on SMTP (probably for some alcoholic reason) when I posted that.
Tom
September 19, 2011 at 9:44 am
GSquared (9/19/2011)
Yes it is. If you take the DB offline or stop SQL you can edit it. Great for documenting the DB.
Editing the text file will not harm the DB in any way.
Make it an HTML file instead of TXT, and you can do nicely formatted documentation in it.
Make it an XML file, and you can create even more confusion, especially if you put the table structures in there in XML format, and include some sample data in them, as "documentation". Just wait to see some poor dev try to understand how SQL Server is using XML data stores.
damn...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 19, 2011 at 11:29 am
Chad Crawford (9/19/2011)
I was having a hard time figuring out when/why a checkpoint would write out dirty log pages, but ended up closing my question when I found a line in the 2K8 internals book stating that it did in fact write out dirty log pages (p.183). It seemed that transactions in progress may in fact have log information that wasn't hardened yet, is that not so?
The general principle is that any process that writes changed data pages to durable storage (e.g. checkpoint, lazy writer, eager writer, worker threads calling HelpLazyWriter) must ensure that all log records with an LSN less than or equal to the LSN on the changed data page are safely in permanent storage first. This is just the familiar Write-Ahead Logging (WAL) protocol.
So, yes, in-flight transactions often will have log records that are not persisted until the transaction commits (i.e. when FlushToLSN is called) or some other process comes along and writes a data page changed by the transaction to disk (which also calls FlushToLSN). In any case, WAL assures recovery to a transactionally-consistent state is possible (product bugs notwithstanding).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 19, 2011 at 1:27 pm
WayneS (9/19/2011)
Gianluca Sartori (9/19/2011)
WayneS (9/19/2011)
Opinions wanted: Is it fair to post a Denali solution?Fair but slow, based on your performance tests.
Slow compared to QU, but still faster than c.u.r.s.o.r.
Maybe I ought to do some comparison tests with Hugo's set-based methods, and others that come out of this challenge.
Are you aware that only single statement solutions are allowed? That eliminates the QU method since you can't even declare a variable. For the same reason (at least from my point of view) Hugos solution is not permitted.
Maybe there's a magic solution out there... But my inner voice is telling me otherwise... 😉
September 19, 2011 at 2:40 pm
LutzM (9/19/2011)
Are you aware that only single statement solutions are allowed? That eliminates the QU method since you can't even declare a variable. For the same reason (at least from my point of view) Hugos solution is not permitted. Maybe there's a magic solution out there... But my inner voice is telling me otherwise... 😉
That's the main reason I only ever submitted one solution to those challenges. Single statement - why?
Hugo's solution is very nice, and often outperforms a (single-threaded) SQLCLR solution.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 19, 2011 at 3:32 pm
LutzM (9/19/2011)
WayneS (9/19/2011)
Gianluca Sartori (9/19/2011)
WayneS (9/19/2011)
Opinions wanted: Is it fair to post a Denali solution?Fair but slow, based on your performance tests.
Slow compared to QU, but still faster than c.u.r.s.o.r.
Maybe I ought to do some comparison tests with Hugo's set-based methods, and others that come out of this challenge.
Are you aware that only single statement solutions are allowed? That eliminates the QU method since you can't even declare a variable. For the same reason (at least from my point of view) Hugos solution is not permitted.
Maybe there's a magic solution out there... But my inner voice is telling me otherwise... 😉
I posted a single select statement solution... but it requires Denali (didn't see anything in the rules that restricted one to any particular version of SQL, especially one that is RTM :w00t:)
Check out my last article and my blog for an idea of how I did it. I don't expect it to be the fastest, but I wanted to rock the boat with a simple Denali version.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 19, 2011 at 4:12 pm
GilaMonster (9/19/2011)
The log doesn't have pages (it has VLFs, log blocks and log records). Checkpoint will cause the log buffer to be flushed to disk (the current set of un-hardened log records).The dirty data pages I referred to are data pages. Data pages aren't written to the log (log records are), data pages are written to the data file (by lazy writer or checkpoint) after their associated log records have been hardened in the log file.
Got it, that makes sense. Thanks for clarifying!
SQL Kiwi (9/19/2011)
So, yes, in-flight transactions often will have log records that are not persisted until the transaction commits (i.e. when FlushToLSN is called) or some other process comes along and writes a data page changed by the transaction to disk (which also calls FlushToLSN). In any case, WAL assures recovery to a transactionally-consistent state is possible (product bugs notwithstanding).
Does that imply that a single DML statement could have multiple log records scattered (not contiguous) in the log? I’ve moved this question back to the thread I created 2 weeks ago so as not to clutter The Thread.
Thanks,
Chad
September 19, 2011 at 4:44 pm
WayneS (9/19/2011)
LutzM (9/19/2011)
WayneS (9/19/2011)
...Slow compared to QU, but still faster than c.u.r.s.o.r.
Maybe I ought to do some comparison tests with Hugo's set-based methods, and others that come out of this challenge.
Are you aware that only single statement solutions are allowed? That eliminates the QU method since you can't even declare a variable. For the same reason (at least from my point of view) Hugos solution is not permitted.
Maybe there's a magic solution out there... But my inner voice is telling me otherwise... 😉
I posted a single select statement solution... but it requires Denali (didn't see anything in the rules that restricted one to any particular version of SQL, especially one that is RTM :w00t:)
Check out my last article and my blog for an idea of how I did it. I don't expect it to be the fastest, but I wanted to rock the boat with a simple Denali version.
My comment was more to lower your expectations...
Regarding Pauls comment: There's a reason for #11 of the (current) TcWinners list being "Anonymous" who cannot be referenced anymore... 😉
September 20, 2011 at 5:37 am
LutzM (9/19/2011)
WayneS (9/19/2011)
Gianluca Sartori (9/19/2011)
WayneS (9/19/2011)
Opinions wanted: Is it fair to post a Denali solution?Fair but slow, based on your performance tests.
Slow compared to QU, but still faster than c.u.r.s.o.r.
Maybe I ought to do some comparison tests with Hugo's set-based methods, and others that come out of this challenge.
Are you aware that only single statement solutions are allowed? That eliminates the QU method since you can't even declare a variable. For the same reason (at least from my point of view) Hugos solution is not permitted.
Maybe there's a magic solution out there... But my inner voice is telling me otherwise... 😉
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?
September 20, 2011 at 5:40 am
Whoot! So, who wants to go to Curacoa?
http://www.sqlsaturday.com/eventhome.aspx - SQL Saturday 103
September 20, 2011 at 5:43 am
Brandie Tarvin (9/20/2011)
Whoot! So, who wants to go to Curacoa?http://www.sqlsaturday.com/eventhome.aspx - SQL Saturday 103
I'm registered!
September 20, 2011 at 5:44 am
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?
Yes - most solutions to past challenges use CTEs extensively. Some would say to the point of being daft about it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2011 at 5:44 am
Brandie Tarvin (9/20/2011)
LutzM (9/19/2011)
WayneS (9/19/2011)
Gianluca Sartori (9/19/2011)
WayneS (9/19/2011)
Opinions wanted: Is it fair to post a Denali solution?Fair but slow, based on your performance tests.
Slow compared to QU, but still faster than c.u.r.s.o.r.
Maybe I ought to do some comparison tests with Hugo's set-based methods, and others that come out of this challenge.
Are you aware that only single statement solutions are allowed? That eliminates the QU method since you can't even declare a variable. For the same reason (at least from my point of view) Hugos solution is not permitted.
Maybe there's a magic solution out there... But my inner voice is telling me otherwise... 😉
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?
Yes.
Try doing ;with () Without any select after the with and you'll see if the engine thinks the statement is complete ;-).
Viewing 15 posts - 30,061 through 30,075 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply