Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performce SP Expand / Collapse
Author
Message
Posted Thursday, February 28, 2013 6:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 1, 2016 4:57 PM
Points: 68, Visits: 617
Hi ,


One of SP is having below code and it is running more than 2 hrs. This job is daily schedule.

Table_B is truncate and load from souce systems on daily basis.
Number of rows in Table b is morethan 33000000.

On TableA 3 non-clustered indexes are already created.

UPDATE STATISTICS dbo.Table_A
UPDATE STATISTICS dbo.Table_B

Insert into Table_A
(columns...)
Select columns... from Table_B B1

LEFT JOIN Table_A A1 (NOLOCK)
ON B1.[Column1] = A1.[Column1]
AND A1.[SDate] IS NULL
WHERE A1.[Column1] IS NULL

udpate Table_a
.............
.............

I am not sure where I need to debug this issue. While running sp I ran the Profiler on database and ran the DB Tunning Advisor also. But I got only one recommandation on one column on Table A. This column is already tide with non-clustered index . Can any one please advice..
Thanks..
Post #1425031
Posted Thursday, February 28, 2013 7:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 16,135, Visits: 16,836
There is nowhere near enough information to provide anything other than a shot in the dark. Please read the following article about what to post for performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1425043
Posted Tuesday, March 12, 2013 10:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 17, 2013 10:18 PM
Points: 21, Visits: 133
can u please send me your complete SP.
Post #1430204
Posted Thursday, June 6, 2013 11:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 2, 2016 4:16 PM
Points: 988, Visits: 1,923
Not enough information here, but I can guess that this query would benefit from a filter by date on table B. (At the least).

----------------------------------------------------
How to post forum questions to get the best help
Post #1460823
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse