I'm running the same query on two different PCs and tracing results in Profiler on my PC. When executing the query on PC1 - the total number of reads is 200000. When executing the same query on PC2 - the toal number of reads is 13000. It is almost 15 times more reads when executing query on PC2. The executed query is same on PC1 and PC2. Any reason for this?
I'm trying to analyse that query and reduce the number of logical reads as it's is too high but then I get completly different result on different PC.
Thanks.
The question is whether you are running it against the same dataset on the two PCs and if so how you moved/synchronised that data between the PCs. Also it is worth looking at the query plans to determine if it is using different strategies.
Can you provide more information?
|||Thanks your time and interest in this problem.
The dataset is the same as PC1 and PC2 are connected to the same server and same database. PC1 and PC2 are only client running on two different machines using the same backend.
PC1 is the server where an application is running and that application is running SP called ProcedureXX.
After I noticed huge number of reads is SQL Profiler, I executed the same procedure (ProcedureXX) in Query Analyzer
on PC2 (my PC) and I got completly different results.
Basicaly, both PCs are using same procedure on the same server and on the same database.
It's very strange. It looks like that application on PC1 is doing something extra but still reads are recorded against SQL statement.
Thanks
|||Below is the queryplan if it helps:
Execution Tree
--
Compute Scalar(DEFINE
[RSCData].[ICText]=[RSCData].[ICText]))
|--Sort(ORDER BY[Expr1018] ASC, [Expr1014] ASC, [RSCData].[ManuscriptID] ASC))
|--Compute Scalar(DEFINE[Expr1016]=rtrim(replace(replace(replace([ArticleTable].[FAuthor]+', '+[ArticleTable].[AddAuthors], ltrim(substring([ArticleTable].[FAuthor]+' , '+[ArticleTable].[AddAuthors], len([ArticleTable].[FAuthor]+' , '+[ArticleTable].[AddAuthors])-charindex(',', reverse([ArticleTable].[FAuthor]+' , '+[ArticleTable].[AddAuthors]), 1)+1, 1500)), replace(replace(' and '+substring([ArticleTable].[FAuthor]+', '+[ArticleTable].[AddAuthors], len([ArticleTable].[FAuthor]+', '+[ArticleTable].[AddAuthors])-charindex(',', reverse([ArticleTable].[FAuthor]+', '+[ArticleTable].[AddAuthors]), 1)+3, 1500)+'ICT_RSC', ' and ICT_RSC', ''), 'ICT_RSC', '')), '. and ', '.,'), 'and ,', '')), [Expr1018]=replace(replace(substring([RSCData].[ManuscriptID], 9, 1), 'X', '91'), 'P', '92')))
|--Nested Loops(Left Outer Join, OUTER REFERENCES[RSCData].[PriceCode]))
|--Compute Scalar(DEFINE[Expr1014]=Convert(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace([ArticleTable].[FPage], 'E', ''), 'J', ''), 'm', ''), '1-', ''), 'i', ''), 'K', ''), 'L', ''), '2-', ''), 'A', ''), 'F', ''), 'X', ''), 'G', ''), 'N', ''), 'R', ''), 'I', ''), 'V', ''), 'H', ''), 'P', ''), 'C', ''), 'D', ''), 'B', ''), 'Q', ''), '`', ''), 'S', ''), 'O', ''), '-', ''), 'T', ''), 'U', ''))))
| |--Bookmark Lookup(BOOKMARK[Bmk1004]), OBJECT[XlinkV2].[dbo].[Journal]))
| |--Nested Loops(Inner Join, OUTER REFERENCES[ArticleTable].[JournalID]))
| |--Hash Match(Inner Join, HASH[RSCData].[ArticleType])=([Expr1030]), RESIDUAL[RSCData].[ArticleType]=[Expr1030]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES[ArticleTable].[ArticleID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[RSCData].[ArticleID]))
| | | | |--Bookmark Lookup(BOOKMARK[Bmk1002]), OBJECT[XlinkV2].[dbo].[RSCData]))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[ArticleTable].[ArticleID]))
| | | | | |--Filter(WHERE[ArticleTable].[PubYear]=Convert([@.SubYear]) AND like([ArticleTable].[Volume], Convert([@.Volume]), NULL)))
| | | | | | |--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[XlinkV2].[dbo].[ArticleTable]))
| | | | | | |--Index Seek(OBJECT[XlinkV2].[dbo].[ArticleTable].[art_indx_linkexistandissue]), SEEK[ArticleTable].[LinkExists]=1 AND [ArticleTable].[Issue]=Convert([@.Issue])) ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT[XlinkV2].[dbo].[RSCData].[ind_TST_1]), SEEK[RSCData].[ArticleID]=[ArticleTable].[ArticleID] AND [RSCData].[SubYear]=Convert([@.SubYear])) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT[XlinkV2].[dbo].[ArticleTable].[ArticleTable11]), SEEK[ArticleTable].[ArticleID]=[RSCData].[ArticleID]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT[XlinkV2].[dbo].[RSCData].[MSt_RSCData_ArticleID]), SEEK[RSCData].[ArticleID]=[ArticleTable].[ArticleID]) ORDERED FORWARD)
| | |--Compute Scalar(DEFINE[Expr1030]=Convert([Manuscript_Code].[Manuscript_Code])))
| | |--Clustered Index Scan(OBJECT[XlinkV2].[dbo].[Manuscript_Code].[ind_Manuscript_Code]))
| |--Index Seek(OBJECT[XlinkV2].[dbo].[Journal].[Journal_JournalID_SerCode_CommonTitle_CorrectAbbrevTitle_ContentsPageURL]), SEEK[Journal].[JournalID]=[ArticleTable].[JournalID] AND [Journal].[SerCode]=Convert([@.sercode])) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT[XlinkV2].[dbo].[PriceCodeLookup].[ind_PriceCode]), SEEK[PriceCodeLookup].[PriceCode]=[RSCData].[PriceCode]) ORDERED FORWARD)
hi Alex,
can I suggest that you check the set options for each query to ensure that the values are the same, if not then very likely there are 2 different plans for this query. determine what options are set for each connection and replicate the options setup for the setopts value that works. this can be found in the syscacheobjects table in the master database
thanks
|||Thanks for your help. It was problem with the execution plan. The SP was dynamic and was executed from the same connection without RECOMPILE option.