SQL Managed Instance Performance Issue

sai saketh guduru 1

I have created a SQL Managed Instance and I have Migrated all my Dev data into managed instance. After migrating I am running query in Dev which is taking 1 Minute to complete the Query at the same time when I run query in Managed Instance it is taking 12 Minutes. I have Increased Cores Increased File Size everything. Checked in all possible ways but still the issue persists.

Could someone help me on this?

GeethaThatipatri-MSFT 29,382 Reputation points Microsoft Employee

2022-12-07T09:04:49.577+00:00

@sai saketh guduru Welcome to the Microsoft Q&A forum, Thanks for posting your question.
Can you please share the query you are trying to execute?

Regards
Geetha
sai saketh guduru 1 Reputation point

2022-12-07T09:36:10.22+00:00

Hello @GeethaThatipatri-MSFT Thanks for looking into my issue please find the query

SELECT MAX(RCS.RECORDTIME) EM_RECORD_TS, MAX(ISS.EM_LOAD_TS) EM_LOAD_TS, RCS.JOBID IS_JOB_ID, RCS.SESSIONID IS_SESSION_ID, MAX(ISS.IS_SERVER_ID) IS_SERVER_ID, CASE WHEN IDT.IS_DB_TAB_ID IS NULL THEN -1 ELSE IDT.IS_DB_TAB_ID END IS_TABLE_ID, CASE WHEN IC.IS_COL_ID IS NULL THEN -1 ELSE IC.IS_COL_ID END IS_COL_ID, MAX(IC.IS_COL_NAME) IS_COL_NAME, RCS.SQLCLAUSETYPEID SQL_CLAUSE_TYPE_ID, MAX(RCS.COUNTER) COUNTER, MAX(RF.IS_PROJ_ID) IS_PROJ_ID, MAX(RCS.DAY_ID) DAY_ID, MAX(RCS.HOUR_ID) HOUR_ID, MAX(RCS.MINUTE_ID) MINUTE_ID, CASE WHEN MD.REP_ID IS NOT NULL THEN MD.REP_ID ELSE -1 END IS_REPOSITORY_ID FROM IS_REP_COL_STATS RCS JOIN IS_SESSION ISS ON RCS.SESSIONID = ISS.IS_SESSION_ID JOIN IS_REP_FACT RF ON RF.IS_REP_JOB_SES_ID = RCS.SESSIONID AND RF.IS_REP_JOB_ID = RCS.JOBID JOIN IS_DB_TAB IDT ON IDT.IS_DB_TAB_GUID = RCS.TABLEID AND IDT.IS_PROJ_ID = RF.IS_PROJ_ID JOIN IS_COL IC ON IC.IS_COL_GUID = RCS.COLUMNID AND IC.IS_COL_NAME = RCS.COLUMNNAME AND IC.IS_PROJ_ID = RF.IS_PROJ_ID LEFT JOIN EM_MD MD ON MD.REP_GUID = RCS.REPOSITORYID WHERE RF.IS_CACHE_HIT_ID = 0 GROUP BY RCS.SESSIONID, RCS.JOBID, IDT.IS_DB_TAB_ID, IC.IS_COL_ID, RCS.SQLCLAUSETYPEID, MD.REP_ID

GO
sai saketh guduru 1 Reputation point

2022-12-07T09:36:51.33+00:00

This Query which is running for 1 Minute in Dev database and 9 minutes in Managed Instance
GeethaThatipatri-MSFT 29,382 Reputation points Microsoft Employee

2022-12-08T10:07:22.49+00:00

@sai saketh guduru I would start with comparing query plans and IO consumption (set statistics io on)

Regards
Geetha

Share via

SQL Managed Instance Performance Issue

Your answer