Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQL Server Read ismore Expand / Collapse
Author
Message
Posted Sunday, April 6, 2014 11:52 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:37 AM
Points: 335, Visits: 1,143

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?
Post #1558918
Posted Sunday, April 6, 2014 11:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:46 AM
Points: 13,622, Visits: 10,513
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...




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558920
Posted Monday, April 7, 2014 12:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:37 AM
Points: 335, Visits: 1,143
i got this from profiler, so i dont have execution plan.
Post #1558929
Posted Monday, April 7, 2014 12:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:46 AM
Points: 13,622, Visits: 10,513
yuvipoy (4/7/2014)
i got this from profiler, so i dont have execution plan.


You do have the table DDL, don't you?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558930
Posted Monday, April 7, 2014 12:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1558936
Posted Monday, April 7, 2014 6:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:37 AM
Points: 335, Visits: 1,143
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.

Post #1559018
Posted Monday, April 7, 2014 6:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
How about providing the actual execution plan for the query as a .sqlplan file instead of pasting a bit of the xml plan?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1559040
Posted Monday, April 7, 2014 6:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1559042
Posted Monday, April 7, 2014 7:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1559046
Posted Monday, April 7, 2014 7:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:37 AM
Points: 335, Visits: 1,143
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


  Post Attachments 
plan.sqlplan (16 views, 49.48 KB)
Post #1559054
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse