« first day (3123 days earlier)      last day (61 days later) » 

5:54 AM
Morning
 
6:48 AM
Morning
 
7:24 AM
@mustaccio I found it again.
Morning
 
 
2 hours later…
9:24 AM
20
Q: What is "ass door"?

GrgorI'm not English speaking, I understand the joke but say please what is "ass door" Is it the same as butt door? Didn't find it in the dictionary... ck.imgur.com/pS3KW.jpg

 
10:10 AM
 
10:44 AM
@JackDouglas :-)
 
 
1 hour later…
12:00 PM
Thnx for the edit, @hot2use
 
12:17 PM
@ypercubeᵀᴹ Minor glitch
 
1:01 PM
i always forget and fail to find on googling >:/ ... anyone know offhand what the program is that you can give it a pg wal segment file and it spits out the plaintext log records?
 
Is there any drinking meeting in London next Aug 5 or 6?
 
well i'll be drinking in London then almost certainly
 
@PeterVandivier pg_xlogdump ?
 
@PeterVandivier me too, with or without you!! ;)
 
1:15 PM
whatcha doing in London?
or are you always here?
(are you behind me right now?)
 
holidays, short trip, 3 days
I missed some pubs last year
 
1:28 PM
@McNets I miss pubs every year
 
@hot2use Then come to London on August!!
;-)
 
Haven't been to England since Major was Prime Minister (well, when my grandma died)
Did that meet and greet happen in July?
 
@hot2use I think so, but maybe there is another in August.
These DB people is always drinking!!
 
Jun 24 at 13:10, by Forrest
What is a group of DBAs called? I was thinking cluster but they don't actually work together, do they?
Jun 24 at 13:15, by dezso
@Forrest AA?
 
1:45 PM
Question about C# - ConnectionString
Do you know any reason why I can open an SqlConnection only if I remove InitialCatalog parameter from ConnectionString? It's the first time I see this issue.
SQL user
 
can you repro the behaviour with a new credential? possible the credential doesn't have connect perms to the DB you were using for InitialCatalog
that or you need to use Initial Catalog (with the space)
¯\_(ツ)_/¯
 
I have a feeling we have some stars coming up ¯_(ツ)_/¯
 
you're all stars to me
 
2:04 PM
@PeterVandivier I can use this credentials to connect with SSMS
And I can add a new server on SQL Server object explorer
 
If you specify an Initial Catalog, you have to have access to it. If you expand before connection and put that in the Options...Connection Properties instead of <default> or master or what is there, what happens?
 
check the "Connect To Database" in Connection Properties or Additional Connection Properties for other connection string params you may be inheriting
 
@CadeRoux I'm able to see all dbs
By now I'm forced to use SELECT [db].[schema].[object] syntax
What's unclear about it? A function returns a table. One of the columns of that table is calculated from a parameter of the function together with the data from a table existing in the database. The other columns of the returned table are unchanged from the table in the database. I want to be able to run update statements on the returned table. — beerockxs 32 mins ago
 
see also sp_helpuser and sp_helplogins as your sysadmin credential to see if there's an obvious permissions conflict
 
Perhaps someone can understand this question better than me ^^
 
2:15 PM
@TomV or pg_waldump from PG 10
@hot2use that seemed to suggest at first that Margaret Thatcher was your grandma
 
@dezso i've been on your blog like white on rice for the past few weeks. so... thanks for making that a thing that exists
and.... i meant depesz -_-
homer-disappear.gif
that'll teach me to not re-read my chat messages 12 times before hitting send :/
 
@McNets Once you are connected, what happens when you do USE [db];
 
@CadeRoux I can run scripts with USE[db], it only fails on SqlConnection.Open
The ConnectionString is similar to:
Data Source=10.10.10.1\INSTANCE_NAME;User ID=db_user;Password=****;Application Name=X3_Database
 
2:31 PM
Even when your current DB is db, you have to use [db].[schema].[object] notation?
 
@CadeRoux No, but I can't set InitialCatalog
 
have you tried with the space?
 
I have seen some pretty weird stuff in connection strings before. Like in Oracle depending on the setup of tnsnames, we had to use different setting names on test and prod because one was set up differently in TNS. They aren't all as interchangeable as you think.
 
@PeterVandivier I use a SqlConnectionStringBuilder to build the ConnectionString
 
@PeterVandivier haha
 
2:36 PM
Also, I think recently I saw a case-sensitive thing on the parameter names.
Any difference in behavior if you use "database" instead of "Initial Catalog"
 
@CadeRoux my db instance is case sensitive (Latin1_General_BIN), it's a 3rd party software, I've added a new database where I'm adding some views and functions. I don't like to modify the production db.
 
i assume not but... login triggers?
 
Shit, so it should be pretty clean if you are using a ConnectionStringBuilder
 
@PeterVandivier No, as I said I can login with SSMS and VStudio - SQL server object explorer, even if I specify the DB
 
Nothing weird left over in your connectionstring builder from building a string for another connection?
 
2:49 PM
I've tried this: a) Add a new SQL Server on VStudio SQL Object explorer (selecting a default db), b) Editing connection properties and copy and paste the connection string.
But there isn't an InitialCatalog parameter
 
Initial Catalog is same as Database and all it does is override of user's default DB if it's provided in connection string. I had something weird with that recently, but I think it was just I was on the wrong instance and the database wasn't on that instance, because I have too many instances right now: ., .\SQL2012, .\SQL2014, .\SQL2016.
 
@CadeRoux Well, thanks for your time. (And Peter too). I need to go on, I'll use [db].[sch].[obj] by now
 
@dezso nearly
@McNets I hope your connection strings password doesn't contain either of these: _ % @ ?
Can break stuff.
 
@hot2use Ahhhh
there is an @ in the password
Let me change it
 
I'm not saying it is the issue, but it can be the issue.
(emphasis mine)
 
3:04 PM
@hot2use And it is
 
The following characters are equally prone to fail in some form or the other: ` " ' ~ ´ `
My password generator for database systems excludes the following characters:
 
@hot2use Now it's working fine, thank you. BTW, where have you found this info?
 
@ % ? / \ ? ´ ` ~ " * &
@McNets Trial and error
 
* too?
 
yes
 
3:07 PM
Ok, I'll change it, thanks. London beers are on me. ;)
 
e.g. the % will work everywhere, except when you use it in a batch file when calling a silent installation INI file for SQL Server.
 
Everyone can join us, btw
4
 
Example of a setup_SQL.bat calling a setup_SQL.INI file while using % in a password:
E:\setup.exe /ACTION=INSTALL /CONFIGURATIONFILE=%~dpn0.ini /IACCEPTSQLSERVERLICENSETERMS /SAPWD="MyPassword%WillFail"
...and things like that.
 
3:50 PM
Wow, good catch. Connection strings have bad gotchas.
That Oracle thing stumped us for a whole day.
 
 
1 hour later…
5:04 PM
Question about reading execution plans. My cost percentages add up to be over 100% (by quite a bit actually). From what I can gather, the general consensus online is "the displayed cost percentage is a garbage number anyway, just start with the highest percentage and go from there". I'm not sure if that means I should tackle the insert marked with 62% first or if I should go after the three eager spools, each marked with 30%
 
 
1 hour later…
6:10 PM
I would start by ignoring percentages and try to get an actual plan. If you're on 2014+, you can get actual time per operator.
 
6
Q: Execution plan percentages don't add up to 100%

IT researcherI am referring to SQL Server query execution plans to take query cost and then optimize the required things by looking at the plan. But the total of individual query costs adds up to more than 100%. This is my query: DECLARE @date SMALLDATETIME SELECT Reffd AS NAME ,( SELECT ( ...

Also do what Forrest said =P
If you look at the plan in SentryOne Plan Explorer, I think it does some tricks to make the costs add up correctly.
 
6:29 PM
@Forrest cries in 2012
 
:'(
I would still go for the actual plan
 
Is this something different from the "Include Actual Execution Plan" option in SSMS?
 
that's it
 
Well good news, I have that
 
if you want to pastetheplan it, we'll take a quick look
 
6:35 PM
I'm warning you, it's not pretty.
Oh, it's too big anyway
 
sounds like a fun one
 
> The bright side is that fixing these issues are fun! How queries are optimized and executed against different sets of data is super interesting to dive into.
- Brent Ozar
For extremely small values of "fun"
 
Fixing this stuff is what got me hooked on SQL
now I spend weekends trying to convince other people it's fun
 
Fixing this stuff is what got me hooked on single malt Scotch.
If I could get a screenshot to upload, I'd show you the specific part I'm looking at but We Are Currently Experiencing Technical Difficulties.
 
ooooh, a spill!
 
6:44 PM
Those two pathways off the bottom lead to another Index Insert <- Sort <- Eager Spool that appear to be identical to what's shown
The spill is a known issue, I'm trying to figure out why
This query absolutely pegs the ram on the server though
 
how many rows?
 
It's querying a large amount of data but not that large
Couple million
 
wide rows? developers added some max lengths in there?
 
Let me check. It's a couple (2-3) hundred columns I think
It's also joined against a very ugly view because of course it is
 
nothing worth doing is easy™ :-)
 
6:49 PM
There's a few nvarchar(max)s in there but only a few (two that I count but there could be more hidden away in a view or sproc that I haven't stumbled into yet)
 
@Forrest Do you think those spools are there for Halloween protection?
I'm wondering if manual HP could help with performance.
 
Hardcore Baader Meinhof right now. I literally only learned about the Halloween problem this morning (trying to figure out wtf a spool was)
The query takes somewhere between 20 minutes and 16 hours to complete, if that helps.
 
I'm not an expert on HP, and would prefer to ask Joe or Paul. Given that it's a self insert, and the spool doesn't seem to serve another purpose, I would guess yes.
 
7:25 PM
@MikeTheLiar This is mostly uninformed opinion based on your screenshot, but if you have a test environment where you can try things out, you may want to give the approached outlined in this Q&A a shot: dba.stackexchange.com/questions/230722/…
Also, it looks like that sort is because you have an index on the temp table you're inserting stuff into. Do you need that index at that point in the process? You could eliminate it and the sort by dropping the index.
Also, if the estimates vs actuals are good going into that sort that spills, sometimes TF 7470 can help: dba.stackexchange.com/questions/228089/…
 
@JoshDarnell thanks, I'll read over these links. I do have a test env where I can successfully reproduce the issue and can also basically do whatever I want, thankfully. We do need the index (I think) - IIRC, the temp table exists to to cut down what we need to query from the aforementioned nasty view. Basically, create the table, add the indexes that we're going to need to query against for this sproc specifically, and populate it from the giant, nasty view.
 
@MikeTheLiar It's hard for me to trust what you're telling me.
Because of your username.
 
Mike knows much, tells some. Mike knows many things others do not.
 
I am only giving you the time of day because you are a fellow North Carolinian.
😜
 
Now I'm not sure if I should tell you I only moved here two months ago 😀
 
7:38 PM
As long as you're not from that other Carolina.
Er, I mean, uh...welcome to NC!
 
Fun fact, the place I grew up has an Upper Carolina.
No Lower, to the best of my knowledge.
 
Mysterious.
 

« first day (3123 days earlier)      last day (61 days later) »