Tables Involved in Stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Tables Involved in Stored procedure

13 posts, Page 1 of 2

1 2 »»

Tables Involved in Stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

OnlyOneRJ

Posted Thursday, January 24, 2013 4:24 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

A stored Procedure which use to execute in 10 minutes, now it takes 1 hour to execute w00t

the data inside the Tables are same, i mean the volume of data is same as previous..
please suggest how do i make this execute fast???

Please help me with any query which will tell me what needs to be done?

************************************
Every Dog has a Tail !!!!!

Post #1411053

anthony.green

Posted Thursday, January 24, 2013 4:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

check the execution plan, update all statistics rebuild all indexes

also check out the links in my signature on posting performance problems

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1411055

OnlyOneRJ

Posted Thursday, January 24, 2013 4:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

Update statistics?

Hmm i have never done Update statistics on Database yet..

Please correct my belwo plan..

Sunday no one wotks on the Database
will perfom the below activity..

1) Full Backup - Using manitenencae plan
2) Transaction Log Backup - Using manitenencae plan
3) Truncate Logs (this grows up to GB every day Angry

)
4) Update Statistics & then Rebuild Indexes - Using manitenencae plan

Please suggest

************************************
Every Dog has a Tail !!!!!

Post #1411058

OnlyOneRJ

Posted Thursday, January 24, 2013 4:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

Also please tell me how do i set the execution plan for that Stored procedure so that i will suggest developer to look into the specific query which is slowing the entire execution.

************************************
Every Dog has a Tail !!!!!

Post #1411063

anthony.green

Posted Thursday, January 24, 2013 5:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Truncating your log files, eeesh, you need to put some proper transaction log management in place, regular shrinking of files is not best practise.

http://www.sqlservercentral.com/stairway/73776/
Also get a copy of the accidental DBA guide and read through all of it, again link in my signature for that eBook - has a full section on transaction logs and other main issues faced.

Don't use maintenance plans, they are very static in what do and dont provide any flexability, instead look at Ola's scripts (Link in my signature) they will do all your maintenance needs, backups, index rebuilds, statistic updates etc.

As for getting the execution plan, again look in my signature on posting performance problems, it tells you how to get the plan.

Post #1411067

MasterDB

Posted Thursday, January 24, 2013 5:17 AM

Old Hand

Group: General Forum Members
Last Login: Saturday, February 09, 2013 3:44 PM
Points: 345, Visits: 565

Check the fragmentation of all indexes by using sys.dm_index_usage_physical_stats.
If the fragmentation is more than 35% --Rebuild all the indices.

Ask the users about any change of code..? If so, Check for missing indices by using sys.dm_missing_index_details.

or else, save the SP to .sql file and open DTA(Database Tuning Advisor) and load this .sql file under workload option select your database from the list which shows below and click on Start Analysis, If you got the recommendations more than 65% apply all the recommendations provided SQL Server.

Thanks,

Post #1411074

anthony.green

Posted Thursday, January 24, 2013 5:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Take what the missing index view and DTA say with a pinch of salt, just dont apply anything it says at think it will work.

Your best off running DTA against a full workload, not just a single query, as DTA may advise on things which can impact other processes.

Run on a dev system, test it, test anything else which uses the tables you have just changed, then do the same on a test system, let the business use it, if they sign it off, then you can go to production

Post #1411079

OnlyOneRJ

Posted Thursday, January 24, 2013 5:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

I ran the DTA tool on testing server considering that SP & that DB,
I got some object names in Index Recommendation & Estimated improvement = 0%, partition Recommendation = NULL

Date 1/24/2013
Time 7:32:52 AM
Server 182.16.14.151
Database(s) to tune [test_DB]
Workload file C:\RND.sql
Maximum tuning time 58 Minutes
Time taken for tuning 1 Minute
Expected percentage improvement 0.00
Maximum space for recommendation (MB) 16072
Space used currently (MB) 6126
Space used by recommendation (MB) 6126
Number of events in workload 4
Number of events tuned 4

Now next what to be done???

************************************
Every Dog has a Tail !!!!!

Post #1411086

anthony.green

Posted Thursday, January 24, 2013 5:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

One more time

Read the link in my signature on posting performance problems, also read the link on posting code and data.

Update all your statitics, rebuild your indexes, post the execution plan as a SQLPLAN file, post your DDL of the tables involved, post the DDL of all indexes on the objects, post the definition of the store proc.

Post #1411091

GilaMonster

Posted Thursday, January 24, 2013 6:01 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 37,651, Visits: 29,903

runal_jagtap (1/24/2013)

Sunday no one wotks on the Database
will perfom the below activity..

1) Full Backup - Using manitenencae plan
2) Transaction Log Backup - Using manitenencae plan
3) Truncate Logs (this grows up to GB every day Angry

)
4) Update Statistics & then Rebuild Indexes - Using manitenencae plan

Please suggest

I suggest you reconsider that set of operations.
Please read through this - Managing Transaction Logs

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1411097

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions