Running Aggregated Duration in .NET

Encoder QR in .NET Running Aggregated Duration

Table 3-13. Running Aggregated Duration
QR Code JIS X 0510 Creation In .NET
Using Barcode maker for .NET framework Control to generate, create Denso QR Bar Code image in Visual Studio .NET applications.
www.OnBarcode.com
Denso QR Bar Code Recognizer In Visual Studio .NET
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
cs 368623506 184235228 total_s running_total_s pct running_pct rn 1 2 3
Bar Code Creator In Visual Studio .NET
Using Barcode printer for Visual Studio .NET Control to generate, create bar code image in .NET applications.
www.OnBarcode.com
Barcode Scanner In Visual Studio .NET
Using Barcode scanner for Visual Studio .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
60723.16 60723.16 660.95 61384.11 61402.99
QR Creator In C#
Using Barcode generator for VS .NET Control to generate, create Quick Response Code image in VS .NET applications.
www.OnBarcode.com
Encoding QR Code 2d Barcode In VS .NET
Using Barcode printer for ASP.NET Control to generate, create QR Code 2d barcode image in ASP.NET applications.
www.OnBarcode.com
98.87 98.87 1.08 0.03 99.95 99.98
Encode QR Code ISO/IEC18004 In VB.NET
Using Barcode drawer for .NET Control to generate, create Quick Response Code image in Visual Studio .NET applications.
www.OnBarcode.com
Drawing GS1 - 13 In .NET
Using Barcode encoder for Visual Studio .NET Control to generate, create EAN-13 image in Visual Studio .NET applications.
www.OnBarcode.com
1872968693 18.88
ANSI/AIM Code 39 Drawer In Visual Studio .NET
Using Barcode encoder for Visual Studio .NET Control to generate, create USS Code 39 image in Visual Studio .NET applications.
www.OnBarcode.com
Generate PDF-417 2d Barcode In VS .NET
Using Barcode generation for VS .NET Control to generate, create PDF-417 2d barcode image in .NET framework applications.
www.OnBarcode.com
total_s
Generate Bar Code In Visual Studio .NET
Using Barcode drawer for Visual Studio .NET Control to generate, create barcode image in .NET framework applications.
www.OnBarcode.com
British Royal Mail 4-State Customer Code Drawer In Visual Studio .NET
Using Barcode generation for Visual Studio .NET Control to generate, create RM4SCC image in .NET applications.
www.OnBarcode.com
running_total_s pct 61415.84 61415.85 0.02 0.00
Painting Code 128C In None
Using Barcode generator for Software Control to generate, create Code 128 Code Set B image in Software applications.
www.OnBarcode.com
Encoding Data Matrix ECC200 In Java
Using Barcode printer for Java Control to generate, create Data Matrix image in Java applications.
www.OnBarcode.com
running_pct rn 100.00 100.00 4 5
USS-128 Recognizer In Visual Basic .NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Encode ECC200 In None
Using Barcode encoder for Online Control to generate, create Data Matrix 2d barcode image in Online applications.
www.OnBarcode.com
1018047893 12.86 1037912028 0.01
Drawing GS1-128 In Objective-C
Using Barcode creator for iPhone Control to generate, create GTIN - 128 image in iPhone applications.
www.OnBarcode.com
QR Recognizer In C#
Using Barcode decoder for .NET framework Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
You can see at the top that there's one query pattern that accounts for 98.87 percent of the total duration. Based on my experience, typically a handful of query patterns cause most of the performance problems in a given system. To get back the actual queries that you need to tune, you should join the result table returned from the preceding query with the Workload table, based on a match in the checksum value (cs column), like so: WITH RunningTotals AS ( SELECT AQ1.cs, CAST(AQ1.total_duration / 1000. AS DECIMAL(12, 2)) AS total_s, CAST(SUM(AQ2.total_duration) / 1000. AS DECIMAL(12, 2)) AS running_total_s, CAST(AQ1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(AQ2.pct) AS DECIMAL(12, 2)) AS run_pct, AQ1.rn FROM #AggQueries AS AQ1 JOIN #AggQueries AS AQ2 ON AQ2.rn <= AQ1.rn GROUP BY AQ1.cs, AQ1.total_duration, AQ1.pct, AQ1.rn HAVING SUM(AQ2.pct) - AQ1.pct <= 90 -- percentage threshold -- OR AQ1.rn <= 5 ) SELECT RT.rn, RT.pct, W.tsql_code FROM RunningTotals AS RT JOIN dbo.Workload AS W ON W.cs = RT.cs ORDER BY RT.rn;
Data Matrix 2d Barcode Reader In None
Using Barcode reader for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
Create QR Code In Java
Using Barcode creator for Java Control to generate, create QR Code image in Java applications.
www.OnBarcode.com
You will get the output shown in abbreviated form in Table 3-14.
Table 3-14. Top Slow Queries in Abbreviated Form
rn pct 1 98.87 [View full width]SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderdate >= '20060101' AND orderdate < '20060201'; 1 98.87 tsql_code
rn pct 1 98.87 tsql_code [View full width]SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderdate >= '20060401' AND orderdate < '20060501'; 1 98.87 [View full width]SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderdate >= '20060201' AND orderdate < '20070301'; ...
Of course, with a more realistic workload you might get a large number of queries back, but you're really interested in the query pattern that you need to tune. So instead of joining back to the Workload table, use the APPLY operator to return only one row for each query signature with the query pattern, and a single sample per pattern out of the actual queries like so: WITH RunningTotals AS ( SELECT AQ1.cs, CAST(AQ1.total_duration / 1000. AS DECIMAL(12, 2)) AS total_s, CAST(SUM(AQ2.total_duration) / 1000. AS DECIMAL(12, 2)) AS running_total_s, CAST(AQ1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(AQ2.pct) AS DECIMAL(12, 2)) AS run_pct, AQ1.rn FROM #AggQueries AS AQ1 JOIN #AggQueries AS AQ2 ON AQ2.rn <= AQ1.rn GROUP BY AQ1.cs, AQ1.total_duration, AQ1.pct, AQ1.rn HAVING SUM(AQ2.pct) - AQ1.pct <= 90 -- percentage threshold ) SELECT RT.rn, RT.pct, S.sig, S.tsql_code AS sample_query FROM RunningTotals AS RT CROSS APPLY (SELECT TOP(1) tsql_code, dbo.fn_SQLSigCLR(tsql_code) AS sig FROM dbo.Workload AS W WHERE W.cs = RT.cs) AS S ORDER BY RT.rn;
You will get the output shown in Table 3-15.
Table 3-15. Signature and Sample of the Top Slow Queries
rn pct 1 98.87
sig SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderdate >= '#' AND orderdate < '#';
sample_query SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderdate >= '20060101' AND orderdate < '20060201';
Now you can focus your tuning efforts on the query patterns that you got backin our case, only one. Of course, in a similar manner you can identify the query patterns that generate the largest result sets, most of the I/O, and so on.
Tune Indexes/Queries
Now that you know which patterns you need to tune, you can start with a more focused query-tuning process. The process might involve index tuning or query code revisions, and we will practice it thoroughly throughout the book. Or you might realize that the queries are already tuned pretty well, in which case you would need to inspect other aspects of the system (for example, hardware, database layout, and so on). In our case, the tuning process is fairly simple. You need to create a clustered index on the orderdate column: CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate);
Later in the chapter, I'll cover index tuning and explain why a clustered index is adequate for query patterns such as the ones that our tuning process isolated. To see the effect of adding the index, run the following code to start a new trace: DECLARE @dbid AS INT, @traceid AS INT; SET @dbid = DB_ID('Performance'); EXEC dbo.sp_perfworkload_trace_start @dbid = @dbid, @tracefile = 'c:\temp\Perfworkload 20060829', @traceid = @traceid OUTPUT;
When I ran this code, I got the following output showing that the trace ID generated is 2: Trace ID: 2, Trace File: 'c:\temp\Perfworkload 20060829.trc'
Run the sample queries from Listing 3-2 again, and then stop the trace: EXEC sp_trace_setstatus 2, 0; EXEC sp_trace_setstatus 2, 2;
Copyright © OnBarcode.com . All rights reserved.