Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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, September 18, 2014 5:08 AM
Points: 64, Visits: 519
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
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


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 4:50 PM
Points: 358, Visits: 876
Not enough information here, but I can guess that this query would benefit from a filter by date on table B. (At the least).
Post #1460823
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse