June 15, 2017 at 2:47 am
Hi All,
I have a sql statement working on static tables (no Insert,Update,Delete) on our testing server it's running very fast almost 0 m sec execution time while on production servers it takes much more ranging from 3 to 9 secs depending on where conditions.
I suspected the indexes could be fragmented or statistics might be out dated so i made rebuild indexes for all tables used in the SQL.
Also i compared index structure on all tables in both databases and both are the same.
I attached the execution plan for slow and fast servers and ready to post anything else that might be needed to determine what's reason for that strange behaviour.
Looking forward for you help.
Regards
Nader
June 15, 2017 at 2:54 am
Is it getting blocked by other queries on production?
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
June 15, 2017 at 3:01 am
Thanks for yo
GilaMonster - Thursday, June 15, 2017 2:54 AMIs it getting blocked by other queries on production?
Thanks for your reply.
I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).
June 15, 2017 at 3:01 am
Is the physical hardware and configuration of both servers identical?
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 15, 2017 at 3:05 am
nadersam - Thursday, June 15, 2017 3:01 AMGilaMonster - Thursday, June 15, 2017 2:54 AMIs it getting blocked by other queries on production?I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).
Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.
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
June 15, 2017 at 3:25 am
ThomasRushton - Thursday, June 15, 2017 3:01 AMIs the physical hardware and configuration of both servers identical?
The live server is much much higher configuration , in fact that's the only SQL that perform slower in live environment that test.
June 15, 2017 at 3:28 am
GilaMonster - Thursday, June 15, 2017 3:05 AMnadersam - Thursday, June 15, 2017 3:01 AMGilaMonster - Thursday, June 15, 2017 2:54 AMIs it getting blocked by other queries on production?I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).
Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.
Let me please make sure i understand you correctly, do you mean that normal select SQLs cause locks which might be the reason for that slow performance?
If so could i add with (nolock) to all tables used in my SQL.
Thanks
June 15, 2017 at 3:31 am
From the execution plan does it show where is the bottle neck coming from?
June 15, 2017 at 3:33 am
nadersam - Thursday, June 15, 2017 3:28 AMGilaMonster - Thursday, June 15, 2017 3:05 AMnadersam - Thursday, June 15, 2017 3:01 AMGilaMonster - Thursday, June 15, 2017 2:54 AMIs it getting blocked by other queries on production?I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).
Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.
Let me please make sure i understand you correctly, do you mean that normal select SQLs cause locks which might be the reason for that slow performance?
Well, yes. Queries take locks to ensure that they read correct data.
If so could i add with (nolock) to all tables used in my SQL.
You could... You'd probably cause more problems than you solve due to the incorrect results that nolock causes, but if you're happy with your users getting incorrect data fast, then go ahead.
Or, you could check and see whether you have blocking first, and if so take steps to fix the affected queries.
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
June 15, 2017 at 3:55 am
GilaMonster - Thursday, June 15, 2017 3:33 AMnadersam - Thursday, June 15, 2017 3:28 AMGilaMonster - Thursday, June 15, 2017 3:05 AMnadersam - Thursday, June 15, 2017 3:01 AMGilaMonster - Thursday, June 15, 2017 2:54 AMIs it getting blocked by other queries on production?I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).
Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.
Let me please make sure i understand you correctly, do you mean that normal select SQLs cause locks which might be the reason for that slow performance?
Well, yes. Queries take locks to ensure that they read correct data.
If so could i add with (nolock) to all tables used in my SQL.
You could... You'd probably cause more problems than you solve due to the incorrect results that nolock causes, but if you're happy with your users getting incorrect data fast, then go ahead.
Or, you could check and see whether you have blocking first, and if so take steps to fix the affected queries.
If the data in those tables are static would the with (nolock) hint still cause a problem?
If i found any blocking happening , what should be the solution knowing that index wise they have got all what they need.
Thanks again
June 15, 2017 at 4:00 am
nadersam - Thursday, June 15, 2017 3:55 AMGilaMonster - Thursday, June 15, 2017 3:33 AMnadersam - Thursday, June 15, 2017 3:28 AMGilaMonster - Thursday, June 15, 2017 3:05 AMnadersam - Thursday, June 15, 2017 3:01 AMGilaMonster - Thursday, June 15, 2017 2:54 AMIs it getting blocked by other queries on production?I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).
Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.
Let me please make sure i understand you correctly, do you mean that normal select SQLs cause locks which might be the reason for that slow performance?
Well, yes. Queries take locks to ensure that they read correct data.
If so could i add with (nolock) to all tables used in my SQL.
You could... You'd probably cause more problems than you solve due to the incorrect results that nolock causes, but if you're happy with your users getting incorrect data fast, then go ahead.
Or, you could check and see whether you have blocking first, and if so take steps to fix the affected queries.
If the data in those tables are static would the with (nolock) hint still cause a problem?
If i found any blocking happening , what should be the solution knowing that index wise they have got all what they need.Thanks again
DO NOT USE NOLOCK!!!!!!!!!!!!!
It's not a solution, it's hiding problems that you can fix in far better ways (if the table's static, you don't need nolock, as static means no inserts, updates, deletes, and hence nothing to bloc or be blocked by the read queries)
And, please can you go and check whether the slow delete performance that you started the thread to ask about is caused by blocking.
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
June 15, 2017 at 4:22 am
I have logged in to live environment and monitored if there are blocks happening and didn't find any.
I think there is a small misunderstanding, there is no delete issue in fact no delete at all as those tables are static only select.
Not sure where i should i check else now.
Thanks Gail
June 15, 2017 at 4:29 am
Sorry, that was me mis-reading. I could have sworn that the execution plan you posted was for a delete. Apologies.
Ok, so no blocking. Does the query go suspended at all while running? If so, what's the wait type?
Do test and production have the same amount of data in these tables?
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
June 15, 2017 at 4:37 am
GilaMonster - Thursday, June 15, 2017 4:29 AMSorry, that was me mis-reading. I could that the execution plan you posted was for a delete. Apologies.Ok, so no blocking. Does the query go suspended at all while running? If so, what's the wait type?
Do test and production have the same amount of data in these tables?
I tried to find the wait type when running the sql from the activity monitor but no wait type showed and yes the data is the same.
June 20, 2017 at 3:16 am
i was able to narrow down the problem to a certain join causing this problem, i will show you below the query before and after i changed it.
The question is why is it behaving like that although the tables it joins with are 2000 or 3000 records.
If i run the part
SELECT DISTINCT GetDrug2DrugInteractions.DRUGINTERACTIONID, GetDrug2DrugInteractions.SEVERITYID,
GetDrug2DrugInteractions.IMPLICITROUTEID, GetDrug2DrugInteractions.DSMROUTEID,INTERACTINGCPNUM FROM GetDrug2DrugInteractions
WHERE GetDrug2DrugInteractions.CP_NUM IN (2144) AND GetDrug2DrugInteractions.INTERACTINGCPNUM IN (44,270,565)
it takes no time and IO is very small and thats the part where the tables with big numbers of rows are there, but if i join it with GenericName and the the rest of the tables which are very small, things become very slow and very high IO.
I changed the first part to CTE and then join it with the rest and that solved the problem but not sure why 🙂
Before
SELECT DISTINCT GetDrug2DrugInteractions.DRUGINTERACTIONID, trade_drug.trade_name, GetDrug2DrugInteractions.SEVERITYID, trade_drug.trade_code,
GetDrug2DrugInteractions.IMPLICITROUTEID, GetDrug2DrugInteractions.DSMROUTEID FROM TradeName_GenericName INNER JOIN GenericName ON
TradeName_GenericName.GenericName_ID = GenericName.GenericName_ID INNER JOIN trade_drug ON TradeName_GenericName.TradeName_ID = trade_drug.trade_code
INNER JOIN GetDrug2DrugInteractions ON GenericName.CPNUM = GetDrug2DrugInteractions.INTERACTINGCPNUM
WHERE (GetDrug2DrugInteractions.CP_NUM IN (2144)) AND (GetDrug2DrugInteractions.INTERACTINGCPNUM IN (44,270,565))
AND (trade_drug.trade_code IN (227,540,1750)) AND (GenericName.CPNUM > 0)
ORDER BY GetDrug2DrugInteractions.SEVERITYID
After
with DI AS (
SELECT DISTINCT GetDrug2DrugInteractions.DRUGINTERACTIONID, GetDrug2DrugInteractions.SEVERITYID,
GetDrug2DrugInteractions.IMPLICITROUTEID, GetDrug2DrugInteractions.DSMROUTEID,INTERACTINGCPNUM FROM GetDrug2DrugInteractions
WHERE GetDrug2DrugInteractions.CP_NUM IN (2144) AND GetDrug2DrugInteractions.INTERACTINGCPNUM IN (44,270,565)
)
select DI.*, trade_drug.trade_name, trade_drug.trade_code FROM TradeName_GenericName INNER JOIN GenericName ON
TradeName_GenericName.GenericName_ID = GenericName.GenericName_ID INNER JOIN trade_drug ON TradeName_GenericName.TradeName_ID = trade_drug.trade_code
INNER JOIN DI ON GenericName.CPNUM = DI.INTERACTINGCPNUM
where (trade_drug.trade_code IN (99,236,2221)) AND (GenericName.CPNUM > 0)
Regards
Nader
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply