Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tables Involved in Stored procedure


Tables Involved in Stored procedure

Author
Message
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10350 Visits: 9613
If 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: http://www.sqlservercentral.com/stairway/72399/

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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17651 Visits: 32272
runal_jagtap (1/24/2013)
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


If your database is in FULL recovery and you're only running log backups once a week, you have another issue besides the query. See this blog post.

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.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17651 Visits: 32272
MasterDB (1/24/2013)
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,


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.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
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