none
Using SQL Profiler for SSRS Reports - Missing main dataset SQL Query in trace RRS feed

  • Question

  • I have 10 reports on SQL Server 2008R2 SSRS on SharePoint 2010. Each have 4 parameters. I don't have OLAP cube so each reports and parametres have SQL query. My purpose is to analyse indexing needs with Database Engine Tning Advisor.

    I have created Trace with Profiler. I'm using standard template. I'm saving to file. I have checked only TSQL in Events Selection.

    It seem like I don't get the main SQL query of reports (Dataset1) recorded. WHY?

    All dataset SQL query related to parametres are recorded. I can clearly see these SQL Statements in TextData.

    There are 2 different types of Events in trace.

    1) SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation'), COLLATIONPROPERTY(CONVERT(char, DATABASEPROPERTYEX(DB_NAME(), 'collation')), 'LCID')

    2) SELECT query for each parameters.


    Kenny_I

    Monday, July 29, 2013 5:46 AM

Answers

  • Main dataset was in SP, not T-SQL.  It was configuration issue and not solved


    Kenny_I

    • Marked as answer by Kenny_I Tuesday, July 30, 2013 11:35 AM
    Tuesday, July 30, 2013 11:35 AM

All replies

  • To find a specific query to trace in SQL Server Profiler I used the SPID of the query as a filter in Column filters under Event Selection. You can see the SPID of a query in the properties window in SSMS.

    Hoping this helps. I don't have much experience with using DTA and Profiler but this is what I did when I want to trace a specific query.

    Monday, July 29, 2013 6:32 AM
  • This is not search from trace problem.  I'm quite sure that main dataset SQL Query is not recorded to trace. Why?

    Is this SSRS feature? I would understand if reporting is based on in-memory, but I have assumed that every time when you call SSRS reports, SQL query is made to SQL Server and that should be registered to the trace.


    Kenny_I


    • Edited by Kenny_I Tuesday, July 30, 2013 11:07 AM modified
    Tuesday, July 30, 2013 11:07 AM
  • I also think that's the way it works. But if you want to analyse the query with DTA, do you think it matters where the query is run from initially? DTAs advice, if you implement it, will affect the database objects you query, so I don't think it matters. Taking the trace from the same dataset query run in SMSS should still help you get to analyse it in DTA.

    Hopefully someone with more know-how can come along and help out.

    Tuesday, July 30, 2013 11:30 AM
  • Main dataset was in SP, not T-SQL.  It was configuration issue and not solved


    Kenny_I

    • Marked as answer by Kenny_I Tuesday, July 30, 2013 11:35 AM
    Tuesday, July 30, 2013 11:35 AM