May 6, 2015 at 11:40 am
WHERE Modified_Date >= (DATEDIFF(s, '19700101', GETDATE())- 2678400)
what is the data type of "Modified Date"?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 6, 2015 at 11:51 am
So COL_CORE_Audit_Data is a view? Need to see the DDL for the view and the underlying tables and the indexes on the tables.
Table:
CREATE TABLE [dbo].[T387](
[C1] [nvarchar](15) NOT NULL,
[C2] [nvarchar](254) NULL,
[C3] [int] NOT NULL,
[C4] [nvarchar](254) NULL,
[C5] [nvarchar](254) NOT NULL,
[C6] [int] NOT NULL,
[C7] [int] NOT NULL,
[C8] [nvarchar](254) NOT NULL,
[C112] [nvarchar](255) NULL,
[C675001003] [int] NULL,
[C812000101] [nvarchar](255) NULL,
[C875000000] [nvarchar](255) NULL,
[C875000001] [nvarchar](255) NULL,
[C875000002] [nvarchar](255) NULL,
[C875000003] [nvarchar](255) NULL,
[C875000004] [nvarchar](255) NULL,
[C875156000] [nvarchar](255) NULL,
[C875156001] [nvarchar](255) NULL,
[C875156002] [nvarchar](255) NULL,
[C875156005] [nvarchar](255) NULL,
[C875156006] [int] NULL,
[C875156007] [nvarchar](255) NULL,
[C875156003] [nvarchar](255) NULL,
[C875156004] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED
(
[C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ViewCREATE VIEW [dbo].[COL_CORE_Audit_Data] (Request_ID,Submitter,Create_Date,Assigned_To,Last_Modified_By,Modified_Date,Status,Short_Description,Record_Access,Audit_Object_Type,Company_Name,Hierarchy_Key,Parent_Hierarchy_Key,Record_ID,Hierarchy_Identifier,Company_Hierarchy_Key,Audit_Record_Key,Field,Field_ID,From_Value,To_Value,Updated_By_Login,Field_Type,Updated_By) AS SELECT T387.C1,C2,C3,C4,C5,C6,C7,C8,C112,C675001003,C812000101,C875000000,C875000001,C875000002,C875000003,C875000004,C875156000,C875156001,C875156002,C875156003,C875156004,C875156005,C875156006,C875156007 FROM T387
"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 6, 2015 at 11:54 am
So COL_CORE_Audit_Data is a view? Need to see the DDL for the view and the underlying tables and the indexes on the tables.
Index 1:CREATE NONCLUSTERED INDEX [I387_6_1] ON [dbo].[T387]
(
[C6] 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
Index 2:CREATE NONCLUSTERED INDEX [I387_812000101_1] ON [dbo].[T387]
(
[C812000101] 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
Index 3:CREATE NONCLUSTERED INDEX [I387_875000000_1] ON [dbo].[T387]
(
[C875000000] 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
Index 4:CREATE NONCLUSTERED INDEX [I387_875000001_1] ON [dbo].[T387]
(
[C875000001] 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
Index 5:CREATE NONCLUSTERED INDEX [I387_875156000_1] ON [dbo].[T387]
(
[C875156000] 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
Index 6:ALTER TABLE [dbo].[T387] ADD PRIMARY KEY CLUSTERED
(
[C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
"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 6, 2015 at 12:11 pm
New Born DBA (5/6/2015)
So COL_CORE_Audit_Data is a view? Need to see the DDL for the view and the underlying tables and the indexes on the tables.
Index 1:
CREATE NONCLUSTERED INDEX [I387_6_1] ON [dbo].[T387](
[C6] 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
Index 2:
CREATE NONCLUSTERED INDEX [I387_812000101_1] ON [dbo].[T387](
[C812000101] 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
Index 3:
CREATE NONCLUSTERED INDEX [I387_875000000_1] ON [dbo].[T387](
[C875000000] 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
Index 4:
CREATE NONCLUSTERED INDEX [I387_875000001_1] ON [dbo].[T387](
[C875000001] 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
Index 5:
CREATE NONCLUSTERED INDEX [I387_875156000_1] ON [dbo].[T387](
[C875156000] 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
Index 6:
ALTER TABLE [dbo].[T387] ADD PRIMARY KEY CLUSTERED(
[C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Index 1 isn't going to help this query due to the number of rows of data that will be returned. It is more efficient for SQL Server to scan the table than do bookmark lookups for 31 days worth of information.
My suggestion, if most of your SELECT queries read on column C6, is to change the primary key from a CLUSTERED index to a NONCLUSTERED index and then make the index on C6 your CLUSTERED index.
May 6, 2015 at 12:38 pm
My suggestion, if most of your SELECT queries read on column C6, is to change the primary key from a CLUSTERED index to a NONCLUSTERED index and then make the index on C6 your CLUSTERED index.
I have considered that.
"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 7, 2015 at 8:45 am
New Born DBA (5/6/2015)
My suggestion, if most of your SELECT queries read on column C6, is to change the primary key from a CLUSTERED index to a NONCLUSTERED index and then make the index on C6 your CLUSTERED index.
Well, I Created non clustered index on C6, and query ran much better. It used 70% less CPU, even logical reads were down to 50,000 instead of 175,000 and it ran in 13 seconds instead of 40 seconds, but this is what I was told, "that it looks promising, however, The index on C1 will ALWAYS be used to create a new record in the table. It is a system field. We can’t delete that index"
"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 7, 2015 at 8:55 am
New Born DBA (5/7/2015)
New Born DBA (5/6/2015)
My suggestion, if most of your SELECT queries read on column C6, is to change the primary key from a CLUSTERED index to a NONCLUSTERED index and then make the index on C6 your CLUSTERED index.
Well, I Created non clustered index on C6, and query ran much better. It used 70% less CPU, even logical reads were down to 50,000 instead of 175,000 and it ran in 13 seconds instead of 40 seconds, but this is what I was told, "that it looks promising, however, The index on C1 will ALWAYS be used to create a new record in the table. It is a system field. We can’t delete that index"
Sorry, but I actually spit soda out my nose on this comment:
"that it looks promising, however, The index on C1 will ALWAYS be used to create a new record in the table. It is a system field. We can’t delete that index"
It isn't a system field, the column c1is a user defined column on the table, and the index can be dropped and recreated as a nonclustered index and a clustered index created on the column c6.
May 7, 2015 at 9:07 am
Sorry, but I actually spit soda out my nose on this comment:
Well I hope you are OK. 😀
It isn't a system field, the column c1is a user defined column on the table, and the index can be dropped and recreated as a nonclustered index and a clustered index created on the column c6.
So this is what I was given.
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
"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 7, 2015 at 9:17 am
New Born DBA (5/7/2015)
Sorry, but I actually spit soda out my nose on this comment:
Well I hope you are OK. 😀
It isn't a system field, the column c1is a user defined column on the table, and the index can be dropped and recreated as a nonclustered index and a clustered index created on the column c6.
So this is what I was given.
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
I don't see anything in that information that would prevent you from changing the primary key from a clustered index to a nonclustered index.
For others, here is the link made clickable:
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
May 7, 2015 at 9:20 am
Lynn Pettis (5/7/2015)
New Born DBA (5/7/2015)
Sorry, but I actually spit soda out my nose on this comment:
Well I hope you are OK. 😀
It isn't a system field, the column c1is a user defined column on the table, and the index can be dropped and recreated as a nonclustered index and a clustered index created on the column c6.
So this is what I was given.
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
I don't see anything in that information that would prevent you from changing the primary key from a clustered index to a nonclustered index.
For others, here is the link made clickable:
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
Thanks Lynn for helping me out. I really appreciate that 🙂
"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 7, 2015 at 12:41 pm
Lynn Pettis (5/7/2015)
New Born DBA (5/7/2015)
Sorry, but I actually spit soda out my nose on this comment:
Well I hope you are OK. 😀
It isn't a system field, the column c1is a user defined column on the table, and the index can be dropped and recreated as a nonclustered index and a clustered index created on the column c6.
So this is what I was given.
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
I don't see anything in that information that would prevent you from changing the primary key from a clustered index to a nonclustered index.
For others, here is the link made clickable:
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
It did expose a need for data normalization being needed though. Nothing like having a column comprised of parts.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2015 at 12:35 am
New Born DBA (5/7/2015)
Lynn Pettis (5/7/2015)
New Born DBA (5/7/2015)
Sorry, but I actually spit soda out my nose on this comment:
Well I hope you are OK. 😀
It isn't a system field, the column c1is a user defined column on the table, and the index can be dropped and recreated as a nonclustered index and a clustered index created on the column c6.
So this is what I was given.
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
I don't see anything in that information that would prevent you from changing the primary key from a clustered index to a nonclustered index.
For others, here is the link made clickable:
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
Thanks Lynn for helping me out. I really appreciate that 🙂
From your last comment, I can't decipher if Lynn helped you. Her advise is right on. The SQL engine was scanning the table as it saw that most of the data returned happen in the last 31 days. Ordering the table by this C6/modified_date field will help, probably tremendously. If data inserts to this table later become a issue, then the answer is not to drop the clustered index, but instead look to partition (again by this C6/modified_date field) the data in the base table.
----------------------------------------------------
May 8, 2015 at 8:30 am
MMartin1 (5/8/2015)
New Born DBA (5/7/2015)
Lynn Pettis (5/7/2015)
New Born DBA (5/7/2015)
Sorry, but I actually spit soda out my nose on this comment:
Well I hope you are OK. 😀
It isn't a system field, the column c1is a user defined column on the table, and the index can be dropped and recreated as a nonclustered index and a clustered index created on the column c6.
So this is what I was given.
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
I don't see anything in that information that would prevent you from changing the primary key from a clustered index to a nonclustered index.
For others, here is the link made clickable:
http://theremedyforit.com/2012/03/understanding-the-request-id-field-in-bmc-remedy/
Thanks Lynn for helping me out. I really appreciate that 🙂
From your last comment, I can't decipher if Lynn helped you. Her advise is right on. The SQL engine was scanning the table as it saw that most of the data returned happen in the last 31 days. Ordering the table by this C6/modified_date field will help, probably tremendously. If data inserts to this table later become a issue, then the answer is not to drop the clustered index, but instead look to partition (again by this C6/modified_date field) the data in the base table.
First, I am not a her but a him. Second, partitioning is not a performance tuning tool.
May 8, 2015 at 9:30 am
Long story short is that the clustered index on C1 column cannot be dropped. I was going to suggest maybe using C1 column in where clause, but I don't think the result will be same.
"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 8, 2015 at 9:44 am
New Born DBA (5/8/2015)
Long story short is that the clustered index on C1 column cannot be dropped. I was going to suggest maybe using C1 column in where clause, but I don't think the result will be same.
The clustered index does two things in this case. One, it sets the logical order of the data in the table. Two, it supports the primary key constraint. My question is who says you can't drop and recreate the primary key constraint as a nonclustered index on C1? Putting a clustered index on C6 would improve this query, and possibly others that do range searches on the same column. Your other choice would be adding the necessary columns to support this query to the current index on C6 as included columns, but that may duplicate the data in the clustered index if you need all of the data columns.
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply