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


Find expensive Queries in sql server 2000


Find expensive Queries in sql server 2000

Author
Message
jkhilosia
jkhilosia
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 181
Hi,

I am working as jr dba.
i want to find out the expensive queries with execution time in sql server 2000.
i have read many blogs but not able to fine proper answer.

if any one cay help me for this issue.
handkot
handkot
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2025 Visits: 585
MSSQL does not have built-in diagnostics.
but you can create your own based on sp_trace_create and etc. or use Profiler
jkhilosia
jkhilosia
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 181
Thanx,For update.

My question is,
Use of Profile put extra load on sql server.
Because we are using sql server 2000 and our server frequently down. we are using Java application.
so there is any other way. that you can suggest.
handkot
handkot
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2025 Visits: 585
I think no

1. if you reduce the number of events to a minimum, the load will be minimal
read this article Finding the Causes of Poor Performance in SQL Server, Part 1

2. check may be you have enabled "default Trace"

3. other too "DB Audit Expert™3.1"l
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14087 Visits: 15950
Don't use Profiler. Set up a server-side trace. It uses less resource than Profiler.

John
jkhilosia
jkhilosia
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 181
Thanx for updates.
jkhilosia
jkhilosia
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 181
Thanx for your valuable response.

I presume my DB do not have proper index and other stats,
When I am using DMV/DMF in SQL Server 2005 I am able to get list of missing index and review Execution plan but in SQL Server 2k I am not able execute Index management stuffs,

especially non cluster index and covering index, apart from this I can use scripts to find heap table and deadlock occurrences(incidents)

Moreover Please suggest me to configure blocked process thresh hold

Jitesh.
hitesh6221
hitesh6221
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 225
Blacked process Th Hold is used to identify blocked query and process/alert or trigger any WMI event,

moreover you can use it for broker services

When you configure BPTHold SQL will automatically notify event.

For that you can use SP_Configure for configuring BPT. Values would be seconds
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