| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2008-12-05 : 13:12:36
|
| i was wondering if there's any way to capture all quries taking longer then 1 minute?also, i'm wondering how the DETA's ability to use a table for the workload, i always just copy/paste problem quries into a text file and use the file method, but i'd liek to kinda steam-line things.Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-05 : 13:15:13
|
| http://8086.blogcu.com/how-to-identify-slow-running-queries_4880469.html |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-05 : 13:20:33
|
| SQL Profiler with Filter-Duration>1min or see Standard Reports for Longest Running Queries. You can use whole trace or batches as workload in DETA. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-12-05 : 13:36:07
|
| i used the query it had on there, but it looks like it cuts the statement_text off at appro 257 characters... anyway i can adjust that?also, is there a way to filter by the user? i have a web-app and would like to filter by the account that is being used.thanks! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-05 : 13:38:05
|
| Yes. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-12-05 : 15:06:05
|
| Okay, so i got a trace running that pulls everything obert 60000 (1min) new question, my web app that executes the quries uses a stored procedure, so i only see the exec + parameters... is there any way to capture the actual sql command that was compiled from the stored procedure instead?also, how can i filter by user? do i have to make my own trace template? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-05 : 15:20:01
|
quote:
Originally posted by albertkohl
Okay, so i got a trace running that pulls everything obert 60000 (1min) new question, my web app that executes the quries uses a stored procedure, so i only see the exec + parameters... is there any way to capture the actual sql command that was compiled from the stored procedure instead?Sp:StmStarting and Sp:Stmtcompletedalso, how can i filter by user? do i have to make my own trace template?Use Filter with Loginname
|
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-12-05 : 16:32:34
|
| i got that working, now how do i filter by login name? i only see binarydata, duration, spid, textdata(under column filters on events selection tab)don see anything about loginname |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-05 : 16:46:55
|
| Choose all column. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-05 : 23:40:28
|
quote:
Originally posted by albertkohl
i got that working, now how do i filter by login name? i only see binarydata, duration, spid, textdata(under column filters on events selection tab)don see anything about loginname
you've loginname available as one of option under column filter |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-05 : 23:41:51
|
quote:
Originally posted by albertkohl
i used the query it had on there, but it looks like it cuts the statement_text off at appro 257 characters... anyway i can adjust that?also, is there a way to filter by the user? i have a web-app and would like to filter by the account that is being used.thanks!
that becuase of default settings of mgmnt studio editor. you've edit it. go to tools-> options and take results -> results to text and change number of characters displayed from 256 to some higher number. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-06 : 08:49:23
|
quote:
Originally posted by visakh16
quote:
Originally posted by albertkohl
i got that working, now how do i filter by login name? i only see binarydata, duration, spid, textdata(under column filters on events selection tab)don see anything about loginname
you've loginname available as one of option under column filter
That's correct but for other non-default columns you have to choose 'show all columns' before they appear in filter list. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-12-10 : 12:30:27
|
| okay, i'm trying to capture quries ran on a specific table, i set up a duration template to capture everything for ntuser albert (that works file) but when i try to capture everything that is ran on "MyTable" it doesnt workfilters:NTUser Like: AlbertTextData Like: (i've tried each of the following:) MyTable | 'MyTable' | %MyTable% | '%MyTable%' | MyTable% | %MyTable none of them have worked :( any pointers? (trying to capture for example: select top * from mytable where id=1)i've also tried the objectname, and i get problems there too. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-10 : 13:03:31
|
| Choose Databasename First in Filter and then choose objectname with [schema].[tablename].It will work. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-12-10 : 13:12:01
|
| using the tsql_duration template, i noticed that ObjectName is un-checkable... is that why it keeps telling me the trace failed? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-10 : 13:18:28
|
| Never tried that Template. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-12-10 : 14:21:24
|
| what one would you reccommend i use then? because if i use the databasename like MYDB and objectname like MyTable it says it trace failed... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-10 : 18:41:35
|
| Use standard template and apply filters. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-12-11 : 12:03:39
|
| i think i'm almost there... but really quick. i'm not a sql-god by any means, and i havnt really got the skills yet to develop my own indexes. would you recommend i maybe run a trace on the server for like a week, and then run the captured quries though DETA? and if i do that, do you think it would be a good idea to filer only >1min, or should i run EVERYTHING to start off with a base, and then go from there with the quries >1min?follow me? |
|
|
|
|
capture quries over 1 minute