April 6, 2014 at 11:52 pm
Insert into Table
Select * from anothertable where somecondition in(select some_condition from thirdtable)
While doing this operation i see there are more reads are hapenning in profiler
CPU, Reads, Writes duration
800,89854,4000,15989
SQL Server Read is more can we reduce this?
April 6, 2014 at 11:59 pm
Without the table DDL and the execution plan, all we can do is guess.
Maybe you don't have the right indexes and you are doing scans instead of seeks...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2014 at 12:29 am
i got this from profiler, so i dont have execution plan.
April 7, 2014 at 12:30 am
yuvipoy (4/7/2014)
i got this from profiler, so i dont have execution plan.
You do have the table DDL, don't you?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2014 at 12:57 am
yuvipoy, you know full well by this point what we need to answer performance questions.
Table definitions, index definitions and execution plan please. Otherwise there is absolutely nothing anyone can say. You keep giving insufficient information to help you/
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
April 7, 2014 at 6:08 am
Really sorry gail for not giving much informations.
Here is my DDL statements.
CREATE TABLE [dbo].[TABLE1](
[COL1] [uniqueidentifier] NOT NULL,
[COL2] [float] NOT NULL,
[COL3] [uniqueidentifier] NOT NULL,
[COL4] [float] NOT NULL,
[COL5] [varchar](64) NULL,
[COL6] [smallint] NOT NULL,
[COL7] [smallint] NOT NULL,
[COL8] [float] NOT NULL,
[COL9] [float] NOT NULL,
[COL10] [float] NULL,
[COL11] [float] NULL,
[COL12] [float] NOT NULL,
[COL13] [float] NOT NULL,
[COL14] [float] NOT NULL,
[COL15] [float] NOT NULL,
[COL16] [float] NOT NULL,
[COL17] [float] NOT NULL,
[COL18] [float] NULL,
[COL19] [smallint] NOT NULL,
[COL20] [varchar](256) NULL,
[COL21] [float] NOT NULL,
CONSTRAINT [PK_TABLE1] PRIMARY KEY NONCLUSTERED
(
[COL1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [TABLE1_COL3_12_13] ON [dbo].[TABLE1]
(
[COL3] ASC,
[COL12] ASC,
[COL13] 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, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX TABLE_1_COL15 ON [dbo].TABLE1
(
COL15 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, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Table1_Col17] ON [dbo].TABLE1
(
[COL17] 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, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX TABLE1_COL3_7 ON [dbo].TABLE1
(
COL3 ASC,
[COL7] 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, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX TABLE1_COL5 ON [dbo].TABLE1
(
COL5 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, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX TABLE1_COL20 ON [dbo].TABLE1
(
COL20 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, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX TABLE1_Col3_4 ON [dbo].TABLE1
(
COL3 ASC,
Col4 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, FILLFACTOR = 80) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TABLE1] WITH CHECK ADD CONSTRAINT [FK_RUN_ID] FOREIGN KEY([COL3])
REFERENCES [dbo].[TABLE4] ([COL1])
GO
CREATE TABLE [dbo].[TABLE2](
[COL1] [uniqueidentifier] NOT NULL,
[COL2] [uniqueidentifier] NOT NULL,
[COL3] [decimal](10, 0) NOT NULL,
[COL4] [float] NOT NULL,
[COL5] [int] NULL,
[COL6] [varchar](10) NULL,
[COL7] [varchar](50) NULL,
[COL8] [decimal](10, 0) NULL,
[COL9] [decimal](10, 0) NOT NULL,
[COL10] [float] NOT NULL,
[COL11] [real] NOT NULL,
[COL12] [float] NULL,
[COL13] [decimal](1, 0) NOT NULL,
[COL14] [decimal](10, 0) NULL,
[COL15] [int] NOT NULL,
[COL16] [uniqueidentifier] NULL,
[COL17] [smallint] NULL,
[COL18] [int] NULL,
CONSTRAINT [PK_TABLE2] PRIMARY KEY NONCLUSTERED
(
[COL1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TABLE2] WITH NOCHECK ADD CONSTRAINT [FK_SE_CD_MEAS_MEASUREMENT] FOREIGN KEY([COL2])
REFERENCES [dbo].[TABLE1] ([Col1])
GO
CREATE CLUSTERED INDEX [IX_TABBLE2_COL10] ON [dbo].[TABLE2]
(
[COL10] 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, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_TABLE2_Col2] ON [dbo].[TABLE2]
(
[COL2] 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, FILLFACTOR = 80) ON [PRIMARY]
GO
Select a*, Col5,Col7,Col9,Col10,Col12 from Table1 a (nolock), Table2 b (nolock) where a.col1=b.col2
and a.col3=@value
order by col4 , col15,col6, col10,col11 desc , col9,col12,col16
Execution plan
<RelOp AvgRowSize="127" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="20768.8" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="64" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="62.5696" TableCardinality="4778930">
showing this information on col18 on table2, where as this where condition is not been used in the above query, but still this column is been used and huge amount of lookup happens here.
April 7, 2014 at 6:58 am
How about providing the actual execution plan for the query as a .sqlplan file instead of pasting a bit of the xml plan?
April 7, 2014 at 6:59 am
Looking at this query, it won't even run:
Select a*, Col5,Col7,Col9,Col10,Col12 from Table1 a (nolock), Table2 b (nolock) where a.col1=b.col2
and a.col3=@value
order by col4 , col15,col6, col10,col11 desc , col9,col12,col16
You have ambiguous column names between the two tables, Table1 and Table2.
April 7, 2014 at 7:03 am
Here is your code reformatted and using ANSI-92 style inner join:
Select
a.*,
Col5, -- which table?
Col7, -- which table?
Col9, -- which table?
Col10, -- which table?
Col12 -- which table?
from
Table1 a
inner join Table2 b
on (a.col1 = b.col2)
where
a.col3 = @value
order by
col4, -- which table?
col15, -- which table?
col6, -- which table?
col10, -- which table?
col11 desc, -- which table?
col9, -- which table?
col12, -- which table?
col16; -- which table?
Now you just need to answer the questions listed in the formatted code.
April 7, 2014 at 7:18 am
yes.
inorder to hide my business process i have renamed the column.
while pasting here i did not mention as b.column names for the columns.
For execution plan since it is having
April 7, 2014 at 7:36 am
You are aware of the side effects of nolock? Your users are happy with intermittent errors in their reports?
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
April 7, 2014 at 8:01 am
GilaMonster (4/7/2014)
Your users are happy with intermittent errors in their reports?
Could not get your point Gila
April 7, 2014 at 8:06 am
Are you aware of the side effects of nolock?
Are your users happy to be getting reports and results which are intermittently incorrect?
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
April 7, 2014 at 8:11 am
yuvipoy (4/7/2014)
yes.inorder to hide my business process i have renamed the column.
while pasting here i did not mention as b.column names for the columns.
For execution plan since it is having
Would help if you put data in your tables. Running the query against empty tables doesn't help us help you.
April 7, 2014 at 8:16 am
Those are the existing codes.
i need to modify them.
Yes using dead lock will lead to incorrect reports.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply