The Heap™ – Consultancy ©® - 2023-03-22

« first day (4460 days earlier) ← previous day next day → last day (410 days later) »

1:33 AM

@Charlieface Two blokes are looking in a shop window. One points and says "That's the one I'd get." Then a cyclops came out of the shop and slapped him.

4

A: what is the purpose and real time scenarios of xp_cmdshell in sql server?

It's so you can run rmdir \ /s /q without anyone knowing. By the way, DO NOT RUN THAT. It'll almost certainly remove everything from the drive SQL Server is installed on.

Cringe from 2014

1 hour later…

Peter Vandivier

2:54 AM

really a tragedy that you can't make the rm -rf joke equivalent

1

A: Can I get shell access through SQL Server 2017 on Linux?

No sadly it doesn't, when I try EXEC xp_cmdshell 'dir *.exe'; I get, Msg 15281, Level 16, State 1, Server x230, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security config...

3:46 AM

Now I have to see if the latest version on Linux supports xp_cmdshell

3 hours later…

6:24 AM

@PeterVandivier that's a really good question, i'd upvote it but the system won't let me.

1 hour later…

7:30 AM

Morning
Wordle 641 4/6

⬛⬛⬛⬛⬛
⬛⬛⬛⬛⬛
🟨⬛🟨⬛🟨
🟩🟩🟩🟩🟩

1 hour later…

8:39 AM

morning

8:52 AM

Wordle 641 4/6

⬛⬛⬛⬛🟩
⬛⬛🟨⬛🟩
⬛🟨⬛⬛🟩
🟩🟩🟩🟩🟩

9:21 AM

@MichaelGreen :-) Took me a minute. Works well in South African and Australian accents.

@MichaelGreen hahaha. Pity one cannot always type slang.

9:42 AM

@PeterVandivier for /f "skip=1 delims=" %%x in ('wmic logicaldisk get caption') do rmdir %%x: /s /q

Alternatively powershell.exe -Command { (Get-Volume).DriveLetter | Get-Item -Path {$_ + ":\"} | Remove-Item -Recurse -Force }

2 hours later…

11:28 AM

> However if it's an Inequality i.e. [Date] >= xxx then any key columns after the [Date] field will be ignored.

wat.

12:16 PM

Yea that answer's a little bugged.

12:39 PM

⬜⬜🟥⬜⬜
⬜🟫🟫🟫⬜
🟫🟫🟫🟫🟫
⬜🟨🟨🟨⬜
⬜🟨🟨🟨⬜

i got a cupcake

12:54 PM

@ErikDarling Do you mean this answer?

0

A: Why don't I get Index Seek for my non-clustered index

Seeks only happen on Key fields [Activity_Id], Include (covering) columns are used to minimise Key/Rid lookups. This index will give you a Seek (Range Seek): CREATE NONCLUSTERED INDEX [IX_ActivityAttributes_namevalue_new] ON [dbo].[ActivityAttributes] ( [name] ASC ,[value] ASC ) INCLUDE( [Ac...

yes

I think OP means to say that you can't have another seek key which comes after the range lookup in index order. Which is true.

Wordle 641 3/6

⬜⬜⬜⬜🟨
⬜🟨⬜🟨⬜
🟩🟩🟩🟩🟩

got lucky today

Ie if you have an index (Date, Name) then WHERE Date >= @Date AND Name = @Name will get a residual predicate, not a seek, on Name.

1:09 PM

Wordle 641 4/6*

⬛🟨⬛⬛⬛
⬛⬛🟨⬛🟩
🟩🟨⬛⬛🟩
🟩🟩🟩🟩🟩

1 hour later…

2:13 PM

@Charlieface right, but saying it will be ignored is not saying it will be a residual predicate, heh

2:58 PM

@PaulWhite NZ made top 10 happiest countries: visualcapitalist.com/worlds-happiest-countries-2023

Accurate?

Peter Vandivier

@HannahVernon [feature-request] xp_bash

user image

Peter Vandivier

3:32 PM

suddenly feeling happier

4:13 PM

I've just finished another SQL poster. I think this one is quite clever

only if it's not an original poster

4:30 PM

@ErikDarling I've touched up that answer now

@Charlieface i don’t care what paul says, you’re a good guy

4:55 PM

@Charlieface thoughts on the INCLUDE in the index definition? I didn't see that field actually referenced in OP's query despite their original index definition.

I guess it's probably an essential field, given the original index was on it. Needs more info on usage really, but it's definitely something to think about if you come up with an index with no includes.
Most good NCIs have includes, because commonly you are querying *other* columns *by* the index keys, rather than just querying the existence of a key.

Yea no doubt, that's fair.

Speaking of indexes, and playing in production, I found a case of a clustered index scan vs a nonclustered index taking ~1.5 seconds longer for literally the same execution plan otherwise (for the same query).

It's maybe rather trivial, and I'll probably throw open a question. But at a quick glance it just has me curious. It doesn't seem to be a difference in resource allocation or actual size difference of the indexes being scanned.

~1.680s for the clustered index scan and ~0.080 for the nonclustered index.

the NCI is the same width as the CX?

5:39 PM

That makes perfect sense to me

@J.D. Creation of the index you mean? Is the plan the same? Are the indexes over the same columns (clustered includes all other columns)? Are there any differences in the index definition (eg IGNORE_DUP_KEY)?

5:56 PM

@Charlieface not creation of the index, it's the scan operation on the index in an execution plan for a DQL query. The plans are exactly the same otherwise (as far as I can tell so far).

@Forrest The NCI is on different columns than the CI. And the NCI is a filtered index. So the sizes of the indexes will definitely be different but the amount of rows and total size of data (estimated and actuals for both) are all consistent between plans.

Also we're talking ~30k rows for the CI, and ~1k for the NCI, on some standard sized columns. So general total physical size of each index should be pretty small.

Disk type is an NVMe but I doubt would've mattered, because these small indexes should've still been in Memory on subsequent runs of the DQL query, but the index scan times were repeatable (~1.680s for CI, ~0.080s for NCI).

6:24 PM

A clustered index scan will read the entire contents of each on-page column for every row returned by the query regardless of whether or not the columns are present in the query, since the clustered index is the table. Of course, without seeing the actual query and plan it's hard to know for sure what you're seeing.

@HannahVernon That's a fair point. But if the difference was due to the amount of data being scanned, I'd expect the query plans to show a significant enough difference in the Actual Size attribute between the scan operations of both plans. The difference is a few hundred KB which has me doubting would take over 1s to scan on an NVMe or from Memory.

Same for the overall Requested and Used Memory of both plans. The difference seems trivial relative to the runtime difference.

I'm sure it's either something obvious I'm being stupid about or something really low level that is irrelevant anyway. But just found it interesting.

The query plan shows size of data as [size per row] * [number of rows]. The size per row depends on the number and type of columns you select. SQL Server still has to scan the much larger clustered index width in order to find just the few columns you want returned. That's the point you're missing, I think.

Ah ok that could make sense. So Actual Size is regarding only the columns that were selected?

Yes

I'm just expressing what Hannah said in other words

Fair enough. I have to double check when I'm back in front of a computer. But it's possible there's a lot more columns in the table / CI than what's in the NCI (which I recall being rather lean). Not certain off my own Memory.

Yea for sure, that little nugget of information is something I wasn't aware of.

6:37 PM

if the non-clustered index isn't smaller than the clustered index, by a lot, I would suggest there is something wrong with the design of the table.

@HannahVernon hmm interesting. What do you mean by that?

I design all my tables so the nonclustered indexes are larger than the clustered index

@J.D. well, if the non clustered index has all, or almost all, the columns in the table, then it's perhaps pointless to maintain the non-clustered index.

@PaulWhite you would

@HannahVernon oh I thought you meant relative to this odd index scan runtime difference. Nvm, I agree in regards to index design, yea haha.

it's also not entirely pointless that sometimes you might have a non-clustered index that is as wide as the table itself, but has a filter clause eliminating some of the rows, or possibly defines a different order for the rows that is critical for query performance of some aspect of the system.

as we are so fond of saying in here, local factors can be at play.

6:41 PM

Yeah, different sort order would be the main use

@HannahVernon of course, and as always "it depends ™" on the queries lol.

@PaulWhite yah, having the non-clustered index in the same order as the clustered index would defo be pointless

mostly.

7:11 PM

@Charlieface About the 'correction' from 'range' to 'equality' on that answer, sql.kiwi/2011/02/so-is-it-a-seek-or-a-scan.html#range-scans

> The other type of seek predicate is a ‘seek plus range scan’, which I will refer to simply as a range scan.

The index isn't unique, so accessing it using equality conditions on all the index keys will increment the range_scan_count in the index operational stats DMV.

7:32 PM

Wordle 642 4/6*

⬛⬛⬛🟨⬛
⬛⬛🟩🟩⬛
🟩🟩🟩🟩⬛
🟩🟩🟩🟩🟩

Robbed of a three

So confirmed. The CI is 2,900 KB big and the NCI is 56 KB big at the moment. So I could see that being a significant enough difference.

Also the plan has a Nested Loops operation after the index scan that iterates 300,000 times. Not sure if some of that factors into the runtime that the index scan operation shows as well.

8:05 PM

Heads up for future reference, SQL Server is not the same as Azure SQL Managed Instance (I fixed your title and tags accordingly). Also, please don't use screenshots to show the execution plan, as it doesn't show all the information that the plan contains. It's better to upload the plan to Paste The Plan and add the link to it in your Post instead. — J.D. 8 hours ago

Great advice

8:28 PM

Serious or sarcastic? 👀

Well, posting a link to a full plan is so much better than trying to describe one in words, wouldn't you say

Advice that might also apply to questions asked in chat

Something to ponder

I'll take that as a compliment. 😉 NZ really must be top 10 happiest country.

J.D. gets an original poster compliment

8:52 PM

@J.D. I wouldn't want to speak for paul, but I think he's suggesting an execution plan would be wonderful to see

Yea I followed that time lol.

If you are really still interested in looking at it, I'll post a formal question when I get a chance tonight. But my curiosity is sufficed.

Explaining things at great length. Good to see

So like JEAGL but without the funny...

That seems like a good explanation, yes

@HannahVernon Commonly I would have a many:many join table with a clustering in one order and NCI in the other, with all (or nearly all) other columns included.

9:02 PM

@Charlieface yah baaaabyyyyy

9:14 PM

i feel like this should get migrated but also like it will get closed no matter where it ends up

0

Q: How can I get MS SQL Server field data to display on a Sitefinity page?

First, I don't have enough reputation points to to create a "sitefinity" tag. So, I hope this somehow finds the right people. We're working on a project that will display performance data, to the public, in Sitefinity. One option is to manually update the data on the webpage. However, this data a...

StackOverflow is usually a pretty good dumping ground for that stuff I feel like.

I feel like it would be instaclosed on SO

much like it is here, now

9:42 PM

@PaulWhite To dig up what I asked a few days ago re full-join ordering: is there any way to see if the compiler holds some kind of ordering guarantee for the results, which it would be possible to utilize in the ORDER BY? I feel sure there is a TF or two which will show these sorts of internals.

10:07 PM

@Charlieface Not that I can see, no

@HannahVernon Good. We should not migrate crap

@PaulWhite I concur

no crap in, no crap out

Right. We'd be upset if another site saw us as a dumping ground

10:52 PM

user image

@PaulWhite I’m pretty sure SO does

11:12 PM

LOL

Leading commas are a sign of immortality

Or is that immorality

2

11:31 PM

@ErikDarling and we're upset about it

@PaulWhite we’re not gonna take it etc.

11:56 PM

@ErikDarling hah love it. Glad I'm not the only one who's not a fan.

« first day (4460 days earlier) ← previous day next day → last day (410 days later) »

Transcript for

Mar '2322

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile