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 12»»

Tables Involved in Stored procedure Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 4:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
A stored Procedure which use to execute in 10 minutes, now it takes 1 hour to execute
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
Posted Thursday, January 24, 2013 4:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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
Posted Thursday, January 24, 2013 4:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
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 )
4) Update Statistics & then Rebuild Indexes - Using manitenencae plan

Please suggest








************************************
Every Dog has a Tail !!!!!
Post #1411058
Posted Thursday, January 24, 2013 4:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
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
Posted Thursday, January 24, 2013 5:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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.




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 #1411067
Posted Thursday, January 24, 2013 5:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:15 AM
Points: 345, Visits: 567
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
Posted Thursday, January 24, 2013 5:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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




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 #1411079
Posted Thursday, January 24, 2013 5:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
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
Posted Thursday, January 24, 2013 5:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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.




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 #1411091
Posted Thursday, January 24, 2013 6:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
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 )
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 »

Add to briefcase 12»»

Permissions Expand / Collapse