« first day (4474 days earlier)      last day (691 days later) » 

00:20
@ErikDarling Well, having to move all the data out and reload it is just a small detail
@JoshDarnell It seems better that way yeah
@PaulWhite we have huge table with 1 lakh rows
@ErikDarling yeah terrible question and I imagine a terrible partitioning implementation too. I'm trying not to think about it
1
A: Keep smaller databases out of an availability group (and recover via backup) to avoid cluster/AG issues taking the db offline?

Sean Gallardy We've run into issues with some of the two nodes + file share witness clusters, where the cluster goes offline for some unknown reason, bringing every AG database offline. Along with: [...] so I'm thinking we may want to take those out of their AGs and have them be normal dbs, where cluster pr...

@SeanGallardy finally admits to log shipping supremacy
@PaulWhite whatcha thinking about
I suppose it's like asking someone not to think about bananas
Now you're thinking about bananas
I’m thinking about Gwen Stefani
2
00:34
Like ok so moving all the data out, splitting the partition, then reloading will 'fix' the problem. Maybe they don't have a file/group naming scheme to keep in sync or they just don't care. Anyway it strikes me as a very SO Q&A now. Low quality question with an answer that worked well enough for the OP but that's it.
@ErikDarling with a 🍌?
@PaulWhite 🚬🥸
@PaulWhite now that we know the solution is redoing everything there are a lot of options
The terrible partitioning question got an up vote whereas the reasonably well asked AG question did not
> Have you tried not having AG-specific problems
@ErikDarling Indeed. You might just as well recreate the entire scheme
00:55
The answer is “fix your AG” now feel free to ask your question 🔮
Or make your setup more complicated with a DAG
Or move it to a fully managed cloud solution where all problems are magically fixed
Cross-cloud for maximum robustness
Or just use log shipping 🛳️🪵
01:10
But then you'd have p much full control over a simple arrangement
Oh
01:20
Where’s the money in that
01:32
@ErikDarling I prefer replication.
Mar 29 at 20:10, by Erik Darling
yes you always seem to find the most obtuse way to do things
03:13
'Morning.
 
2 hours later…
05:16
Morning
Wordle 655 4/6*

⬛⬛🟩⬛⬛
🟩⬛🟩⬛🟩
🟩⬛🟩🟩🟩
🟩🟩🟩🟩🟩

Robbed of 3. It would have been a nice symmetric art as well
05:59
Wordle 655 4/6

⬛⬛⬛⬛⬛
⬛⬛⬛⬛⬛
⬛🟨⬛⬛⬛
🟩🟩🟩🟩🟩
06:35
Impressive how you've managed to avoid all the letters for so long
07:24
@SeanGallardy I feel you
 
1 hour later…
08:38
@ErikDarling Nah, I said if you can't use anything else, so it's a last resort ;)
Sounds like an admission of log shipping supremacy
🧮 <- Log shipping
Also known as Paul's first calculator
How do you respond to this, without sarcasm?
> Table schema consists of a ‘datetime’ column and 100-200 ‘image’ data type columns.
> Query performance is inconsistent when reading data the ‘image’ data type columns.
> Objective is to provide consistent good query performance, when querying LOB data.
Buy a farm
In other news, I finally installed SSMS 19
Already trying :)
Was it life-changing?
08:47
Then spent half an hour copying my preferred settings across
@SeanGallardy 2005 called, they want their data types back
Some of them were done automatically, but several important ones were missed
@Zikato One part of the issue for sure
What time is it for you now, Sean?
Sounds like someone wants their LOB data in row
08:50
@Zikato 4:50 AM
LOB should be renamed to YEET to keep up with the times
2
I get up at 4 AM to get ready to go to the gym at 5 AM
No wonder you're constantly unwell
I haven't been close to death yet this year, so we'll see ;)
dedication
08:51
I have 3 lives left
Well, I haven't been quite healthy yet this year. Maybe I should go to the gym more.
everyone should!
Morning
Wordle 655 3/6

🟩⬛🟩⬛⬛
🟩🟨🟩⬛⬛
🟩🟩🟩🟩🟩
sideways F
08:53
I can't imagine anything worse than hanging out in an enclosed space with other people sweating and grunting and/or recording tiktok videos
@JohnK.N. Good first guess
You need a better imagination
if that's the worst
Well, I'm trying not to imagine it
🍌🍌🍌🍌🍌
you have to put your mind to it
08:55
Anyway, look, it's idiomatic. You're not literal computers. I can imagine worse things ofc.
Hanging out with literal nerds in chat, for example.
3
shudder
Personally, I prefer walking and cycling in the fresh air over raising and lowering heavy objects, but each to their own
Took me an hour to read through transcript. I'm still asking myself: Why?
@PaulWhite ...but then again...
@PaulWhite Nicest thing Paul ever said to me in this chat
Don't get used to it
08:58
:O
Adieu
09:52
@SeanGallardy sounds like they need a LOBotomy
 
2 hours later…
11:55
@PaulWhite I never had a bike. Probably explains my gym condition.
12:17
weak and fat?
At least my dumbbells aren’t pink
Wordle 655 3/6*

🟩🟨⬛⬛⬛
🟩🟨🟩🟨⬛
🟩🟩🟩🟩🟩
12:36
@Zikato Yes, I thought I might get three completely black rows
I don't think I've ever done that
Wordle 655 4/6

⬛⬛⬛⬛⬛
🟩🟩🟩⬛⬛
🟩🟩🟩⬛⬛
🟩🟩🟩🟩🟩
Sideways P?
13:17
Wordle 655 4/6

🟨⬜⬜⬜⬜
⬜⬜🟩🟨⬜
⬜⬜🟩⬜🟨
🟩🟩🟩🟩🟩
13:28
Wordle 655 4/6*

🟩⬜⬜⬜🟨
🟩🟩⬜⬜⬜
🟩🟩⬜⬜⬜
🟩🟩🟩🟩🟩
@HannahVernon I'm impressed.
@Charlieface Can you imagine storing that much out of row stuff, which could be GB worth of data and expecting the query to take the same time whether returning 1KB or 1GB of data. I don't get people sometimes.
@ErikDarling Jelly
I modified a copy of the Stack Overflow Posts table to store the Body column out of row
So you could have an out of body experience?
It's nvarchar(max) so inlined by default where it will fit
Which is fine for point lookups, but a tragedy for p much any other use
  index_type_desc   alloc_unit_type_desc   index_level   page_count
 ----------------- ---------------------- ------------- ------------
  CLUSTERED INDEX   IN_ROW_DATA                      0       184758
  CLUSTERED INDEX   LOB_DATA                         0      4328705
It's bad for searches on the Body column of course, but string searches are often not best performed in T-SQL anyway
13:45
You must love JSON and XML searches, then...
I can't wait for the next textual representation of formats makes its way into databases
YAML columns.
100% make it happen
I don't particularly mind storing XML/JSON in the database. I do object to manipulating it there, except on entry and exit.
yaml(max)
YAML(XAML(MAX))
@PaulWhite I generally agree with this
13:50
I suppose a case could be made for storing Posts Body in a separate 1:1 table
6NF style
@JoshDarnell what do they say, never mistake luck for skill? Thanks though, I appreciate it.
Never attribute to skill what can be easily explained as luck
6
Crikey four NAA flags and a LQ flag on the same post
And no downvotes
true
I suppose people don't want to be unwelcoming
And to be fair, it's a perfectly reasonable question just in the wrong place
Well, it was
@J.D. very Taoist
14:00
@PaulWhite i did that once, ruined most demos
@ErikDarling Yeah
0
Q: How to resolve SQL Function is timing out

bteeIm having this issue, i have a function timing out on my stg sql server, how do i resolve this issue ? Thanks

WTF
I enabled Advanced Protection for my google account and now Google Play Store refuses to install Microsoft Swiftkey keyboard lol. Such subversive behaviour from the Googs.
What's advanced protection?
I recently installed Microsoft Family / XBox and M$ blocks Google Chrome by default.
it makes it so you can only log in with a Fido2 key, and enables various security protections to avoid your account being hijacked
@JohnK.N. that's for your own good
14:09
What's a fido2 key?
Same bunch of wan*ers
@PaulWhite its for unlocking your dog
3
What's a dog?
Tell that to my son who had to a project to research via a login stored in Google Chrome that he could no longer access.
this is an example fido2 key amazon.ca/dp/…
14:11
Oh, a hardware key
just don't tell me your dog is named kusto
Kerberos
2
> HOTP secret key can be programmed by the user with a tool available on our website
I'd be terrified of losing the key
that's why you have multiple keys assigned to your account
one in the safety deposit box at your bank
14:13
Do you work for the CIA
no, but I subscribe to the zero trust principle
Is that the same thing as paranoia?
pretty much lol, but it sounds nicer
@HannahVernon who hurt you
you lot with all the comments about "Is Service Broker involved" lol
14:15
Putting heaps of trust into the key provider, just saying
well akshually lol
the FIDO2 protocol ensures the private keys never leave the FIDO2 device.
Heaps of trust to put in a protocol
Ideally, you'd have a prime number of different keys assigned to as many trusted people geographically dispersed around the world who all have to turn their keys at the same time
@HannahVernon Haha fair point 😁
SHA1 as well, tsk tsk
that's HMAC-SHA1 and is only used if you enable the key to spit out OTP values
14:21
nerd
thank you
Also the queries run slowly without running the profiler trace. We are using Entity framework and this all makes me think that the library is doing something odd when we call SaveChangesAsync. Even with monitoring I cannot see any bottlenecks in our applications execution either. — Joni yesterday
Sounds like an ORM problem
@SeanGallardy I'm Daown with that.
Funnily enough, "fido" is a current SQL Server code name
Well, current-ish
for a bunch of crap no one will ever use
14:28
we're such a jaded group
Synapse thing IIRC
Who can keep up
as in, if you have synapses, you're jaded
whatever feature(s) they pump up the most during a sql server release is what will get the least adoption
dan guzman loves writing a whole ass query in comments huh
@ErikDarling I think it might be a virus of some kind
Maybe he needs a fido
i'd suspect a parasite
perhaps he spends a lot of time around cordyceps
14:37
I wish I hadn't looked that up
@ErikDarling Recent example or are you talking about the previous GAM question?
@PaulWhite there have been a few recently that i nudged him to turn into answers
I see. Good man then
@ErikDarling Like Blockchain?
Oh my gosh I forgot about that.
@SeanGallardy blockchain tables reminds of the feedback request to have an immutable property for columns.
14:47
LEDGER TABLES
No-one'sgonnausethem tables
but blockchain was the buzzword of the day
so we have that now
how is graph/node adoption looking
Chapter 11
I wonder what happens when an append-only ledger table runs out of space.
it's not like you can delete rows
"disk is cheap" etc. 🤣
15:00
ad-infinitum ala cloud
I just hit F1 on CREATE TABLE and SSMS took me to the graph version.
@HannahVernon Database goes into read only mode
I brought this up in testing, but it's not a V1 feature
so the customer must then point the application at a new ledger table in a different database. Fun.
just hyperscale it bro
15:18
Hope you don't need log throughput
16:07
CREATE INDEX
    p0
ON dbo.Posts
(
    OwnerUserId
);
erik why
what is upsetting you my dear young josh
The formatting seems unconventional to me.
v. high whitespace to non-whitespace ratio.
I guess it would look better if the index had a jillion columns in it.
CREATE INDEX p0 ON dbo.Posts (OwnerUserId);

CREATE INDEX p0
ON dbo.Posts (OwnerUserId);
yeah i wouldn't normally do it like that but i find it helpful in blog posts to really make it clear what the index definition is
16:23
also white space is free
white page free space too
@ErikDarling Fair.
> On the GA side for Azure SQL Database, DBCC SHRINKDATABASE is now supported in Azure SQL Database, enabling customers to shrink database files without impacting query performance.
That statement is...weird.
I think they're referring to the new low priority thing for shrink not impacting concurrent queries, not that shrinking won't affect perf in the long run
Ohhh.
Well I'm sure other people won't misinterpret that like I did.
"shrink is no longer bad for performance!"
4
The wording is awkward
But they no longer employ poets
16:33
Also, that post reminded me of "Optimized Locking," which Paul mentioned awhile back. I tried reading the Docs page about it, and it really baked my noodle.
Yeah, it could be explained more simply
The concept itself isn't hard
For everyone's reference.
I was going to post a new link, but didn't want to denormalize the chat.
2
very good
I keep hoping opt locking will appear in 2022
not that it will benefit people who follow the best practice of getting data right first time
this documentation really is laqing
they're called learnings now
but yes
I really h8 that name change from 'docs' to 'learn'
It's twee and condescending
Not to mention inaccurate
I don't go to the documentation to learn for the most part
Nobody hits F1 on CREATE TABLE to learn how to create a table
16:51
ooh OL requires ADR
@PaulWhite I hate how difficult it is to describe a link to learn instead of docs
> For customers using RCSI, to force blocking between two queries when optimized locking is enabled, use the READCOMMITTEDLOCK query hint.
praise the gods
@PaulWhite edited
16:52
though it would be spiffy if it were called accelerated locking to further confuse people
WE TAKE REALLY FAST LOCKS
FOR A LONG TIME
Accelerated Duration Locking (ADR)
also, regarding problems with ledger, I ran out of space in a test db, dropped the ledger table hoping that would resolve the issue, but of course it doesn't because auditability. So, I got these errors when attempting a CHECKPOINT:
> Msg 1105, Level 17, State 2, Procedure sys.sp_flush_ledger_transactions_table, Line 15 [Batch Start Line 54]
> Could not allocate space for object 'sys.db_ledger_transactions'.'ci_block_id' in database 'LedgerTest' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
> Msg 37336, Level 17, State 1, Line 55
> Failed to flush the Ledger Transactions table to disk in database with ID 22 due to error 1105. Check the errorl
try with a database that has an odd database id
22 is an unlucky number
all my databases are odd
especially in a try/catch block
16:56
the takeaway is don't use Ledger unless you have a really big disk
I said disk ya filthy animals
some disks are bigger than others
do I touch that or just let it go
sean: twss
paul: bans sean
I hear fat disks are the best disks
someday i hope paul refers to me as exfat
😭
16:58
LOL
@ErikDarling maybe when you're 64
22 is a cute number
fat chance
looks like two swans
my 21 other databases are jealous now
🦆🦆
17:01
CREATE DATABASE [🦆🦆]
ON
(NAME = N'🦆🦆_primary', FILENAME = N'/var/opt/mssql/data/🦆🦆_Primary.mdf', SIZE = 128MB, MAXSIZE = 128MB, FILEGROWTH = 128MB)
LOG ON
(NAME = N'🦆🦆_Log', FILENAME = N'/var/opt/mssql/log/🦆🦆_Log.ldf', SIZE = 128MB, MAXSIZE = 128MB, FILEGROWTH = 128MB);
So long as it gets ID 22, all good
OMG you're using *nix
-rw-rw----. 1 mssql mssql  134217728 Apr  5 12:01 🦆🦆_Primary.mdf
that's the actual filename
The red review indicator lit up because there are three first posts waiting
three
🚨 RED ALERT 🚨
17:06
state hypercritical
I hope DuckDuckGo.com uses 🦆🦆 as their database name
2 hours ago, by Sean Gallardy
Hope you don't need log throughput
🤡🤡🤡
🏆
crisis averted
i cleared the heck out of some review queues
ywofc
17:30
@PaulWhite HMAC-SHA1 is actually safe as I understand, at least when the hash result is not known to the attacker, because of the way HMAC works..
People just don't seem to care, or even want to care, what goes on under the hood. Ultimately every good abstraction is leaky, so you need to know at least the basics of what happens under the hood of whatever tool you are using.
I try to learn the finer details of the tools I use, which is why I can quote the spec for C# and the docs of Win32, at the same time as knowing the ins and outs of SQL Server and silly tricks in VBA/VB6.
I just can't bear *not* knowing what happens when I press "Run".
what's your favorite quote from the c# spec
you can totally make something up here, i've never read it
17:54
this is a funny comment exchange
Which database system and version are you using? (Lists are not a concept in Microsoft SQL Server.) — J.D. 11 mins ago
SQL - I used Lists but in the context of Select from a where name in () and team in () — Mogsy Mo 4 mins ago
"what city in france did you grow up in?"
"europe"
18:33
@Charlieface I'm the same way, if I have enough time (for example, if it's a hobby/funsies versus what I work with each day)
@ErikDarling Hah I was trying to think of a good analogy to comment back with but was juggling too much at the time, and missed my opportunity.
sean is way into funsies
I've transitioned into writing mostly C# (but not every day) but I don't know it very well. I'd love to dig in and learn more (I did see what seems to be a nice C# via CLR book on the matter) but I use it infrequently and when I do use it, I'm constrained into specifics, so I haven't invested any time on it per se.
For a funsies, I use C/C++ but mostly C.
Although I've been really digging into ASM
The Intel volume on the subject literally puts me to sleep though, so I try to read it at bed time.
I watched some really amazing Pluralsight videos years ago about .NET internals - how the CLR works, the different components (EE, GC, JIT, BCL), remoting / PInvoke, etc. It really helped me a lot in terms of getting a handle on how things work under the hood in .NET languages.
That's good to know, I think we can get access to that
18:41
From a more practical standpoint, the book "Writing High Performance .NET Code" is one of the best tech books I've ever read, and covers some similar ground.
what's good beginner material on c#
I don't really know. I learned the basics in college. But "get a computer science degree" isn't really great universal advice.
yeah but you were also drinking handles of gas station whiskey in college, so maybe there's hope for me
🤣🤣🤣 True
I'm in-process of upgrading my 4 SQL Servers in my DAG from 2019 to 2022 using unattended upgrade
<- DBA, clearly
18:53
ballroom blitz
yah, good fun
 
1 hour later…
19:57
The Pluralsight course I was thinking of is this (retired) course: CLR Fundamentals by Mike Woodring
 
1 hour later…
21:19
interesting new column in RESTORE HEADERONLY for 2022: TimeZone. From the docs Learn site: The time zone of the server from which the backup was taken.. Values being returned in my newly upgraded instance: -20. I'm not sure if that's the temperature, but it sure isn't the timezone as far as I can tell.
 
2 hours later…
22:58
perhaps the offset from lunar time.

« first day (4474 days earlier)      last day (691 days later) »