Workload Failed in Database Engine Tuning Advisor

What to do when Consuming Workload Fails in Database Engine Tuning Advisor? You may face below errors during analysis so let’s solve these issues.

Error 1:

The minimum storage space required for the selected physical design 
structures exceeds the default storage space selected by Database Engine 
Tuning Advisor. Either keep fewer physical design structures, or increase 
the default storage space to be larger than at least 3 MB.Use one of the 
following methods to increase storage space: (1) If you are using the 
graphical user interface, enter the required value for Define max. space 
for recommendations (MB) in the Advanced Options of the Tuning Options 
tabbed page; (2) If you are using dta.exe, specify the maximum space 
value for the -B argument; (3) If you are using an XML input file, 
specify the maximum space value for the <StorageBoundInMB> element under 
<TuningOptions>

Error 2:

The minimum storage space required for the existing physical design 
structures (PDS) you have selected to keep is larger than the storage 
space provided. Choose fewer PDS to keep, or set the storage space 
to be larger than 22 MBs.

Figure-1 is showing same error as above in GUI format.

Figure 1: Consuming Workload Failed

To solve this error through GUI kindly follow these steps as shown in Figure-2.

1. Go to Tuning Options

2. Tick mark ‘Define max. space for recommendations (MB)’

3. Assign memory, more than mentioned in the error so that Database Engine can easily process your workload.

4. Click OK and Start Analysis again. Issue resolved, it’s so simple.

Figure 2: Tuning Advisor Advanced Option

Database Engine Tuning Advisor Step By Step

Database Engine Tuning Advisor is inbuilt tool which comes with Microsoft SQL Server Management Studio which helps in analyzing required Indexes and Indexed views, Statistics, Partitioning Strategy and Physical Design Structure (PDS) to keep in the database for performance improvement. We will see step by step how Tuning Advisor provides recommendations for given workload by Query. We are going to use 3 HEAP tables mentioned in the below query.

SELECT stb.name, idx.type_desc FROM sys.tables stb 
INNER JOIN sys.indexes idx ON stb.object_id=idx.object_id
AND idx.type=0 ORDER BY stb.name

Fig: Find HeapTables

SELECT *FROM [tbl_Customer] CU INNER JOIN [tbl_SalesOrderHeader] SOH
ON CU.CustomerID = SOH.CustomerID INNER JOIN [tbl_SalesOrderDetail] SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE CU.AccountNumber = 'AW00025663'

Step 1:

First, write your SQL query on query window for which you want tuning recommendations, then right click and select “Analyze Query in Database Engine Tuning Advisor”.

Figure 1: Analyze Query in Database Engine Tuning Advisor

Step 2:

Database Engine Tuning Advisor window will pop up where by default your Session Name and Database will be selected in General tab.

Figure 2: Select Session Name and Database Name

Step 3:

Go to Tuning Options tab, select required Physical Design Structure (PDS) to use in the database,Partitioning Strategy to Employ and Physical Design Structure to keep in the database then click on Advanced Options and Define Maximum space for recommendation in MB, this space is required to Database Engine for Analysis purpose otherwise you may get error if there is insufficient space. You can give approximate space for this process and check.

Figure 3: Tuning Options

Step 4:

Now, click on “Start Analysis” button. The Status bar will show you the current progress and error if any. Once process is complete you will get Success status as mentioned in below image.

Figure 4: Progress Status

Step 5:

Go to Recommendations tab, where you will find Estimated Improvement in percentage for your workload. In our case, we can achieve 99% performance improvement after implementing recommended indexes on the tables. You can click on Recommended Definition Link and Copy the query from clipboard and implement it on your required database. You have option to apply query one by one or Go to Actions tab in Tuning Advisor and select Apply Recommendationswhere you can schedule it for later implementation for all your definitions.

Figure 5: Recommendations for Tuning

Step 6:

You can see the Tuning Summary from Reports tab where you can find some quick information of your workload query as in Figure 6.

Figure 6: Tuning Advisor Reports

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

SQL Server Architecture

Bulk Copy Program (BCP) Utility to Import and Export Data in SQL Server

Bulk Copy Program (BCP) is a powerful command line utility to import and export data in SQL Server database. If you do not have much knowledge about database commands then also you can use it easily. BCP utility is very fast in operation which can save lots of time for processing as compared to other method of bulk import and export of data. We will see how to use this utility and different useful switches involved in it.

First of all open windows command prompt and type bcp and press enter button where you can see different arguments which you can use for copying data to and from SQL Server instance and database. [refer Figure-1]

Figure 1: BCP Arguments

C:\Windows\system32>bcp
usage: 
bcp {dbtable | query} {in | out | queryout | format} datafile 
 [-m maxerrors]            [-f formatfile]          [-e errfile]
 [-F firstrow]             [-L lastrow]             [-b batchsize]
 [-n native type]          [-c character type]      [-w wide character type] 
 [-N keep non-text native] [-V file format version] [-q quoted identifier]
 [-C code page specifier]  [-t field terminator]    [-r row terminator]
 [-i inputfile]            [-o outfile]             [-a packetsize]
 [-S server name]          [-U username]            [-P password]
 [-T trusted connection]   [-v version]             [-R regional enable]
 [-k keep null values]     [-E keep identity values] [-h "load hints"] 
 [-x generate xml format file] [-d database name]   [-K application intent]

First line shows syntax for BCP.

bcp {dbtable | query} {in | out | queryout | format} datafile

{dbtable | query} – represents database table name to use as a source.
{in | out | queryout | format} – this arguments represents if you want to import data or export data and type of format file.
datafile – represents the full path of data file been used.

Now second part represents different arguments which can be used with bulk copy program (BCP) operations as shown in Figure-1.

Here, to understand it better, we will take few examples.

bcp AdventureWorks2012.dbo.tbl_Person out C:\BCPDemo\tbl_Person.txt -T -c

As shown above, we are copying data from database table to file on disk. AdventureWorks2012 is the name of database, dbo is schema name and tbl_Person is the name of the table which we are exporting to data file on disk. -T is switch used for trusted connections and -c is used for character data format. As you can see we have used out option which is normally used to export data.

Figure 2: Export Data with Trusted Connection

Figure shows 106 rows copied in 1 ms clock time with an average of 106000 rows per second, you can actually imagine how fast this utility is performing operations.

Below is a scenario of mixed mode authentication where you have to specify user name and server name to export data. -Usa is username and -SManzoor is server name, once you click enter, it will prompt you to enter password for user sa, you can also specify the password by switch -P (e.g. -P123456). After successful login it will copy data to the file. [refer Figure-3]

bcp AdventureWorks2012.dbo.tbl_Person out C:\BCPDemo\tbl_Person.txt -c -Usa -SManzoor

Figure 3: Export Data with Mixed Mode Authentication

If you are passing password along with other switches then it will directly perform the operation without prompting for it as shown below.

Figure 4: Export Data with Mixed Mode without prompting for Password

bcp AdventureWorks2012.dbo.tbl_Person out C:\BCPDemo\tbl_Person.txt -c -Usa -P123456 -SManzoor

we have passed -P switch with password 123456 which indicates password for login sa.

Now, we will take an example of import data from file location to database server, for that we need to understand structure of data file and create the table with same structure in database where you want to insert records.

Here we will copy data file tbl_Person.txt to a new table which we have created as tbl_PersonInfo with same table structure.

bcp AdventureWorks2012.dbo.tbl_PersonInfo in "C:\BCPDemo\tbl_Person.txt" -T -c

We have used ‘in‘ argument here and successfully imported data into tbl_PersonInfo table, you can select the table from database and verify it with data file. [refer Figure-5]

Figure 5: Import Data with Trusted Connection

If you have a requirement to export specific columns then we can do the same with queryoutargument. To use queryout argument you need basic knowledge of SQL commands as we have to write query as per requirements. Let’s see an example, we require two columns FirstName and LastName from table tbl_Person for that we will execute below query on windows command prompt.

Figure 6: Export Data with Specific Columns

bcp "SELECT FirstName, LastName FROM AdventureWorks2012.dbo.tbl_Person" queryout C:\BCPDemo\tbl_Person.txt -T -c

Once all rows copied you can cross check data  from table tbl_Person of BCPDemo folder on the disk.

Let’s take one more example where we will copy specific rows from table to a data file. [refer Figure-7]

Figure 7: Export Data with Specific Rows

bcp "SELECT * FROM AdventureWorks2012.dbo.tbl_Person WHERE FirstName='Gigi' AND LastName='Matthew'" queryout C:\BCPDemo\tbl_Person_Rows.txt -T -c

I have given you few examples of bulk copy program with txt file format, you can also use different file format like .dat, .fmt .xml etc by using -f and -x switches.

How to create xml file format, here is a simple line of commands.

Figure 8: Create XML File Format

bcp AdventureWorks2012.dbo.tbl_Person format nul -T -c -x -f C:\BCPDemo\tbl_Person.xml

You have to specify nul with format which creates format file including -x which indicates general xml format file along with -f.

Let’s open xml file which we have created just now, we will find as below.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
 <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
 <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
 <COLUMN SOURCE="1" NAME="BusinessID" xsi:type="SQLINT"/>
 <COLUMN SOURCE="2" NAME="Title" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="4" NAME="MiddleName" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="5" NAME="LastName" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="6" NAME="Country" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]