I did what you said about the temp table using a memory optimized table. So, basically I inserted the records of an intermiedate result. That improved the query but then, when I had to join the memory optimized table to another tables, I had issues. I got to a point when joining to any table makes it bad. I cannot use table variable or temp tables because this query will finally be called from Tableau and that product has its limitations. — Francisco Goldenstein13 mins ago
@NickCraver Background: there's a running joke in here that question askers (OPs) are always trying to trick us by providing misleading or incorrect information in their questions. The joke is carried further by assuming that anytime the substring "OP" is found it indicates some kind of subtle trick.
Oh and SSMS is absolutely terrible says sp_who*...we've found some super bad behaviors with all of the locking in there. For example, IntelliSense loops through every added database and queries all tables...regardless of if IntelliSense is disabled entirely...
Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:
DROP TABLE IF EXISTS #LOB_FOR_ME;
CREATE TABLE #LOB_FOR_ME (
ID BIGINT,
MAX_VERNON_WAS_HERE VARCHAR(MAX)
);
CREATE INDEX IX O...
index_id | 0 | NULL | -1 | DEFAULT Is the ID of the index. index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. The default is -1. NULL, -1, and DEFAULT are equivalent values in this context.
"Valid inputs are the ID number of an index"
did you create the NCI on the table? if so, what's the index id of it?
I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE checker from Denis Gobo and got a Buffer cache hit ratio of 100% with PLE of 103, how is that possible?
The b...
I want to create a table with a NOT NULL bool column.
I use TINYINT with CHECK constraint BETWEEN 0 and 1. The constraint is new and thus trusted
Now I would expect that SQL optimizer now knows that this column can only be 0 and 1 so when I write query col >= 2 I will see Constant Scan in actua...
Background
Data for the statistics object are gathered using a statement of the form:
SELECT
StatMan([SC0], [SC1], [SB0000])
FROM
(
SELECT TOP 100 PERCENT
[SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
FROM
(
SELECT
[TextVa...
One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the application.
So i converted those SCALAR functions to TVF functions,but still the query is running in se...
Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:
DROP TABLE IF EXISTS #LOB_FOR_ME;
CREATE TABLE #LOB_FOR_ME (
ID BIGINT,
MAX_VERNON_WAS_HERE VARCHAR(MAX)
);
CREATE INDEX IX O...
I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE checker from Denis Gobo and got a Buffer cache hit ratio of 100% with PLE of 103, how is that possible?
The b...
I want to create a table with a NOT NULL bool column.
I use TINYINT with CHECK constraint BETWEEN 0 and 1. The constraint is new and thus trusted
Now I would expect that SQL optimizer now knows that this column can only be 0 and 1 so when I write query col >= 2 I will see Constant Scan in actua...
Background
Data for the statistics object are gathered using a statement of the form:
SELECT
StatMan([SC0], [SC1], [SB0000])
FROM
(
SELECT TOP 100 PERCENT
[SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
FROM
(
SELECT
[TextVa...
