| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 12/07/2000 : 08:39:47
|
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
15558 Posts |
Posted - 12/07/2000 : 13:26:37
|
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.
|
 |
|
|
lajmanov
Starting Member
USA
2 Posts |
Posted - 12/07/2000 : 13:47:16
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15558 Posts |
Posted - 12/07/2000 : 14:11:02
|
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.
|
|
|
|
lajmanov
Starting Member
USA
2 Posts |
Posted - 12/07/2000 : 17:04:25
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15558 Posts |
Posted - 12/07/2000 : 17:54:09
|
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.
|
|
|
|
vivekgupta85
Starting Member
India
2 Posts |
Posted - 12/24/2012 : 07:20:40
|
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 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/24/2012 : 11:47:27
|
| Based on Duration you can filter out in SQL Profiler and save as Trace Table for you to analyze. |
|
|
| |
Topic |
|
Long running queries
