#108275 - 2003-11-18 07:35 PM
Slow views running on SQL7 SP2
|
d.adams
Fresh Scripter
Registered: 2000-12-07
Posts: 41
Loc: Eagle Rock, VA, USA
|
I know this is not about Kix, but I was hoping someone could help us out here.
Our SQL server recently crashed so we had to rebuild everything and restore the data from backup. Everything went fine except now certain views with a filter will take from 30-120 seconds to run the first time and almost instantly thereafter, even under different sessions or logins. The same view with a different filter will take 30-120 seconds again. The first view will still return quick and the second one will be quick thereafter. After a few hours the first and second filters will take a long time again. These long queries will also nearly peg the server.
We did not have this problem before the crash. Is there a setting in SQL server or NT that may control this? We are currently running SQL 7 SP2 on NT 4.0 SP6a server. There is also 1GB RAM, 1GB pagefile, 400 MHz dual PIII processors, 100 Mbs/Full duplex network connection.
The view code and table structure here are to complicated to try to replicate here, but I'm willing to try anything I can do. Besides, I don't think it is the views. I feel it is something to do with the cache or something in SQL7 or NT4.0.
When I get time to work on the server after work hours I will upgrade to SQL7 SP4 and hope for the best, but if anyone has any ideas, it sure would be appreciated!
I will answer any questions about this situation as best I can.
Thanks,
Don Adams
|
|
Top
|
|
|
|
#108276 - 2003-11-18 07:55 PM
Re: Slow views running on SQL7 SP2
|
Howard Bullock
KiX Supporter
Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA
|
It sounds to me that some indexes or table keys were not restored or not restored properly. The initial long execution time would seem to indicate that table scans are happening instead of indexed lookups. After the initial query the tables are cached for a while which gives you improved response time.
|
|
Top
|
|
|
|
|
#108277 - 2003-11-18 07:59 PM
Re: Slow views running on SQL7 SP2
|
d.adams
Fresh Scripter
Registered: 2000-12-07
Posts: 41
Loc: Eagle Rock, VA, USA
|
I have already rebuilt the indexes on each table affected by the query view. It did not help any. All the indexes, triggers and other procedures were restored and seem to be running. Though not at optimal speed as before the crash.
Thanks,
Don
|
|
Top
|
|
|
|
|
#108278 - 2003-11-18 08:08 PM
Re: Slow views running on SQL7 SP2
|
Howard Bullock
KiX Supporter
Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA
|
You restore the database across what type of hardware/drive configuration? Is it the same or different compared to pre-crash? If different what changed?
Have you ever used Query Analyzer to display an execution plan? Look into that to see where some bottom necks may be.
Also look into using Profiler.exe to get a SQL trace of the action. This will show you elapsed time of each step.
|
|
Top
|
|
|
|
|
#108279 - 2003-11-18 08:12 PM
Re: Slow views running on SQL7 SP2
|
Howard Bullock
KiX Supporter
Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA
|
Did you place your tempDB on a different set of disks? Search the web on SQL performance and you will see this.
|
|
Top
|
|
|
|
#108280 - 2003-11-19 02:36 PM
Re: Slow views running on SQL7 SP2
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
Don,
Go over to http://sqlteam.com and there are a number of great articles to address your issue(s). In fact, they have a discussion board too (searchable too).
Hope this helps.
Kent
|
|
Top
|
|
|
|
|
#108281 - 2003-11-19 05:16 PM
Re: Slow views running on SQL7 SP2
|
d.adams
Fresh Scripter
Registered: 2000-12-07
Posts: 41
Loc: Eagle Rock, VA, USA
|
Thanks for your replies. The server is still using the same RAID5 configuration from before, though it had to be rebuilt after the crash as well. I have looked at the query analyzer, and found bottlenecks but when I pull that little bit out and run by itself, then it runs fine. I have also looked at the Profiler some, and it really didn't tell me much. Maybe I haven't figured out the right events/properties to trace. I will have to try again later. We've been out of power (again  ) here in SW VA for a couple of hours and not had much chance to work on it today. Do you have any suggestions on what events or properties I should focus on in the Profiler?
Thanks again, I will keep looking.
|
|
Top
|
|
|
|
|
#108282 - 2003-11-19 05:31 PM
Re: Slow views running on SQL7 SP2
|
d.adams
Fresh Scripter
Registered: 2000-12-07
Posts: 41
Loc: Eagle Rock, VA, USA
|
Found it. I will dig into it and see what I can find. First glance looks interesting.
Thanks!
Don
|
|
Top
|
|
|
|
|
#108283 - 2003-11-19 05:32 PM
Re: Slow views running on SQL7 SP2
|
d.adams
Fresh Scripter
Registered: 2000-12-07
Posts: 41
Loc: Eagle Rock, VA, USA
|
Thanks, I will check it out as soon as I finish looking at sql-server-performance.com.
I appreciate all your help.
Don
|
|
Top
|
|
|
|
Moderator: Arend_, Allen, Jochen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Mart
|
0 registered
and 460 anonymous users online.
|
|
|
