October 24, 2007 at 4:03 am
Hi Friends,
my application is getting very slow performance.:D
Because sql server not responding properly in particular case like run some big query.
Can u any tell me how we can identify the particular query is working fine or not.:)
i am using sql server profiler. its shows duration of TSQL.
If we know using sql server profiler duration means
What is the maximum response duration for TSQL Query?:w00t:
Regards,
Kiruba sankar.S
😛
October 24, 2007 at 4:20 am
October 24, 2007 at 5:11 am
VAIYDEYANATHAN.V.S (10/24/2007)
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx
Huh? What does an indexed view have to do with anything?
Kiruba:
I'm not sure I understood your question totally. You have an application that's exhibiting poor performance. Using profiler, you've identified some big queries that are running?
Best thing, if you know what queries are hogging your system is to take those queries and see if you can improve their performance. That may be by createing or modifying indexes, or it may require modification of the query itself. If you're runing into hardware bottlenecks, it may require a hardware upgrade. I don't have enough information on your problem to be more specific.
If you're not sure how to improve the speed of a query, start a thread here and post the query and the schema of the tables involved. There are a few of us around here who love tuning queries (myself included)
If this problem is widespread, you don't know how to improve it and its a serious problem, you may want to consider hiring a SQL consultant for a few days to look specifically at performance and to show you some tricks. I don't know where you're situated, so I can't make any suggestions.
kiruba (10/24/2007)
What is the maximum response duration for TSQL Query?
Are you asking what's the maximum amount of time a SQL query can run? There's no limit. Timouts are a client-side restriction, not server side.
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
October 24, 2007 at 6:48 am
Take everything Gail says to heart.
In addition, you might want to check out this article[/url].
It's meant as a basic introduction to checking performance of your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2007 at 10:05 am
Nice blog Grant!
1) If it is too slow to be useful -> then you need to tune it more 🙂
2) If no one is complaining, then it might be ok, but refer to 1 just in case.
3) If they are complaining... Good! You don't have 1 (they are trying to use it).
4) If they are using system and NOT complaining, you are busy with other things.
Then you are done.
5) If they are using the system and NOT complaining, but you have some free time.
Then you should look for something that could be improved...
6) HOWEVER don't change it until 3 happens 😉
October 26, 2007 at 11:03 am
Hey, nice trouble shooting pattern. I'll have to see if we can begin to apply that one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 31, 2007 at 3:38 pm
Bob Fazio (10/26/2007)
Nice blog Grant!1) If it is too slow to be useful -> then you need to tune it more 🙂
2) If no one is complaining, then it might be ok, but refer to 1 just in case.
3) If they are complaining... Good! You don't have 1 (they are trying to use it).
4) If they are using system and NOT complaining, you are busy with other things.
Then you are done.
5) If they are using the system and NOT complaining, but you have some free time.
Then you should look for something that could be improved...
6) HOWEVER don't change it until 3 happens 😉
V E R Y N I C E
* Noel
November 1, 2007 at 8:13 am
I think 6 should be removed from the list. Performance improvements are always welcome IMHO. Preventing user complaints should be a goal of all companies!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 1, 2007 at 9:29 am
TheSQLGuru (11/1/2007)
I think 6 should be removed from the list. Performance improvements are always welcome IMHO. Preventing user complaints should be a goal of all companies!
Actually that really isn't there to be funny. I am serious about that (mostly).
This is from painful experience:
If you change something that is broke, only to remain broken or broken in some new way. In general all you hear is FIX IT!
However the pain you will experience if you break something that isn't broken trying to improve performance... Expect to be burned at the stake. Don't be surprised if costs you your job.
Now incorporating these changes in a build for something else that is broken if tested VERY throughly is an option.
Complaints come from many sources. A stable system is a good goal too. Performance is also a goal. If you haven't noticed before a high performance system is almost always less stable than one that performs just a little less optimal.
November 1, 2007 at 9:37 am
Bob Fazio (11/1/2007)[hrComplaints come from many sources. A stable system is a good goal too. Performance is also a goal. If you haven't noticed before a high performance system is almost always less stable than one that performs just a little less optimal.
Very true. But the kicker comes when they want both total stability and constantly improving performance. I don't know about you, but I get "Can't you just add an index" most times when I propose either a structural or a code change.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 1, 2007 at 10:00 am
1) Appropriate testing covers the "don't fix something that is slow because you may break it".
2) Besides, many performance fixes are either adding/altering indexes, defragmentation of same, updating statistics or rewriting a query to removed unnecessary temp tables, cursors, etc. Only the latter really carries any significant risk, and see one for risk management.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 1, 2007 at 10:42 am
TheSQLGuru (11/1/2007)
2) Besides, many performance fixes are either adding/altering indexes, defragmentation of same, updating statistics or rewriting a query to removed unnecessary temp tables, cursors, etc. Only the latter really carries any significant risk, and see one for risk management.
Adding/Altering indexes has significant risk.
Adding an index WILL increase the time involved for inserts. It WILL impact the locks that need applied. It COULD increase time involved for updates (+ or -).
Altering an index can also have a significant impact. For example: Say you change the order of fields in an index only to find out that Quarter-end reports that you have never seen run in your testing needed that index as written.
Another hazard of alteringing an index especially if it is a clustered index. The order of results will change if an order by is not in the sql. This could be considered an application issue, but it will still be caused by your change.
Updating statistics is a common thing, and is generally considered safe... However, there are situations when you DON'T update statistics. This of course depends on the system, but this is a BIG NO-NO in older versions of Oracle (8-9i) since they didn't do automatic statistics updates. In general, I will agree this is a safe operation, but don't be fooled into thinking this is not changing the system which can lead to unexpected results (bug).
Don't misunderstand me. I agree that a WELL performing system is a goal, and that you should strive to never hear from the user/customer. That can be prevented with monitoring and such. Good instrumentation can be used to justify a performance only change. However in general, you will find that if you propose such a change to management unless you are talking about an insane gain in performance, the answer to changing the system will be "Not at this time".
November 1, 2007 at 10:53 am
Agreed on all Bob. I should have been more specific about my low-risk index stuff. I consider that to be nothing more than adding/including a column for example. Usually the biggest issue is the time it takes to actually do the alter. It may need to be performed during scheduled maint period. Adding a new single index that allows a frequently used query avoid a table scan has never in my experience led to such an increase in maintenance work that it wasn't still a big win. It is also my experience that the work to maintain a single new index and the locks involved are very, very low overhead compared to system total. Again, see my point 1. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 1, 2007 at 11:03 am
I've been at both ends of the spectrum on this. I used to work for a company where any, and I mean ANY, change was required authorisation and no less than 10 days notification before it was made. The only exception to this was during a crisis situation, and then a post change request had to be made.
Now where I work, the feeling is if you can gain 10 seconds of CPU a day by re-writing a procedure, your given the green light. Only requirement is you at the least have a backout plan and have done sufficient testing. And the word sufficient is basically if more than one person did the testing and they say it is ok, it is sufficient.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply