The Heap™ – Consultancy ©®

hot2use

6:15 AM

Bonghjornu (Corsican)

hot2use

6:27 AM

Quick one for The Heap™. When do I flag an answer (to close) and when should I just VtC?

Paul White

@hot2use Exactly the same thing as soon as you have the VtC privilege.

hot2use

@PaulWhite K. Thought so. Just wanted to verify.

Paul White

31

A: Increasing Flag Weight when you have Voting Privileges?

Once you reach 3000 reputation, if you attempt to flag a question as off-topic, your flag will get converted into a close vote automatically. A generic "flag for moderator attention" can be used instead, but as you said, that's poor etiquette (and depending on the moderator might result in your f...

Colin 't Hart

7:00 AM

Morning

Strange upvoting and downvoting continues.

Last week I saw lame questions voted up.

This morning, this question, while not difficult, includes the data, the query the asker tried along with its output, and the expected, different output.

0

Q: How to join two tables with different numbers of rows

I've already search the net about this but it can't solve my problem. I have two tables one with 10 rows and other with 9 rows table 1 RNUM JOB_ID ---------- ---------- 1 AC_ACCOUNT 2 AC_MGR 3 AD_ASST 4 AD_PRES 5 AD_VP 6 FI_A...

And it got downvoted...

Not only that, the asker provided an answer to his own question that works, and it has also been downvoted, twice.

hot2use

7:56 AM

It might have to do with the quality of the content.

Fast forward ten years Everybody will have forgotten the basics. Will the post provide a wealth of information for future generations?

@sp_BlitzErik Hard Times

Tom V

9:47 AM

Ooh, Aaron is speaking at SQL Saturday Holland

hot2use

@TomV :thumbsup:

Spambot alarm: dba.stackexchange.com/a/183411/15356

ConcernedOfTunbridgeWells

11:44 AM

@MaxVernon @JackDouglas @MarkSinkinson @Ypercube @Phil @MartinSmith - This is a gastropub near Tunbridge Wells that might be suitable for drinkies. You can get a taxi from the Tunbridge Wells station. blackhorsepembury.co.uk/index.php/restaurant/menutradpubgrub

@McNets - does Easyjet fly to Gatwick from anywhere near your home?

Tom V

12:01 PM

@ConcernedOfTunbridgeWells Not a bad idea

ConcernedOfTunbridgeWells

@TomV Where do you live?

Tom V

Belgium

Crazy, but it's 60€ brussels > madrid > gatwick and bloody expensive brussels > gatwick

ConcernedOfTunbridgeWells

@TomV Anywhere accessible from the Eurostar? - Tunbridge wells is taxi-able from both Folkstone and Dartford.

Tom V

@ConcernedOfTunbridgeWells Yeah I live a 20 minute drive from Lille

ConcernedOfTunbridgeWells

eurostar.com/uk-en/travel-info/stations/ebbsfleet-international

That's probably the closest station to Tunbridge Wells

Tom V

12:06 PM

Looks affordable

ConcernedOfTunbridgeWells

google.co.uk/maps/place/Ebbsfleet+International/…

Stay overnight in a B&B or some such. You could probably find somewhere on airbnb.com

Or you could take the train into St. Pancras and then get the northern line to London Bridge, where you can get trains to Tunbridge Wells.

Tom V

We'll see what date you pick, I might make it a weekend or something

I have 2 weeks vacation in september so it could work

unless you guys don't want me there, which would be understandable :)

ConcernedOfTunbridgeWells

Will try to make it a Friday - @MaxVernon - can you try to steer it so you can be in the UK on a Friday for this?

Philᵀᴹ

12:30 PM

@ConcernedOfTunbridgeWells When are you thinking?

220 mile drive. Might do that, rather than the train

If you decide on a Friday I'll book the day off work. Or maybe try and sort a client meeting for the morning :D

I'm free all Fridays in Sept

McNets

1:10 PM

@ConcernedOfTunbridgeWells from Barcelone

Max Vernon

1:21 PM

@ConcernedOfTunbridgeWells I arrive in the area on Friday September 22nd at around 10pm and leave the next Tuesday morning about 6am. Saturday likely won't work either since I'll have the family with me and they'll want my attention. Hopefully I can make it out there another time to meet you all.

