SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tables Involved in Stored procedure


Tables Involved in Stored procedure

Author
Message
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 692
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 !!!!! :-D
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25066 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 692
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 !!!!! :-D
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 692
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 !!!!! :-D
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25066 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


MasterDB
MasterDB
Right there with Babe
Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)

Group: General Forum Members
Points: 736 Visits: 574
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,
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25066 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 692
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 !!!!! :-D
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25066 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229438 Visits: 46344
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search