Post reply

How to log what table fields querys are using

  • August 3, 2011 at 2:52 am

    #255153

    Hi experts

    I need to be able to find out what tables and fields a large number of reports are using. Rather than sift through all the code and manually recording the tables and fields is there a way to say create a user, and run the reports one by one and somehow using a trace to record the details a bit more automatically?

    cheers

    Tim

  • August 3, 2011 at 6:35 am

    #1362979

    Are you using sps or selects in datasets? Or maybe both?

  • August 3, 2011 at 2:01 pm

    #1363322

    Hi there - thanks for the reply

    Most are just select statements in stored procedures, that are presented through ssrs and ms access

  • August 3, 2011 at 2:04 pm

    #1363325

    I don't have a ready made script for that, hopefully someone else did (you might want to search the script section here).

    This is where you need to start : http://msdn.microsoft.com/en-us/library/ms190325.aspx

  • August 3, 2011 at 2:13 pm

    #1363332

    thankyou - so bascially I need to do a query on this view, which will map the stored procedure (depid) to the id (object - is this a table or field?)

  • August 4, 2011 at 6:05 am

    #1363580

    Yup, I wrote one back 7 years ago with other objects but I don't remember the field names too well. It's not hard to do, just a little trial / error.

    As I said, search the scripts here, I can't imagine that code not being posted.

  • August 4, 2011 at 9:57 am

    #1363775

    HI ,

    I think by using the below query you can find out which SP is taking more cpu time :::

    SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime',

    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

    qs.execution_count AS 'Execution Count',

    ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',

    ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

    qs.max_logical_reads, qs.max_logical_writes,

    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    -- WHERE qt.dbid = db_id() -- Uncomment to filter by current database

    ORDER BY qs.total_worker_time DESC

    Thanks

    Lavanya Sri

  • August 4, 2011 at 10:04 am

    #1363784

    He wants to know what columns / table is used on what reports... not the most <> intensive queries.

  • August 4, 2011 at 10:05 am

    #1363785

    this question made me explore one of those "scary places" outside my comfort zone of tsql: XQuery and XML.

    I know you can get the actual XML plan from teh cache, and if you can figure out how to XQuery that XML, you could get some of the object names;

    this query so far, is returning one row for each COLUMN for a given query...this is returning the outputted columns, not whatever columns it used internally.

    here's my first crack at it, so please feel free to pile on and give some constructive input:

    --returns the output list of each simple query, so select * from TallyCalendar might return a dozen or more rows

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

    SELECT p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'VARCHAR(max)') AS TheCommand

    ,s.sql_handle

    --,s.total_elapsed_time

    --,s.last_execution_time

    --,s.execution_count

    --,s.total_logical_writes

    --,s.total_logical_reads

    --,s.min_elapsed_time

    --,s.max_elapsed_time

    --,p.query_plan

    ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Database)[1]', 'NVARCHAR(256)') AS DBName

    ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName

    ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table)[1]', 'NVARCHAR(256)') AS TableName

    ,ColumnGroup.value('./@Column', 'NVARCHAR(256)') AS ColumnGroupColumn

    --,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName

    FROM sys.dm_exec_query_stats s

    CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p

    --LEFT OUTER JOIN sys.dm_exec_requests x ON s.plan_handle = x.plan_handle

    CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference') AS t1 (ColumnGroup)

    --CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)

    WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table') = 1

    --ORDER BY s.total_elapsed_time DESC

    --WHERE x.session_id=@@spid

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 4, 2011 at 3:31 pm

    #1364017

    Thanks guys for your input. I guess I would be not only after selected columns but all columns including ones used internally.

    I found this article that may help?

    http://www.sqlservercentral.com/articles/Administration/3214/

  • August 7, 2011 at 2:36 am

    #1364864

    hey all thanks for your help, managed to find APEX sql clean which looks promising.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply