September 1, 2009 at 8:09 pm
Guys, I really need help badly!!!
We have SQL Server 2000 and it has about 20 DBase on it. My problem is that sometimes my VIEWS on some DBAse did not show any data, But when I restart the Machine Server itself and when I checked the VIEWS it's now ok, showing all the data. I just experienced it since last week only but our SQL Server is running for about a year now.
Can anyone give me information regarding that scenario that I experienced. And please if you have any suggestion on to solve that problem, please share it to me. I really need that badly..
Thanks,
Morris
September 2, 2009 at 9:07 am
Damien (9/1/2009)
Guys, I really need help badly!!!We have SQL Server 2000 and it has about 20 DBase on it. My problem is that sometimes my VIEWS on some DBAse did not show any data, But when I restart the Machine Server itself and when I checked the VIEWS it's now ok, showing all the data. I just experienced it since last week only but our SQL Server is running for about a year now.
Can anyone give me information regarding that scenario that I experienced. And please if you have any suggestion on to solve that problem, please share it to me. I really need that badly..
Be specific........what do you mean by
did not show any data?
are you getting any error ?
what are those views? ...........on what all objects they are based?
If possible pls. mention the code behind those Views so that we can help you.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
September 2, 2009 at 12:08 pm
What happens when you run the view query in the query analyzer. In other words, copy/paste the view definition in query analyzer and execute. What happens?
September 3, 2009 at 3:57 am
Thank you for the reply...
It's kind of weird thing that happens to my views becase it's running for about a year now. And this is the first time it happened, When I run the views It looks like having a hard time getting the records, So I wait for a long time and nothing happened so I decided to restart the server(machine). After doing that I run again the views and now it's working fine.
Regarding of my sql statement on that views, Im 100% sure that It can handle large amount of data. If your thinking that maybe theirs something wrong with my sql query. I already tested that on our largest amount of data and it works fine. I already check the amount of data, and there's no difference compare before. So i think my sql statement working fine.
My question here is that, What really happen? because I just restart the server(machine) and it works fine again.
Thanks,
Morris
September 3, 2009 at 4:52 am
I would collect query plans when the view works and when it doesn't - then compare. There are times when the optimizer may not be choosing an index because statistics are out of date or that it thinks a table scan is more efficient.
The reboot fix suggests that maybe your query plan cache is bogus after a while and needs to be refreshed. Try a DBCC FREEPROCCACHE and see what happens.
Statistics can be goofy as well depending on how your data is distributed. For example, one of my databases frequently experiences a time-out when generating a query plan. The net result is a table scan and very long running queries. Updating statistics is unreliable in this case because the data is not consistantly distributed. One update stats might look at a good sample where another might not.
I understand the view has been working for a while, but that doesn't mean it will always work or that the query is bullet-proof. Don't assume anything. Dig into the query and find out what it is really doing at different points.
Good luck.
September 6, 2009 at 11:42 am
which version of SQL you are running ? SP 4?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply