Slow Performance of SQL Server 2005...
-
Hi Experts... I am developing a Database application using Delphi 6 using SQL Server 2005 as backend. Its a working Live project now at my customer end. But Now the SQL Server getting slow as the Database and its Log file size increases. It becomes very critical situation that the customer some times feels like Server gets Hang and they Restart them. After Restart it will again works fine. I dont realize the actual problem..Can you Help me out..Its very Urgent.
My server configuaration is : Xeon Quad Processor 8 Gb RAM 1 TB HDD and total 30 CLients are accesing the data as well as Updating the Database.PLEASE PLEASE PLEASE HELP ME.....I have never failed,I just found 1000 ways that never works. Regards, Victory.
-
Hi Experts... I am developing a Database application using Delphi 6 using SQL Server 2005 as backend. Its a working Live project now at my customer end. But Now the SQL Server getting slow as the Database and its Log file size increases. It becomes very critical situation that the customer some times feels like Server gets Hang and they Restart them. After Restart it will again works fine. I dont realize the actual problem..Can you Help me out..Its very Urgent.
My server configuaration is : Xeon Quad Processor 8 Gb RAM 1 TB HDD and total 30 CLients are accesing the data as well as Updating the Database.PLEASE PLEASE PLEASE HELP ME.....I have never failed,I just found 1000 ways that never works. Regards, Victory.
Unfortunately, this is a huge issue to try to tackle on a forum. To adequately diagnose, I'd need to review your entire application. Unless your willing to contract for my services, that will not be possible here. Other than that, you might look to see how your handling your db connections - make sure they are all being closed after use. Another possibility is that you are locking db resources which make it impossible for other users to access at the same time. Rebooting the database, while not the recommended solutions, would free up both of these conditions. Just a few things to look into. Hope this helps.
-
Hi Experts... I am developing a Database application using Delphi 6 using SQL Server 2005 as backend. Its a working Live project now at my customer end. But Now the SQL Server getting slow as the Database and its Log file size increases. It becomes very critical situation that the customer some times feels like Server gets Hang and they Restart them. After Restart it will again works fine. I dont realize the actual problem..Can you Help me out..Its very Urgent.
My server configuaration is : Xeon Quad Processor 8 Gb RAM 1 TB HDD and total 30 CLients are accesing the data as well as Updating the Database.PLEASE PLEASE PLEASE HELP ME.....I have never failed,I just found 1000 ways that never works. Regards, Victory.
Stryder_1 is quite right - this is too-big an issue to get a simple answer to. However, a good place to start may be with SQL Server Profiler (on the Tools menu of you Management Studio). Use this to set up a trace and identify what's happening when things are starting to go wrong. Two other (easy) things to check are: In your code, make sure you aren't opening new connections all over the place (the trace will tell you this) and, if you have stored procedures, make sure you are deallocating cursors correctly. Good luck!
-
Unfortunately, this is a huge issue to try to tackle on a forum. To adequately diagnose, I'd need to review your entire application. Unless your willing to contract for my services, that will not be possible here. Other than that, you might look to see how your handling your db connections - make sure they are all being closed after use. Another possibility is that you are locking db resources which make it impossible for other users to access at the same time. Rebooting the database, while not the recommended solutions, would free up both of these conditions. Just a few things to look into. Hope this helps.
Thankx Stryder_1... I already checked it..and.. By the way.. as soon as the command gets executed and result get back. If I close the connection every time then for new command I think it will take some time to reopen the connection. If the command object is a local then after the Procedure is exited the command object will be free..then connection will also get Reset??? please correct me....
I have never failed,I just found 1000 ways that never works. Regards, Victory.
-
Stryder_1 is quite right - this is too-big an issue to get a simple answer to. However, a good place to start may be with SQL Server Profiler (on the Tools menu of you Management Studio). Use this to set up a trace and identify what's happening when things are starting to go wrong. Two other (easy) things to check are: In your code, make sure you aren't opening new connections all over the place (the trace will tell you this) and, if you have stored procedures, make sure you are deallocating cursors correctly. Good luck!
hi dan_fish, I have checked it on SQL profiler..
But I dont understand it. I found some procedures get executed at background and some of my queries take time (apporx 123455), whats does it mean??
Is it take more time than expected?
then what should I do??Please help me.
I will check in my Source code.. and all the queries having Select * will replaced with only needed Column name. Will it do anything to performance???I have never failed,I just found 1000 ways that never works. Regards, Victory.
-
hi dan_fish, I have checked it on SQL profiler..
But I dont understand it. I found some procedures get executed at background and some of my queries take time (apporx 123455), whats does it mean??
Is it take more time than expected?
then what should I do??Please help me.
I will check in my Source code.. and all the queries having Select * will replaced with only needed Column name. Will it do anything to performance???I have never failed,I just found 1000 ways that never works. Regards, Victory.
Hi Vijay
vijay.victory wrote:
some of my queries take time
then what should I do??
Identify where the queries are coming from in your code, then run them in Management Studio and take it from there. Right-clicking in the query pane and selecting 'Display Estimated Execution Plan' may be of help in identifying where your problem lies.
vijay.victory wrote:
Is it take more time than expected?
I can't tell you that - it's your database and code, so only you can know.
vijay.victory wrote:
all the queries having Select * will replaced with only needed Column name.
That's a good idea - you should always do that anyway because it reduces network traffic, but your problem here is the time of execution, not the time of retrieval. Cheers, Dan