InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 09:00:41 GMT20http://www.sqlservercentral.com/Forums/Topic1421594-360-1.aspxI got the answerBeginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.http://msdn.microsoft.com/en-us/library/ms175848.aspxThu, 21 Feb 2013 04:03:25 GMTthbaig1http://www.sqlservercentral.com/Forums/Topic1421594-360-1.aspxI have completed the profiling and now have all the data in table :)I am using following query to extract results. I have observed that for statements data is saying sql took 593 Sec and 8000 reads. But if I may use that query from TextData and execute, it gives me results in less than second and reads are fewer. Total execution as per query is 1 . What could be the reason or I am interpreting data wrongly ? SELECT TOP 5 COUNT(*) AS TotalExecutions, EventClass, CAST(TextData as nvarchar(4000)) AS query ,SUM(Duration)/1000 AS DurationTotal_Sec ,SUM(CPU) AS CPUTotal_Sec ,SUM(Reads) AS ReadsTotal ,SUM(Writes) AS WritesTotalFROM [Halsoos].[dbo].[tmpTable]WHERE eventclass IN (41)GROUP BY EventClass, CAST(TextData as nvarchar(4000))ORDER BY DurationTotal_sec DESC--Order by ReadsTotal desc--ORDER BY WritesTotal DESC--ORDER BY CPUTotal_Sec DESCWed, 20 Feb 2013 10:51:36 GMTthbaig1http://www.sqlservercentral.com/Forums/Topic1421594-360-1.aspx[quote][b]thbaig1 (2/19/2013)[/b][hr]thank you very much SSCrazy. These queries are really great to have. Can I modify these queries to extract queries executed by specific user?[/quote]No.[quote][b]thbaig1 (2/19/2013)[/b][hr]I want to use profiler too, as I have to save records in table for some historical analysis of application benchmark :(Can you guide me for profiler too ?[/quote]OK, I guess that all you probably need is "SQL:StmtCompleted" with the columns "TextData", "SPID", "Duration", "StartTime", "EndTime", "Reads" and "Write" selected.Tue, 19 Feb 2013 06:27:10 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1421594-360-1.aspxthank you very much SSCrazy. These queries are really great to have. Can I modify these queries to extract queries executed by specific user?I want to use profiler too, as I have to save records in table for some historical analysis of application benchmark :(Can you guide me for profiler too ?Tue, 19 Feb 2013 06:10:27 GMTthbaig1http://www.sqlservercentral.com/Forums/Topic1421594-360-1.aspxWhy not use the system dmvs? e.g.[code="sql"]SELECT TOP 100 [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count, [Total IO] = (total_logical_reads + total_logical_writes), [Execution count] = qs.execution_count, [Individual Query] = SUBSTRING(qt.TEXT, qs.statement_start_offset / 2, (CASE WHEN qs.statement_end_offset = - 1THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2), [Parent Query] = qt.TEXT, DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtORDER BY [Average IO] DESC;SELECT TOP 100 [Average CPU used] = total_worker_time / qs.execution_count, [Total CPU used] = total_worker_time, [Execution count] = qs.execution_count, [Individual Query] = SUBSTRING(qt.TEXT, qs.statement_start_offset / 2, (CASE WHEN qs.statement_end_offset = - 1THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2), [Parent Query] = qt.TEXT, DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtORDER BY [Average CPU used] DESC;[/code]Tue, 19 Feb 2013 05:52:58 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1421594-360-1.aspxHi,I want to trace all sql,stored proc with sql server profiler. So that I may able to extract top resource consuming components.I have created a template with with following selection[b]Performance [/b]Performance StatisticsShowPlanAllShowPlanXML[b]TSQL[/b]SQL batch completedSql batchStartingSql StmtCompletedSql StmtStartingCan you please suggest1- is above selection enough or should add or remove some options ?2- I ran trace with above and stored results in a table. But was not able to extract use full info.I think I don't know which column to group etc to find costly query in term of read,write or time.Kindly guide me how should I read trace table to extract required information?thanksTue, 19 Feb 2013 05:48:25 GMTthbaig1