Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TSQL by Duration (Response Time) Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2007 4:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 13, 2011 10:24 PM
Points: 18, Visits: 46
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?

Regards,

Kiruba sankar.S

:P
Post #414298
Posted Wednesday, October 24, 2007 4:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Sunday, September 7, 2014 11:11 PM
Points: 2,622, Visits: 328
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx
Post #414306
Posted Wednesday, October 24, 2007 5:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #414334
Posted Wednesday, October 24, 2007 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #414389
Posted Friday, October 26, 2007 10:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, Visits: 683
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 ;)
Post #415491
Posted Friday, October 26, 2007 11:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #415524
Posted Wednesday, October 31, 2007 3:38 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
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
Post #417333
Posted Thursday, November 1, 2007 8:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 4,406, Visits: 6,270
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 at GMail
Post #417515
Posted Thursday, November 1, 2007 9:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, 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.
Post #417546
Posted Thursday, November 1, 2007 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #417549
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse