| Author |
Topic |
|
d.adams
Starting Member
7 Posts |
 Posted - 2003-11-19 : 12:07:20
|
| 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 and 6 18.2 GB disks in RAID5 configuration logical drive for total of approx. 91 GB.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 am currently reviewing this site and sql-server-performance.com for possible answers.I will answer any questions about this situation as best I can.Thanks,Don Adams |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-19 : 12:21:49
|
| Have you tried rebuilding the indexes with DBCC DBREINDEX or by dropping and recreating the indexes? How about UPDATE STATISTICS? Have you run Performance Monitor to determine if you have a hardware bottleneck? How about SQL Profiler to see if anything is causing this view to slow down?Tara |
 |
|
|
d.adams
Starting Member
7 Posts |
Posted - 2003-11-19 : 12:29:56
|
quote:
Originally posted by tduggan
Have you tried rebuilding the indexes with DBCC DBREINDEX or by dropping and recreating the indexes? How about UPDATE STATISTICS? Have you run Performance Monitor to determine if you have a hardware bottleneck? How about SQL Profiler to see if anything is causing this view to slow down?Tara
Yes I have rebuilt indexes on relavent tables, I have not done anything with UPDATE STATISTICS. What would that have to do with performance? 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-19 : 12:37:10
|
quote:
Originally posted by d.adams
Yes I have rebuilt indexes on relavent tables, I have not done anything with UPDATE STATISTICS. What would that have to do with performance?
Perhaps you need to do some reading on statistics. Read all about them in BOL. Yes they will help you out with performance.quote:
Originally posted by d.adams
Do you have any suggestions on what events or properties I should focus on in the Profiler?
As far as events go, I keep the default trace and then I add Stored Procedures:SP:Stmt Completed. That's the minimum amount of events that I trace for. But I'm thinking that you need to check for hardware bottlenecks as well. Have a look at Performance Monitor. Here is an awesome site that will get you started on it if you don't know what to do:http://www.sql-server-performance.com/Tara |
|
|
|
d.adams
Starting Member
7 Posts |
Posted - 2003-11-19 : 12:42:24
|
| Thanks Tara! I have already begun to read up on STATISTICS in BOL and now I understand what they do. I'm still not sure thats what the problem is, but I will continue to investigate. I will try the profiler again and see what I can find.Regards,Don |
|
|
|
d.adams
Starting Member
7 Posts |
Posted - 2003-11-19 : 13:36:38
|
| I have updated statistics on all relavant tables and did not help at all. I ran the Profiler and it really told me nothing other than it takes approx. 650 reads the first time a filter is queried and only about 50 after that. I'm still no closer than I was. If you have any other suggestions, please let me know.Thanks again,Don Adams |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-19 : 13:39:39
|
| Have you checked out Performance Monitor? What happened to the server when it crashed? Did you replace any hardware? You might have bad RAM in there, might want to consider swapping them out. Do you have excessive page faults occurring on the sqlsrvr.exe process? Performance Monitor can show this.Tara |
|
|
|
d.adams
Starting Member
7 Posts |
Posted - 2003-11-19 : 14:24:02
|
quote:
Originally posted by tduggan
Have you checked out Performance Monitor? What happened to the server when it crashed? Did you replace any hardware? You might have bad RAM in there, might want to consider swapping them out. Do you have excessive page faults occurring on the sqlsrvr.exe process? Performance Monitor can show this.Tara
When the server crashed, the NT operating system got corrupted because of power fluctuations and a bad battery backup. We recovered all data up to the point of the crash and rebuilt from the server restore CD (Compaq Proliant 7000). We reinstalled NT 4.0, added SP 6a and IE 4.0 with desktop update. I have yet to update to IE 6.0 SP1. I will do this soon. We reinstalled SQL 7 and added SP2 which is where it was at the time of the crash. From tape backup, we restored the contents of the 'Data' and 'Backup' folders. everything seemed to work just fine after the restore. When end users were allowed back into the system is when this problem became apparent.I not convinced memory or page faults are a problem because I can reproduce the problem on a different test server set up with MSDE and a copy of the database as it was before the crash. I did run the performance monitor for page faults and found it to be running in the mid teens to lower twenties per second, averaging about 35/sec and rarely spiking to 500/sec or so.Is there an option in the setup routine I may have chosen incorrectly that could be a problem? I chose the "dictionary sort order, ignore case" (I think that what it said, not sure, but that's the gist of the option I chose).I really appreciate your help.Thanks,Don |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-19 : 14:54:31
|
| No there is not an option in the setup which would have caused this problem. Maybe you could set the priority boost for SQL Server in the SQL Server properties. You restored the data and backup folders, but you didn't restore the databases. I'm actually surprised that it worked. Tara |
|
|
|
d.adams
Starting Member
7 Posts |
Posted - 2003-11-19 : 14:59:15
|
| Isn't the .mdf files in the data folder the databases? Short of restoring a .BAK backup, is there anything else we should have done?I will see if I can find the priority boost you mention.Thanks again!Don |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-19 : 15:02:06
|
| Yes the MDF files would be in the data folder. But those files should not be copied to tape unless the MSSQLSERVER service was down or a detach of the database occurred. We only copy the .BAK and .TRN files to tape. Restoring a backup is what you do. Why wasn't that done?Tara |
|
|
|
d.adams
Starting Member
7 Posts |
Posted - 2003-11-19 : 16:58:54
|
| We also only store the .bak and .trn files to tape on normal backups. When the server crashed, we spent most of a day trying to access the D drive where the data was stored. When we got access to it, we backed up EVERYTHING, including the .mdf files because SQL was not running. We saw the files on the tape backup and thought it would be worth a shot to recover 100% of data instead of losing between the last backup and the crash. It appeared to work, so we left it. SQL just started up, recognized the databases immediately and never complained.I have not looked for the priority boost yet, I will do that soon.Thanks,Don |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-19 : 18:01:19
|
| Priority boost:Right click on your server in EM and go to properties, then to Processor tab.Tara |
|
|
|
|
Slow views running on SQL7 SP2