October 9, 2014 at 6:50 pm
Hello,
There is a small group of devs at my work who will occasionally come to me with some new top SQL secret that makes everything faster than it was.
Today it was replacing a traditional WHERE col IS NULL with WHERE ISNULL(col, 0) = 0
So I sat down with them and tested it on a pretty simple TOP 1000 select. Showed them that without the ISNULL there will be a missing index hint, and with that index the query takes up 33% of the batch compared with 66% of the batch.
That didn't do the trick. They insisted something was wrong and that it was always faster. I told them to come back when they found it faster their way. They've been quiet ever since, which makes me think they're just disregarding our conversation and updating all the code with what they think is faster.
I'm mostly sure that I'm right, Grant covered that well enough here recently[/url].
But now I have two questions:
1. Have you ever seen the code example run faster the other way?
2. If no, what are some 'misunderstandings' developers have come to you with?
Thanks
October 10, 2014 at 12:47 am
I just changed a query where there was an ISNULL function to the "column IS NULL" function so that an index could be used.
So no, I haven't encountered a case where the second option was faster 🙂
Regarding the group of Devs: are you in charge of deployments? If yes, you could just review the code and sent it back due to "performance issues". 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 6:25 am
With the huge variety of things going on within SQL Server, I've no doubt there are places or instances where you'll see faster performance. But they're going to be a vanishingly small exception to the rule. That function will lead to scans. Scans, in places where seeks are better, are bad.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 10, 2014 at 6:42 am
sqldriver (10/9/2014)
Today it was replacing a traditionalWHERE col IS NULLwithWHERE ISNULL(col, 0) = 0
It's extremely important to realise that those two queries are not equivalent.
The first one returns rows where the column Col is NULL. The second returns rows where the column Col is either NULL or equal to 0. The second (in addition to potentially being less efficient due to the function preventing index seeks) could return more rows than the first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2014 at 7:43 am
GilaMonster (10/10/2014)
sqldriver (10/9/2014)
Today it was replacing a traditionalWHERE col IS NULLwithWHERE ISNULL(col, 0) = 0It's extremely important to realise that those two queries are not equivalent.
The first one returns rows where the column Col is NULL. The second returns rows where the column Col is either NULL or equal to 0. The second (in addition to potentially being less efficient due to the function preventing index seeks) could return more rows than the first.
Thanks, Gail. I should have mentioned that when I posted. They were looking to replace some other logic for handling NULLS and zeroes, which in this case indicate the same thing essentially: no logged activity.
And yes, they refuse to implement a default constraint of 0 to make their lives easier. Because less code is... bad? Something?
October 10, 2014 at 7:47 am
sqldriver (10/10/2014)
And yes, they refuse to implement a default constraint of 0 to make their lives easier. Because less code is... bad? Something?
Constraints are evil! Because of uh... EVIL!
😎
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 7:58 am
Koen Verbeeck (10/10/2014)
sqldriver (10/10/2014)
And yes, they refuse to implement a default constraint of 0 to make their lives easier. Because less code is... bad? Something?
Constraints are evil! Because of uh... EVIL!
😎
How can you look like a genius for solving a problem if it doesn't become a problem?
October 10, 2014 at 8:07 am
sqldriver (10/10/2014)
Koen Verbeeck (10/10/2014)
sqldriver (10/10/2014)
And yes, they refuse to implement a default constraint of 0 to make their lives easier. Because less code is... bad? Something?
Constraints are evil! Because of uh... EVIL!
😎
How can you look like a genius for solving a problem if it doesn't become a problem?
It doesn't have to be a problem. All you need is for the people for whom you want to look like a genius to believe it was a problem. 😎
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 12, 2014 at 5:12 am
Probably one of the most important factors here is the index maintenance, as the "health" of the index can greatly affect the performance, even in some not so exceptional cases. To underline this, I put together a quick test set, feel free to play around with it and of course correct me if you find any discrepancies. To equalize the number of rows returned, none has zero value.
😎
Test cases summary:
1. An index is created on a empty table which is then populated with @SAMPLE_SIZE number of rows. The server's default index fill factor is set to 0 (eq. 100)
2. Using the same set, the index is rebuilt with fill factor 100.
3. A filter, "WHERE TS_VALUE IS NULL" is added and the index is rebuilt.
4. The index is rebuilt with the filter condition of "WHERE TS_VALUE IS NOT NULL"
5. The index is rebuilt without a filter and a fill factor of 50.
6. The index is first rebuilt with a fill factor of 80 and then additional @SAMPLE_SIZE / 3 rows are added.
USE tempdb;
GO
SET NOCOUNT ON;
IF EXISTS (SELECT OBJECT_ID(N'dbo.TBL_TEST_SET')) DROP TABLE dbo.TBL_TEST_SET;
CREATE TABLE dbo.TBL_TEST_SET
(
TS_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_SET_TS_ID PRIMARY KEY CLUSTERED
,TS_VALUE INT NULL
);
CREATE INDEX NCLIDX_DBO_TBL_TEST_SET_TS_VALUE ON dbo.TBL_TEST_SET
(TS_VALUE ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100);
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @INT_BUCKET INT = 0;
DECLARE @TIMING_RESULTS TABLE
(
TR_ID INT IDENTITY(1,1) NOT NULL
,TR_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())
,TR_TX VARCHAR(100) NOT NULL
);
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_TEST_SET(TS_VALUE)
SELECT
NULLIF(ABS(CHECKSUM(NEWID())) % 13,0)
FROM NUMS NM;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #1: Default index settings');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #1: Default index settings');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#1 WHERE TS.TS_VALUE IS NULL: Default index settings');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#1 WHERE TS.TS_VALUE IS NULL: Default index settings');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings');
ALTER INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #2: Index rebuilt');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #2: Index rebuilt');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt');
CREATE NONCLUSTERED INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
(
[TS_VALUE] ASC
)
WHERE TS_VALUE IS NULL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100);
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #3: Index filter IS NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #3: Index filter IS NULL');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL');
CREATE NONCLUSTERED INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
(
[TS_VALUE] ASC
)
WHERE TS_VALUE IS NOT NULL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100);
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #4: Index filter IS NOT NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #4: Index filter IS NOT NULL');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL');
CREATE NONCLUSTERED INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
(
[TS_VALUE] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50);
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #5: FILLFACTOR = 50');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #5: FILLFACTOR = 50');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50');
CREATE NONCLUSTERED INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
(
[TS_VALUE] ASC
)
WHERE TS_VALUE IS NOT NULL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80);
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE / 3) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_TEST_SET(TS_VALUE)
SELECT
NULLIF(ABS(CHECKSUM(NEWID())) % 13,0)
FROM NUMS NM;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #6: 1/3 addition, FILLFACTOR = 80');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #6: 1/3 addition, FILLFACTOR = 80');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80');
SELECT
TR.TR_TX AS OPERATION
,DATEDIFF(MICROSECOND, MIN(TR.TR_TS), MAX(TR.TR_TS)) AS DURATION
FROM @TIMING_RESULTS TR
GROUP BY TR.TR_TX
ORDER BY 2;
Results (1000000)
OPERATION DURATION
----------------------------------------------------------------------- -----------
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 9000
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 9001
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 10001
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 49003
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 82004
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 83004
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 83005
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 83005
BASELINE #4: Index filter IS NOT NULL 115007
BASELINE #1: Default index settings 116007
BASELINE #3: Index filter IS NULL 116007
BASELINE #5: FILLFACTOR = 50 118007
BASELINE #2: Index rebuilt 119006
BASELINE #6: 1/3 addition, FILLFACTOR = 80 153008
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 726041
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 790046
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 822047
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 872050
October 12, 2014 at 9:19 am
sqldriver (10/9/2014)
They insisted something was wrong and that it was always faster. I told them to come back when they found it faster their way.
That's kind of a big mistake on your part. Although you've demonstrated both sides, it doesn't sound like you've done it using THEIR data or THEIR code on a strictly in-situ. Work with them to capture their query performance in a profiler run. Rewrite THEIR query for them and then have them run both in-situ. Make sure that you capture CPU, Reads, Writes, Duration, and Rowcounts so that they can see for themselves because NOW it's being worked on THEIR turf.
Yeah... I know... you shouldn't have to go through all of that... and, in the future, you probably won't (most of the time. There will be exceptions). You have to prove to them a couple of times that you actually do know what you're talking about. And I know you know this already but have to say it out loud just to feel better... Remember that they came to you and are now in disbelief (they insisted something was wrong). That's going to require the extra work this time and you need to be as nice/helpful as you can so the keep coming back instead of doing something silly that came for some self-proclaimed expert with a bad test on the internet.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2014 at 12:15 pm
Jeff Moden (10/12/2014)
sqldriver (10/9/2014)
They insisted something was wrong and that it was always faster. I told them to come back when they found it faster their way.That's kind of a big mistake on your part. Although you've demonstrated both sides, it doesn't sound like you've done it using THEIR data or THEIR code on a strictly in-situ. Work with them to capture their query performance in a profiler run. Rewrite THEIR query for them and then have them run both in-situ. Make sure that you capture CPU, Reads, Writes, Duration, and Rowcounts so that they can see for themselves because NOW it's being worked on THEIR turf.
Yeah... I know... you shouldn't have to go through all of that... and, in the future, you probably won't (most of the time. There will be exceptions). You have to prove to them a couple of times that you actually do know what you're talking about. And I know you know this already but have to say it out loud just to feel better... Remember that they came to you and are now in disbelief (they insisted something was wrong). That's going to require the extra work this time and you need to be as nice/helpful as you can so the keep coming back instead of doing something silly that came for some self-proclaimed expert with a bad test on the internet.
Since I will probably never again in my medium legged life get to say this: au contraire, Herr Moden!
I skipped over the boring parts. The restoring 3.5tb of prod data to dev for them to work with. The way way way oversubscribing a VM to bring it closer to production specs, the initial feedback I gave them on their code which replaced 3 subquery columns with an outer apply, a nifty POC index for some averages they were calculating, and a half-stolen apply/values construct from Luis to help them pull out a text identifier.
They came back to me an hour later with "this will always be faster", "this" referring to the ISNULL nonsense. I am always nice and helpful, and pretty good at restraining the twitches I regularly feel coming on (the same twitches I imagine you guys feel when I ask questions). If anything, the situation was opposite. They were on my lawn! I'm the only DBA, and, according to my boss, I'm not a developer (unless he needs some custom code to keep from having to pay a vendor to write it, of course.). I don't get to spend that much time with the devs. I do my monitoring and pass along a few recs to the senior developer, but it's not as much of my focus as I'd like.
October 12, 2014 at 12:45 pm
Eirikur Eiriksson (10/12/2014)
Probably one of the most important factors here is the index maintenance, as the "health" of the index can greatly affect the performance, even in some not so exceptional cases. To underline this, I put together a quick test set, feel free to play around with it and of course correct me if you find any discrepancies. To equalize the number of rows returned, none has zero value.😎
Test cases summary:
1. An index is created on a empty table which is then populated with @SAMPLE_SIZE number of rows. The server's default index fill factor is set to 0 (eq. 100)
2. Using the same set, the index is rebuilt with fill factor 100.
3. A filter, "WHERE TS_VALUE IS NULL" is added and the index is rebuilt.
4. The index is rebuilt with the filter condition of "WHERE TS_VALUE IS NOT NULL"
5. The index is rebuilt without a filter and a fill factor of 50.
6. The index is first rebuilt with a fill factor of 80 and then additional @SAMPLE_SIZE / 3 rows are added.
USE tempdb;GO
SET NOCOUNT ON;
IF EXISTS (SELECT OBJECT_ID(N'dbo.TBL_TEST_SET')) DROP TABLE dbo.TBL_TEST_SET;
CREATE TABLE dbo.TBL_TEST_SET
(
TS_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_SET_TS_ID PRIMARY KEY CLUSTERED
,TS_VALUE INT NULL
);
CREATE INDEX NCLIDX_DBO_TBL_TEST_SET_TS_VALUE ON dbo.TBL_TEST_SET
(TS_VALUE ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100);
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @INT_BUCKET INT = 0;
DECLARE @TIMING_RESULTS TABLE
(
TR_ID INT IDENTITY(1,1) NOT NULL
,TR_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())
,TR_TX VARCHAR(100) NOT NULL
);
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_TEST_SET(TS_VALUE)
SELECT
NULLIF(ABS(CHECKSUM(NEWID())) % 13,0)
FROM NUMS NM;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #1: Default index settings');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #1: Default index settings');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#1 WHERE TS.TS_VALUE IS NULL: Default index settings');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#1 WHERE TS.TS_VALUE IS NULL: Default index settings');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings');
ALTER INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #2: Index rebuilt');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #2: Index rebuilt');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt');
CREATE NONCLUSTERED INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
(
[TS_VALUE] ASC
)
WHERE TS_VALUE IS NULL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100);
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #3: Index filter IS NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #3: Index filter IS NULL');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL');
CREATE NONCLUSTERED INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
(
[TS_VALUE] ASC
)
WHERE TS_VALUE IS NOT NULL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100);
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #4: Index filter IS NOT NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #4: Index filter IS NOT NULL');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL');
CREATE NONCLUSTERED INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
(
[TS_VALUE] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50);
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #5: FILLFACTOR = 50');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #5: FILLFACTOR = 50');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50');
CREATE NONCLUSTERED INDEX [NCLIDX_DBO_TBL_TEST_SET_TS_VALUE] ON [dbo].[TBL_TEST_SET]
(
[TS_VALUE] ASC
)
WHERE TS_VALUE IS NOT NULL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80);
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE / 3) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_TEST_SET(TS_VALUE)
SELECT
NULLIF(ABS(CHECKSUM(NEWID())) % 13,0)
FROM NUMS NM;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #6: 1/3 addition, FILLFACTOR = 80');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('BASELINE #6: 1/3 addition, FILLFACTOR = 80');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE TS.TS_VALUE IS NULL;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80');
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80');
SELECT
@INT_BUCKET = TS.TS_VALUE
FROM dbo.TBL_TEST_SET TS
WHERE ISNULL(TS.TS_VALUE,0) = 0;
INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80');
SELECT
TR.TR_TX AS OPERATION
,DATEDIFF(MICROSECOND, MIN(TR.TR_TS), MAX(TR.TR_TS)) AS DURATION
FROM @TIMING_RESULTS TR
GROUP BY TR.TR_TX
ORDER BY 2;
Results (1000000)
OPERATION DURATION----------------------------------------------------------------------- -----------
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 9000
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 9001
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 10001
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 49003
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 82004
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 83004
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 83005
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 83005
BASELINE #4: Index filter IS NOT NULL 115007
BASELINE #1: Default index settings 116007
BASELINE #3: Index filter IS NULL 116007
BASELINE #5: FILLFACTOR = 50 118007
BASELINE #2: Index rebuilt 119006
BASELINE #6: 1/3 addition, FILLFACTOR = 80 153008
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 726041
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 790046
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 822047
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 872050
Here's what I get running your harness a few times. Whatever your tempdb is on kicks the school lunch out of the XtremIO my tempdb is on. Heh.
OPERATION DURATION
---------------------------------------------------------------------------------------------------- -----------
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 15623
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 15628
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 62503
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 78150
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 93755
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 93756
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 93764
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 109377
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 125003
BASELINE #5: FILLFACTOR = 50 140621
BASELINE #3: Index filter IS NULL 140642
BASELINE #2: Index rebuilt 156258
BASELINE #1: Default index settings 156262
BASELINE #4: Index filter IS NOT NULL 156262
BASELINE #6: 1/3 addition, FILLFACTOR = 80 203131
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 1046917
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 1125052
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 1265677
OPERATION DURATION
---------------------------------------------------------------------------------------------------- -----------
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 15612
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 15616
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 46874
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 93746
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 93755
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 93757
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 93759
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 109375
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 125008
BASELINE #3: Index filter IS NULL 156251
BASELINE #1: Default index settings 156264
BASELINE #2: Index rebuilt 156265
BASELINE #5: FILLFACTOR = 50 171875
BASELINE #4: Index filter IS NOT NULL 187513
BASELINE #6: 1/3 addition, FILLFACTOR = 80 203147
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 1078191
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 1156322
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 1203174
OPERATION DURATION
---------------------------------------------------------------------------------------------------- -----------
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 15626
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 15630
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 62516
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 93745
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 93760
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 124985
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 124996
BASELINE #2: Index rebuilt 140622
BASELINE #1: Default index settings 156226
BASELINE #4: Index filter IS NOT NULL 156235
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 156239
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 156243
BASELINE #3: Index filter IS NULL 203112
BASELINE #6: 1/3 addition, FILLFACTOR = 80 203116
BASELINE #5: FILLFACTOR = 50 203123
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 1031244
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 1124980
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 1249987
October 12, 2014 at 1:18 pm
This is on a mediocre i5 laptop (E6220), single SSD (3rd gen) and SQL Server 2014, similar results on both 2012 and 2008 R2 on the same system. Tempdb has 4 equally sized files on all instances, some SSD driver tweaks but nothing major.
Interesting to compare the results, especially #3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL, wonder why it's so slow on your system.
😎
October 12, 2014 at 1:32 pm
Eirikur Eiriksson (10/12/2014)
This is on a mediocre i5 laptop (E6220), single SSD (3rd gen) and SQL Server 2014, similar results on both 2012 and 2008 R2 on the same system. Tempdb has 4 equally sized files on all instances, some SSD driver tweaks but nothing major.Interesting to compare the results, especially #3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL, wonder why it's so slow on your system.
😎
I have tempdb set up the same way. I'm running this on pretty beefy machines on a really quiet day.
Here are results from a couple different servers. That 0 duration showed up more than once :w00t:
Run #1 Server #1
OPERATION DURATION
---------------------------------------------------------------------------------------------------- -----------
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 15601
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 31202
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 62403
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 109206
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 124806
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 124806
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 140407
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 140407
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 171609
BASELINE #3: Index filter IS NULL 202810
BASELINE #2: Index rebuilt 218411
BASELINE #4: Index filter IS NOT NULL 280814
BASELINE #6: 1/3 addition, FILLFACTOR = 80 280814
BASELINE #1: Default index settings 280815
BASELINE #5: FILLFACTOR = 50 296415
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 1357270
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 1404072
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 1497677
Run #2 Server #1
OPERATION DURATION
---------------------------------------------------------------------------------------------------- -----------
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 15601
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 31201
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 93604
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 124806
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 140406
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 140406
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 140407
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 171608
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 187209
BASELINE #1: Default index settings 218410
BASELINE #6: 1/3 addition, FILLFACTOR = 80 280813
BASELINE #4: Index filter IS NOT NULL 296413
BASELINE #5: FILLFACTOR = 50 312014
BASELINE #2: Index rebuilt 312014
BASELINE #3: Index filter IS NULL 327615
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 1419664
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 1466465
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 1591271
Run #1 Server #2
OPERATION DURATION
---------------------------------------------------------------------------------------------------- -----------
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 0
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 15601
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 62401
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 78001
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 78002
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 78002
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 78002
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 109202
BASELINE #1: Default index settings 124802
BASELINE #3: Index filter IS NULL 124802
BASELINE #4: Index filter IS NOT NULL 124803
BASELINE #2: Index rebuilt 140403
BASELINE #6: 1/3 addition, FILLFACTOR = 80 171604
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 202804
BASELINE #5: FILLFACTOR = 50 312006
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 889217
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 904817
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 998419
Run #2 Server #2
OPERATION DURATION
---------------------------------------------------------------------------------------------------- -----------
#2 WHERE TS.TS_VALUE IS NULL: Index rebuilt 15600
#5 WHERE TS.TS_VALUE IS NULL: FILLFACTOR = 50 31201
#4 WHERE TS.TS_VALUE IS NULL: Index filter IS NOT NULL 62401
#4 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NOT NULL 78001
#2 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index rebuilt 78002
#3 WHERE ISNULL(TS.TS_VALUE,0) = 0: Index filter IS NULL 93602
#6 WHERE ISNULL(TS.TS_VALUE,0) = 0: 1/3 addition, FILLFACTOR = 80 109202
BASELINE #2: Index rebuilt 124802
BASELINE #3: Index filter IS NULL 124802
BASELINE #4: Index filter IS NOT NULL 124803
BASELINE #1: Default index settings 140402
#1 WHERE ISNULL(TS.TS_VALUE,0) = 0: Default index settings 156003
BASELINE #6: 1/3 addition, FILLFACTOR = 80 171603
#5 WHERE ISNULL(TS.TS_VALUE,0) = 0: FILLFACTOR = 50 218404
BASELINE #5: FILLFACTOR = 50 312006
#3 WHERE TS.TS_VALUE IS NULL: Index filter IS NULL 889217
#6 WHERE TS.TS_VALUE IS NULL: 1/3 addition, FILLFACTOR = 80 967219
#1 WHERE TS.TS_VALUE IS NULL: Default index settings 1684833
October 12, 2014 at 1:54 pm
sqldriver (10/12/2014)
Jeff Moden (10/12/2014)
sqldriver (10/9/2014)
They insisted something was wrong and that it was always faster. I told them to come back when they found it faster their way.That's kind of a big mistake on your part. Although you've demonstrated both sides, it doesn't sound like you've done it using THEIR data or THEIR code on a strictly in-situ. Work with them to capture their query performance in a profiler run. Rewrite THEIR query for them and then have them run both in-situ. Make sure that you capture CPU, Reads, Writes, Duration, and Rowcounts so that they can see for themselves because NOW it's being worked on THEIR turf.
Yeah... I know... you shouldn't have to go through all of that... and, in the future, you probably won't (most of the time. There will be exceptions). You have to prove to them a couple of times that you actually do know what you're talking about. And I know you know this already but have to say it out loud just to feel better... Remember that they came to you and are now in disbelief (they insisted something was wrong). That's going to require the extra work this time and you need to be as nice/helpful as you can so the keep coming back instead of doing something silly that came for some self-proclaimed expert with a bad test on the internet.
Since I will probably never again in my medium legged life get to say this: au contraire, Herr Moden!
I skipped over the boring parts. The restoring 3.5tb of prod data to dev for them to work with. The way way way oversubscribing a VM to bring it closer to production specs, the initial feedback I gave them on their code which replaced 3 subquery columns with an outer apply, a nifty POC index for some averages they were calculating, and a half-stolen apply/values construct from Luis to help them pull out a text identifier.
They came back to me an hour later with "this will always be faster", "this" referring to the ISNULL nonsense. I am always nice and helpful, and pretty good at restraining the twitches I regularly feel coming on (the same twitches I imagine you guys feel when I ask questions). If anything, the situation was opposite. They were on my lawn! I'm the only DBA, and, according to my boss, I'm not a developer (unless he needs some custom code to keep from having to pay a vendor to write it, of course.). I don't get to spend that much time with the devs. I do my monitoring and pass along a few recs to the senior developer, but it's not as much of my focus as I'd like.
Heh... you didn't leave out the boring parts... you left out the important parts. 😉
I stand corrected and I have to give you a very hearty "Well done". I wish more DBAs would take the time.
So, let me get this straight though (because I'm insanely curious of the human element)... you went through all of this and THEN they came back with the ISNULL solution as being "faster" and you had to show them? What was the basis for them saying that? Did they show up with the test they had run or did they just make a statement?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply