Story start with a call as i was oncall that weekend. On Sunday evening , Application support team called DBA team and tell that one of the job which was working all fine till yesterday , today it is running since last 3 hours. Normally, it takes approx 10 mins to complete.
My reaction was, there may be blocking or any optimization job may be running (Usually maintenance job runs over weekend) . Let me have a look.
I checked and found that indexes had been rebuilt on that instance and Resources utilization was also normal. There were no blocking at all.
While i was analyzing , Application team told that Job got completed but we still need to analyse to avoid issues in weekdays.
My second question was, whether there were any changes which were implemented recently. I got reply "Friday implementation added two new columns in a table which is part of query and job is an informatica Job". As it was informatica job, I asked for actual SQL code to get to know that whether problem is with SQL Server databases or with informatica server.
I ran SQL code on prod DB to capture estimate and actual execution plans and get IO and CPU statistics. I concluded that stats are updated as estimate and actual execution plans are almost same.
I decided to compare with non-prod DBs as Data volume is comparable in Dev and Prod both the environments . Query ran all fine on dev DB ( took less than 10 mins) however Execution plans are different.
While comparing both execution plans, i observed that key lookup is there on prod for same table on which 2 columns were added and that gave me clue.
I further checked the query and found that part where one column was mentioned in where clause but there was no index on it while other colomns which were part of lookup and index scan were being selected based upon colomn mentioned in where clause.
I added one covering indexes and added those 2 columns in include clause and that worked like a magic.
CREATE INDEX [IX_dsc] ON [dbo].[case_t] ([case_typ_dsc]) INCLUDE ([cd], [id])
While dealing with above mentioned scenario,I case across a awesome article written by Denny Cherry (One of my favorite) and You should also read that.
https://redmondmag.com/articles/2013/12/11/slow-running-sql-queries.aspx
Hope it will help.
My reaction was, there may be blocking or any optimization job may be running (Usually maintenance job runs over weekend) . Let me have a look.
I checked and found that indexes had been rebuilt on that instance and Resources utilization was also normal. There were no blocking at all.
While i was analyzing , Application team told that Job got completed but we still need to analyse to avoid issues in weekdays.
My second question was, whether there were any changes which were implemented recently. I got reply "Friday implementation added two new columns in a table which is part of query and job is an informatica Job". As it was informatica job, I asked for actual SQL code to get to know that whether problem is with SQL Server databases or with informatica server.
I ran SQL code on prod DB to capture estimate and actual execution plans and get IO and CPU statistics. I concluded that stats are updated as estimate and actual execution plans are almost same.
I decided to compare with non-prod DBs as Data volume is comparable in Dev and Prod both the environments . Query ran all fine on dev DB ( took less than 10 mins) however Execution plans are different.
While comparing both execution plans, i observed that key lookup is there on prod for same table on which 2 columns were added and that gave me clue.
I further checked the query and found that part where one column was mentioned in where clause but there was no index on it while other colomns which were part of lookup and index scan were being selected based upon colomn mentioned in where clause.
I added one covering indexes and added those 2 columns in include clause and that worked like a magic.
CREATE INDEX [IX_dsc] ON [dbo].[case_t] ([case_typ_dsc]) INCLUDE ([cd], [id])
While dealing with above mentioned scenario,I case across a awesome article written by Denny Cherry (One of my favorite) and You should also read that.
https://redmondmag.com/articles/2013/12/11/slow-running-sql-queries.aspx
Hope it will help.
Lucky Club Casino Site | Live Dealer Casino Games
ReplyDeleteLucky Club luckyclub Casino is a live casino site with a large and varied selection of games and promotions, and a world-class gambling library. Rating: 3.9 · Review by LuckyClub.live