Tuning Methodology
When dealing with performance problems, database professionals tend to focus on the technical aspects of the system, such as resource queues, resource utilization, and so on. However, users perceive performance problems simply as waitsthey make a request and have to wait to get the results back. A response that takes longer than three seconds to arrive after an interactive request is typically perceived by users as a performance problem. They don't really care how many commands wait on average on each disk spindle or what the cache hit ratio is, and they don't care about blocking, CPU utilization, average page life expectancy in cache, and so on. They care about waits, and that's where performance tuning should start.
The tuning methodology I recommend applies a top-down approach. It starts by investigating waits at the instance level, and then drills down through a series of steps until the processes/components that generate the bulk of the waits in the system are identified. Once you identify the offending processes, you can focus on tuning them. Following are the main steps of the methodology:
|
1.
|
Analyze waits at the instance level.
|
|
2.
|
Correlate waits with queues.
|
|
3.
|
Determine a course of action.
|
|
4.
|
Drill down to the database/file level.
|
|
5.
|
Drill down to the process level.
|
|
6.
|
Tune indexes/queries.
|
Analyze Waits at the Instance Level
The first step in the tuning methodology is to identify, at the instance level, which types of waits contribute most to the waits in the system. In SQL Server 2005, you do this by querying a dynamic management view (DMV) called sys.dm_os_wait_stats; in SQL Server 2000, you do this by running the command DBCC SQLPERF(WAITSTATS). The aforementioned DMV in SQL Server 2005 is fully documented, and I urge you to read the section describing it in Books Online. I'm not sure why, but the command in SQL Server 2000 is undocumented and surfaced only several years after the product was released. However, from the documentation in SQL Server 2005 you can learn about the different types of waits that are relevant to SQL Server 2000 as well.
Run the following query to return the waits in your system sorted by type:
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
The following query isolates the top waits that accumulate in total to 90 percent of the wait time in the system, and it generates (on my system) the output shown in Table 3-2:
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
-- filter out additional irrelevant waits
)
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
ORDER BY W1.rn;
Query Template
DECLARE @my_templatetext AS NVARCHAR(MAX);
DECLARE @my_parameters AS NVARCHAR(MAX);
EXEC sp_get_query_template
N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78',
@my_templatetext OUTPUT,
@my_parameters OUTPUT;
SELECT @my_templatetext AS querysig, @my_parameters AS params;
The problem with this stored procedure is that you need to use a cursor to invoke it against every query string from the trace data, and this can take quite a while with large traces.
Lis Creation script for the fn_SQLSigTSQL UDF
IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL
|
The function accepts as inputs a query string and the length of the code you want to parse. The function returns the query signature of the input query, with all parameters replaced by a number sign (#). Note that this is a fairly simple function and might need to be tailored to particular situations. Run the following code to test the function:
SELECT dbo.fn_SQLSigTSQL
(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);
You will get the following output:
SELECT * FROM dbo.T1 WHERE col1 = # AND col2 > #
Of course, you could now use the function and aggregate the trace data by query signature. However, keep in mind that although T-SQL is very efficient with data manipulation, it is slow in processing iterative/procedural logic. This is a classic example where a CLR implementation of the function makes more sense. The CLR is much faster than T-SQL for iterative/procedural logic and string manipulation. SQL Server 2005 introduces .NET integration within the product, allowing you to develop .NET routines based on the common language runtime (CLR). CLR routines are discussed in Inside T-SQL Programming; there you will find more thorough coverage and a comparison of the T-SQL and CLR-based implementations of the function that generates query signatures. You can also find some background information about CLR development in SQL Server .
fn_SQLSigCLR and fn_RegexReplace functions, C# version
using System.Text; |
The code has the definitions of two functions: fn_SQLSigCLR and fn_RegexReplace. The function fn_SQLSigCLR accepts a query string and returns the query signature. This function covers cases that the T-SQL function overlooks, and it can be easily enhanced to support more cases if you need it to. The function fn_RegexReplace exposes more generic pattern-based string replacement capabilities based on regular expressions, and it is provided for your convenience.
Note
|
I didn't bother checking for NULL inputs in the CLR code because T-SQL allows you to specify the option RETURNS NULL ON NULL INPUT when you register the functions, as I will demonstrate later. This option means that when a NULL input is provided, SQL Server doesn't invoke the function at all; rather, it simply returns a NULL output. |
If you're familiar with developing CLR routines in SQL Server, deploy these functions in the Performance database. If you're not, just follow these steps:
|
1.
|
Create a new Microsoft Visual C#, Class Library project in Microsoft Visual Studio 2005 (File>New>Project...>Visual C#>Class Library).
|
|
2.
|
In the New Project dialog box, name the project and solution SQLSignature, specify C:\ as the location, and confirm.
|
|
3.
|
Rename the file Class1.cs to SQLSignature.cs, and within it paste the code from Listing 3-5, overriding its current content.
|
|
4.
|
Build the assembly by choosing the Build>Build SQLSignature menu item. A file named C:\SQLSignature\SQLSignature\bin\Debug\SQLSignature.dll containing the assembly will be created.
|
|
5.
|
At this point, you go back to SQL Server Management Studio (SSMS) and apply a couple of additional steps to deploy the assembly in the Performance database, and then register the fn_SQLSigCLR and fn_RegexReplace functions. But first, you need to enable CLR in SQL Server (which is disabled by default) by running the following code:
EXEC sp_configure 'clr enable', 1; |
|
6.
|
Next, you need to load the intermediate language (IL) code from the .dll file into the Performance database by running the following code:
USE Performance; |
|
7.
|
Finally, register the fn_SQLSigCLR and fn_RegexReplace functions by running the following code:
CREATE FUNCTION dbo.fn_SQLSigCLR(@querystring AS NVARCHAR(MAX)) |
You're done. At this point, you can start using the functions like you do any other user-defined function. In case you're curious, the CLR implementation of the function runs faster than the T-SQL one by a factor of 10.
To test the fn_SQLSigCLR function, invoke it against the Workload table by running the following query, which will generate the output shown in abbreviated form in Table 3-9:
SELECT
dbo.fn_SQLSigCLR(tsql_code) AS sig,
duration
FROM dbo.Workload;
You can also use the more generic fn_RegexReplace function to achive the same output, like so:
SELECT
dbo.fn_RegexReplace(tsql_code,
N'([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?# expression coming
)(?:([N])?('')(?:[^'']|'''')*(''))(?# character
)|(?:0x[\da-fA-F]*)(?# binary
)|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?# precise number
)(?:[eE]?[\d]*)))(?# imprecise number
)|(?:[~]?[-+]?(?:[\d]+))(?# integer
))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?# operators
))',
N'$1$2$3#$4') AS sig,
duration
FROM dbo.Workload;
As you can see, you get back query signatures, which you can use to aggregate the trace data. Keep in mind, though, that query strings can get lengthy, and grouping the data by lengthy strings is slow and expensive. Instead, you might prefer to generate an integer checksum for each query string by using the T-SQL CHECKSUM function. For example, the following query generates a checksum value for each query string from the Workload table, and it generates the output shown in abbreviated form in :
SELECT
CHECKSUM(dbo.fn_SQLSigCLR(tsql_code)) AS cs,
duration
FROM dbo.Workload;
Clearing the Cache
When analyzing query performance, you sometimes need to clear the cache. SQL Server provides you with tools to clear both data and execution plans from cache. To clear data from cache globally, use the following command:
DBCC DROPCLEANBUFFERS;
To clear execution plans from cache globally, use the following command:
DBCC FREEPROCCACHE;
To clear execution plans of a particular database, use the following command:
DBCC FLUSHPROCINDB(<db_id>);
Note that the DBCC FLUSHPROCINDB command is undocumented.
TableSample
Here's an example for using the TABLESAMPLE clause in a query against the Orders table, requesting 1,000 rows:
SELECT *
FROM dbo.Orders TABLESAMPLE SYSTEM (1000 ROWS);
SELECT *
FROM dbo.Orders TABLESAMPLE (0.1 PERCENT);
When you use the ROWS option, SQL Server internally first converts the specified number of rows to a percentage. Remember that you are not guaranteed to get the exact number of rows that you requested; rather, you'll get a close value that's determined by the number of pages that were picked and the number of rows on those pages (which may vary).
To make it more likely that you'll get the exact number of rows you are after, specify a higher number of rows in the TABLESAMPLE clause and use the TOP option to limit the upper bound that you will get, like so:
SELECT TOP(1000) *
FROM dbo.Orders TABLESAMPLE (2000 ROWS);
There's still a chance that you will get fewer rows than the number you requested, but you're guaranteed not to get more. By specifying a higher value in the TABLESAMPLE clause, you increase the likelihood of getting the number of rows you are after.
If you need to get repeatable results, use a clause called REPEATABLE which was designed for this purpose, providing it with the same seed in all invocations. For example, running the following query multiple times will yield the same result, provided that the data in the table has not changed:
SELECT *
FROM dbo.Orders TABLESAMPLE (1000 ROWS) REPEATABLE(42);
Cursor solution
DECLARE |
Others
USE [NutsAndBolts] go /****** Object: StoredProcedure [dbo].[utility_Trace_GetPerformanceWorkLoadTrace] Script Date: 2013/11/28 12:38:13 ******/
SET ansi_nulls ON go SET quoted_identifier ON go -- =============================================
-- Author: Alex Tian
-- Create date: 2012-09-01
-- Description: Define your trace
-- DECLARE @dbid INT
-- DECLARE @tracefile NVARCHAR(245)
-- SET @dbid = DB_ID('TestDB')
-- SET @tracefile=N'd:\temp\Test'
-- EXEC [dbo].[utility_Trace_GetPerformanceWorkLoadTrace] @dbid,@tracefile
-- EXEC sp_trace_setstatus 2, 0; --stop
-- EXEC sp_trace_setstatus 2, 1; --start
-- EXEC sp_trace_setstatus 2, 2; -- close
-- SELECT * FROM sys.traces
-- SELECT CHECKSUM(dbo.fn_SQLSigTSQL(TextData,4000)) AS CodeSerial,
-- Duration
-- FROM fn_trace_gettable('D:\TEMP\ Test.trc',null)
-- =============================================
ALTER PROCEDURE [dbo].[Utility_trace_getperformanceworkloadtrace] @dbid AS
INT,
@tracefile AS
NVARCHAR(245)
AS
BEGIN
-- Create a Queue
DECLARE @rc AS INT;
DECLARE @traceid AS INT
DECLARE @maxfilesize AS BIGINT; SET @maxfilesize = 5; EXEC @rc = Sp_trace_create
@traceid output,
0,
@tracefile,
@maxfilesize,
NULL IF ( @rc != 0 )
GOTO error; -- Set the events
DECLARE @on AS BIT; SET @on = 1; -- RPC:Completed
EXEC Sp_trace_setevent
@traceid,
10,
15,
@on; EXEC Sp_trace_setevent
@traceid,
10,
8,
@on; EXEC Sp_trace_setevent
@traceid,
10,
16,
@on; EXEC Sp_trace_setevent
@traceid,
10,
48,
@on; EXEC Sp_trace_setevent
@traceid,
10,
1,
@on; EXEC Sp_trace_setevent
@traceid,
10,
17,
@on; EXEC Sp_trace_setevent
@traceid,
10,
10,
@on; EXEC Sp_trace_setevent
@traceid,
10,
18,
@on; EXEC Sp_trace_setevent
@traceid,
10,
11,
@on; EXEC Sp_trace_setevent
@traceid,
10,
12,
@on; EXEC Sp_trace_setevent
@traceid,
10,
13,
@on; EXEC Sp_trace_setevent
@traceid,
10,
6,
@on; EXEC Sp_trace_setevent
@traceid,
10,
14,
@on; -- SP:Completed
EXEC Sp_trace_setevent
@traceid,
43,
15,
@on; EXEC Sp_trace_setevent
@traceid,
43,
8,
@on; EXEC Sp_trace_setevent
@traceid,
43,
48,
@on; EXEC Sp_trace_setevent
@traceid,
43,
1,
@on; EXEC Sp_trace_setevent
@traceid,
43,
10,
@on; EXEC Sp_trace_setevent
@traceid,
43,
11,
@on; EXEC Sp_trace_setevent
@traceid,
43,
12,
@on; EXEC Sp_trace_setevent
@traceid,
43,
13,
@on; EXEC Sp_trace_setevent
@traceid,
43,
6,
@on; EXEC Sp_trace_setevent
@traceid,
43,
14,
@on; -- SP:StmtCompleted
EXEC Sp_trace_setevent
@traceid,
45,
8,
@on; EXEC Sp_trace_setevent
@traceid,
45,
16,
@on; EXEC Sp_trace_setevent
@traceid,
45,
48,
@on; EXEC Sp_trace_setevent
@traceid,
45,
1,
@on; EXEC Sp_trace_setevent
@traceid,
45,
17,
@on; EXEC Sp_trace_setevent
@traceid,
45,
10,
@on; EXEC Sp_trace_setevent
@traceid,
45,
18,
@on; EXEC Sp_trace_setevent
@traceid,
45,
11,
@on; EXEC Sp_trace_setevent
@traceid,
45,
12,
@on; EXEC Sp_trace_setevent
@traceid,
45,
13,
@on; EXEC Sp_trace_setevent
@traceid,
45,
6,
@on; EXEC Sp_trace_setevent
@traceid,
45,
14,
@on; EXEC Sp_trace_setevent
@traceid,
45,
15,
@on; -- SQL:BatchCompleted
EXEC Sp_trace_setevent
@traceid,
12,
15,
@on; EXEC Sp_trace_setevent
@traceid,
12,
8,
@on; EXEC Sp_trace_setevent
@traceid,
12,
16,
@on; EXEC Sp_trace_setevent
@traceid,
12,
48,
@on; EXEC Sp_trace_setevent
@traceid,
12,
1,
@on; EXEC Sp_trace_setevent
@traceid,
12,
17,
@on; EXEC Sp_trace_setevent
@traceid,
12,
6,
@on; EXEC Sp_trace_setevent
@traceid,
12,
10,
@on; EXEC Sp_trace_setevent
@traceid,
12,
14,
@on; EXEC Sp_trace_setevent
@traceid,
12,
18,
@on; EXEC Sp_trace_setevent
@traceid,
12,
11,
@on; EXEC Sp_trace_setevent
@traceid,
12,
12,
@on; EXEC Sp_trace_setevent
@traceid,
12,
13,
@on; -- SQL:StmtCompleted
EXEC Sp_trace_setevent
@traceid,
41,
15,
@on; EXEC Sp_trace_setevent
@traceid,
41,
8,
@on; EXEC Sp_trace_setevent
@traceid,
41,
16,
@on; EXEC Sp_trace_setevent
@traceid,
41,
48,
@on; EXEC Sp_trace_setevent
@traceid,
41,
1,
@on; EXEC Sp_trace_setevent
@traceid,
41,
17,
@on; EXEC Sp_trace_setevent
@traceid,
41,
10,
@on; EXEC Sp_trace_setevent
@traceid,
41,
18,
@on; EXEC Sp_trace_setevent
@traceid,
41,
11,
@on; EXEC Sp_trace_setevent
@traceid,
41,
12,
@on; EXEC Sp_trace_setevent
@traceid,
41,
13,
@on; EXEC Sp_trace_setevent
@traceid,
41,
6,
@on; EXEC Sp_trace_setevent
@traceid,
41,
14,
@on; -- Set the Filters
-- Application name filter
EXEC Sp_trace_setfilter
@traceid,
10,
0,
7,
N'SQL Server Profiler%'; -- Database ID filter
EXEC Sp_trace_setfilter
@traceid,
3,
0,
0,
@dbid; -- Set the trace status to start
EXEC Sp_trace_setstatus
@traceid,
1; -- Print trace id and file name for future references
PRINT 'Trace ID: '
+ Cast(@traceid AS VARCHAR(10))
+ ', Trace File: ''' + @tracefile + '.trc'''; GOTO finish; ERROR: PRINT 'Error Code: ' + Cast(@rc AS VARCHAR(10)); FINISH:
END
USE [NutsAndBolts] go /****** Object: StoredProcedure [dbo].[utility_Wait_GetWaitsForAllDatabase] Script Date: 2013/11/28 12:42:56 ******/
SET ansi_nulls ON go SET quoted_identifier ON go -- =============================================
-- Author: Alex Tian
-- Create date: 2012-09-01
-- Description: DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
-- =============================================
ALTER PROCEDURE [dbo].[Utility_wait_getwaitsforalldatabase]
AS
BEGIN
SET TRANSACTION isolation level READ uncommitted; WITH waits
AS (SELECT Row_number()
OVER(
ORDER BY wait_time_ms DESC) AS RowNumber,
wait_type AS WaitType,
wait_time_ms / 1000. AS WaitSecond,
100. * wait_time_ms / Sum(wait_time_ms)
OVER() AS WaitPercent
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
-- filter out additional irrelevant waits
)
SELECT W1.waittype AS WaitType,
Cast(W1.waitsecond AS DECIMAL(12, 2)) AS WaitSecond,
Cast(W1.waitpercent AS DECIMAL(12, 2)) AS WaitPercent,
Cast(Sum(W2.waitpercent) AS DECIMAL(12, 2)) AS RunningPercent
FROM waits AS W1
JOIN waits AS W2
ON W2.rownumber <= W1.rownumber
GROUP BY W1.rownumber,
W1.waittype,
W1.waitsecond,
W1.waitpercent
HAVING Sum(W2.waitpercent) - W1.waitpercent < 90 -- percentage threshold
ORDER BY W1.rownumber;
END USE [NutsAndBolts] go /****** Object: UserDefinedFunction [dbo].[fn_SQLSigTSQL] Script Date: 2013/11/28 12:44:09 ******/
SET ansi_nulls ON go SET quoted_identifier ON go -- =============================================
-- Author: Alex Tian
-- Create date: 2012-09-01
-- Description: Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
-- SELECT dbo.fn_SQLSigTSQL(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);
-- DECLARE @my_templatetext AS NVARCHAR(MAX);
-- DECLARE @my_parameters AS NVARCHAR(MAX);
-- EXEC sp_get_query_template N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78',@my_templatetext OUTPUT,@my_parameters OUTPUT;
-- SELECT @my_templatetext AS querysig, @my_parameters AS params;
-- =============================================
ALTER FUNCTION [dbo].[Fn_sqlsigtsql](@p1 NTEXT,
@parselength INT = 4000)
returns NVARCHAR(4000)
AS
BEGIN
DECLARE @pos AS INT;
DECLARE @mode AS CHAR(10);
DECLARE @maxlength AS INT;
DECLARE @p2 AS NCHAR(4000);
DECLARE @currchar AS CHAR(1),
@nextchar AS CHAR(1);
DECLARE @p2len AS INT; SET @maxlength = Len(Rtrim(Substring(@p1, 1, 4000)));
SET @maxlength = CASE
WHEN @maxlength > @parselength THEN @parselength
ELSE @maxlength
END;
SET @pos = 1;
SET @p2 = '';
SET @p2len = 0;
SET @currchar = '';
SET @nextchar = '';
SET @mode = 'command'; WHILE ( @pos <= @maxlength )
BEGIN
SET @currchar = Substring(@p1, @pos, 1);
SET @nextchar = Substring(@p1, @pos + 1, 1); IF @mode = 'command'
BEGIN
SET @p2 = LEFT(@p2, @p2len) + @currchar;
SET @p2len = @p2len + 1; IF @currchar IN ( ',', '(', ' ', '=',
'<', '>', '!' )
AND @nextchar BETWEEN '0' AND '9'
BEGIN
SET @mode = 'number';
SET @p2 = LEFT(@p2, @p2len) + '#';
SET @p2len = @p2len + 1;
END IF @currchar = ''''
BEGIN
SET @mode = 'literal';
SET @p2 = LEFT(@p2, @p2len) + '#''';
SET @p2len = @p2len + 2;
END
END
ELSE IF @mode = 'number'
AND @nextchar IN ( ',', ')', ' ', '=',
'<', '>', '!' )
SET @mode= 'command';
ELSE IF @mode = 'literal'
AND @currchar = ''''
SET @mode= 'command'; SET @pos = @pos + 1;
END RETURN @p2;
END