August 24, 2008 at 2:36 am
i am having a server with 6GB RAM, Xeon Processor, windows server 2003 x64, sql server 2005 x64. we are using this server to handle datastorage of a hospital management system. the application has been developed using .net 2003. In the live situation when there are more transactions, some of the queries are getting time out expired error but not always. can anyone help me in this matter.
August 25, 2008 at 7:14 am
If you know the queries that are timing out you should run them in SSMS and check the execution plans to see where you can tune them. You can also use the performance DMV's like sys.dm_exec_query_stats to determine which queries need to be tuned.
You could also create server-side trace to determine what else is going on that causes the timeouts.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 7:28 am
Timeout errors are generated at the provider / application layer and not by the SQL Server. Check the connection string of the client application to make sure it has the correct timeout value. Chances are its not defined, meaning the default timeout value of the .NET provider is what's used. Typically by adding TIMEOUT=0 means that the application will never timeout, and always wait for a SQL Server response.
Not sure if that meets your business requirements, but it may help.
August 25, 2008 at 7:59 am
- Did you design your unit of work for each operation ?
- try to keep your transactions aligned to a single unit of work and commit or rollback as soon as possible.
- you may want to focus your start on the conflicting procedures.
(sql profiler / sqlserver errorlog (startup parameter 1222 / 3605)) may provide you the info you seek
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 25, 2008 at 9:17 am
cool_itprofessional (8/24/2008)
i am having a server with 6GB RAM, Xeon Processor, windows server 2003 x64, sql server 2005 x64. we are using this server to handle datastorage of a hospital management system. the application has been developed using .net 2003. In the live situation when there are more transactions, some of the queries are getting time out expired error but not always. can anyone help me in this matter.
There are a kajillion things that could be causing this. If you own the source code then you can start profiling and finding bad queries and fixing them. If not, you are pretty much limited to either creating indexes and/or forcing query plans with the use plan system. Either of which may void your vendor warranty. As someone else suggested, see if you can adjust your timeout setting on the client side. Also consider getting some professional help. You could hunt-and-peck back and forth on this site for days without getting any closer to improving performance on what is likely a critical system.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 26, 2008 at 12:56 am
A short while after we upgraded to SQL Server 2005 (I was still trying to find my feet in SQL 2005) I noticed that our webbased system became very slow and on accasion would time-out. I researched a bit and came upon somebody telling me to rebuild and/or reload all the indexes on the database. I did that and it worked but this was only a short term solution. As I carried on learning SQL 2005 I learned about the database tuning advisor through a book written by Brad McGehee. So, one day I ran the Database Tuning Advisor (I learned the hard way not to do it during working hours and also warn your users when you are going to do it in case they want to work late or you simply have to do it in working hours). The first time it had hundreds of recommendations but after applying the recommendations once and then run it say weekly you will have much less indexes in the recommendations and your database performance will increase considerably. If you do not know how to do it, place as post on this website and I will take you through the steps. I know that there is a vast number of possibilities of what can be wrong but once your indexes and statistics are in place (both corrected by DTA) you can build on it from there.
The wannabe DBA;););););)
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
August 26, 2008 at 1:08 am
Be careful with DTA. It has a habit of over-recommending things. Take its recomendations with a large pinch of salt and test carefully before putting the indexes it suggests in.
I've seen it suggest three indexes and seven statistics for a single query. In testing, one of those indexes reduced the queries's execution time greatly, the others had little to no effect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 6:36 am
GilaMonster (8/26/2008)
Be careful with DTA. It has a habit of over-recommending things. Take its recomendations with a large pinch of salt and test carefully before putting the indexes it suggests in.I've seen it suggest three indexes and seven statistics for a single query. In testing, one of those indexes reduced the queries's execution time greatly, the others had little to no effect.
My main beef with DTA is it's amazing propensity for including gobs of columns, often upwards of 70% of all the columns in a table. Sure, you get a covering index, but at what price?!?!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 26, 2008 at 8:58 am
GilaMonster (8/26/2008)
Be careful with DTA. It has a habit of over-recommending things. Take its recomendations with a large pinch of salt and test carefully before putting the indexes it suggests in.I've seen it suggest three indexes and seven statistics for a single query. In testing, one of those indexes reduced the queries's execution time greatly, the others had little to no effect.
Could you make some suggestions as to how to check the recommendations and/or what would you do in this situation? This is a bit of a problem for me as I do not know the system stored procs so well.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
August 26, 2008 at 9:15 am
Take the indexes that DTA recommends for one table, find all the queries that affect the table. Run the queries one by one and note how they perform. Apply one of the indexes, test the queries again, make notes on what's improved and what hasn't. Remove that index, apply another one, repeat.
And do that on a test system, not on the live production server.
Take notes in terms of the query's cost, as calculated in the exec plan. Be careful, it's an estimate and there are things that will make that very, very wrong. Use STATISTICS TIME and STATISTCS IO as well.
It is time consuming. What I would do is to evaluate indexes one table at a time, or for a small set of queries that are known to have performance problems. Don't try and tune the entire database in one massive tuning session. You'll never know what changes worked and what were wastes of space and time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply