« first day (4894 days earlier)      last day (21 days later) » 

12:21 AM
@PaulWhite Was thinking about this today for parallelization operations in plans. The timings aren't to be trusted eh? Can any meaning be derived from the time they show?
 
 
5 hours later…
5:13 AM
Wordle 1,075 4/6*

🟩⬛⬛⬛⬛
🟩⬛⬛⬛⬛
🟩🟨🟨⬛🟨
🟩🟩🟩🟩🟩
 
 
1 hour later…
6:13 AM
Wordle 1,075 4/6*

⬛⬛⬛🟩⬛
⬛⬛⬛🟩🟩
⬛⬛⬛🟩🟩
🟩🟩🟩🟩🟩
 
 
2 hours later…
8:34 AM
Morning
How do you prepare a SQL training plan for someone new to SQL? Asking for a friend
I'm just tempted to buy Erik's training and translate it to Czech
 
9:05 AM
1. Introduction into DBMS
2. Installation and instances
3. Transactions, Backups & Restores
4. Creating Tables, INSERT, SELECT & Joins
5. System Management Views (SELECT)
6. Execution Plans, Indexes & Statistics
7. Transaction Isolation Levels
8. SQL Server Collations
What I give my apprentices.
 
9:54 AM
Nice! Thank you
Do Collations come up often?
 
10:32 AM
A chairde - Morning all!
 
I give them the link to the Collation pages on MS Learn and then ask them to create two tables in a database. One named 'Collation' and the other named 'collation'. Then I ask them a bunch of questions:
1. What could you do to facilitate the creation of the tables in the task?
2. Where can collations pop up on a SQL Server instance (where can a collation be set).
3. Can you create a database named 'Collation' and another named 'collation'?
4. Why not? Workaround?
Some of the steps in training are a combination of reading some documentation and then completing an exercise.
For example JOINs. I use the following PDF and ask them to CREATE the tables in the excersie and then perform all JOINs on db<>fiddle.
I'll then give them a starter fiddle
Step 5 in training is a combination of showing them that SQL Server records everything that goes on in SQL Server (sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_query_plan()) and then ask them to create their own statement to retrieve a bunch of information:
· Session ID
· Connection ID
· Host/Client Name
· Database Name
· SQL Login Name
· NT Login Name
· Blocking Session
· Programm Name
· SQL Text current statement
· Query Execution Plan current statement
 
10:51 AM
@Zikato I would say, SQL first - on fiddles? Get them to look and learn from questions here and on SO - get them to find good questions and explain to you (briefly) why they're good and why the solutions offered were "good" or "bad"". Other good forums are Oracle and orafaq (lots of sites) - many others - search - it's up to them. The basics - CRUD. Then, all of the enhancements (CTE, regex, JSON... the list goes on).
Then, performance - indexes - when, why and how - EXPLAIN ANALYZE (or equivalent). Transactions - 1 statement -> many in 1 txn. Then go from logical to physical - what's happening on disk? Then you can make their lives a misery and introduce them to backups, RAID and all that other stuff! Get the basics right first!
 
The reason I sometimes ask "silly"/"simple" questions here on DBA is because during training something might crop up that the apprentice stumbled upon which I didn't think about when creating the lesson.
In step 3. I introduce them to backups (FULL, DIFF, TLOG) and explain what the RECOVERY MODEL is for.
etc. etc.
 
 
2 hours later…
12:56 PM
> Show 19 more comments
disgrazia! disgrazia!
 
1:08 PM
Shame 🔔
 
fixed
 
@PaulWhite this is another interesting one, for different reasons
SELECT
    v.c
FROM dbo.Posts AS p
CROSS APPLY
(
    SELECT
        c = COUNT_BIG(*)
    FROM dbo.Votes AS v
    WHERE v.PostId >= p.Id
    AND   v.PostId <= p.Id
) AS v
WHERE p.Id = 11227809
OPTION
(
    RECOMPILE,
    QUERYTRACEON 8649
);
the post-predicate threads show the per-thread breakdown, but the reads only report all threads on the inner side of the join
i've never seen that in parallel plan details before
 
