InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:09:03 GMT20http://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspx[quote][b]MasterDB (1/24/2013)[/b][hr]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,[/quote]But, only rebuild the indexes if they're more than a single extent in size, otherwise you get no benefit from the defrag at all.Thu, 24 Jan 2013 08:09:35 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspx[quote][b]runal_jagtap (1/24/2013)[/b][hr]Update statistics?Hmm i have never done Update statistics on Database yet..Please correct my belwo plan..Sunday no one wotks on the Databasewill perfom the below activity..1) Full Backup - Using manitenencae plan2) Transaction Log Backup - Using manitenencae plan3) Truncate Logs (this grows up to GB every day :angry: )4) Update Statistics & then Rebuild Indexes - Using manitenencae planPlease suggest[/quote]If your database is in FULL recovery and you're only running log backups once a week, you have another issue besides the query. [url=http://www.scarydba.com/2011/01/18/dba-101-why-is-my-log-file-full/]See this blog post.[/url]As to the query, it does sound like your statistics are stale. Rebuilding the index automatically does a full scan on the statistics for that index. So, be sure you don't update the statistics after a rebuild because that leads to problems.Thu, 24 Jan 2013 08:02:45 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxIf nothing changed, then it wouldn't be taking 6 times as long to run. Therefore, something must have changed. It might not be the data in the tables, but there's probably something.Start by looking at the procedure and examine the execution plans. They'll tell you where your problems lie.Take the information from there and check your existing indexes. Create new ones where appropriate, but don't believe everything that SQL Server tells you is missing; make sure they make sense and that you don't create duplicates. Overindexing is a real danger you need to avoid.If you need more information on execution plans, check out Grant Fritchey's book on them. It is very good.For more information on indexing, this site has an excellent stairway on the topic: [url]http://www.sqlservercentral.com/stairway/72399/[/url]Tuning is both an art and a science. Many people in this world spend a lot of time doing it and there's a lot to consider. You'll probably come up with the phrase "it depends" a lot. There's also most likely more than one way to fix the problem, but first you have to identify the problem. Don't underestimate the value of trying different approaches on a test server and benchmarking to see your results.Thu, 24 Jan 2013 07:49:11 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspx[quote][b]runal_jagtap (1/24/2013)[/b][hr]Sunday no one wotks on the Databasewill perfom the below activity..1) Full Backup - Using manitenencae plan2) Transaction Log Backup - Using manitenencae plan3) Truncate Logs (this grows up to GB every day :angry: )4) Update Statistics & then Rebuild Indexes - Using manitenencae planPlease suggest[/quote]I suggest you reconsider that set of operations. Please read through this - [url=http://www.sqlservercentral.com/articles/64582/]Managing Transaction Logs[/url]Thu, 24 Jan 2013 06:01:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxOne more timeRead 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.Thu, 24 Jan 2013 05:51:43 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxI 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 = NULLDate 1/24/2013Time 7:32:52 AMServer 182.16.14.151Database(s) to tune [test_DB]Workload file C:\RND.sqlMaximum tuning time 58 MinutesTime taken for tuning 1 MinuteExpected percentage improvement 0.00Maximum space for recommendation (MB) 16072Space used currently (MB) 6126Space used by recommendation (MB) 6126Number of events in workload 4Number of events tuned 4Now next what to be done???Thu, 24 Jan 2013 05:42:46 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxTake 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 productionThu, 24 Jan 2013 05:27:22 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxCheck 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,Thu, 24 Jan 2013 05:17:24 GMTMasterDBhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxTruncating your log files, eeesh, you need to put some proper transaction log management in place, regular shrinking of files is not best practise.[url]http://www.sqlservercentral.com/stairway/73776/[/url]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.Thu, 24 Jan 2013 05:04:20 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxAlso 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.Thu, 24 Jan 2013 04:56:16 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxUpdate statistics?Hmm i have never done Update statistics on Database yet..Please correct my belwo plan..Sunday no one wotks on the Databasewill perfom the below activity..1) Full Backup - Using manitenencae plan2) Transaction Log Backup - Using manitenencae plan3) Truncate Logs (this grows up to GB every day :angry: )4) Update Statistics & then Rebuild Indexes - Using manitenencae planPlease suggestThu, 24 Jan 2013 04:46:27 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxcheck the execution plan, update all statistics rebuild all indexesalso check out the links in my signature on posting performance problemsThu, 24 Jan 2013 04:30:20 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411053-391-1.aspxA 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?Thu, 24 Jan 2013 04:24:30 GMTOnlyOneRJ