| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2005-06-29 : 13:01:50
|
| An old problem has returned. A stored proc that returns a recordset (not the same proc every time) will begin to timeout when called from an ASP web page. Sometimes the proc will run correctly from QA without a timeout. Not always.The problem is resolved by dropping / creating the stored proc.I am reindexing this DB nightly. I believe this updates statistics too. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-06-29 : 13:20:12
|
| I only DROP and create all of my objects. Now that may get problematic with tables with lots of relationships a tons of data, but I still would make that my first approach.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-29 : 13:33:43
|
quote:
Originally posted by X002548
I only DROP and create all of my objects. Now that may get problematic with tables with lots of relationships a tons of data, but I still would make that my first approach.
Only occurs on my production server. I don't think so... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-29 : 14:24:22
|
quote:
I am reindexing this DB nightly. I believe this updates statistics too.
No. You must setup an UPDATE STATISTICS job as well. You also can't rely on the auto update statistics database option according to a MS person as reported at sql-server-performance.com.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-29 : 14:52:05
|
Tara,Do you have an UPDATE STATISTICS proc somewhere in your blog  Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-29 : 14:53:32
|
| Just use sp_updatestats.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-29 : 15:01:43
|
| Thanks,Should I add sp_updatestats before, or after, I reindex?How often should update stats be done? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-29 : 15:39:26
|
| I run it nightly. I don't think it matters when it runs as related to the reindex. I run the reindex once per night but only a few tables each night. So each table gets hit once per week.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-29 : 15:56:15
|
| OK. I'll add it and see.Just so I get this right... REINDEX should update statistics, but the word from deep inside Microsoft is that it isn't reliable and an explict execution of sp_updatestats is needed. Right?Thanks for point this out Tara.Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-29 : 15:57:34
|
| One last item. Does sp_updatestats block access to tables? Put differently, does it erode production performance? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-29 : 16:01:49
|
| REINDEX doesn't touch statistics at all. The mention about MS was related to the auto update stats option that you can set on a database. You should have this turned on, but you should also have a job that updates statistics as well according to the MS guy.Updating statistics is an online operation and doesn't block access others unlike DBCC DBREINDEX (which is why we don't do the entire database each night).Tara |
|
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2005-06-29 : 16:57:47
|
| Would running sp_refreshview help, hurt or have no effect?Tim S |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-29 : 17:01:19
|
| sp_refreshview is not applicable to this. Sam's issue is that a stored procedure performs slowly sometimes until it is dropped and recreated. sp_refreshview has to do with refreshing the metadata of a view and doesn't deal with performance.Tara |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-06-29 : 20:18:48
|
| As another approach, you may want to look at using the WITH RECOMPILE option in your CREATE PROC statement. It looks as though the original execution plan goes out of sync with your data over time.HTH================================================================='Tis with our judgements as our watches: none Go just alike, yet each believes his own. -Alexander Pope, poet (1688-1744) |
|
|
|
rgdmimi
Starting Member
1 Post |
Posted - 2005-07-12 : 16:32:36
|
| The issue I think your having is related to STATS as the others mentioned. They are also correct on AUTO Stats not being of much help. I had a simaler issue. Thi sis whatI recal.1. Turn Auto Stats "OFF" for that particular table. One time deal" Do not turn it on again for the table".Next set up a maint. job to:1. Reindex first. 3. Use update statistics Tablename (IndexName) WITH FULLSCAN, NORECOMPUTE -- to up date stats on that one table.Yes - you need to us a FULL scanThe sampling of data that occurse when adding a stored procedure is about only 25% of what is really in the table. Thats why the query runs fast for a while then the performance drops off.Also may help:Sample the fragmentation with SHOWCONTIG for this table fro time to time and check it against how the query is performing. Once you see the query time start to degrade see what the SCANDENSITY is, (the sweet spot). Use the SCANDENSITY as a threshhold in a job to trigger and update stats on the table. |
|
|
|
|
Drop/Create resolves performance problem