tag:blogger.com,1999:blog-3702702923592093288.comments2023-11-25T05:29:22.718+13:00Page Free SpacePaul Whitehttp://www.blogger.com/profile/04690243284528295117noreply@blogger.comBlogger78125tag:blogger.com,1999:blog-3702702923592093288.post-45891701018208013652023-11-25T05:25:58.470+13:002023-11-25T05:25:58.470+13:00Great deep dive on this, thanks.Great deep dive on this, thanks.Alex Stuarthttp://alexstuart.netnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-3223283567648928952023-11-23T11:52:49.777+13:002023-11-23T11:52:49.777+13:00Appreciate the effort that went into this article,...Appreciate the effort that went into this article, and how well it is written. I hope I never see the slowdown in the flesh, but at least I'll have a clue what it is if I do see it.<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-58697219969974674652023-06-02T21:33:52.143+12:002023-06-02T21:33:52.143+12:00Nice post thank you AnaNice post thank you AnaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-56354296972064486842023-06-02T10:15:18.021+12:002023-06-02T10:15:18.021+12:00Nice post thank you TaniaNice post thank you TaniaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-22602146193275574422023-06-02T01:59:37.344+12:002023-06-02T01:59:37.344+12:00Nice post thank you SethNice post thank you SethAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-13337561468362793492023-05-28T12:49:06.261+12:002023-05-28T12:49:06.261+12:00Nice post thank you AshleeNice post thank you AshleeAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-15896203697063689582023-03-24T04:01:18.018+13:002023-03-24T04:01:18.018+13:00Hello Anonymous,
SQL Server caches one data page ...Hello Anonymous,<br /><br />SQL Server caches one data page and one IAM page for each temporary object.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-40134902749416025712023-03-24T03:50:53.211+13:002023-03-24T03:50:53.211+13:00As the cached temp tables are metadata only, its s...As the cached temp tables are metadata only, its size should be very small. But I saw few cached tables size of 30-40 MB in my systems. How to check why its size is that much?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-52120391929277654302022-12-07T01:05:17.977+13:002022-12-07T01:05:17.977+13:00Hi Martin,
Oh dear 🤣 that's a little unfortu...Hi Martin,<br /><br />Oh dear 🤣 that's a little unfortunate isn't it.<br /><br />It does seem like <i>GetRangeThroughConvert</i> doesn't handle the empty string edge case correctly. There's no boundary value just beyond, so it returns an invalid NULL result. It's not limited to an equality comparison either - any comparator exposes the bug (plus LIKE without a wildcard).<br /><br />I was surprised this didn't throw an assert somewhere, since the non-NULL interval generators should never emit a range that includes NULL.<br /><br />It doesn't seem like there would be an easy general fix for this. If Microsoft take note of this issue, they might simply disable <i>SeekableCompareThroughConvert</i> for the general string case, perhaps limited to the times when a literal isn't available at compilation time.<br /><br />Cheers,<br /><br />PaulPaul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-36933054662491795282022-12-05T14:00:17.812+13:002022-12-05T14:00:17.812+13:00 A case where the application of the residual pred... A case where the application of the residual predicate here seems to go awry! https://stackoverflow.com/a/74682250/73226Martin Smithhttps://www.blogger.com/profile/02394178327340792196noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-5190557925220312282022-09-23T01:57:15.300+12:002022-09-23T01:57:15.300+12:00Dwainew,
Some features and fixes (preview or othe...Dwainew,<br /><br />Some features and fixes (preview or otherwise) appear in the box product first, some (most) appear on cloud versions first. There has been a preference to release cloud first in the past, but it's not an absolute rule.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-3578424969758169412022-09-22T02:59:42.945+12:002022-09-22T02:59:42.945+12:00'IS DISTINCT FROM' is now available in the...'IS DISTINCT FROM' is now available in the 2022 perview, but, curiously, missing from any Azure SQL species. This is highly unusual since it's my understanding that the on-prem kernel is now considered the "cloud-scale tested" product of Azure functionality. Does anyone have an idea of the specifics about this unusual circumstance?Dwainewnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-12111965345961400272022-09-18T08:07:08.997+12:002022-09-18T08:07:08.997+12:00This might even be a bug.
Documentation says &quo...This might even be a bug.<br /><br />Documentation says "An exception is made for large object types. If the output type of the folding process is a large object type (text,ntext, image, nvarchar(max), varchar(max), varbinary(max), or XML), then SQL Server does not fold the expression." <br /><br />https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver16#foldable-expressions<br /><br />Then again, it might not be folding as such that's causing the issue, maybe it's just the fact that it's in the parse tree altogether (hashing etc).Charlienoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-40780896079615347182022-09-07T21:01:54.436+12:002022-09-07T21:01:54.436+12:00Sorry but I missed the anonymous part and commente...Sorry but I missed the anonymous part and commented without my account.<br />Really good to check in a batch mode for individual operator times for those edge cases where they might struggle to get good execution times.Ismamadhttps://www.blogger.com/profile/01657668321369521041noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-7844546364016785472022-07-15T23:15:08.541+12:002022-07-15T23:15:08.541+12:00This Problem was fixed these days with SQL Server ...This Problem was fixed these days with SQL Server 2017 CU 30!<br />Regards, EyckAnonymoushttps://www.blogger.com/profile/11532376071680051670noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-60200720974022289152022-03-26T22:49:58.449+13:002022-03-26T22:49:58.449+13:00Yes the temporary table is truncated before cachin...Yes the temporary table is truncated before caching.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-30745774436864118992022-03-26T11:55:43.822+13:002022-03-26T11:55:43.822+13:00I presume the data is deleted from the temp table ...I presume the data is deleted from the temp table before the object is cached, right?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-29395823069077366932022-03-23T22:53:47.508+13:002022-03-23T22:53:47.508+13:00Still unfixed as of SQL Server 2019 CU 15 (build 1...Still unfixed as of SQL Server 2019 CU 15 (build 15.0.4198)Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-62238971156241028212022-02-24T11:20:48.257+13:002022-02-24T11:20:48.257+13:00I imagine so. I don't know for sure though bec...I imagine so. I don't know for sure though because I never use AGs.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-65131081989603781362022-02-24T11:11:06.482+13:002022-02-24T11:11:06.482+13:00Hello! Excellent article, as always!
The "Ro...Hello! Excellent article, as always!<br />The "Row Versioning" section makes me thing that non-updating updates to a primary replica in an Availability Group will always be fully logged for the sake of the existing row-versioning ID in the rows and the use of rcsi under-the-hood on Availability Group secondaries. Is that correct?sqL_handLenoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-49268340592677804722022-01-25T05:27:36.686+13:002022-01-25T05:27:36.686+13:00Fantastic article! I've been fighting a coupl...Fantastic article! I've been fighting a couple of tough query performance issues lately and the insight provided here will be of great help.Mark Edgarhttp://pithysql.comnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-32199840117813364372021-12-15T21:13:00.849+13:002021-12-15T21:13:00.849+13:00thanks for this!thanks for this!Yanghttps://www.blogger.com/profile/16547611827637692547noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-33118297109001996902021-12-12T20:45:55.949+13:002021-12-12T20:45:55.949+13:00Hi Erland,
It's not so much what the comparis...Hi Erland,<br /><br />It's not so much what the comparison is between (column, variable, parameter...), rather it is the accuracy of the showplan representation of the internal plan.<br /><br />The comparison is <b>always</b> correctly IS or EQ depending on the requested semantic.<br /><br />In your case, it is the way the seek predicate comparison is rendered. Remove the nonclustered index to see EQ or IS in showplan. Alternatively, add OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607) to the statement in the procedure to see ScaOp_Comp x_cmpIs or x_cmpEq in the optimizer output.<br /><br />Cheers,<br /><br />PaulPaul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-29867570380735580062021-12-12T10:06:47.514+13:002021-12-12T10:06:47.514+13:00It seems that the IS operator is only exhibited in...It seems that the IS operator is only exhibited in the plan if the comparison is between two columns, not between a column and a variable. I get seemingly identical plans for the three variations below. The result is nevertheless not the same for the first one as the latter two:<br /><br />DROP TABLE IF EXISTS DemoTable<br />CREATE TABLE DemoTable(a int NOT NULL,<br /> b int NOT NULL,<br /> c int NULL,<br /> d int NULL,<br /> CONSTRAINT pk_DemoTable PRIMARY KEY (a, b),<br /> INDEX c_d_ix NONCLUSTERED (c, d)<br />)<br />go<br />INSERT DemoTable (a, b, c, d)<br /> SELECT object_id, column_id, iif(max_length = -1, NULL, abs(checksum(*) % 200)), <br /> IIf(len(name) = 8, NULL, abs(checksum(*) % 100))<br /> FROM sys.columns<br />go<br />CREATE OR ALTER PROCEDURE GetDataByCD @c int, @d int AS<br />SELECT a, b, c, d<br />FROM DemoTable<br />WHERE c = @c<br /> AND d = @d<br />go<br />EXEC GetDataByCD NULL, NULL<br />go<br />CREATE OR ALTER PROCEDURE GetDataByCD @c int, @d int AS<br />SELECT a, b, c, d<br />FROM DemoTable<br />WHERE (c = @c OR c IS NULL AND @c IS NULL)<br /> AND (d = @d OR d IS NULL AND @d IS NULL)<br />go<br />EXEC GetDataByCD NULL, NULL<br />go<br />CREATE OR ALTER PROCEDURE GetDataByCD @c int, @d int AS<br />SELECT a, b, c, d<br />FROM DemoTable<br />WHERE EXISTS (SELECT c, d INTERSECT SELECT @c, @d)<br />go<br />EXEC GetDataByCD NULL, NULL<br />Erland Somarskognoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-3659962642683765542021-12-08T07:37:09.855+13:002021-12-08T07:37:09.855+13:00I take it this is still not fixed in the latest SQ...I take it this is still not fixed in the latest SQL 2019 CU?Anonymoushttps://www.blogger.com/profile/09015860690569734485noreply@blogger.com