Morning

Morning

@mustaccio I found it again.

Morning

@JackDouglas :-)

Thnx for the edit, @hot2use

@ypercubeᵀᴹ Minor glitch

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!! ;)

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

@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!!

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

@PeterVandivier I can use this credentials to connect with SSMS

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

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 ^^

@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

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?

connectionstrings.com/sql-server

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

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?

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)

@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

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

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.

Wow, good catch. Connection strings have bad gotchas.

That Oracle thing stumped us for a whole day.

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%

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.

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.

@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

I'm warning you, it's not pretty.

Oh, it's too big anyway

sounds like a fun one

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!

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™ :-)

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.

LOL

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.

@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 😀

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.