September 4, 2008 at 2:17 am
First of all I apologize for my bad English,Follows our problem : we migrate on a new server a sql2000 database to sql2005 sp2, it is really small ( about 1,5 GB) :
now every 2 or 3 day and not on regular basis , the response time is becoming very slow, the sql log give us no particular information or error messages , after several attempts we have find out that if we refresh all the views the performance level returns in a normal condition.
I programmed a job that execute the view refresh every night , trying to understand if this condition is due to an increasing quantity of problems or arise casually, it seems the it arise casually.
Did someone experiment the same situation, or could someone suggest us a way to do other investigation ??
Thanks a lot to all of you
September 4, 2008 at 3:33 am
- please post the sqlserver version info.
Select Serverproperty( 'ProductVersion' ) as ProductVersion
- did you check the fixlist for the available cumulative hotfixes (9) for this kind of bug fixed ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 6:05 am
Some things you might consider:
* Did you run a
- dbcc updateusage on the migrated database?
- sp_updatestats on the database
* Do you have statistics? ("auto create statistics" enabled)
* Is "auto update statistics" enabled for the database?
* Do you have a lot of inserts/updates/deletes ?
* Is it just one view(table) of more?
Wilfred
The best things in life are the simple things
September 5, 2008 at 6:46 am
He some other informations !!
Update statistics is enabled, and automatics.
Product version is 9.00.3068.00 (workgroup edition).
The o.s. is windows 2003 server SBS 32 bits. 4 mbyte ram
Yesterday I changed the the transaction log model from simple to full and the problems happens more often , 3 time in 36 hours, ten mnutes ago i rechanged the transaction log model to simple(minimun).
Thanks to all of you
September 5, 2008 at 7:45 am
marco.crescini (9/5/2008)
...ten mnutes ago i rechanged the transaction log model to simple(minimun)...
- How big is your transaction log file ?
- what's the growth size for the log file ?
- is the log file on a raid volume ? What kind of raid ? (prefer RAID1)
- did you take log-backups (you need to do this becaus otherwise your log-file will keep on growing !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 8:06 am
marco.crescini (9/5/2008)
He some other informations !!Update statistics is enabled, and automatics.
Product version is 9.00.3068.00 (workgroup edition).
The o.s. is windows 2003 server SBS 32 bits. 4 mbyte ram
Yesterday I changed the the transaction log model from simple to full and the problems happens more often , 3 time in 36 hours, ten mnutes ago i rechanged the transaction log model to simple(minimun).
Thanks to all of you
I don’t think that the database’s recovery model has anything to do with it, but I also can’t think of anything else that can cause this problem. Few questions that I have:
1) When this problem occurs do you see it with every view or just few views?
2) Do you see a difference in the query plan when it runs fast and when it runs slow?
3) When you use the view and the query runs slow, what happens if you query the base tables directly? Does it also run slow?
4) When you refresh the views is it all that you are doing or are you running other maintenance operation as well?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply