none
SQL Server slow joins query RRS feed

  • Question

  • Hi ! 

    I tried to join those 4 tables but i can see its very slow.

    I did indexed foreign keys,made a primary keys but still is very slow.

    Any help appreciated

    SET STATISTICS IO ON
    SELECT *
    FROM [dbo].[test_workstream] [workstream]
       --left JOIN [dbo].[test_status_report] [status_report] ON ([workstream].id = [status_report].sr_workstream)
       left JOIN [dbo].[test_risk] [risk] ON ([workstream].id = [risk].worksteam_id)
       left JOIN [dbo].[test_plan] [plan] ON ([workstream].id = [plan].[p_workstream])


    Statistics are : 

    (20585 rows affected)
    Table 'Worktable'. Scan count 26, logical reads 5304, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'test_risk'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'test_workstream'. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'test_plan'. Scan count 1, logical reads 45, physical reads 1, read-ahead reads 43, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row affected)


    USE [PMO_NEXUS_ERP_TEST]
    GO
    /****** Object:  Table [dbo].[test_plan]    Script Date: 12.07.2019 08:50:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[test_plan](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[p_completeness] [float] NOT NULL,
    	[p_status] [varchar](20) NOT NULL,
    	[p_workstream] [int] NOT NULL,
    	[p_main_workstream] [varchar](400) NOT NULL,
    	[p_task_type] [varchar](200) NOT NULL,
    	[p_task_name] [varchar](500) NOT NULL,
    	[p_assignee] [varchar](1000) NOT NULL,
    	[p_effort_work] [float] NULL,
    	[p_duration] [float] NOT NULL,
    	[p_start_date] [date] NULL,
    	[p_end_date] [date] NULL,
    	[p_workpackage] [varchar](500) NOT NULL,
    	[p_mandatory_phase] [nvarchar](50) NULL,
    	[p_deliverable_link] [varchar](500) NULL,
     CONSTRAINT [PK_test_plan] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[test_risk]    Script Date: 12.07.2019 08:50:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[test_risk](	[r_id] [varchar](10) NOT NULL,
    	[r_category] [varchar](200) NULL,
    	[r_subcategory] [varchar](200) NULL,
    	[r_title] [varchar](800) NOT NULL,
    	[r_response] [varchar](50) NOT NULL,
    	[r_response_strategy] [varchar](max) NOT NULL,
    	[worksteam_id] [int] NOT NULL,
    	[r_comment] [varchar](max) NOT NULL,
    	[r_submitter] [varchar](200) NOT NULL,
    	[r_owner] [varchar](200) NOT NULL,
    	[r_datecreated] [date] NOT NULL,
    	[r_status] [varchar](50) NOT NULL,
    	[r_assigned] [varchar](200) NOT NULL,
    	[r_target_resolution] [date] NOT NULL,
    	[r_last_review] [date] NOT NULL,
    	[r_risk_impact] [int] NOT NULL,
    	[r_risk_probability] [int] NOT NULL,
    	[r_risk_rating] [int] NOT NULL,
    	[r_review_impact] [int] NOT NULL,
    	[r_review_probability] [int] NOT NULL,
    	[r_review_rating] [int] NOT NULL,
    	[ID] [int] IDENTITY(1,1) NOT NULL,
     CONSTRAINT [PK_test_risk] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[test_status_report]    Script Date: 12.07.2019 08:50:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[test_status_report](
    	[sr_reporting_date] [varchar](200) NOT NULL,
    	[sr_reportingweek] [int] NOT NULL,
    	[sr_reportingyear] [int] NOT NULL,
    	[sr_status] [varchar](50) NOT NULL,
    	[sr_workstream] [int] NOT NULL,
    	[sr_workstream_lead] [varchar](100) NOT NULL,
    	[sr_overall_previous] [varchar](20) NOT NULL,
    	[sr_overall_actual] [varchar](20) NOT NULL,
    	[sr_scope_previous] [varchar](20) NOT NULL,
    	[sr_scope_actual] [varchar](20) NOT NULL,
    	[sr_time_previous] [varchar](20) NOT NULL,
    	[sr_time_actual] [varchar](20) NOT NULL,
    	[sr_team_previous] [varchar](20) NOT NULL,
    	[sr_team_actual] [varchar](20) NOT NULL,
    	[sr_quality_previous] [varchar](20) NOT NULL,
    	[sr_quality_actual] [varchar](20) NOT NULL,
    	[sr_risk_previous] [varchar](20) NOT NULL,
    	[sr_risk_actual] [varchar](20) NOT NULL,
    	[sr_key_risk] [varchar](max) NOT NULL,
    	[sr_working_results] [varchar](max) NOT NULL,
    	[sr_key_milestone] [varchar](max) NOT NULL,
    	[sr_up_task] [varchar](max) NOT NULL,
    	[sr_other_topics] [varchar](max) NOT NULL,
    	[sr_last_update] [varchar](10) NOT NULL,
    	[sr_completeness_actual] [numeric](10, 2) NOT NULL,
    	[sr_completeness_previous] [numeric](10, 2) NOT NULL,
    	[ID] [int] IDENTITY(1,1) NOT NULL,
     CONSTRAINT [PK_test_status_report] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    /****** Object:  Table [dbo].[test_workstream]    Script Date: 12.07.2019 08:50:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[test_workstream](
    	[w_active] [bit] NULL,
    	[w_name] [varchar](200) NOT NULL,
    	[w_type] [varchar](100) NOT NULL,
    	[w_reference] [varchar](100) NOT NULL,
    	[w_lead] [varchar](200) NOT NULL,
    	[w_completeness] [float] NULL,
    	[w_target] [float] NULL,
    	[w_startdate] [date] NULL,
    	[w_enddate] [date] NULL,
    	[w_itemtype] [nvarchar](50) NULL,
    	[w_path] [nvarchar](max) NULL,
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[w_reporting] [bit] NULL,
     CONSTRAINT [PK_test_workstream] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[test_plan] ADD  CONSTRAINT [test_DF_plan_p_mandatory_phase]  DEFAULT (N'not mandatory') FOR [p_mandatory_phase]
    GO
    ALTER TABLE [dbo].[test_risk] ADD  CONSTRAINT [test_DF_risk_r_risk_impact]  DEFAULT ((0)) FOR [r_risk_impact]
    GO
    ALTER TABLE [dbo].[test_risk] ADD  CONSTRAINT [test_DF_risk_r_risk_probability]  DEFAULT ((0)) FOR [r_risk_probability]
    GO
    ALTER TABLE [dbo].[test_risk] ADD  CONSTRAINT [test_DF_risk_r_risk_rating]  DEFAULT ((0)) FOR [r_risk_rating]
    GO
    ALTER TABLE [dbo].[test_status_report] ADD  CONSTRAINT [test_DF_status_report_sr_completeness_act]  DEFAULT ((0)) FOR [sr_completeness_actual]
    GO
    ALTER TABLE [dbo].[test_status_report] ADD  CONSTRAINT [test_DF_status_report_sr_completeness_previous]  DEFAULT ((0)) FOR [sr_completeness_previous]
    GO
    ALTER TABLE [dbo].[test_workstream] ADD  CONSTRAINT [test_DF_workstream_w_active]  DEFAULT ((1)) FOR [w_active]
    GO
    ALTER TABLE [dbo].[test_workstream] ADD  CONSTRAINT [test_DF_workstream_w_completeness_active]  DEFAULT ((0)) FOR [w_completeness]
    GO
    ALTER TABLE [dbo].[test_workstream] ADD  CONSTRAINT [test_DF_workstream_w_reporting]  DEFAULT ((1)) FOR [w_reporting]
    GO
    ALTER TABLE [dbo].[test_plan]  WITH CHECK ADD  CONSTRAINT [FK_test_plan_test_workstream] FOREIGN KEY([p_workstream])
    REFERENCES [dbo].[test_workstream] ([ID])
    GO
    ALTER TABLE [dbo].[test_plan] CHECK CONSTRAINT [FK_test_plan_test_workstream]
    GO
    ALTER TABLE [dbo].[test_risk]  WITH CHECK ADD  CONSTRAINT [FK_test_risk_test_workstream] FOREIGN KEY([worksteam_id])
    REFERENCES [dbo].[test_workstream] ([ID])
    GO
    ALTER TABLE [dbo].[test_risk] CHECK CONSTRAINT [FK_test_risk_test_workstream]
    GO
    ALTER TABLE [dbo].[test_status_report]  WITH CHECK ADD  CONSTRAINT [FK_test_status_report_test_workstream] FOREIGN KEY([sr_workstream])
    REFERENCES [dbo].[test_workstream] ([ID])
    GO
    ALTER TABLE [dbo].[test_status_report] CHECK CONSTRAINT [FK_test_status_report_test_workstream]
    GO
    

    Friday, July 12, 2019 7:03 AM

Answers

All replies

  • StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
    SELECT *																	
    FROM [dbo].[test_workstream] [workstream]																	
       --left JOIN [dbo].[test_status_report] [status_report] ON ([workstream].id = [status_report].sr_workstream)																	
       left JOIN [dbo].[test_risk] [risk] ON ([workstream].id = [risk].worksteam_id)																	
       left JOIN [dbo].[test_plan] [plan] ON ([workstream].id = [plan].[p_workstream])	1	1	0	NULL	NULL	1	NULL	6510,334	NULL	NULL	NULL	0,1430178	NULL	NULL	SELECT	0	NULL
      |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([plan].[p_workstream])=([workstream].[ID]), RESIDUAL:([PMO_NEXUS_ERP_TEST].[dbo].[test_workstream].[ID] as [workstream].[ID]=[PMO_NEXUS_ERP_TEST].[dbo].[test_plan].[p_workstream] as [plan].[p_workstream]))	1	2	1	Merge Join	Right Outer Join	MANY-TO-MANY MERGE:([plan].[p_workstream])=([workstream].[ID]), RESIDUAL:([PMO_NEXUS_ERP_TEST].[dbo].[test_workstream].[ID] as [workstream].[ID]=[PMO_NEXUS_ERP_TEST].[dbo].[test_plan].[p_workstream] as [plan].[p_workstream])	NULL	6510,334	0,007825	0,02595552	15158	0,1430178	[workstream].[w_active], [workstream].[w_name], [workstream].[w_type], [workstream].[w_reference], [workstream].[w_lead], [workstream].[w_completeness], [workstream].[w_target], [workstream].[w_startdate], [workstream].[w_enddate], [workstream].[w_itemtype], [workstream].[w_path], [workstream].[ID], [workstream].[w_reporting], [risk].[r_id], [risk].[r_category], [risk].[r_subcategory], [risk].[r_title], [risk].[r_response], [risk].[r_response_strategy], [risk].[worksteam_id], [risk].[r_comment], [risk].[r_submitter], [risk].[r_owner], [risk].[r_datecreated], [risk].[r_status], [risk].[r_assigned], [risk].[r_target_resolution], [risk].[r_last_review], [risk].[r_risk_impact], [risk].[r_risk_probability], [risk].[r_risk_rating], [risk].[r_review_impact], [risk].[r_review_probability], [risk].[r_review_rating], [risk].[ID], [plan].[ID], [plan].[p_completeness], [plan].[p_status], [plan].[p_workstream], [plan].[p_main_workstream], [plan].[p_task_type], [plan].[p_task_name], [plan].[p_assignee], [plan].[p_effort_work], [plan].[p_duration], [plan].[p_start_date], [plan].[p_end_date], [plan].[p_workpackage], [plan].[p_mandatory_phase], [plan].[p_deliverable_link]	NULL	PLAN_ROW	0	1
           |--Sort(ORDER BY:([plan].[p_workstream] ASC))	1	3	2	Sort	Sort	ORDER BY:([plan].[p_workstream] ASC)	NULL	1768	0,01126126	0,02985557	1674	0,07745475	[plan].[ID], [plan].[p_completeness], [plan].[p_status], [plan].[p_workstream], [plan].[p_main_workstream], [plan].[p_task_type], [plan].[p_task_name], [plan].[p_assignee], [plan].[p_effort_work], [plan].[p_duration], [plan].[p_start_date], [plan].[p_end_date], [plan].[p_workpackage], [plan].[p_mandatory_phase], [plan].[p_deliverable_link]	NULL	PLAN_ROW	0	1
           |    |--Clustered Index Scan(OBJECT:([PMO_NEXUS_ERP_TEST].[dbo].[test_plan].[PK_test_plan] AS [plan]))	1	4	3	Clustered Index Scan	Clustered Index Scan	OBJECT:([PMO_NEXUS_ERP_TEST].[dbo].[test_plan].[PK_test_plan] AS [plan])	[plan].[ID], [plan].[p_completeness], [plan].[p_status], [plan].[p_workstream], [plan].[p_main_workstream], [plan].[p_task_type], [plan].[p_task_name], [plan].[p_assignee], [plan].[p_effort_work], [plan].[p_duration], [plan].[p_start_date], [plan].[p_end_date], [plan].[p_workpackage], [plan].[p_mandatory_phase], [plan].[p_deliverable_link]	1768	0,03423611	0,0021018	1674	0,03633791	[plan].[ID], [plan].[p_completeness], [plan].[p_status], [plan].[p_workstream], [plan].[p_main_workstream], [plan].[p_task_type], [plan].[p_task_name], [plan].[p_assignee], [plan].[p_effort_work], [plan].[p_duration], [plan].[p_start_date], [plan].[p_end_date], [plan].[p_workpackage], [plan].[p_mandatory_phase], [plan].[p_deliverable_link]	NULL	PLAN_ROW	0	1
           |--Merge Join(Left Outer Join, MERGE:([workstream].[ID])=([risk].[worksteam_id]), RESIDUAL:([PMO_NEXUS_ERP_TEST].[dbo].[test_workstream].[ID] as [workstream].[ID]=[PMO_NEXUS_ERP_TEST].[dbo].[test_risk].[worksteam_id] as [risk].[worksteam_id]))	1	5	2	Merge Join	Left Outer Join	MERGE:([workstream].[ID])=([risk].[worksteam_id]), RESIDUAL:([PMO_NEXUS_ERP_TEST].[dbo].[test_workstream].[ID] as [workstream].[ID]=[PMO_NEXUS_ERP_TEST].[dbo].[test_risk].[worksteam_id] as [risk].[worksteam_id])	NULL	174	0	0,0060248	13492	0,03177949	[workstream].[w_active], [workstream].[w_name], [workstream].[w_type], [workstream].[w_reference], [workstream].[w_lead], [workstream].[w_completeness], [workstream].[w_target], [workstream].[w_startdate], [workstream].[w_enddate], [workstream].[w_itemtype], [workstream].[w_path], [workstream].[ID], [workstream].[w_reporting], [risk].[r_id], [risk].[r_category], [risk].[r_subcategory], [risk].[r_title], [risk].[r_response], [risk].[r_response_strategy], [risk].[worksteam_id], [risk].[r_comment], [risk].[r_submitter], [risk].[r_owner], [risk].[r_datecreated], [risk].[r_status], [risk].[r_assigned], [risk].[r_target_resolution], [risk].[r_last_review], [risk].[r_risk_impact], [risk].[r_risk_probability], [risk].[r_risk_rating], [risk].[r_review_impact], [risk].[r_review_probability], [risk].[r_review_rating], [risk].[ID]	NULL	PLAN_ROW	0	1
                |--Clustered Index Scan(OBJECT:([PMO_NEXUS_ERP_TEST].[dbo].[test_workstream].[PK_test_workstream] AS [workstream]), ORDERED FORWARD)	1	6	5	Clustered Index Scan	Clustered Index Scan	OBJECT:([PMO_NEXUS_ERP_TEST].[dbo].[test_workstream].[PK_test_workstream] AS [workstream]), ORDERED FORWARD	[workstream].[w_active], [workstream].[w_name], [workstream].[w_type], [workstream].[w_reference], [workstream].[w_lead], [workstream].[w_completeness], [workstream].[w_target], [workstream].[w_startdate], [workstream].[w_enddate], [workstream].[w_itemtype], [workstream].[w_path], [workstream].[ID], [workstream].[w_reporting]	45	0,003865741	0,0002065	4423	0,004072241	[workstream].[w_active], [workstream].[w_name], [workstream].[w_type], [workstream].[w_reference], [workstream].[w_lead], [workstream].[w_completeness], [workstream].[w_target], [workstream].[w_startdate], [workstream].[w_enddate], [workstream].[w_itemtype], [workstream].[w_path], [workstream].[ID], [workstream].[w_reporting]	NULL	PLAN_ROW	0	1
                |--Sort(ORDER BY:([risk].[worksteam_id] ASC))	1	7	5	Sort	Sort	ORDER BY:([risk].[worksteam_id] ASC)	NULL	149	0,01126126	0,001787101	9077	0,02167945	[risk].[r_id], [risk].[r_category], [risk].[r_subcategory], [risk].[r_title], [risk].[r_response], [risk].[r_response_strategy], [risk].[worksteam_id], [risk].[r_comment], [risk].[r_submitter], [risk].[r_owner], [risk].[r_datecreated], [risk].[r_status], [risk].[r_assigned], [risk].[r_target_resolution], [risk].[r_last_review], [risk].[r_risk_impact], [risk].[r_risk_probability], [risk].[r_risk_rating], [risk].[r_review_impact], [risk].[r_review_probability], [risk].[r_review_rating], [risk].[ID]	NULL	PLAN_ROW	0	1
                     |--Clustered Index Scan(OBJECT:([PMO_NEXUS_ERP_TEST].[dbo].[test_risk].[PK_test_risk] AS [risk]))	1	8	7	Clustered Index Scan	Clustered Index Scan	OBJECT:([PMO_NEXUS_ERP_TEST].[dbo].[test_risk].[PK_test_risk] AS [risk])	[risk].[r_id], [risk].[r_category], [risk].[r_subcategory], [risk].[r_title], [risk].[r_response], [risk].[r_response_strategy], [risk].[worksteam_id], [risk].[r_comment], [risk].[r_submitter], [risk].[r_owner], [risk].[r_datecreated], [risk].[r_status], [risk].[r_assigned], [risk].[r_target_resolution], [risk].[r_last_review], [risk].[r_risk_impact], [risk].[r_risk_probability], [risk].[r_risk_rating], [risk].[r_review_impact], [risk].[r_review_probability], [risk].[r_review_rating], [risk].[ID]	149	0,008310185	0,0003209	9077	0,008631085	[risk].[r_id], [risk].[r_category], [risk].[r_subcategory], [risk].[r_title], [risk].[r_response], [risk].[r_response_strategy], [risk].[worksteam_id], [risk].[r_comment], [risk].[r_submitter], [risk].[r_owner], [risk].[r_datecreated], [risk].[r_status], [risk].[r_assigned], [risk].[r_target_resolution], [risk].[r_last_review], [risk].[r_risk_impact], [risk].[r_risk_probability], [risk].[r_risk_rating], [risk].[r_review_impact], [risk].[r_review_probability], [risk].[r_review_rating], [risk].[ID]	NULL	PLAN_ROW	0	1
    

    Friday, July 12, 2019 7:07 AM
  • Review the execution plan to see wether the Indexes are used or not: Display an Actual Execution Plan

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 12, 2019 7:09 AM
    Moderator
  • is there any way to export this execution plan so You could look at it ?

    i am not able to upload images yet here

    Friday, July 12, 2019 8:17 AM
  • Do not you have a WHERE condition? 

    Please do not use SELECT * but specify only need columns

    How large are the tables?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, July 14, 2019 4:27 AM
    Moderator
  • Hi Uri,

    For this join i am having 20 000rows and its taking more than 20 seconds to get query result.

    (if i join commented table which i would like to do too its getting a lot longer )

    SELECT *
    FROM [dbo].[test_workstream] [workstream]
       --left JOIN [dbo].[test_status_report] [status_report] ON ([workstream].id = [status_report].sr_workstream)
       left JOIN [dbo].[test_risk] [risk] ON ([workstream].id = [risk].worksteam_id)
       left JOIN [dbo].[test_plan] [plan] ON ([workstream].id = [plan].[p_workstream])

    I am not able to post here query exec plan

    Any tips appreciated




    Tuesday, July 16, 2019 4:09 PM
  • Are you sure you need LEFT JOIN Test_status_report?

    Please have an index on Test_status_report (sr_workstream)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 17, 2019 4:02 AM
    Moderator
  • yes i still need to left join all of those tables.

    having an index to sr_workstream does not improve it at all.

    Whats strange for me is that i have a PROD version of those tables on the same database (without test_ prefix) without any indexes,keys and i got results same as fast as in test version with indexes pks etc.


    Wednesday, July 17, 2019 2:51 PM
  • O this is great news, can you compare two execution plans ? Is it on the same server? Update stats and see how was it going

    DECLARE @tablename varchar(80),@shemaname varchar(80)
    DECLARE @SQL AS NVARCHAR(200)
    DECLARE TblName_cursor CURSOR FOR
    SELECT t.name,s.name FROM sys.tables t join sys.schemas s
    on s.schema_id=t.schema_id


    OPEN TblName_cursor

    FETCH NEXT FROM TblName_cursor
    INTO @tablename,@shemaname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'

    EXEC sp_executesql @statement = @SQL

       FETCH NEXT FROM TblName_cursor
       INTO @tablename,@shemaname
    END

    CLOSE TblName_cursor
    DEALLOCATE TblName_cursor


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 18, 2019 3:57 AM
    Moderator