SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Long running queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/07/2000 :  08:39:47  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Gabe writes "I have been using the Profiler to determine what queries are taking a long time to run, and it works great when T-SQL statements are sent from client apps to Server.

But unfortunately, some systems I am tracking are creating client side cursors (prepared sql statements) and executing them, which makes it diffucult to determine what they are.

I am using the "Worst Performing Queries" trace which tells me what queries are taking long but they only show me the actual "execution" (sp_cursorexecute) and duration of these prepared statements and not the preparation (sp_cursorprepare) itself, which would show the sql statements being used.

I was wondering if it is possible to link these executions to their corresponding prepare statements so that I can tell what statements are the ones taking a long time.

Thanks in advance"

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 12/07/2000 :  13:26:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
If you have access to the client source code (we should all be so lucky) you would have to look at how it utilizes the cursor(s) it creates. You're right that Profiler will only get the cursor_execute blip. You might be able to look at total connection duration and get something meaningful, but otherwise I think you're SOL, unless you can get your clients to change software or use stored procedures.

Go to Top of Page

lajmanov
Starting Member

USA
2 Posts

Posted - 12/07/2000 :  13:47:16  Show Profile  Reply with Quote
Gabe here : I guess I can also try tracing all statements being run by each connection and see the preparation of the SQL and later its execution, which will show me the duration. This would only work if they are in order, which is not very reliable huh ?

Thanks

Gabe

Gabe
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 12/07/2000 :  14:11:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
Good idea. Thing is, is it one process using one connection, or multiple (independent) processes? The evils of client-side cursors! Seriously, if you can get the software developers and ask them exactly what goes on with the client you can pinpoint it with needing a straightjacket afterwards.

Go to Top of Page

lajmanov
Starting Member

USA
2 Posts

Posted - 12/07/2000 :  17:04:25  Show Profile  Reply with Quote
Luckily it is just one connection per process, so in that sense I am fine. I will be working with developers on this as well.. But I guess they would want me to point out where the culprits are in the app... which I cant pinpoint yet... I have just tried querying this trace sent to a table to see the actual SQL statements being prepared, and it seems that the SQL statements are being truncated !!! I might post this out on to another question...

Do you know if this is configurable in the Profiler ?? Can u limit the size of fields beingtraced. Because I am seeing some long SQL statements being trucncated when the trace is sent to a table. And t he field datatype is ntext, which allows for more...

SOS !!!

Gabe
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 12/07/2000 :  17:54:09  Show Profile  Visit robvolk's Homepage  Reply with Quote
I haven't used profiler much, just a few times and then I fixed my problems and moved on. If Books Online doesn't fill you in, try getting "Inside SQL Server (version number)" by Microsoft Press. It is probably the best book on SQL Server and covers EVERYTHING about the product, including profiler, SQL Trace, etc. (might be on clearance too now that 2000 is out).

BTW, are you sure there's only one process (or cursor) running on one connection? If they're using cursors, they could be running a cute little subquery on the side as well. I'm not down on cursor use, but they are too easy to misuse, and easier still to lose track of.

Go to Top of Page

vivekgupta85
Starting Member

India
2 Posts

Posted - 12/24/2012 :  07:20:40  Show Profile  Reply with Quote
Hi All,

I have to find worst queries that are taking too much time in sql server 2000.
I have take the help of Sql profiler and I found some queries related to aspx page.
My boss wants this should be in sql format. How can i find out.
Please help me.

Thanks
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/24/2012 :  11:47:27  Show Profile  Reply with Quote
Based on Duration you can filter out in SQL Profiler and save as Trace Table for you to analyze.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000