November 21, 2017 at 10:01 am
patrickmcginnis59 10839 - Monday, November 20, 2017 6:33 PMJeff Moden - Monday, November 20, 2017 3:41 PMpatrickmcginnis59 10839 - Monday, November 20, 2017 3:26 PMJeff Moden - Sunday, November 19, 2017 12:20 PMSQL-DBA-01 - Sunday, November 19, 2017 11:34 AMEirikur Eiriksson - Sunday, November 19, 2017 11:05 AMJeff Moden - Sunday, November 19, 2017 9:42 AMSQL-DBA-01 - Friday, November 17, 2017 1:29 PMZZartin - Friday, November 17, 2017 1:17 PMWhy not?Do you know the sequence is like this?
It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?
Trying to comprehend the reason and the goal of this question, like questioning north south east and west, kilos and punds etc.
😎If you can't make anything out of it, then the best option is to ignore it. 🙂
That's a poor attitude. Why do you want to know this? Curiosity goes both ways here.
I know that I would have asked a similar question when I started out with computer systems. Heck, for that matter, would it be important that these numbers stay constant for each installation during reboots? For any installation? For any version? Sure, we can only guess as we aren't the server implementers, but it turns out that often integer ID's like that need to be consistent throughout a code base but that their order could be completely arbitrary, that it is enough that master be 1 and other databases NOT be 1, and in that code base, when we asked for database 1, it be the same database as the other time we asked for database 1. Or maybe its important that 1 actually be master?
Maybe that's the case here, maybe its not. I know that during one project of mine, I needed to create about 20 or 30 opcodes, but that internally I could not constantly use character strings to reference these opcodes but rather I assigned integers to them in sequence and the value of these integers were completely arbitrary but once they were assigned they needed to stay that way throughout the codebase. Even though I then would have to keep using these integers, I could still refer (and generate references) to these integers by using something like named constants, similar to #define's with the c macro processor for instance and while I did refer to them, they got changed to integers and the program itself did not have to use character strings to find an occurence of a data structure, a constant integer index was just fine.
Personally, I doubt SQL-DBA-01's question was life or death or even important enough to merit this conglomeration of threadizens, but as a fan of forum psychology I do get interested in what triggers these "gang ups". So tell me, what offended you guys this time? Can you determine where SQL-DBA-01 went wrong? Or was his offense more imaginary in nature? Curious minds wanna know!
Understood. A couple of us wanted to know why and not because we thought the question had an "as designed" answer. Some folks ask questions where they've got a bigger problem than the question they're asking but either don't know how to phrase the question or are, for some reason, embarrassed to ask the question about the real problem they're having. The only way to dig that info out is to ask them why they're asking.
I think he was asking about the design, so "as designed" could be taken as a non response. Like for instance is there any significance to the numbering. Sometimes there is, sometimes there isn't.
He did answer you that it was out of curiosity, and while I'm not completely sure on parsing his English, I'm going to guess with his mention of architecture that he's probably curious about the innards like I often am, I know I went on a wild goose chase about why RBAR was so slow and it took a while for me to reason about it enough to satisfy my curiosity with reading and experimentation and at least in my case its made a world of difference about how I reason about how SQL Server is used and why.
And in Eirikur's case I know I'm struggling with what the OP was supposed to take from his posts, they were very confusing to me, and I think you chimed in on the OP simply because he typed:
"If you can't make anything out of it, then the best option is to ignore it."
yet by golly he really has a nugget of truth there. None the less, you are talking attitudes, instead of typing that, should he have rather apologised to Eirikur? Remember, Eirikur mentioned that he literally had no idea what the OP's goal was here, and the OP gave him some darn solid advice as a response and at this time you evaluated his response as a "poor attitude" and I'm going to respectfully disagree here, in fact were this forum moderated, Eirikur would have gotten my downvotes for a solid lack of contribution to the subject at hand despite posting multiple times, and indeed I'm going to side with the OP in that Eirikur did turn a technical question thread into personal attacks and frankly I'm sort of disappointed that nobody noted Eirikur's conduct in this thread. I shouldn't be, obviously, as long as I've participated here but I can't stop getting my hopes up I guess.
I've been around long enough not trying to guess a meaning of an ambiguous question, when asking for further explanations and getting "If you can't make anything out of it, then the best option is to ignore it." back? Throwing one's toys out of the pram isn't likely to help is it?
The reference to "north south east and west, kilos and punds" is obvious, all are set by someone making a desicion, no more logic to it than that and the same goes for the database_id's.
BTW, I haven't turned anything into a personal attack, please provide some substance to that allegation!
😎
I have helped this bloke many times, it hasn'e been easy, most likely because of language barriers but I've never been disrespectful. Telling someone that they are "persona non grata" is not an attack, it's just a simple statement saying that from now on his posts will be ignored.
November 21, 2017 at 10:01 am
Jeff Moden - Monday, November 20, 2017 11:38 PMJason A. Long - Monday, November 20, 2017 10:03 PMThe load order is far less interesting than the actual database names... "BlackArts01" & "NastyFast01..."
It's kinda like using the Kama Sutra to demonstrate how the Dewey Decimal System works, to a 15 year old...You'd like the BlackArts01 and NastyFast01 databases. They contain the guts of databases used in presentations to demonstrate some really fast code. One of them, for example, demonstrates a 5 aggregate system across 7 million row (1040 bytes per row) of a 7GB table and uses one of the aggregates as a divisor for the others to produce 4 different temporal percentage aggregates by month for 7 years with 4 other columns ranking by month, month of year, year, and grand total... in about 4 seconds. Part of the ancillary learning in that presentation is how to truly use minimal logging to create the table with the clustered index already in place and it clearly demonstrates why you shouldn't necessarily delete "duplicate" indexes. It also demonstrates the fallacy of relying on a Clustered Index for performance especially on wide tables.
BlackArts01 was the precon that Ed Wagner and I did for Pittsburgh back in 2016 and featured parts of NastyFast01. It's also where I pointed out the extreme importance of the number "318" to every DBA in the world. 😉
I'll admit to being intrigued...
Are you going to spill the beans on "318" or is it become the new "42"?
November 21, 2017 at 10:06 am
ZZartin - Tuesday, November 21, 2017 9:12 AMLynn Pettis - Tuesday, November 21, 2017 8:58 AMpatrickmcginnis59 10839 - Tuesday, November 21, 2017 8:29 AMJeff Moden - Tuesday, November 21, 2017 8:13 AMGilaMonster - Monday, November 20, 2017 11:53 PMJeff Moden - Monday, November 20, 2017 8:34 PMFrom the looks of it, master started and then just about everything else started up at the same second mark including all the system databases except TempDB and that started last but only 3 seconds later.
Once master is started, multiple threads are spawned and the DBs are started up in parallel, assuming a normal startup
I don't know why I'm bothering to add more here though.Understood on that. And apologies a bit.... You said that TempDb starts up just before Model and I missed the part where you were able to prove it. with something. I'd really be interested in seeing that because it seems contrary to the fact that if you have something you've added in the Model database (a Tally Table, for example), it will automatically appear in TempDb after a restart. I think the only way that could happen is if Model did fully start before TempDB.... either that or they copy to TempDB after they're both started and I think the wouldn't do it that way.
tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
Also since tempdb has an id of 2 (right????) this contradicts the assertion that id's are assigned in the order that databases are created.
Actually, I don't think it occurs on startup as much as on shutdown.
I just shutdown my SQL Server 2014 DE on my laptop and the datetime on the files after shutdown were at shutdown time.Hmm.... is that the last modified date or the created date though 🙂
Last modified, created date is from when I installed SQL Server 2014.
November 21, 2017 at 10:09 am
Thought I'd chime in and have a look from 2017 view, running on Ubuntu.
Firstly, I got the create times from the ext4 file system (which you can do using debugfs). This gave me the results:
/var/opt/mssql/data/master.mdf Sat Nov 4 01:15:27 2017
/var/opt/mssql/data/model.mdf Sat Nov 4 01:15:27 2017
/var/opt/mssql/data/msdbdata.mdf Sat Nov 4 01:15:27 2017
/var/opt/mssql/data/tempdb.mdf Sat Nov 4 01:15:28 2017
So, we can see here that (apparently), tempdb was created last (albeit by 1 second). I thought I'd check the ID's though, just incase:
master 1
tempdb 2
model 3
msdb 4
So, yeah, all the same. Just for double checking though, I checked the Container running on the same PC, and performed the same checks:
/var/lib/lxd/containers/sia/rootfs/var/opt/mssql/data/master.mdf Sat Nov 4 01:20:29 2017
/var/lib/lxd/containers/sia/rootfs/var/opt/mssql/data/model.mdf Sat Nov 4 01:20:29 2017
/var/lib/lxd/containers/sia/rootfs/var/opt/mssql/data/msdbdata.mdf Sat Nov 4 01:20:29 2017
/var/lib/lxd/containers/sia/rootfs/var/opt/mssql/data/tempdb.mdf Sat Nov 4 01:20:31 2017
TempDB 2 seconds after this time. And...:
master 1
tempdb 2
model 3
msdb 4
Same again.
I suspect, that as others have said, this is more a historic thing, but does seem that at least (in recent version) the create order is not the same as that of the database id's for system databases. Of course, would you want them changing after so many years? I suspect not. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 21, 2017 at 10:09 am
Lynn Pettis - Tuesday, November 21, 2017 8:58 AMActually, I don't think it occurs on startup as much as on shutdown.
I just shutdown my SQL Server 2014 DE on my laptop and the datetime on the files after shutdown were at shutdown time.
TempDB is cleared on startup. It can't be done on shutdown, because of unplanned terminations.
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
November 21, 2017 at 10:16 am
Jason A. Long - Tuesday, November 21, 2017 10:01 AMJeff Moden - Monday, November 20, 2017 11:38 PMJason A. Long - Monday, November 20, 2017 10:03 PMThe load order is far less interesting than the actual database names... "BlackArts01" & "NastyFast01..."
It's kinda like using the Kama Sutra to demonstrate how the Dewey Decimal System works, to a 15 year old...You'd like the BlackArts01 and NastyFast01 databases. They contain the guts of databases used in presentations to demonstrate some really fast code. One of them, for example, demonstrates a 5 aggregate system across 7 million row (1040 bytes per row) of a 7GB table and uses one of the aggregates as a divisor for the others to produce 4 different temporal percentage aggregates by month for 7 years with 4 other columns ranking by month, month of year, year, and grand total... in about 4 seconds. Part of the ancillary learning in that presentation is how to truly use minimal logging to create the table with the clustered index already in place and it clearly demonstrates why you shouldn't necessarily delete "duplicate" indexes. It also demonstrates the fallacy of relying on a Clustered Index for performance especially on wide tables.
BlackArts01 was the precon that Ed Wagner and I did for Pittsburgh back in 2016 and featured parts of NastyFast01. It's also where I pointed out the extreme importance of the number "318" to every DBA in the world. 😉
I'll admit to being intrigued...
Are you going to spill the beans on "318" or is it become the new "42"?
Golden ratio of 7.5714285714285714285714285714286😉
😎
November 21, 2017 at 10:20 am
Jeff Moden - Tuesday, November 21, 2017 8:13 AMI'd really be interested in seeing that because it seems contrary to the fact that if you have something you've added in the Model database (a Tally Table, for example), it will automatically appear in TempDb after a restart. I think the only way that could happen is if Model did fully start before TempDB.... either that or they copy to TempDB after they're both started and I think the wouldn't do it that way.
Back on SQL 2008 I did some research for an article and, iirc (it was a while ago), with some traceflags and some extra logging, what I saw was TempDB files opened, model opened and brought online, tempDB cleared (copied from model).
Now it may have changed since, it may be specific to single user or restricted mode, or it may be something about exactly what results in the logged messages. I didn't research further, it wasn't necessary for the article.
And yes, unnecessarily pedantic distinction.
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
November 21, 2017 at 10:33 am
Eirikur Eiriksson - Tuesday, November 21, 2017 10:16 AMJason A. Long - Tuesday, November 21, 2017 10:01 AMJeff Moden - Monday, November 20, 2017 11:38 PMJason A. Long - Monday, November 20, 2017 10:03 PMThe load order is far less interesting than the actual database names... "BlackArts01" & "NastyFast01..."
It's kinda like using the Kama Sutra to demonstrate how the Dewey Decimal System works, to a 15 year old...You'd like the BlackArts01 and NastyFast01 databases. They contain the guts of databases used in presentations to demonstrate some really fast code. One of them, for example, demonstrates a 5 aggregate system across 7 million row (1040 bytes per row) of a 7GB table and uses one of the aggregates as a divisor for the others to produce 4 different temporal percentage aggregates by month for 7 years with 4 other columns ranking by month, month of year, year, and grand total... in about 4 seconds. Part of the ancillary learning in that presentation is how to truly use minimal logging to create the table with the clustered index already in place and it clearly demonstrates why you shouldn't necessarily delete "duplicate" indexes. It also demonstrates the fallacy of relying on a Clustered Index for performance especially on wide tables.
BlackArts01 was the precon that Ed Wagner and I did for Pittsburgh back in 2016 and featured parts of NastyFast01. It's also where I pointed out the extreme importance of the number "318" to every DBA in the world. 😉
I'll admit to being intrigued...
Are you going to spill the beans on "318" or is it become the new "42"?Golden ratio of 7.5714285714285714285714285714286😉
😎
Of course! How could I have been so blind? 😎
November 21, 2017 at 11:51 am
Jason A. Long - Tuesday, November 21, 2017 10:33 AMEirikur Eiriksson - Tuesday, November 21, 2017 10:16 AMJason A. Long - Tuesday, November 21, 2017 10:01 AMJeff Moden - Monday, November 20, 2017 11:38 PMJason A. Long - Monday, November 20, 2017 10:03 PMThe load order is far less interesting than the actual database names... "BlackArts01" & "NastyFast01..."
It's kinda like using the Kama Sutra to demonstrate how the Dewey Decimal System works, to a 15 year old...You'd like the BlackArts01 and NastyFast01 databases. They contain the guts of databases used in presentations to demonstrate some really fast code. One of them, for example, demonstrates a 5 aggregate system across 7 million row (1040 bytes per row) of a 7GB table and uses one of the aggregates as a divisor for the others to produce 4 different temporal percentage aggregates by month for 7 years with 4 other columns ranking by month, month of year, year, and grand total... in about 4 seconds. Part of the ancillary learning in that presentation is how to truly use minimal logging to create the table with the clustered index already in place and it clearly demonstrates why you shouldn't necessarily delete "duplicate" indexes. It also demonstrates the fallacy of relying on a Clustered Index for performance especially on wide tables.
BlackArts01 was the precon that Ed Wagner and I did for Pittsburgh back in 2016 and featured parts of NastyFast01. It's also where I pointed out the extreme importance of the number "318" to every DBA in the world. 😉
I'll admit to being intrigued...
Are you going to spill the beans on "318" or is it become the new "42"?Golden ratio of 7.5714285714285714285714285714286😉
😎Of course! How could I have been so blind? 😎
Nothing so sophisticated as that. Quite literally, you're looking at the number the wrong way. It's not actually a number... it's a glyph.
Also, the actual golden ratio so far as DBAs are concerned is "3/1"... If you get up to 3 WTFs during a code review, the golden rule is that you're code won't make it past the DBA. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2017 at 12:20 pm
Jeff Moden - Tuesday, November 21, 2017 11:51 AMJason A. Long - Tuesday, November 21, 2017 10:33 AMEirikur Eiriksson - Tuesday, November 21, 2017 10:16 AMJason A. Long - Tuesday, November 21, 2017 10:01 AMJeff Moden - Monday, November 20, 2017 11:38 PMJason A. Long - Monday, November 20, 2017 10:03 PMThe load order is far less interesting than the actual database names... "BlackArts01" & "NastyFast01..."
It's kinda like using the Kama Sutra to demonstrate how the Dewey Decimal System works, to a 15 year old...You'd like the BlackArts01 and NastyFast01 databases. They contain the guts of databases used in presentations to demonstrate some really fast code. One of them, for example, demonstrates a 5 aggregate system across 7 million row (1040 bytes per row) of a 7GB table and uses one of the aggregates as a divisor for the others to produce 4 different temporal percentage aggregates by month for 7 years with 4 other columns ranking by month, month of year, year, and grand total... in about 4 seconds. Part of the ancillary learning in that presentation is how to truly use minimal logging to create the table with the clustered index already in place and it clearly demonstrates why you shouldn't necessarily delete "duplicate" indexes. It also demonstrates the fallacy of relying on a Clustered Index for performance especially on wide tables.
BlackArts01 was the precon that Ed Wagner and I did for Pittsburgh back in 2016 and featured parts of NastyFast01. It's also where I pointed out the extreme importance of the number "318" to every DBA in the world. 😉
I'll admit to being intrigued...
Are you going to spill the beans on "318" or is it become the new "42"?Golden ratio of 7.5714285714285714285714285714286😉
😎Of course! How could I have been so blind? 😎
Nothing so sophisticated as that. Quite literally, you're looking at the number the wrong way. It's not actually a number... it's a glyph.
Also, the actual golden ratio so far as DBAs are concerned is "3/1"... If you get up to 3 WTFs during a code review, the golden rule is that you're code won't make it past the DBA. 😉
Nothing so sophisticated as that. Quite literally, you're looking at the number the wrong way. It's not actually a number... it's a glyph.
Sooo... Like typing 55378008 into a calculator and turning it upside down...
Also, the actual golden ratio so far as DBAs are concerned is "3/1"... If you get up to 3 WTFs during a code review, the golden rule is that you're code won't make it past the DBA. 😉
Ha! If I pulled the emergency brake at the 1st 3 WTFs, I'd bring development to a screeching halt. I've had to revert to the "just don't make it any worse than it already is" rule... Or the, "not all of this belongs in a single, explicit, transaction... Do you know what transactions actually are or what they actually do?" rule...
I just glad no one has the initiative to learn about query hints...
November 21, 2017 at 1:34 pm
Jason A. Long - Tuesday, November 21, 2017 12:20 PMJeff Moden - Tuesday, November 21, 2017 11:51 AMJason A. Long - Tuesday, November 21, 2017 10:33 AMEirikur Eiriksson - Tuesday, November 21, 2017 10:16 AMJason A. Long - Tuesday, November 21, 2017 10:01 AMJeff Moden - Monday, November 20, 2017 11:38 PMJason A. Long - Monday, November 20, 2017 10:03 PMThe load order is far less interesting than the actual database names... "BlackArts01" & "NastyFast01..."
It's kinda like using the Kama Sutra to demonstrate how the Dewey Decimal System works, to a 15 year old...You'd like the BlackArts01 and NastyFast01 databases. They contain the guts of databases used in presentations to demonstrate some really fast code. One of them, for example, demonstrates a 5 aggregate system across 7 million row (1040 bytes per row) of a 7GB table and uses one of the aggregates as a divisor for the others to produce 4 different temporal percentage aggregates by month for 7 years with 4 other columns ranking by month, month of year, year, and grand total... in about 4 seconds. Part of the ancillary learning in that presentation is how to truly use minimal logging to create the table with the clustered index already in place and it clearly demonstrates why you shouldn't necessarily delete "duplicate" indexes. It also demonstrates the fallacy of relying on a Clustered Index for performance especially on wide tables.
BlackArts01 was the precon that Ed Wagner and I did for Pittsburgh back in 2016 and featured parts of NastyFast01. It's also where I pointed out the extreme importance of the number "318" to every DBA in the world. 😉
I'll admit to being intrigued...
Are you going to spill the beans on "318" or is it become the new "42"?Golden ratio of 7.5714285714285714285714285714286😉
😎Of course! How could I have been so blind? 😎
Nothing so sophisticated as that. Quite literally, you're looking at the number the wrong way. It's not actually a number... it's a glyph.
Also, the actual golden ratio so far as DBAs are concerned is "3/1"... If you get up to 3 WTFs during a code review, the golden rule is that you're code won't make it past the DBA. 😉
Nothing so sophisticated as that. Quite literally, you're looking at the number the wrong way. It's not actually a number... it's a glyph.
Sooo... Like typing 55378008 into a calculator and turning it upside down...
You're closer than you think. 😉
November 21, 2017 at 3:28 pm
Ed Wagner - Tuesday, November 21, 2017 1:34 PMYou're closer than you think. 😉
Maybe so... but right now I feel like a cat trying to catch a laser pointer dot.
November 21, 2017 at 4:49 pm
Jason A. Long - Tuesday, November 21, 2017 3:28 PMEd Wagner - Tuesday, November 21, 2017 1:34 PMYou're closer than you think. 😉Maybe so... but right now I feel like a cat trying to catch a laser pointer dot.
Nah... nothing so deep. Rotate the number 90 degrees to the right (clockwise) and interpret each symbol. The 3 looks like a body part (spelled with 3 letters) that both men and women have. The 1 becomes a "line". The 8 becomes the symbol for "infinity" but should be expressed temporally here. The 3 is ON the 1.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2017 at 7:25 pm
LOL... I think I got it now.
Makes more sense than the Hawaiian Punch guy wearing Harry Caray’s glasses.
November 22, 2017 at 8:39 am
Viewing 15 posts - 61 through 75 (of 90 total)
You must be logged in to reply to this topic. Login to reply