Search results matching tags 'Performance' and 'Tuning'

How to Tune a Database Using the Database Tuning Advisor (DTA)

Eric — Sat, 05 Apr 2008 13:51:14 GMT

The Database Engine Tuning Advisor (DTA) is a new tool in SQL Server 2005 that enables you to tune databases and improve the performances of your queries. DTA examines how queries are processed in the databases you specify and then it recommends how you can improve performance by suggesting the creation of indexes and statistics. It replaces the Index Tuning Wizard from Microsoft SQL Server 2000.

You can use DTA to tune a database by using workload (trace files) that were previously created with the profiler or you can tune a Query directly in Management Studio.

A workload is a set of Transact-SQL statements that execute against the database that you want to tune. The Database Engine Tuning Advisor use trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases.

In this article, we will create a few tables in the Adventureworks sample database. Run the following script:

USE AdventureWorks

SELECT * INTO DTA_Individual

FROM Sales.Individual

SELECT * INTO DTA_Customer

FROM Sales.Customer

SELECT * INTO DTA_CustomerAddress

FROM Sales.CustomerAddress

SELECT * INTO DTA_Address

FROM Person.Address

SELECT * INTO DTA_Vendor

FROM Purchasing.Vendor

SELECT * INTO DTA_VendorContact

FROM Purchasing.VendorContact

SELECT * INTO DTA_Employee

FROM HumanResources.Employee

SELECT * INTO DTA_SalesOrderHeader

FROM Sales.SalesOrderHeader

SELECT * INTO DTA_SalesOrderDetail

FROM Sales.SalesOrderDetail

SELECT * INTO DTA_Product

FROM Production.Product

Now we have tables to work with.

Creating a Trace file:

To run SQL Server Profiler, on the Start menu, point to All Programs, Microsoft SQL Server 2005, Performance Tools, and then click SQL Server Profiler. Or, you can to start the profiler from the tools menu in Management Studio.

Once the SQL Profiler is started, you need to create a new trace. “Select New Trace…” from the file menu:

After you have connected to the server, the Trace Properties window appears:

The DTA assumes that the workload trace file is a rollover file.

The captured workload information can be stored in a file (recommended for tuning) or in a table. I recommend that you use the SQL Server Profiler Tuning template for capturing workloads for DTA.

If you want to use your own template, ensure that the following trace events are captured for the version of SQL Server that you are using.

SQL Server 2005:

RPC:Completed

SQL:BatchCompleted

SP:StmtCompleted

SQL Server 2000:

RPC:Completed

SQL:BatchCompleted

The best time to create a trace file is when actual users are on the server doing real work. Not you running a few queries right and left. A workload should be representative of your typical load on the server. Starting the profiler WILL have an effect on the performance of the server. I also recommend increasing the max file size, since the profiler will create a new file once it reaches the limit. I normally increase it to 100MB.

Running the Trace:

Click on the run button and go back to Management Studio and run the following script:

USE AdventureWorks

SELECT City, NumberOfOrders = count(*)

FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'

JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID

JOIN DTA_Address a ON a.AddressID = ca.AddressID

JOIN DTA_SalesOrderHeader sh ON c.CustomerID = sh.CustomerID

JOIN DTA_SalesOrderDetail sd ON sd.SalesOrderID = sh.SalesOrderID

JOIN DTA_Product p ON p.ProductID = sd.ProductID

WHERE StateProvinceID = (SELECT StateProvinceID FROM Person.StateProvince WHERE StateProvinceCode = 'CA')

GROUP BY City

ORDER BY 2 DESC

SELECT i.CustomerID, c.ModifiedDate, City

FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'

JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID

JOIN DTA_Address a ON a.AddressID = ca.AddressID

WHERE i.CustomerID = 13311

SELECT i.CustomerID, c.TerritoryID, City

FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'

JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID

JOIN DTA_Address a ON a.AddressID = ca.AddressID

WHERE StateProvinceID = (SELECT StateProvinceID FROM Person.StateProvince WHERE StateProvinceCode = 'CA')

ORDER BY i.CustomerID, City

When you go back to the profiler, you will see everything that was sent to the server:

When you will be running this for real (on your production server), you have to let the trace run for a while; I let it run for 20-30 minutes. When you feel you have a good workload, you can stop the trace and quit the profiler.

Running the Database Tuning Advisor:

To begin, open the DTA, From the Windows Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Performance Tools, and then click Database Engine Tuning Advisor. In the Connect to Server dialog box, verify the default settings, and then click Connect.

By default, Database Engine Tuning Advisor opens to the configuration in the following illustration:

Before you can start the DTA, you need to select the trace file and select the database(s) to tune.

Click the Start Analysis button on the toolbar. While Database DTA is analyzing the workload, you can monitor the status on the Progress tab. When tuning is complete, the Recommendations tab is displayed.

After a couple of minutes (could take a while depending on the trace file size) the Recommendations tab is displayed and you can see the recommendations:

At this point, you have two choices; you can apply the recommendations directly or save them to a file. I personally save the recommendation to file because I like to see what suggestions were made and I try to understand them. You do this by clicking Save Recommendations on the Actions menu. You will have to execute the file in Management Studio to implement the changes.

For this demo, it is OK to apply them. If you apply the recommendation directly from the DTA, you will have more problems removing them afterwards.

What was created?

Once you have implemented the recommendations you can see what was created by looking in the Indexes and the Statistics level for a given table in the Object Explorer. All objects created by the DTA starts with “_dta”.

Cleaning up:

You can run this script to clean up our mess:

USE AdventureWorks

DROP TABLE DTA_Individual

DROP TABLE DTA_Customer

DROP TABLE DTA_CustomerAddress

DROP TABLE DTA_Address

DROP TABLE DTA_Vendor

DROP TABLE DTA_VendorContact

DROP TABLE DTA_Employee

DROP TABLE DTA_SalesOrderHeader

DROP TABLE DTA_SalesOrderDetail

DROP TABLE DTA_Product

Hope this was useful,

Eric Vaillancourt

http://www.sqlprof.com

Technorati : SQL Server 2005,Tuning,Profiler,Index,Statistics,Performance

Avoid using local variables in your Select statements

Eric — Sat, 29 Mar 2008 18:36:04 GMT

People are always surprised when I tell that their queries will run slower when they use local variables in their queries. Let me explain: If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a guesstimate for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer will do a better selecting a query plan. For example, execute both of these queries. The first one uses a local variable and the second one a literal:

use adventureworks

--first one with a local varaiable

declare @StartOrderDate datetime

set @StartOrderDate = '20040731'

select * from Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl

WHERE hdr.SalesOrderID = dtl.SalesOrderId

AND hdr.OrderDate >= @StartOrderDate

--secondeone with a literal

SELECT * FROM Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl

WHERE hdr.SalesOrderID = dtl.SalesOrderId

AND hdr.OrderDate >= '20040731'

The number of rows from Sales.SalesOrderHeader that the optimizer estimates will qualify vs. the condition hdr.OrderDate >= @StartOrderDate is 9439.5, which is exactly 30% of the size of the table. (the observations that follow are based on my SQL Server 2005 version; your results may differ depending on your SQL Server version, available memory, etc.).

The cardinality of the result set for the predicate "hdr.OrderDate >= '20040731'" is estimated as 40 for a selectivity of 0.13%. The plan chosen for this query uses a nested loop join instead of a merge join because of this improved estimate.

To force SQL Server to optimize a query every time it is run, and use the values of local variables to estimate cardinality and cost during optimization of the query, add the RECOMPILE query hint to the query. For example, modify the first of the two preceding example queries shown to:

SELECT * FROM Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl

WHERE hdr.SalesOrderID = dtl.SalesOrderId

AND hdr.OrderDate >= @StartOrderDate

OPTION(RECOMPILE)

This eliminates the problem of bad estimates due to local variables, at the cost of recompiling the query every time you run it. Compilation overhead can be quite high, especially for frequently-run, small queries. To avoid this overhead, further modify your application to use parameterized batches or stored procedures instead of dynamic SQL.

To eliminate the use of local variables, consider :

Rewriting the query to use literals directly in queries instead of assigning them to variables and then using the variables in the queries;
Using sp_executesql or API server cursors with parameters that replace your use of local variables;
Using a stored procedure with parameters that replace your use of local variables.

Hope this helps,

Eric Vaillancourt

Technorati : SQL Server 2005,Tuning,Statistics,Performance

Reference : http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

Understanding SET STATISTICS IO and SET STATISTICS TIME

Eric — Sun, 23 Mar 2008 14:15:07 GMT

Today, I want to talk about two commands that are often overlooked. SET STATISTICS IO and SET STATISTICS TIME these two commands are very useful when comes time to tune a query.

Tuning a query seems simple enough. In essence, we want our searches to run faster. When I teach my class on “Optimizing SQL Server” I realize that many people find it difficult to accomplish this task. While there are many reasons why query tuning is difficult, this article will concentrate on one aspect. And that is that query tuning takes place in an environment that is often changing from second to second, making it hard to really know what exactly is going on.

When you run a query, different server resources are used. One of those resources is the CPU time. If you run the same query over and over again, the CPU time needed to run the query will remain very close from one execution to other. I am not talking about the total time it takes to run query from the start to the end, but the amount of CPU time needed to run the query.

Another resource that the Server needs is IO. Whenever you run a query, the server must retrieve data from memory (logical reads) or from the disk (physical reads). Using high CPU and IO resources will result in slow query performance.

So your goal should be to rewrite your queries in such a way that they use less CPU and IO resources. If you accomplish this, then the performance of your query will improve.

Both the SET STATISTICS IO and the SET STATISTICS TIME Transact-SQL commands have been around a long time. But for some reason, they are often ignored.

Both commands can be “turned on” using Transact-SQL commands or by setting the appropriate connection properties in Options dialog from the tools menu in Management Studio.

By default, these settings are not turned on. The SET STATISTICS IO and the SET STATISTICS TIME commands act like settings, turning on and off various reported information on the resources our queries use.

So let’s look at an example of how use these commands.

To begin our example, start Management Studio and connect to a SQL Server. For this example, we will use the sample database provided by with SQL Server 2005 and that is AdventureWorks. So start by making AdventureWorks your default database.

Next, run this query:

USE AdventureWorks;

SELECT * FROM Person.Contact;

Normally this query should return 19972 rows. Now, let’s run the same query, but this time, let's run the SET STATISTICS IO and the SET STATISTICS TIME commands before we run the query. Before we begin our example, run these two commands DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE (don’t run these on a production server) first. These two commands will clear out SQL Server’s data and procedure cache.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Now, enter and run the following Transact-SQL commands:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Once you have done this, then run this command again:

SELECT * FROM Person.Contact;

After you run these command, you will see new information in the message tab that you did not see before.

(The results you get may vary, but we will talk about this below as we explain everything you see above.)

Let me explain what it all means:

SET STATISTICS TIME Results:

“SQL Server parse and compile time:” tells us how much CPU time and total time it took SQL Server to parse and compile our T-SQL statement. In my case, it took 0 ms. of CPU time, and a total time of 359 ms. of elapsed time. Your CPU time will probably vary because your server is different than mine.

If you re-execute the query, again without clearing the cache (as recommended above), most likely both instances of CPU and compile time will be 0, as the cached query plan will be reused, so no new compile time is required.

This is not too important, what is of interest to us is the time information displayed at the bottom of the query’s output. Here is it is again:

SQL Server Execution Times:

CPU time = 125 ms, elapsed time = 1112 ms.

What this tells us is how much CPU time was used to execute the query, and how long the query took to run. The CPU time is a somewhat consistent measurement of the amount of CPU resources it takes for your query to run, and this is relatively independent of how busy your CPU is.

The elapsed time number is a measurement of how long the query took to execute (not counting the time for locks or reads). This number will vary a lot because depending on the load on your server.

Because the CPU time is relatively consistent, this is what you should use to determine whether the changes you make in your queries, are helping or making things worse.

SET STATISTICS IO Results:

Table 'Contact'. Scan count 1, logical reads 561, physical reads 1, read-ahead reads 559, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is not all useful; let me try to explain what it means.

Output item	Meaning
Table	Name of the table, in our case ‘Contact’
Scan count	Refers to the number of times that tables referenced in the query have been accessed. In our example, the table in our query was only accessed 1 time. For queries that don’t include joins, this information is not too helpful. But if your query has one or more joins, then this information may be of use to you. You may want to pay attention to the Scan Count, watching to see if it is increasing or decreasing as you tune the query.
logical reads	Number of pages read from the data cache. This is the most useful piece of data provided by the SET STATISTICS IO commands. As you probably know, before SQL Server can do anything with data, that data must be in its data cache. In addition, when SQL Server reads data from the data cache, it reads it in 8K pages. A logical read is the number of pages SQL Server had to read from the data cache in order to produce the results specified by our query. The exact same number of logical reads will always occur when the same query runs against the exact same data on SQL Server. Knowing the number of logical reads that SQL Server takes to execute a query is important because it is the one thing that never changes from one execution to another giving us the perfect reference to use when we are tuning a query to determine if you we are succeeding or failing. The fewer the logical reads performed by a query, the more efficient it is, and the faster it will perform, assuming all other things are held equal.
physical reads	Number of pages read from disk. This information is not too useful for query tuning. First of all, a physical read refers to when SQL Server must go to disk to get data pages it needs to put into the data cache before it can execute the query. As mentioned above, all data must be in the data cache before SQL Server can execute a query. Whenever SQL Server begins to execute a query, the first thing it does is to check and see if the data pages it needs are in the data cache.
read-ahead reads	Number of pages placed into the cache for the query. Like physical reads, this information is not too useful for query tuning. This value tells you the number of physical page reads that SQL Server performed as part of its read-ahead mechanism. To help optimize its performance, SQL Server reads physical data pages ahead of when it thinks your queries might need the data.
lob logical reads	Number of text, ntext, image, or large value type (varchar(max), nvarchar(max), varbinary(max)) pages read from the data cache.
lob physical reads	Number of text, ntext, image or large value type pages read from disk.
lob read-ahead reads	Number of text, ntext, image or large value type pages placed into the cache for the query.

Now for a real example:

To be honest with you, when you execute a query without a WHERE clause there’s not much you can do to tune a query because the server performs a scan on the table. Let’s take another example:

Execute the following code:

USE AdventureWorks;

SELECT * FROM Person.Contact WHERE firstname='Carla';

Now look at the Message tab:

Even though we have filtered our query and the elapsed time is down (data is cached), the logical reads are still at 561. This means that reading 25 rows is costing us the same as in the previous example that was returning 19972 rows.

One thing we could do to reduce the number of logical reads is to create an index on firstname (I will cover the creation of index in another article).

You can create an index directly in Management Studio by selecting the table in the object explorer and right clicking on the Indexes folder:

A new index dialog will appear:

Or you can execute the following code:

USE [AdventureWorks]

CREATE NONCLUSTERED INDEX [IDX_firstname] ON [Person].[Contact]

(

[FirstName] ASC

)

Either way, you will achieve the same result. Now re-execute the query and look at the message tab:

Now that is quite an improvement…

Hope this was useful, feel free to post any comments.

Eric

Technorati : SET STATISTICS IO,SET STATISTICS TIME,Tuning,SQL Server,Performance