1:32 PM
@ErikDarling I can't say I've noticed it b4 either. The XML shows the ActualRowsRead attribute only present on one thread.
Rather than being present and showing zero.
 
yeah, which is what i'd expect
will we ever know the truth
man, they have definitely nerfed parallel plan forcing
 
If you change it to WHERE p.Id IN (11227809, 11227810), you see two per-thread counts
 
SELECT
    c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.Id IN
(
    67850443,
    60039034,
    60039033,
    59983271,
    59973912,
    59973911,
    59965675,
    59965674
)
AND 1 = (SELECT 1)
OPTION
(
    USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'),
    QUERYTRACEON 8649
);
@PaulWhite hah yeah, randomly thread 1 and thread 6, then thread 7 and thread 8
 
We're used to seeing a complete set of threads for that counter.
I wonder if it's a deliberate change and they're planning to do it for the other ones as well to reduce noise?
In any case, it's unhelpful for SSMS to display it as "All Threads" when there's only one used. Loses information about which thread it was.
 
i guess you could infer it from the actual number of rows for all executions
 
1:44 PM
If you're a human, yes
 
did i finally come up with a real turing test
 
2:00 PM
would you believe that query runs for 5649ms and generates 5649ms of cxconsumer
 
2:25 PM
In 17 minutes I was only able to generate 36 GB of log on a Gen5_8 SQL DB :(
I don't want to even do the math on the cost of that for 1 hour
 
2:46 PM
throws hands in air
 
pretty cool
 
better with a row mode sort
@ErikDarling do you see similar with OPTION (QUERYTRACEON 9347)?
(it's your slow BM sort demo)
 
3:39 PM
Hate to have a slow BM.
I'm not a postgres user, but this was an interesting article from a top user on this site: Why do I have a slow COMMIT in PostgreSQL?
4
On an unrelated, web dev note: I really think using text-transform: uppercase is strange.
The HTML source for the title on that blog post is cased in a particular way, presumably by the person who wrote the article.
<span id="span-8-117606" class="ct-span">Why do I have a slow COMMIT in PostgreSQL?</span>
And then some goon throws CSS on the page that uppercases the whole thing.
Casing is also correct in the page title (shown in the tab header in most browsers).
<title>
    Why do I have a slow COMMIT in PostgreSQL? | CYBERTEC PostgreSQL | Services &amp; Support
</title>
 
Page theme, I assume?
 
Yeah, I would think it's a theme for the site. Blog post titles are always uppercased.
 
4:13 PM
@PaulWhite yep, sort finishes lickety split
> SLOW COMMIT CAUSED BY CURSORS WITH HOLD
@PaulWhite do you happen to remember the question you answered about a compile loop because of a join on non-matching date datatypes?
 
not immediately, no
live query stats can manage it (bm sort)
ActualElapsedms in RunTimeCountersPerThread is just nonsense
 
8
A: Unable to execute query and not even able to generate Estimated execution plan

Paul WhiteThis is a product defect as Martin Smith has already answered. Under certain conditions, the optimizer becomes stuck in a loop adding the same implied predicates to the outer join as it has derived from the earlier join. Eventually, so many duplicate predicates are added that the process runs out...

 
I remember it now, HtH
 
4:31 PM
thanks i feel assisted
 
In my defence, it doesn't necessarily require date type mismatches
 
i was a bit foggy on the details while trying to recall
 
After 7m 33s of trying to get an estimated plan:
Msg 701, Level 17, State 28
There is insufficient system memory in resource pool 'default' to run this query.
 
imagine being handed that as an average sql server user
you might die
 
Optimization Queue (internal)
---------------------------------
80.16 gb
 
4:40 PM
mama mia
i wonder how high that would go
 
Well, that's about 80% of my current buffer pool max
Message
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Always nice to see in the error log
I see that answer has just had its first birthday
 
i should email vassilis to remind him about it
 
I'm sure it's already pinned to his front door
Open a severity 2558F847-A119-41CF-9785-6146FC45A2D1 ticket for it
 
i often wonder what he does with most of his day
aside from get annoyed with people
 
5:27 PM
this one is fun
SELECT
    p.Id,
    v.c
FROM dbo.Posts AS p
CROSS APPLY
(
    SELECT
        c = COUNT_BIG(*)
    FROM dbo.Votes AS v
    WHERE v.PostId >= p.Id
    AND   v.PostId <= p.Id
) AS v
WHERE p.Id IN
(
    11227806, 11227810, 11227820, 11227824,
    11227826, 11227827, 11227831, 11227833,
    1368149, 17463071, 11227809
)
ORDER BY
    p.Id
OPTION
(
    RECOMPILE,
    QUERYTRACEON 8649
);
 
5:48 PM
@ErikDarling Which bit is fun?
 
 
1 hour later…
6:51 PM
Three threads end up with 2x the rows as the others
At the scan on Votes ofc
 
7:23 PM
/me bashes head on keyboard
 
I suggest bashing your head on the desk or wall -- saves you a keyboard
 
I have lost understanding of logins into SQL
I can login. But the account that has the same permissions can't
I keep getting this message "impersonate required with custom user id, password
 
7:37 PM
@ErikDarling Well, of course they do
 
see i knew you'd figure out
 
There are 11 rows from Posts, distributed unevenly
Each row results in a separate full scan of Votes
So, the threads that get 2 rows perform two full scans
 
why is this not amusing to you
heart of stone
 
I am distracted by the earlier query
 
the batch mode sort?
 
7:42 PM
Yeah, the one where the sort runs much longer than the GS
Your fun query runs beautifully smoothly at DOP 11
5 hours ago, by Paul White
user image
 
heh heh
quite the bugger
 
want to see one weird trick to speed it up?
 
i think i remember it being a little more frustrating with an index on votes too
@PaulWhite you could talk me into it
 
DECLARE @100 integer = 100;

SELECT TOP (@100) PERCENT
    u.Id,
    u.Reputation,
    u.DisplayName,
    p.AvgScore
FROM dbo.Users AS u
JOIN
(
    SELECT
        p.OwnerUserId,
        p.PostTypeId,
        AvgScore =
            AVG(p.Score)
    FROM dbo.Posts AS p
    WHERE p.PostTypeId IN (1, 2)
    AND   EXISTS
    (
        SELECT
            1/0
        FROM dbo.Votes AS v
        WHERE v.PostId  = p.Id
        AND   v.VoteTypeId IN (2, 3)
    )
    GROUP BY
        p.OwnerUserId,
        p.PostTypeId
 
woah woah woah
when did we start putting top on a new line
 
7:49 PM
typo
 
whew
that would have been a drastic code review
 
I feel you're focussing on the wrong aspect here
 
well i had to do something while i was waiting for the index to create
 
I waited 20 minutes for that index to create earlier
Then realised I had set a breakpoint in the create stats code
Not my finest moment of the day
 
i realize that your focus is on the batch mode sort timing
but check out the row mode timing here
two steps forward, dop steps back
 
7:56 PM
interesting
 
there's also this rather interesting segment
 
Does SQL not audit logons anywhere?
 
That's my row mode
@ErikDarling That's just the usual thread 0 shenanigans tho, rite?
 
Oh, you're running fully row mode aren't you
I was just using a row mode sort with TF 9347
 
8:01 PM
yeah 140 compat hint
 
yup, your sort has thread 0 time
ANYWAY
I think the explanation for the slow BM sort and shorter GS is that the elapsed times are accurate, but you have to know what they're measuring. The GS only measures time when it is active, or its subtree is.
BUT, you say, there is nothing above the GS, so it should measure the entire elapsed time of the execution.
 
i am accustomed to GS showing the full plan time for a batch mode/mixed operator plan, yes
 
Well, there are hidden things above the GS that put rows into buffers and send them to the client. The client (SSMS grid) is slow, so you get ASYNC_IO waits.
 
the pesky "select" operator
hiding work
 
When sending rows is blocked, the GS isn't running.
The sort is running, because it's in a different branch, on a different thread, which isn't blocked.
Ofc, it gets blocked soon enough when the exchange buffers fill up.
The GS can't clear the exchange quickly so the sort takes much longer to run overall.
 
8:07 PM
 
Far out. Where'd u c that
Never mind, I'm probably better off not knowing
So, anyway, the eager spool separates the slow sending of rows from generating the result set
 
somewhere that person clearly doesn't belong: SQL Server Parallel Query Placement Decision Logic
 
comments started strongly
exponential dive
 
@PaulWhite that is quite a trick
 
@ErikDarling Isn't it. I'm slightly embarrassed it puzzled me for so long
 
8:15 PM
> The sort is running, because it's in a different branch, on a different thread, which isn't blocked.
this is still twisting my melon man
batch mode exchanges being notoriously difficult to spot
 
Well, there's an exchange
 
oh you mean
i get it
 
The left (consumer side) of the GS runs on the parent task, which is responsible for sending rows to the client
The additional parallel workers are all attached to the right (producer side) of the GS
They don't care about any ASYNC_NET_IO waits the parent task might be experiencing
 
so if i just dumped it into a temp table...
 
Need an identity or friendly clustered index to preserve the sort
CREATE TABLE #Results
(
    Id integer NOT NULL,
    Reputation integer NOT NULL,
    DisplayName nvarchar(40) COLLATE DATABASE_DEFAULT NOT NULL,
    AvgScore integer NOT NULL,
    INDEX c CLUSTERED (AvgScore DESC)
);
That's what I used
 
8:27 PM
tres reasonable
 
Faster than a spool/top combo as usual, but arguably less cool
So, apologise to the nice single-threaded BM Sort output now
Unfair besmirching detected
 
did i smirch it
 
I believe so
24 hours ago, by Erik Darling
thanks, batch mode sort
Might make for a leg-end-ary demo
 
i look forward to your blog post about it
 
I wonder if the 64-bit SSMS grid will be any faster
 
8:34 PM
i had a 5 million row grid of a single integer column absolutely hobble ssms for about 20 minutes earlier
 
I wonder how old that component is
I bet it could get a driving licence if it wanted
 
i think at this point it could drink in america
 
You can't now?
 
there are certain age restrictions on it
some places are pricklier about it than others
 
fake ID is a thing, I hear
saw it in a movie once or twice
 
8:38 PM
scam as old as time
 
bribe the door staff
 
we all know the best way to drink underage is to be attractive
 
It is
 
v true
 
If your in college a good number have a fake ID
 
8:39 PM
best way 2 do nething underage really
 
I worked at a college for a decade and a half. I have seen enough ofthem
 
or have parents whose names show up as links on wikipedia
 
LOL
 
Adaptive joins are still stupid and broken
 
Which part
 
8:44 PM
Elapsed time
Always zero or a tiny number of ms
Copied from the wrong hidden operator
The concat, if memory serves
 
I vaguely recall that now
 
old as the hills, no one wants to fix it
57 mins ago, by Paul White
user image
DBCC TRACEON (9415) to the rescue
 
Forgive my ignorance but what are these flow charts your posting?
 
SQL Server graphical execution plans
 
Why are you making them?
That is a lot of planning to shave off a few seconds
NASA programmer?
 
8:52 PM
SQL Server produces them if you ask for it. We're discussing why the timings look like they do in particular circumstances
Nerd shit, basically
 
hahaah I get it I get it
Can I ask an access question?
 
On the main site? Sure
 
Access as in connecting to SQL. not Access teh Microsoft Database software
 
HAHAHAH
Can you Powershell check NT connections into SQL?
 
8:54 PM
Some may know, but it ain’t me
 
Me either
 
Trying to troubleshoot why I can connect to my database but another account can't even with the same permissions
 
Delete it and start over
 
LOL I can't
 
Why
It’s not working
 
9:05 PM
Only for 1 database
The others are
 
Oh just drop that database then
 
I have
 
@JukEboX That makes things interesting. You're referring to your latest question here?
 
@J.D. yes. I have dropped the database.
I have the account in the security and remade it
And it still says it can not connect
 
So to clarify, you're able to connect to your SQL Server with this other domain account, and it can access other databases on the server, it's only a single database it can't access at the moment?
 
9:14 PM
So it only has access to 2 databased. 1 of them is dropped so it should be making one
the other now it says it cannot connect to
So I go to connect to it with my DOMAIN\User account
With the same privileged and I can connect to it
And the SQL log doesn't show a failed login
 
Yea, it sounds like all good from your account, so let's put that aside for now and focus on just the domain account that isn't working.
 
RGR
 
Taking a step back, what happens in SSMS when you try to login with this other domain account?
 
I can't because SSMS is set for SQL and Windows logon. When I go to connect the username is greyed out
Because it is Windows Authentication
 
Do you have the password for this other domain account?
 
9:17 PM
Yes
 
You have to either 1. Login to the machine as that domain account or...
2. Right click the icon for SSMS and then shift + right click where it says "Microsoft SQL Server Management..." and then click Run as a different user.
 
1. I can't because it is a service account so it is blocked by group policy as not able to logon locally
2. Same reason
 
Probably an important detail you should mention in your original post.
 
Just found this out when trying that
Updating now.
 
👍
Very well could be part of the root cause of your issue.
Would strongly bet if you tried another regular user (non-service) domain account, it would work.
 
9:20 PM
Testing on esec
 
What would be allowed to make a connection with the service account
 
The service account is used to run specific software accross the network instead of making so many user accounts locally.
It can access network shares, databases, and other resources without logging on locally
 
Can you make a random .udl file and enter the details there
 
.udl file?
oh
Oh
um let me chekc
 
@JukEboX A single regular user (as opposed to service) domain account could do the same, FWIW.
 
9:24 PM
I just tested with another service account and it cannot
Let me test another
 
Again, I'd test with a regular non-service domain account (which you provisioned access in SQL Server instance / database for).
 
A general Domain user cannot
So the only accounts with access are these Service Accounts, My account group whichs is a DOMAIN\SQL Admins Group, and some built in NT SERVICE Accounts
 
Sure, but for the purpose of determining if it's an issue with your company's service accounts and how their provisioned, if you're able to, I'd recommend provisioning access in the SQL Server instance to a general domain user account and then test if it can access it.
 
oH I get cha
Standby
 
Seems to be the case since you said your account can, but would be worth testing with another general domain account directly, that isn't part of the SQL Admins Group.
TBH IDK much about network security but I do know service accounts are a little different and the fact you mentioned they're not provisioned to be able to logon locally is interesting to me (idk if that's by default, or specific to what your network team does).
Coupled with the fact, of the things we know, general domain accounts seem to be working (1 for 1) and service accounts aren't (0 for multiple).
 
9:32 PM
I have 2 others DOMAIN service accounts that are working but I am trying to figure out what the difference is
 
Interesting. Sounds like a difference in how they were created or provisioned then. Are you able to login locally as one of those 2 service accounts? (Maybe it's not a possible thing, idk.)
 
They are blocked for logon locally because they are service accounts.
 
@JukEboX Like that's a standard of how Windows service accounts work?...or that's a choice with how they were provisioned by your network team?
 
Provisioned by network security
I am doing some testing against that now
 
Word yea so that would be my first guess. It for sure seems like a difference in creation / provisioning. In any case, best of luck!
 
9:44 PM
@J.D. Thanks. I am working it.
 
10:22 PM
Wordle 1,075 5/6*

⬜⬜⬜🟩⬜
⬜⬜⬜🟩⬜
⬜🟩⬜🟩⬜
⬜🟩⬜🟩🟩
🟩🟩🟩🟩🟩
 
Nice @PaulWhite
@J.D. I think I have an idea
I think the issue is that these service accounts require the use of the ODBC Driver for SQL
The other's dont
 

« first day (4894 days earlier)      last day (21 days later) »