so close, yet so far.

Max Vernon

1:38 PM

@PaulWhite I just recreated the test-bed on my 2012 instance. Same plan as detailed in my post. max_server_memory is 1024MB. maxdop is 0 (I know, I know). Parameterization is "simple". Note, I'm running this in tempdb. SQL Server version is 11.0.6248.0 - SP3GDR + Security Update

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    Table1ID int NOT NULL IDENTITY(1,1)
    , Table1Data nvarchar(30) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Table1ID);
GO

INSERT INTO dbo.Table1 (Table1Data)
SELECT TOP(1000000) LEFT(c.name, 30)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

SELECT *
FROM dbo.Table1
WHERE Table1ID = 500000;

This is the distribution of values in dbo.Table1 for me:

╔══════════════╦════════╗
║ Table1Data   ║ Count  ║
╠══════════════╬════════╣
║ account_name ║ 382204 ║
║ id           ║ 617796 ║
╚══════════════╩════════╝

ConcernedOfTunbridgeWells

@Philᵀᴹ Depends when @MaxVernon can come - he's still arranging it.

@MaxVernon What about a Sunday lunch?

You're right in the middle of the High Weald AONB - there are loads of nice locations for lunch.

Max Vernon

1:54 PM

I'll run it past my wife!

ConcernedOfTunbridgeWells

If you've got a car you could go for a drive around the countryside as well. You're in country lane territory in Kent, or you could go down to the South Coast.

Max Vernon

@ConcernedOfTunbridgeWells there are so many things to see in that part of the island, and so very little time to see them in.

but, yes, I have a rental car.

Joe Obbish

@MaxVernon I have access to 2012 now. Could run a test of something if that would help?

Max Vernon

@JoeObbish Paul was unable to get the same plan as I show in my example above. Perhaps you could run it, and see what you get for a plan?

Joe Obbish

2:12 PM

@MaxVernon there's no parameterization in my plan

index seek + RID lookup with no compute scalar

Max Vernon

so strange.

Joe Obbish

Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0

Paul White

@MaxVernon Ah I think the lower memory might do it. I'll check later thanks

I assume the compute scalar is bookmark to page for prefetch

Max Vernon

2:43 PM

[Expr1004] = Scalar Operator(BmkToPage([Bmk1000]))

increasing max_server_memory to 3072MB makes the compute scalar go away.

still seeing parameterization, though.

not that that's a bad thing

Kendall Frey

Hey folks, I enabled FILESTREAM for my SQL Server 2014 instance (using EXEC sp_configure filestream_access_level, 2) and when I restart the instance, it ignores the configuration and keeps the old value of 0. Why? (And how do I make it bend to my will?)

Max Vernon

did you run RECONFIGURE afterwards?

Kendall Frey

Yes

Though what I read seemed to imply it didn't matter if the instance was restarted.

Tom V

funky

Max Vernon

does EXEC sys.xp_readerrorlog 0,1 show anything related to filestream?

if you run this code below, what do you see for "value" and "value in use" columns:

EXEC sys.sp_configure N'filestream access level', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO
SELECT *
FROM sys.configurations c
WHERE c.name = 'filestream access level';

Joe Obbish

2:54 PM

cardinality estimates for startup expression predicates are weird

though I guess that's not unexpected

Kendall Frey

@MaxVernon Nothing that I noticed

@MaxVernon 2 and 0

Same as the Properties dialog shows

Max Vernon

weird

Kendall Frey

I think it worked the first time I tried it

then I disabled it again

Max Vernon

docs.microsoft.com/en-us/sql/database-engine/configure-windows/… says "Before this option has any effect, the Windows administration settings for FILESTREAM must be enabled. You can enable these settings when you install SQL Server or by using SQL Server Configuration Manager. "

Kendall Frey

Is the configuration manager not installed by default? I can't find it.

Tom V

3:01 PM

@KendallFrey It should be if you have the client tools

Max Vernon

it should be installed by default with every installation. On the start menu it'll be listed in a separate folder. If you open Start, and type in "sql server conf" you should see it

Kendall Frey

Nope

Reading this:

128

Q: SQL Server Configuration Manager not found

After installing SQL Server 2008, I cannot find the SQL Server Configuration Manager in Start / SQL Server 2008 / Configuration Tools menu. What should I do to install this tool?

sql-server sql-server-2008 sql-server-2012 sql-server-config-manager

I found it in MMC, but I can't find any mention of FILESTREAM

Tom V

services > sql server > filestream tab

I think, I don't use filestream myself

Kendall Frey

hmm, let's try that

Tom V

gtg

Kendall Frey

3:09 PM

That seemed to work, thanks

off to test it

ConcernedOfTunbridgeWells

3:31 PM

@MaxVernon There are plenty of things drivable from Tunbridge Wells. The South Coast, or Deal or Margate if you want coastal regions. The High Weald AONB has lots of nice locations. The North Downs (Surrey Hills) are quite nice.

You can take the train into London easily as well. I suggest you do the Borough Market, which is next to London Bridge Station (Trains from TW go into here), the London Eye.

There are also loads of Museums in London - the British Museum, The Natural History Museum, V&A, Tate Modern, Tate Britain, Science Museum.

Max Vernon

3:52 PM

cool, @ConcernedOfTunbridgeWells - thanks very much. Presumably we can also go to victoria station directly from TW as well?

the Natural History Museum is definitely on the list. My daughter is an environmental scientist so she's very interested to see many of the things they have.

Tom V

5:06 PM

You could do the Arcelor Mittal slide. I know I would

ConcernedOfTunbridgeWells

@MaxVernon There is a route that you can take via Tonbridge but you might be better off going into London Bridge and taking the Underground.

Max Vernon

great - thanks for the advice.

ConcernedOfTunbridgeWells

Tunbridge Wells - Cannon Street then take the Circle/District line to Victoria.

The British museum is Yuuuuuuge - you could easily take several days to go through the whole thing, at least in anything more than a cursory glance.

Max Vernon

@ConcernedOfTunbridgeWells that's what I thought. I'm pretty sure we're not going to see nearly as much as we want.

ConcernedOfTunbridgeWells

Mar 30 '16 at 19:04, by ConcernedOfTunbridgeWells

Selfie taken with the Elgin Marbles at the British Museum.

The Greek Government wants them back - can't say I really blame them.

@MaxVernon The London Eye is worth going on as well.

Max Vernon

5:15 PM

I remember talking about those statues a while back.

We're looking at going on the London Eye. looks pretty great.

ConcernedOfTunbridgeWells

If you're feeling really ambitious you could take a trip out to the Thermae at Bath.

That would be a day trip, though.

You could easily find stuff to amuse yourself for three days between Kent and London.

The Borough Market is worth a look in for lunch, and it's right next to London Bridge station.

Quite a large chunk of it is closed, though as they're doing major upgrade works on London Bridge Station.

@MaxVernon - are you a fan of Gin and Tonic?

ypercubeᵀᴹ

and yey, managed to connect to the Internet again!

ConcernedOfTunbridgeWells

O for awesome

2

Max Vernon

@ConcernedOfTunbridgeWells lol, who isn't?

ypercubeᵀᴹ

came very close to losing my strike of consecutive days at dba.se

Max Vernon

5:25 PM

@ConcernedOfTunbridgeWells I went to Bath as a boy when I lived in Wales. It's well worth the visit!

ypercubeᵀᴹ

@MaxVernon also the London museum

Max Vernon

We'll be in between london and brighton for this trip since we only have a few days in England.

ConcernedOfTunbridgeWells

@MaxVernon I work at 20 Gracechurch Street (otherwise known as 'The Dick'). London has many, many boutique gin distillers and there is a good tonic here called Fever Tree. The Leadenhall Market is just across the road and there are many, many pubs in the Square mile.

Max Vernon

@ConcernedOfTunbridgeWells sounds very cool!

ConcernedOfTunbridgeWells

If you wanted to pop into London for Lunch on Monday or Tuesday there are many pubs here with a variety of boutique gins.

London to Brighton is only an hour or so on the train.

Also, lots of good hipster coffee places.

Max Vernon

5:30 PM

will certainly keep that in mind! I just looked at streetview for that address and can see why they call it "the dick" lol.

ConcernedOfTunbridgeWells

Lots of London landmarks around here - the Lloyd's Building, walkie talkie, shard, cheese grater, gherkin etc.

ypercubeᵀᴹ

Creepy google. I search for Betchley park map and it shows how to go there from my current location!

At least it's not so good (yet). Missed my current location by 160 km.

29 hours by car (Greece -> Betchley Park, UK)

ConcernedOfTunbridgeWells

When are you back in the UK next?

ypercubeᵀᴹ

in two weeks

ConcernedOfTunbridgeWells

@Phil, @Ypercube, @JackDouglas, @MarkSinkinson, @MarkStoreySmith, @MartinSmith, @MaxVernon, @TomV, @McNets, @Deszo - how does lunch on Sunday 24 September somewhere in Kent sound?

2

Tom V

5:44 PM

@ConcernedOfTunbridgeWells I would need an extra day off on Monday ideally but I'll try

Depends on the train schedule

ConcernedOfTunbridgeWells

See what you can do.

Tom V

Read in a dutch twitter today:
Dear film studios

If you all start your own streaming service, and most of them aren't available where we live, we'll go back to torrents,

Thanks
The internet

ConcernedOfTunbridgeWells

@TomV The last one from London St. Pancras to Brussels goes at 20:04.

Tom V

I'm much closer to Lille than brussels, but we'll see what date you come up with and I'll see if I can arrange things

But thanks

ConcernedOfTunbridgeWells

I think the same one stops at Lille

That's for 24 September.

Tom V

5:57 PM

That would make a perfect week-end, get there on friday in the morning, spend a few days in London, get back on sunday

ConcernedOfTunbridgeWells

@TomV Sounds good. If you're around London on Friday we could go have lunch.

Tom V

Let's see what Max's wife says, he's the farthest away, I don't have much to do from september 13 to 23 so I can probably make it work

hot2use

6:16 PM

@MaxVernon I was born in Bath. Well Trowbridge. But my Birth Certificate states Bath as Place of Birth.

ConcernedOfTunbridgeWells

@hot2use Could you get a flight to Gatwick for Sunday 24 sep?

hot2use

That could work out. I'd probably arrive on Saturday though. Have to check my business Calendar tomorrow. Small get-together?

ConcernedOfTunbridgeWells

46 mins ago, by ConcernedOfTunbridgeWells

@Phil, @Ypercube, @JackDouglas, @MarkSinkinson, @MarkStoreySmith, @MartinSmith, @MaxVernon, @TomV, @McNets, @Deszo - how does lunch on Sunday 24 September somewhere in Kent sound?

hot2use

ok

Paul White

6:32 PM

@MaxVernon Maybe attempted parameterization. What does the seek predicate on the Index Seek say? Table1ID = @1 or Table1ID = 500000?

Max Vernon

Table1ID = Scalar Operator((500000))

Lamak

@TomV for a moment, I read that as "he's the fartest"....and was wondering what that meant

sorry @MaxVernon

Max Vernon

@Lamak I probably am the fartest. ;-)

@PaulWhite - is there something in the plan xml that would point to attempted parameterization?

<ParameterList>
      <ColumnReference Column="@1" ParameterCompiledValue="(500000)" ParameterRuntimeValue="(500000)" />
</ParameterList>

I see that at the bottom of the plan xml.

Paul White

@MaxVernon Ok that's attempted parameterization that failed.

@MaxVernon Not that I'm aware of, no.

Max Vernon

interesting. I've never heard of that before.

Paul White

6:41 PM

I imagine the difference in display is due to SSMS version.

Tom V

I can't judge, fortunately

Max Vernon

could be... its 17.1

vs the 2012 instance

Paul White

@MaxVernon I'm using 17.2

Max Vernon

ahhhh

Tom V

Let's see what his wife says

Max Vernon

6:42 PM

she'll agree I bet!

Paul White

@MaxVernon I doubt that is it though. Maybe. I feel like I'm not remembering something here.

Max Vernon

@PaulWhite I wonder why parameterization failed - seems like you'd have a hard time finding a simpler query that could be parameterized.

obviously I'm not understanding something about parameterization

Paul White

@MaxVernon Well simple parameterization is only chosen if there's one obviously best plan for all parameter values. i.e. if param is considered "safe".

