Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 128

According to SQL 2005 BOL (February 2007), the NTEXT data types will be removed in a future
version of Microsoft SQL Server. See more details in the article: http://msdn2.microsoft.com/
en-us/library/ms187993.aspx .
To get around this, convert the TextData column from the NTEXT data type to a string function
friendly data type: VARCHAR , NVARCHAR , VARCHAR(MAX) ,or NVARCHAR(MAX) . For example,
use NVARCHAR (MAX) .
ALTER TABLE temp_trace_table_001
ALTER COLUMN TextData NVARCHAR(MAX)
Now you will be able to write a query and analyze trace data. The following query statement will report
the top 50 longest queries. Some queries are executed multiple times. Since the default measurement in
the duration column of the trace file is in microseconds, the following query statement will convert the
duration time to milliseconds to be consistent with the CPU measurement. Figure 5-30 shows the
query result.
SELECT TOP 50
COUNT(*) AS NumberOfExecutions,
TextData,
SUM(Duration)/1000 AS Total_Duration_MilliSeconds,
SUM(CPU) AS Total_CPU_Time_MilliSeconds,
SUM(Reads) AS Total_Logical_Reads,
SUM(Writes) AS Total_Writes
FROM temp_trace_table_001
WHERE TextData IS NOT NULL
GROUP BY
TextData
ORDER BY Total_Duration_MilliSeconds DESC
If your trace data collection has procedures called multiple times with different parameters or simple
query statements with different filters, the above GROUP BY TextData clause will not work well. In
that case, a simple solution is to modify the query to use string functions (substring, charindex) to filter
the TextData in the query statement. In the following example, the query uses a substring function to
get the first 15 character data from the TextData. You can change the substring function to extract a
different set of character strings based on your requirements. There are many ways to tokenize TextData,
but to cover them all is beyond the scope of this topic.
-- As a reminder, you have converted the TextData to NVARCHAR(MAX) data type.
SELECT TOP 50
...,
SUBSTRING(TextData,1,15),
SUM(Duration)/1000 AS Total_Duration_MilliSeconds,
...
FROM temp_trace_table_001
WHERE TextData IS NOT NULL
GROUP BY
SUBSTRING(TextData,1,15)
ORDER BY Total_Duration_MilliSeconds DESC