SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL by Duration (Response Time)


TSQL by Duration (Response Time)

Author
Message
kiruba-370649
kiruba-370649
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 46
Hi Friends,

my application is getting very slow performance.BigGrin

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.Smile

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

Tongue
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231520 Visits: 46352
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100483 Visits: 33014
Take everything Gail says to heart.

In addition, you might want to check out this article.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Bob Fazio
Bob Fazio
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3086 Visits: 683
Nice blog Grant!

1) If it is too slow to be useful -> then you need to tune it more Smile
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 Wink
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100483 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
noeld
noeld
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23300 Visits: 2048
Bob Fazio (10/26/2007)
Nice blog Grant!

1) If it is too slow to be useful -> then you need to tune it more Smile
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 Wink


V E R Y N I C E


* Noel
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33195 Visits: 8680
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
Bob Fazio
Bob Fazio
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3086 Visits: 683
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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100483 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33195 Visits: 8680
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search