| Author |
Topic |
|
vishwadiga
Starting Member
4 Posts |
 Posted - 2011-08-02 : 02:41:04
|
| Hi,We are on SQL Server 2000Front End is VBTotal Concurrent User 125Server - HP ML 350RAM - 2GBDB Size - 15GBSome time Application Gets hang ... no one can acces the applioaction.No blocking session found at that time ( sp_who).If I restart the server, wevrything will be OK ..What was the reason .. could anybody explain me ..ThanksVishwa |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-02 : 02:59:41
|
| might be cpu usage is high.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
vishwadiga
Starting Member
4 Posts |
Posted - 2011-08-02 : 03:16:32
|
| Hi,What is the solution?Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-02 : 07:04:14
|
| check if there are some resource intensive queries running on db------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-02 : 07:57:02
|
| Usually the solution is to optimise the worst-performing queries.[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP |
|
|
|
Jason W
Starting Member
19 Posts |
Posted - 2011-08-02 : 11:17:03
|
| Hi Vishwa,Like they said above, you will need to track down the slow running queries. I like to setup performance traces to monitor how everything looks, constantly and over a long period of time. Here is a link that can get you started with getting query performance data loaded into a database table. The trace just runs on the server and loads data into a file. You then take the file and dump it into a table, which can be queried off of to look at start/end times of procedures. We just have the trace running all the time and move the data into a table every night (or manually make the move if we need to look at current performance data).SQL Performance Trace - http://www.sqloptimizationsschool.com/Pages/Tracking%20Performance/Performance%20Tracing.aspxJason |
|
|
|
vishwadiga
Starting Member
4 Posts |
Posted - 2011-08-03 : 03:15:28
|
| Hi,I generated trace file .. then i used the Database tuning advisor tool for recommandation. It suggested us to create index & statistics.how i can save recomandation in 2000.. i did not get any option.I treid to export but am not getting same data how it will displayed in the recommondation page.ThanksVishwa |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-03 : 04:05:25
|
| Test each recommendation before you implement, don't implement blindly.--Gail ShawSQL Server MVP |
|
|
|
vishwadiga
Starting Member
4 Posts |
Posted - 2011-08-03 : 04:40:37
|
| Hi,Am planning to clone live DB... implementing the recomendation on the clonned DB.Any suggestions?ThanksVishwa |
|
|
|
Zoomer36
Starting Member
13 Posts |
Posted - 2011-09-13 : 10:42:15
|
| Hello,I have and old Everest Enterprise software running at my company. We have two companies in the system. The first and oldest is indexed. The second one is not. Of course, everything is much slower on the non indexed DB. Does anyone know what I can do to index this? Are there potential problems? How would I go about it?I am not a DBA but am acting as one. My background is programming so I know a little. I am just anxious to take that first step without some professional input.Thanks!hello |
|
|
|
Jason W
Starting Member
19 Posts |
Posted - 2011-09-14 : 00:18:34
|
| Hi Zoomer,You probably could have started a new thread, but here are the answers to your questions:- You will need to create the indexes, usually easiest to just type in the syntax on a query window utilizing SQL Query Analyzer (for SQL 2000) / SQL Management Studio (for SQL 2005+)- Creating indexes does create some up front cost when inserting/updating on the table. Think of it as more data the database needs to keep track of. I would guess in your case, this would be minimal compared to the gains by having the indexes.- Here is a link to get you started on index basics - http://sqloptimizationsschool.com/Pages/Basic%20Concepts/Indexes.aspxI don't want to throw a lot at you if are just learning about database stuff, but certainly do some googling for the answers you need. The site above has a lot of introductory performance articles that could help you out and there are a lot of other sites out there as well. |
|
|
|
Zoomer36
Starting Member
13 Posts |
Posted - 2011-09-14 : 15:34:13
|
| Thanks for your advice. My concerns are that it will possibly hurt DB? Probably not. How long will it take? I have a mirror DB of the other company. Can I find out how it is indexed? Since I am unfamiliar, I am willing to type create index but then will I have to select? There are well over 100 tables in this db and I don't want to mess it up. I can (and WILL!) exercise on a copy I have on another machine that we call the testplatform. Always safer to work with a copy than the production model.Have a great day! |
|
|
|
Jason W
Starting Member
19 Posts |
Posted - 2011-09-14 : 18:44:09
|
| You can actually click on the table triggers in the tree-view and script the create statements to a window (or clipboard). It will probably take some time, but that should save you quite a bit. If you want to really narrow it down, just look at the larger tables for indexing. While there might be 100 tables, I don't think you will need to index all of them (maybe I'm wrong, but I am guessing some of those are maintenance tables). |
|
|
|
|
Performance Tuning