SQL Server Performance

SQL Server running SLOW.

Discussion in 'Performance Tuning for DBAs' started by Brandon Good, Feb 5, 2003.

  1. Brandon Good New Member

    Good afternoon. I am running SQL Server 7.0 on a Windows NT 4.0 server.
    It has 256 Megs of RAM and it is running 2 400 MHZ processors. Everything
    else runs great. But once I open up Enterprise Manager, it runs really really
    slow. It took me like 5 minutes just to view properties for one of my databases!
    I have already ran check disk on all my drives, I've ran regcleaner, and
    I have backed up my transaction log. My transaction log file for my main
    database is 1.75 Gigs. My data file for the same database is only 80 Megs.
    Is that causing Enterprise Manager to run slow? Any suggestions would be
    greatly appreciated.
  2. royv New Member

    I have a feeling this is due to your memory, you probably need more memory. Howevwer, this is a GUESS, I am not sure.

    "How do you expect to beat me when I am forever?"
  3. Chappy New Member

    How fast is it to connect to the databases when enterprise manager first opens?
    And are you running EM on the server, or on a network machine ? It could suggest a network related problem, might be worth doublechecking what client netlibs youre using (sql start menu). Try running EM on the server to see if the problem is still evident (if you havent already done so).
    Failing this, check the performance counters on your server to see if this highlights anything obvious.

  4. Brandon Good New Member

    It runs fine using enterprise manager on a workstation. Once I log on locally to the server, enterprise manager runs slow. I noticed on the performance monitor that my buffer cache hit ration is pegged out. I gotta figure out why its maxed.
  5. Chappy New Member

    Buffer cache hit ratio is one of the few counters you want to be maxed <img src='/community/emoticons/emotion-1.gif' alt=':)' /> If its running at 100% it indicates that the cache is performing well with very few requests not being found in the cache.<br /><br />It seems odd that EM is slow locally, but fine remotely. I was kinda expecting it to be the other way around. Is there much else in the way of processes running on the server? Royys suggestion could be right, but I think if the server is dedicated, then 256MB should be adequate (especially as the buffer hit ratio is seen to be good). Check your cpu usage, mem usage and IO queue length in task manager while loading EM.
  6. FrankQ New Member

    Perhaps one of your databases are set to Auto Close. This would definitely cause it to be slow.
    Good Luck
  7. sqljunkie New Member

    you may want to check out the Memory counters in PerfMon perhaps you are paging? Another counter to check out for SQL Server is Page Life Expectancy. The Buffer Cache Hit Ratio could be high, but if buffer pages are getting swapped in and out all the time the Page Life Expectancy will be low (like below 300)
  8. bradmcgehee New Member

    Is this a new problem, or one that you have been having for a long time? In other words, was it fast at one time, but is now slow? If so, can you tell us about any changes made to the server during this time?

    Brad M. McGehee
  9. Brandon Good New Member

    I had to restore it back in December. Before the restore I dont recall it being this slow. I restored all databases and all filegroups.
  10. bradmcgehee New Member

    I don't have an answer, but here is what I would do if I had this problem. I would apply the latest SP for SQL Server 7.0, even if you have already done so, and then reboot. If that doesn't fix the problem, then I would go to www.microsoft.com/data and download the latest MDAC and install it, and then reboot, and then reinstall. If that doesn't work, and if practical, I would reinstalled SQL Server, and then the latest SP, and then the latest MDAC, and reboot. If that doesn't work, I'd go home and rest a long while.

    Brad M. McGehee
  11. Brandon Good New Member

    If I uninstall SQL Server and reinstall, will I still have all my databases and individual login permissions I have set on my users? Also, how do I go about reducing the size of my 1.75 Gig transaction log file? Thanks alot for the advice so far...
  12. satya Moderator

    For login issues, run SP_CHANGE_USERS_LOGIN REPORT for orphaned logons.

    Use DBCC SHRINKFILE to reduce the transaction log file, refer to books online for detailed explanation on both statements.

    Satya SKJ
  13. bradmcgehee New Member

    If you decide to reinstall, what you can do is to first backup all of your system and user databases. This is just an extra precaution if case of problems. Then you can detach all of the databases, reinstall SQL Server, and then reattach all of the databases. The backups are only in case you have some issue with the detach and reattach option.

    Brad M. McGehee
  14. holland_stephen New Member

    If you resolve this issue, please post the cure. I've seen it a couple of times on test servers with both SQL 7 and 2000. (Not yet on production servers, thankfully.)

    My resolution was always KILL KILL KILL the machine and rebuild it, but again, these were test servers.

    One suggestion, maybe a defrag on the OS/SQL Server partition/array would be helpful. You'll need Diskkeeper since it's an NT 4.0 box.

    Thanks, Steve
  15. gaurav_bindlish New Member


    I have just submitted a tip for reducing the log file size. I am sharing the tip with you guys here....

    There have been lots of queries about reducing the size of log file. The option provided by Microsoft for the same is DBCC SHRINKFILE. This process is painfully slow and inefficient. Even after running the procedure numerous times, you will not get much free space.

    For reducing the Log file size, I have been using the following procedure – Back up the database and then detach it. Delete the log file and then attach the database, not providing the old log file location. This will create a new log file with the old log file name in the old location with default size i.e. 512 KB.

    To make sure there is no problem during the operation, the old log file can be renamed and kept until the operation of attaching database is not over. This is because incase SQL server fails to attach the database without old log file, you can revert back the change to make the database operational.

    This trick won#%92t work if the database has more than one log file. For doing the same, after backing up the database, alter the database and reduce the number of log files to one. After carrying out detach and attach database activity, the database can be again altered to add more log files.

    Hope you find this useful.....


Share This Page