Persisted computed columns not being used correctly by SQL

Question

Post reply

Persisted computed columns not being used correctly by SQL

ashley.wardell

SSC Eights!

Points: 983
More actions
December 14, 2011 at 4:54 am

#264856

Hi All
I am having some problems with persisted computed columns.
I wish to use them in a covering index to reduce total reads to the data.
the following code contains my table structure.
i populate Logged_Minute_Id2 with the same data as Logged_Minute_Id
This is key to what i am trying to show.
I have also populated a few hundred thousand test rows
USE Test
GO
CREATE TABLE [dbo].[Fact_Log](
[Fact_log_id] [bigint] IDENTITY(1,1) NOT NULL,
[Unique_Guid] [uniqueidentifier] NOT NULL,
[Logged_Time_Id] [datetimeoffset](7) NOT NULL,
[Logged_Date_Id] AS (CONVERT([date],[Logged_Time_Id],(0))) PERSISTED NOT NULL,
[MN_Machine_Id] [int] NOT NULL,
[SR_Source_Id] [int] NOT NULL,
[IN_Instance_Id] [int] NOT NULL,
[SV_Source_Version_Id] [int] NOT NULL,
[CT_Category_Id] [int] NOT NULL,
[DM_Domain_Id] [int] NOT NULL,
[Inserted_Time_id] [datetimeoffset](7) NOT NULL,
[Inserted_Date_Id] AS (CONVERT([date],[Inserted_Time_id],(0))) PERSISTED NOT NULL,
[Log_Text] [varchar](max) SPARSE NULL,
[Logged_Minute_Id] AS (CONVERT([smalldatetime],[Logged_Time_Id],(0))) PERSISTED NOT NULL,
[Logged_Minute_Id2] [smalldatetime] NOT NULL,
CONSTRAINT [CI_Fact_Log] PRIMARY KEY CLUSTERED
(
[Fact_log_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_Time] ON [dbo].[Fact_Log]
(
[Logged_Minute_Id] ASC,
[MN_Machine_Id] ASC,
[SR_Source_Id] ASC,
[CT_Category_Id] ASC,
[DM_Domain_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Time2] ON [dbo].[Fact_Log]
(
[Logged_Minute_Id2] ASC,
[MN_Machine_Id] ASC,
[SR_Source_Id] ASC,
[CT_Category_Id] ASC,
[DM_Domain_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Dim_Source] Script Date: 12/14/2011 11:13:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Dim_Source](
[SR_Source_Id] [int] IDENTITY(1,1) NOT NULL,
[SR_Source_Name] [varchar](256) NOT NULL,
CONSTRAINT [CI_Dim_Source] PRIMARY KEY CLUSTERED
(
[SR_Source_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE NONCLUSTERED INDEX [UI1_Dim_Source] ON [dbo].[Dim_Source]
(
[SR_Source_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Dim_Machine] Script Date: 12/14/2011 11:13:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Dim_Machine](
[MN_Machine_Id] [int] IDENTITY(1,1) NOT NULL,
[MN_Machine_Name] [varchar](256) NOT NULL,
CONSTRAINT [CI_Dim_Machine] PRIMARY KEY CLUSTERED
(
[MN_Machine_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Dim_Machine] ON [dbo].[Dim_Machine]
(
[MN_Machine_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Dim_Domain] Script Date: 12/14/2011 11:13:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Dim_Domain](
[DM_Domain_Id] [int] IDENTITY(1,1) NOT NULL,
[DM_Domain_Name] [varchar](256) NOT NULL,
CONSTRAINT [CI_Dim_Domain] PRIMARY KEY CLUSTERED
(
[DM_Domain_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Dim_Domain] ON [dbo].[Dim_Domain]
(
[DM_Domain_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Dim_Category] Script Date: 12/14/2011 11:13:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Dim_Category](
[CT_Category_Id] [int] NOT NULL,
[CT_Category_Desc] [varchar](256) NOT NULL,
CONSTRAINT [CI_Dim_Category] PRIMARY KEY CLUSTERED
(
[CT_Category_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
the queries i am perfoming are as follows
SELECT d.DM_Domain_Name AS [Domain] ,
c.CT_Category_Desc AS Category ,
s.SR_Source_Name AS [Source] ,
m.MN_Machine_Name AS [Machine] ,
F.*
FROM ( SELECT SUM(1) AS Logs ,
F.CT_Category_Id ,
F.DM_Domain_Id ,
F.SR_Source_Id ,
F.MN_Machine_Id ,
logged_minute_id AS [Time]
FROM Fact_Log F WITH ( NOLOCK, INDEX=IX_Time )
WHERE F.Logged_minute_Id > DATEADD(MINUTE, -150, SYSDATETIMEOFFSET())
GROUP BY logged_minute_id,
F.CT_Category_Id ,
F.DM_Domain_Id ,
F.SR_Source_Id ,
F.MN_Machine_Id
) F
INNER JOIN dbo.Dim_Category c WITH ( NOLOCK ) ON F.CT_Category_Id = c.CT_Category_Id
INNER JOIN dbo.Dim_Domain D WITH ( NOLOCK ) ON f.DM_Domain_Id = d.DM_Domain_Id
INNER JOIN Dim_Source AS S WITH ( NOLOCK ) ON f.SR_Source_Id = s.SR_Source_Id
INNER JOIN Dim_Machine AS M WITH ( NOLOCK ) ON F.MN_Machine_Id = M.MN_Machine_Id
SELECT d.DM_Domain_Name AS [Domain] ,
c.CT_Category_Desc AS Category ,
s.SR_Source_Name AS [Source] ,
m.MN_Machine_Name AS [Machine] ,
F.*
FROM ( SELECT SUM(1) AS Logs ,
F.CT_Category_Id ,
F.DM_Domain_Id ,
F.SR_Source_Id ,
F.MN_Machine_Id ,
logged_minute_id AS [Time]
FROM Fact_Log F WITH ( NOLOCK, INDEX=IX_Time )
WHERE F.Logged_minute_Id > DATEADD(MINUTE, -150, SYSDATETIMEOFFSET())
GROUP BY logged_minute_id,
F.CT_Category_Id ,
F.DM_Domain_Id ,
F.SR_Source_Id ,
F.MN_Machine_Id
) F
INNER merge JOIN dbo.Dim_Category c WITH ( NOLOCK ) ON F.CT_Category_Id = c.CT_Category_Id
INNER merge JOIN dbo.Dim_Domain D WITH ( NOLOCK ) ON f.DM_Domain_Id = d.DM_Domain_Id
INNER merge JOIN Dim_Source AS S WITH ( NOLOCK ) ON f.SR_Source_Id = s.SR_Source_Id
INNER MERGE JOIN Dim_Machine AS M WITH ( NOLOCK ) ON F.MN_Machine_Id = M.MN_Machine_Id
SELECT d.DM_Domain_Name AS [Domain] ,
c.CT_Category_Desc AS Category ,
s.SR_Source_Name AS [Source] ,
m.MN_Machine_Name AS [Machine] ,
F.*
FROM ( SELECT SUM(1) AS Logs ,
F.CT_Category_Id ,
F.DM_Domain_Id ,
F.SR_Source_Id ,
F.MN_Machine_Id ,
logged_minute_id2 AS [Time]
FROM Fact_Log F WITH ( NOLOCK, INDEX=IX_Time2 )
WHERE F.Logged_minute_Id2 > DATEADD(MINUTE, -150, SYSDATETIMEOFFSET())
GROUP BY logged_minute_id2,
F.CT_Category_Id ,
F.DM_Domain_Id ,
F.SR_Source_Id ,
F.MN_Machine_Id
) F
INNER merge JOIN dbo.Dim_Category c WITH ( NOLOCK ) ON F.CT_Category_Id = c.CT_Category_Id
INNER merge JOIN dbo.Dim_Domain D WITH ( NOLOCK ) ON f.DM_Domain_Id = d.DM_Domain_Id
INNER merge JOIN Dim_Source AS S WITH ( NOLOCK ) ON f.SR_Source_Id = s.SR_Source_Id
INNER MERGE JOIN Dim_Machine AS M WITH ( NOLOCK ) ON F.MN_Machine_Id = M.MN_Machine_Id
as you can see from the following stats the third query perfoms much better.
My main goal here is to reduce IO as there will be mass concurrency on queries, so they will all be fighting for IO.
I really want to use Logged_Minute_Id as it is computed persisted but unfortunately when using merge joins in this instance it seem to default back to the column it is computed from.
Also you can see that not using the merge joins increases my IO on my DIM tables.
Does anyone know why this is happeneing and if there is a way round this.
Unfortunately i will have to scrap using computed columns and compute my own (i.e. Logged_Minute_Id2) with triggers or something.
Thanks in advance
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Warning: The join order has been enforced because a local join hint is used.
Warning: The join order has been enforced because a local join hint is used.
SQL Server parse and compile time:
CPU time = 31 ms, elapsed time = 38 ms.
(47376 row(s) affected)
Table 'Dim_Domain'. Scan count 0, logical reads 94752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Machine'. Scan count 0, logical reads 94752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Source'. Scan count 0, logical reads 94752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Category'. Scan count 0, logical reads 94752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact_Log'. Scan count 1, logical reads 393, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 750 ms, elapsed time = 1552 ms.
(47376 row(s) affected)
Table 'Fact_Log'. Scan count 3, logical reads 380397, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Category'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Domain'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Source'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Machine'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 2001 ms, elapsed time = 4353 ms.
(47376 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Machine'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Source'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Domain'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim_Category'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact_Log'. Scan count 1, logical reads 393, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 609 ms, elapsed time = 3907 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ashley.wardell SSC Eights! Points: 983 More actions · Answer 1

Cheers anyone for looking but i have now fixed this.

if you calculate the date in a variable prior to doing the select then it comes back fine.

GSquared SSC Guru Points: 260824 More actions · Answer 2

Let me guess, the problem was the implicit conversions being short-circuited. That'll add so much to the cost that the optimizer was bypassing it. Right?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ashley.wardell SSC Eights! Points: 983 More actions · Answer 3

yer hopefully one day the query engine will cache caluculations done on current date and they we can put this inline in the sql 🙁 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 4

wouldn't forcing the optimizer to use merge joins, instead of allowing it to pick what it thinks is best also potentially affect the performance?

I'll let others expound on why nolock is a bad idea as well.

INNER merge JOIN dbo.Dim_Category c WITH ( NOLOCK ) ON F.CT_Category_Id = c.CT_Category_Id
INNER merge JOIN dbo.Dim_Domain D WITH ( NOLOCK ) ON f.DM_Domain_Id = d.DM_Domain_Id
INNER merge JOIN Dim_Source AS S WITH ( NOLOCK ) ON f.SR_Source_Id = s.SR_Source_Id
INNER MERGE JOIN Dim_Machine AS M WITH ( NOLOCK ) ON F.MN_Machine_Id = M.MN_Machine_Id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Little pound :

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

I think it's time to make our own little version and post it here! 😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

ashley.wardell (12/14/2011)
yer hopefully one day the query engine will cache caluculations done on current date and they we can put this inline in the sql 🙁 🙂

Not sure what you mean by this. And then my answer varies greatly!

ashley.wardell SSC Eights! Points: 983 More actions · Answer 7

sorry the they should have said then

to solve the problem i changed this.

SELECT d.DM_Domain_Name AS [Domain] ,

c.CT_Category_Desc AS Category ,

s.SR_Source_Name AS [Source] ,

m.MN_Machine_Name AS [Machine] ,

F.*

FROM ( SELECT SUM(1) AS Logs ,

F.CT_Category_Id ,

F.DM_Domain_Id ,

F.SR_Source_Id ,

F.MN_Machine_Id ,

logged_minute_id AS [Time]

FROM Fact_Log F WITH ( NOLOCK, INDEX=IX_Time )

WHERE F.Logged_minute_Id > DATEADD(MINUTE, -150, SYSDATETIMEOFFSET())

GROUP BY logged_minute_id,

F.CT_Category_Id ,

F.DM_Domain_Id ,

F.SR_Source_Id ,

F.MN_Machine_Id

) F

INNER JOIN dbo.Dim_Category c WITH ( NOLOCK ) ON F.CT_Category_Id = c.CT_Category_Id

INNER JOIN dbo.Dim_Domain D WITH ( NOLOCK ) ON f.DM_Domain_Id = d.DM_Domain_Id

INNER JOIN Dim_Source AS S WITH ( NOLOCK ) ON f.SR_Source_Id = s.SR_Source_Id

INNER JOIN Dim_Machine AS M WITH ( NOLOCK ) ON F.MN_Machine_Id = M.MN_Machine_Id

into this

DECLARE @tm as SMALLDATETIME

set @tm = DATEADD(MINUTE, -150, SYSDATETIMEOFFSET())

SELECT d.DM_Domain_Name AS [Domain] ,

c.CT_Category_Desc AS Category ,

s.SR_Source_Name AS [Source] ,

m.MN_Machine_Name AS [Machine] ,

F.*

FROM ( SELECT SUM(1) AS Logs ,

F.CT_Category_Id ,

F.DM_Domain_Id ,

F.SR_Source_Id ,

F.MN_Machine_Id ,

logged_minute_id AS [Time]

FROM Fact_Log F WITH ( NOLOCK, INDEX=IX_Time )

WHERE F.Logged_minute_Id > @tm

GROUP BY logged_minute_id,

F.CT_Category_Id ,

F.DM_Domain_Id ,

F.SR_Source_Id ,

F.MN_Machine_Id

) F

INNER JOIN dbo.Dim_Category c WITH ( NOLOCK ) ON F.CT_Category_Id = c.CT_Category_Id

INNER JOIN dbo.Dim_Domain D WITH ( NOLOCK ) ON f.DM_Domain_Id = d.DM_Domain_Id

INNER JOIN Dim_Source AS S WITH ( NOLOCK ) ON f.SR_Source_Id = s.SR_Source_Id

INNER JOIN Dim_Machine AS M WITH ( NOLOCK ) ON F.MN_Machine_Id = M.MN_Machine_Id

problem solved.

I've had to do this loads of times before and kicked myself when one of the other DBA's pointed it out.

my point was hopfully in the not to distant future the query engine will realise this and i won't have to take these steps.

Hope this is clear.