May 11, 2015 at 2:02 pm
New Born DBA (5/11/2015)
Lynn Pettis (5/11/2015)So the following (hopefully I mapped everything correctly using the VIEW definition) didn't help:
CREATE NONCLUSTERED INDEX [Test] ON [dbo].[T387]
(
[C6] ASC
)
INCLUDE
(
C1,
C3,
C67001003,
C875156001,
C875156006,
C875156003,
C875156004,
C875156005,
C875156007,
C875000001
)
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
No Sir, it didn't.
Okay, for S and G's build the index above then run this query in SSMS and capture, save, and post the actual execution plan generated when the query is run:
SELECT
Audit_Object_Type AS [Audit_Object_Type_Int] ,
Field ,
Field_Type AS [Field_Type_Int] ,
From_Value ,
To_Value ,
Updated_By_Login ,
Updated_By ,
Parent_Hierarchy_Key ,
DATEADD(S, Create_Date, '1970-01-01 00:00:00') AS [Create Date] ,
Request_ID
FROM
COL_CORE_Audit_Data
WHERE
Modified_Date >= (DATEDIFF(s, '19700101', GETDATE())- 2678400);
May 11, 2015 at 2:17 pm
Lynn Pettis (5/11/2015)
New Born DBA (5/11/2015)
Lynn Pettis (5/11/2015)So the following (hopefully I mapped everything correctly using the VIEW definition) didn't help:
CREATE NONCLUSTERED INDEX [Test] ON [dbo].[T387]
(
[C6] ASC
)
INCLUDE
(
C1,
C3,
C67001003,
C875156001,
C875156006,
C875156003,
C875156004,
C875156005,
C875156007,
C875000001
)
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
No Sir, it didn't.
Okay, for S and G's build the index above then run this query in SSMS and capture, save, and post the actual execution plan generated when the query is run:
SELECT
Audit_Object_Type AS [Audit_Object_Type_Int] ,
Field ,
Field_Type AS [Field_Type_Int] ,
From_Value ,
To_Value ,
Updated_By_Login ,
Updated_By ,
Parent_Hierarchy_Key ,
DATEADD(S, Create_Date, '1970-01-01 00:00:00') AS [Create Date] ,
Request_ID
FROM
COL_CORE_Audit_Data
WHERE
Modified_Date >= (DATEDIFF(s, '19700101', GETDATE())- 2678400);
Execution Plan attached
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 11, 2015 at 2:29 pm
New Born DBA (5/11/2015)
Lynn Pettis (5/11/2015)
New Born DBA (5/11/2015)
Lynn Pettis (5/11/2015)So the following (hopefully I mapped everything correctly using the VIEW definition) didn't help:
CREATE NONCLUSTERED INDEX [Test] ON [dbo].[T387]
(
[C6] ASC
)
INCLUDE
(
C1,
C3,
C67001003,
C875156001,
C875156006,
C875156003,
C875156004,
C875156005,
C875156007,
C875000001
)
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
No Sir, it didn't.
Okay, for S and G's build the index above then run this query in SSMS and capture, save, and post the actual execution plan generated when the query is run:
SELECT
Audit_Object_Type AS [Audit_Object_Type_Int] ,
Field ,
Field_Type AS [Field_Type_Int] ,
From_Value ,
To_Value ,
Updated_By_Login ,
Updated_By ,
Parent_Hierarchy_Key ,
DATEADD(S, Create_Date, '1970-01-01 00:00:00') AS [Create Date] ,
Request_ID
FROM
COL_CORE_Audit_Data
WHERE
Modified_Date >= (DATEDIFF(s, '19700101', GETDATE())- 2678400);
Execution Plan attached
Thanks. I have to say I am lost on this one.
May 12, 2015 at 3:34 am
You might want to try updating the statistics on that table prior to running that query.
I say this because there is quite a difference between the estimated and actual number of rows retrieved.
It "thinks" it is fetching nearly 3m rows but actually retrieves just about a third of that.
May 12, 2015 at 8:33 am
CKX (5/12/2015)
You might want to try updating the statistics on that table prior to running that query.I say this because there is quite a difference between the estimated and actual number of rows retrieved.
It "thinks" it is fetching nearly 3m rows but actually retrieves just about a third of that.
Tried that last week but didn't work.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 12, 2015 at 10:18 am
New Born DBA (5/11/2015)
CKX (5/11/2015)
I think you may find this particular response from Lynn probably explains what is going on here.
You can experiment by "including" the other columns in your index and see how it does...
I did create a clustered index on C6 and it worked perfectly fine, but we can't drop clustered index from C1. Won't help in our case, because there are tons of other queries we have in our environment where we need clustered index on C1 column.
I also created a non clustered index, included all the columns in an index, but SQL Server doesn't use that index. Even when I ran the query and the force SQL Server to use the index I created but no luck.
Have you verified that C1 is actually being used more often for lookups? Please post missing index and index usage stats. Most often there really is a column(s) that is(are) clearly best for the clustering key.
Unless you just can't change the clustering key period.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply