Unable to retrieve query hash reports for Duration and Logical reads

Oct 4, 2011 at 5:12 AM
Edited Oct 4, 2011 at 5:17 AM

Your help is very much appreciated. I have downloaded the latest version of SQL Nexus from codeplex.

Below is the snap shot of SQL server version

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
    Jul  9 2008 14:17:44
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Exception thrown when I click on Duration

SQLNexus Critical: 0 : Exception: Invalid object name 'tbl_TopNDurationByQueryHash'.. Source: .Net SqlClient Data Provider. Stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at sqlnexus.fmNexus.FixupDataSources(String filename, String reportname, ReportDataSourceCollection datasources, ReportParameterInfoCollection paramc) in C:\temp\nexus\sqlnexus_codeplex\sqlnexus\fmNexus.cs:line 1259. InnerException: .
    DateTime=2011-10-04T05:07:25.5701997Z
SQLNexus Information: 0 : rvTemplate_Drillthrough - Done with Drillthrough
    DateTime=2011-10-04T05:09:31.0036729Z
SQLNexus Information: 0 : rvTemplate_Drillthrough - End
    DateTime=2011-10-04T05:09:31.0036729Z
SQLNexus Information: 0 : rvTemplate_Drillthrough - Finally
    DateTime=2011-10-04T05:09:31.0036729Z
SQLNexus Critical: 0 : Exception: A data source instance has not been supplied for the data source 'DataSetQueryHashReport'.. Source: Microsoft.ReportViewer.WinForms. Stack:    at Microsoft.Reporting.WinForms.ReportViewer.RenderReportWithNewParameters(Int32 pageNumber). InnerException: .
    DateTime=2011-10-04T05:09:31.0236749Z

Exception thrown when I click on logical Reads

SQLNexus Critical: 0 : Exception: Invalid object name 'tbl_TopNLogicalReadsByQueryHash'.. Source: .Net SqlClient Data Provider. Stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at sqlnexus.fmNexus.FixupDataSources(String filename, String reportname, ReportDataSourceCollection datasources, ReportParameterInfoCollection paramc) in C:\temp\nexus\sqlnexus_codeplex\sqlnexus\fmNexus.cs:line 1259. InnerException: .
    DateTime=2011-10-04T05:09:38.5704295Z
SQLNexus Information: 0 : rvTemplate_Drillthrough - Done with Drillthrough
    DateTime=2011-10-04T05:14:07.6103308Z
SQLNexus Information: 0 : rvTemplate_Drillthrough - End
    DateTime=2011-10-04T05:14:07.6103308Z
SQLNexus Information: 0 : rvTemplate_Drillthrough - Finally
    DateTime=2011-10-04T05:14:07.6103308Z
SQLNexus Critical: 0 : Exception: A data source instance has not been supplied for the data source 'DataSetQueryHashReport'.. Source: Microsoft.ReportViewer.WinForms. Stack:    at Microsoft.Reporting.WinForms.ReportViewer.RenderReportWithNewParameters(Int32 pageNumber). InnerException: .
    DateTime=2011-10-04T05:14:07.6333331Z

Coordinator
Oct 5, 2011 at 6:27 AM

Do you have data in the 'tbl_TopNLogicalReadsByQueryHash' and 'tbl_TopNDurationByQueryHash' tables and do these tables exist in the SQL Nexus database? The data for these tables is captured by the SQL 2008 Perf Stats script which collects the data during the PSSDIAG/SQLDIAG shutdown (provided the SQL Perf Stats collection is enabled).

Oct 5, 2011 at 9:57 AM

Thanks much Amit.

I do not see these 2 tables created in the sqlnexus database.

Yes, I have the SQL Perf Stats collection is enabled. Below are some lines related to perf stats from SQLDiagPerfStats_Trace2008.XML

<CustomGroup name="SQL 2008 Perf Stats" enabled="true" />

<CustomTask enabled="true" groupname="SQL 2008 Perf Stats" taskname="SQL 2008 Perf Stats Script" type="TSQL_Script" point="Startup" wait="No" cmd="SQL_2008_Perf_Stats.sql" pollinginterval="0" />
 <CustomTask enabled="true" groupname="SQL 2008 Perf Stats" taskname="RDTSCTest" type="Utility" point="Startup" wait="No" cmd="RDTSCTestLoop.cmd &gt; &quot;%output_name%.OUT&quot; 2&gt;&amp;1" pollinginterval="0" />
  <CustomTask enabled="true" groupname="SQL 2008 Perf Stats" taskname="SQL 2008 Perf Stats Snapshot" type="TSQL_Script" point="Startup" wait="No" cmd="SQL_2008_Perf_Stats_Snapshot.sql" pollinginterval="0" />
 <CustomTask enabled="true" groupname="SQL 2008 Perf Stats" taskname="SQL 2008 Perf Stats Snapshot" type="TSQL_Script" point="Shutdown" wait="No" cmd="SQL_2008_Perf_Stats_Snapshot.sql" pollinginterval="0" />

 

Also, I checked the sql scripts SQL_2008_Perf_Stats_Snapshot.sql and SQL_2008_Perf_Stats.sql. Neither of them have schema creation code for tables 'tbl_TopNLogicalReadsByQueryHash' and 'tbl_TopNDurationByQueryHash'

Please advice.

Coordinator
Oct 5, 2011 at 2:36 PM

You need to check if the Perf Stats Snapshot output file (<server name>__SQL_2008_Perf_Stats_Startup.OUT)  should have captured the Query Hash output for it to be imported into SQL Nexus. The schema creation script is not present in the .sql files that you have mentioned. The schema is created while the data is imported into SQL Nexus using RowsetImporter. The SQL Nexus application creates these tables in the database, parses the output present in the output file collected by SQLDIAG and imports the relevant data into the appropriate tables. So if the Query Hash output. The output would be listed under the heading "-- top 10 CPU by query_hash --" in the .OUT file.

Oct 10, 2011 at 7:54 AM

The file <server name>__SQL_2008_Perf_Stats_Startup.OUT has been generated by SQLDIAG. But I do not see any heading like top 10 CPU by query_hash in the .OUT file.. Please advice.

Regards..Preethi

Oct 12, 2011 at 5:32 AM

Hello Amit,

It would be of great help if u can help me fix this.

The file <server name>__SQL_2008_Perf_Stats_Startup.OUT has been generated by SQLDIAG. But I do not see any heading like top 10 CPU by query_hash in the .OUT file.. Please advice.

Regards..Preethi

Coordinator
Oct 15, 2011 at 4:21 AM

The information is captured in the <server name>__SQL_2008_Perf_Stats_Snapshot_Shutdown.OUT file. If the SQLDIAG was shutdown or couldn't complete capturing the information for the query hash report, then the query hash report will not be generated. You can manually look up the query hash information using the following query:

select getdate() as runtime, * 
from
(
SELECT TOP 10 query_hash, COUNT (distinct query_plan_hash) as 'distinct query_plan_hash count',
  sum(execution_count) as 'execution_count',
  sum(total_worker_time) as 'total_worker_time',
  SUM(total_elapsed_time) as 'total_elapsed_time',
  SUM (total_logical_reads) as 'total_logical_reads',
 
    max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1,
      CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text]))
        ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
      END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_hash
ORDER BY sum(total_worker_time) DESC
) t