And in particular if the plan is trivial (obvious plan, no cost-based optimization).

How Simple Parameterization works

Joe Obbish

@PaulWhite I'm using 17.1

and didn't see the attempted parameterization

Paul White

@JoeObbish Thanks yeah there's defo something else going on here. Which build of 2012 are you using?

Max Vernon

6:56 PM

the plan has StatementOptmLevel="FULL" which explains why simple parameterization would not have been attempted...

Paul White

@MaxVernon Yes.

Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3

Joe Obbish

Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 for me

Max Vernon

this is the plan (without the Compute Scalar) - it still shows the @1 parameter

Paul White

@MaxVernon Aha!

So that's what I was failing to remember!

You're looking at the post-execution ("actual") plan.

Max Vernon

correct

Paul White

7:01 PM

Look at an estimated plan.

Max Vernon

ah yes, no parameterization

d'oh.

Joe Obbish

oh

yeah I assumed we were looking at estimated plans too

Lamak

I'm gonna say that I was also assuming that

Max Vernon

my bad... I always prefer actual if available.

@PaulWhite and @JoeObbish - sorry for wasting your time!

Joe Obbish

no worries, I barely did anything at all

Paul White

7:11 PM

Blame SSMS. The behaviour and display is very confusing.

Lamak

@JoeObbish he was just being polite

Joe Obbish

@Lamak I WAS TOO

Paul White

@MaxVernon No time wasted, thanks for providing all the information we asked for :)

Max Vernon

hey, my pleasure!

Lamak

@JoeObbish you are. So, what are you gonna do with @sp_BlitzErik's rep?

Max Vernon

7:13 PM

@PaulWhite Do you remember what the "other" example was? You were going to add to that question but didn't?

Paul White

@MaxVernon BTW make that index UNIQUE and see what changes.

Max Vernon

ok

Paul White

(You should get a simply param'd trivial plan).

@MaxVernon Is that something I said in chat?

Max Vernon

I believe so

@PaulWhite - chat.stackexchange.com/transcript/message/39385939#39385939

@PaulWhite sure enough... actual parameterization

Paul White

@MaxVernon Oh thanks yeah I was going to add something like:

SELECT Table1ID
FROM dbo.Table1
WHERE Table1ID = 500000
AND Table1Data LIKE N'%';

Max Vernon

7:21 PM

that takes a while to run ;-)

although I suppose, yet again, that was just IO

ahhh - no - it's not I/O... it appears to be compile time. 9594ms spent on parse and compile.

crazy

SET STATISTICS TIME ON;
GO
SELECT Table1ID
FROM dbo.Table1
WHERE Table1ID = 500000
AND Table1Data LIKE N'%';
SET STATISTICS TIME OFF;
GO

SQL Server parse and compile time:
   CPU time = 9594 ms, elapsed time = 9875 ms.

(1 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 9 ms.

Forrest

Would auto-update of stats show up in the parse/compile time?

Max Vernon

and yet, if I re-run the query with OPTION (RECOMPILE) - that compile time is 1ms.

Joe Obbish

@Forrest yes

Max Vernon

ahhh good point @Forrest... I bet that is what's happening.

Joe Obbish

where's the pun?

:(

Paul White

7:32 PM

@MaxVernon Point being the query doesn't return any column not covered by the index.

Forrest

@JoeObbish oops

Max Vernon

@PaulWhite and yet still does the RID lookup.

Paul White

@MaxVernon Exactly, because it needs to evaluate the LIKE on Table1Data, which is not contained in the index.

Max Vernon

right. a subtle variation that really shows what's going on.

Andriy M

@Forrest You could've pretended as if there were a pun, as in: "Would auto-update of stats show up in the parse/compile time? (If you pardon the pun)"

Not original, of course, but funny

Paul White

7:41 PM

Probably couldn't see the pun opportunity for all the trees.

Forrest

I spent some time on another one of Max's query questions. I encountered PhyOp_TableScan and PhyOp_Range, which both show up as scan operators in the plan. Is the difference that one is expected to be sequential IO and the other is a page-at-a-time scan?

Paul White

@Forrest No.

Joe Obbish

@Forrest Encountered where? Some TF output?

Forrest

I've been working through the 8600 series of TFs. Final memo.

Joe Obbish

@PaulWhite Do you plan to answer? Or should I throw something together?

Forrest

7:50 PM

It's really fun, but also apparently hurting my ability to pun. Currently deciding if it's worth the trade-off.

Paul White

@JoeObbish Answer what?

Joe Obbish

@PaulWhite I presume that Forrest wants to know the actual difference between PhyOp_TableScan and PhyOp_Range

Forrest

I do, but I also take Paul's short answer as indicative that I should investigate more myself

Joe Obbish

ah, well, he does say this:

"As usual, these are undocumented, and unsupported (including by me!) "

how'd you manage to get a scan anyway?

I was hoping that these had logical names, but it looks like they don't

Forrest

@JoeObbish I was on the old CROSS JOIN question. I'll see if I can find the chat links.

Paul White

8:01 PM

@JoeObbish Sure go ahead, I'm busy elsewhere for the time being

Forrest

Feb 10 at 15:40, by Max Vernon

IF OBJECT_ID('tempdb..#Num1') IS NOT NULL
DROP TABLE #Num1;
CREATE TABLE #Num1
(
	num int NOT NULL
);

IF OBJECT_ID('tempdb..#Num2') IS NOT NULL
DROP TABLE #Num2;
CREATE TABLE #Num2
(
	num int NOT NULL
);

INSERT INTO #Num1 (num)
VALUES (4)
	, (5)
	, (6);

INSERT INTO #Num2 (num)
VALUES (7)
	, (8)
	, (9);

SELECT #Num1.*
	, #Num2.*
FROM #Num1
	CROSS JOIN #num2;

Max Vernon

I added a clustered index example to my answer.

Forrest

Feb 12 at 10:53, by Paul White

@Forrest Generally, it's a combination of the heuristics used to choose an initial join order, the order of exploration during cost-based optimization, and a simple comparison of the costs. In @MaxVernon's particular case, the initial join order is the 'expected' one, the first exploration rule looks at reversing the join order, and since the costs of T1 CJ T2 and T2 CJ T1 are identical, the chosen order depends on implementation.

Max Vernon

ahhh, yes. I loved that.

Forrest

Me too. It was a really cool insight (thanks again @PaulWhite!) so I wanted to see it for myself. Apparently the rabbit hole of plan investigation is a deep one.

Max Vernon

8:05 PM

apparently

Forrest

Anyways, @JoeObbish, the physical implementation PhyOp_Range seems to come from the rule GetToIdxScan, and PhyOp_TableScan from GetToScan. I'm still trying to figure out the difference though.

Joe Obbish

it's not just heap vs b-tree?

Lamak

@JoeObbish it's The Heap vs the world

Joe Obbish

no it's not

Forrest

@JoeObbish Both tables in my demo are heaps

Joe Obbish

8:16 PM

I can tell you that you can see different operators for the same ddl and query

Lamak

Aug 4 at 14:27, by Joe Obbish

so.... contrary...

Joe Obbish

if the data changes in a table

and you can see different operators for what appears to be the same operator when looking at the XML of the plan

other than that I got no clue

whenever I have to look at this stuff I usually ignore this level of detail, for better of worse

if I had to guess

Forrest

I think I need to look up the post detailing different scan types.

Joe Obbish

was to do with the different APIs that can be used to get rows from a table

I only see PhyOp_TableScan when the scan is on the inner side of a nested loop

Forrest

@JoeObbish Same. Although either rule can be disabled for a viable plan. (In the demo)

Paul White

8:22 PM

@JoeObbish DECLARE @T AS table (c1 int NULL); SELECT T.c1 FROM @T AS T;

Joe Obbish

I prefer not to use table variables

but really all I did was very brief testing to see if there was something obvious that you missed

now I have to deal with full text indexes

Paul White

@JoeObbish Fine. CREATE TABLE #T (c1 int NOT NULL); SELECT T.c1 FROM #T AS T;

Hint: check at which stage the plan is found.

Joe Obbish

I swear that didn't work

don't you not get 8615 output for trivial plans?

Forrest

@JoeObbish Doesn't look like it

Paul White

@JoeObbish No because cost-based estimation never started.

Memo is for CBO.

Use 8607 instead or my script for showing rules.

Joe Obbish

8:27 PM

right

looking at rules would be a smarter way anyway

Forrest

Hmm. Changing #T to clustered gets me a PhyOp_Range in the trivial plan.

Joe Obbish

full text indexes are the worst thing ever

Lamak

@JoeObbish are they?

worse than SQL Server 2012 columnstore?

Joe Obbish

@Lamak let me use my full text insert to answer that

@Lamak Turns out the answer is yes. had to rebuild my full text index first

Lamak

8:49 PM

@JoeObbish didn't think it would be worse

but I trust you

in all seriousness...is it that bad?. I've played with it sometimes and didn't notice

Joe Obbish

I'm sure it's not bad for some people

besides, you can upgrade sql server to get better columnstore

for full text you need a separate app?

Lamak

@JoeObbish may I ask why is it that bad?, I'm curious and I think it would be very interesting to understand this better

(for me)

Joe Obbish

@Lamak I'm just a grumpy end user as opposed to an expert

but they require special maintenance and the community knowledge for it isn't really out there

see dba.stackexchange.com/questions/107808/…

also anything that supports special querying syntax is annoying because it becomes so difficult to get rid of

we might have a fact table with a full text index on it for example

can't convert that to CCI

Lamak

@JoeObbish ah, well, yeah, that's a PITA

@JoeObbish this is very interesting, thanks

Joe Obbish

also did you see Erik's flowchart?

Aug 3 at 23:51, by sp_BlitzErik

Lamak

9:01 PM

@JoeObbish that doesn't really seem like a flowchart

Joe Obbish

what would you call it?

ypercubeᵀᴹ

a NoChart

6

Lamak

thanks @ypercubeᵀᴹ

@JoeObbish what's the best alternative?. (apart from "It Depends™")

elasticsearch is the way to go?

Joe Obbish

@Lamak the best alternative is for someone else to figure it out

Lamak

@JoeObbish is that your way of telling me to go annoy someone else?

if so, quite effective

;)

Joe Obbish

9:09 PM

no, it's nothing personal

I'm just not that involved or knowledgeable

Lamak

@JoeObbish ah, no, nothing personal at all. I was just very interested in understanding why you think fulltext index is so bad (since you know far more than me about SQL Server in general). I thought that there was some obvious thing that I didn't know about

since that happens a lot

Joe Obbish

I like to think that I know a decent amount about some parts of sql server

but there's just so many different parts

I do try to be clear when I don't know what I'm talking about, which is fairly often

Lamak

I'll wait and annoy @sp_BlitzErik when he comes back, then

Paul White

that definitely took care of it. A proc that took over 5 minutes down to 2 seconds. — Shellz 57 secs ago

That's a good result

@JoeObbish Are you qualified to make that assertion though?

Joe Obbish

9:24 PM

@PaulWhite yes

since all I do is "try"

try and succeed, try and fail

Good effort on that question. I passed on it because I didn't like the level of detail provided by the asker

Lamak

@PaulWhite nice

Joe Obbish

maybe it got cleaned up with comments and edits eventually

Lamak

but he also claimed that it went from 2:55 to 2 seconds

I don't know what to believe

Paul White

@JoeObbish No the only useful thing was the actual plan that got added to the question body.

Joe Obbish

yoda is dead

why should I listen to him?

Lamak

9:27 PM

he came back...in the form of @swasheck...

Paul White

@JoeObbish Yeah there was an element of luck: identify as many things as it could be in the hope that one would stick. I think the combination of new index, recompile, and row goal disabling was needed in the end. Hard to know.

Lamak

ah, now that I remember, he's not using that avatar anymore....so yeah, yoda is dead

Paul White

@Lamak 5 minutes-odd was the original (in the question)

I still have no idea what the cursor was all about, but there we are.

Joe Obbish

I got a syntax error complaint for my trouble

going to suggest that the guy apply himself to figure it out if he persists

Paul White

2

Q: Why is PostgreSQL 9.5 not using my newest index for ORDER BY, even though it uses similar indices just fine?

(Follow up from this post: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?) PostgreSQL 9.5. I can't divulge full details, but table has 22 columns and 5 indexes: primary key ('pk'), text (btree) another text (btree) a timestamp with time zone (btree) a ts...