SQL 2012 Database Tunning Advisor DTA - Procedure Cache

Hi Friends,

Its Sunday afternoon and prior to go out for shopping this evening I tought lets write a blog.

Here by in this blog I will be talking about a new cool feature of SQL Server 2012 Database Tunning Advisor - Plan Cache.

Untill SQL 2008 we were just having 3 option to provide work load to DTA in order to analyze and tune.

Note: If we directly open DTA and create a new session it will show 2 options only i.e. File and Table (as above pic). But if we open query analyzer and select the query that want to tune and then right click and then say "Analyze Query In Database Tunning Advisor" then it will show the third option "Query" (below pic).

This same feature "Query" is available there in SQL 2012 as well.

SQL 2012 Database Tunning Advisor Workload - Plan Cache

Starting from SQL 2012, in Database Tunning Advisor we can provide a new form of workload i.e. Plan Cache. This is pretty cool feature as it can analyze and provide recommendations on the workload which is there present in Plan Cache. This feature pretty good when you want to fix/optimize databases which are performing slow. We can directly use DTA an use Plan Cache workload option and find the recommendations.

Lets try using this feature:

Connect to database "AdventureWorks2012" and execute DBCC FreeProcCache to clear existing procedure cache entries.

Use AdventureWorks2012
Go
DBCC FREEPROCCACHEGo

Lets execute few queries against few of the tables to fill Procedure Cache.

Select

B.FirstName+' '+ B.MiddleName+' ''+B.LastName,
A.JobTitle,C.PhoneNumber,D.EmailAddress
From HumanResources.Employee A, Person.Person B, Person.PersonPhone C, Person.EmailAddress DWhere A.BusinessEntityID=B.BusinessEntityIDAnd A.BusinessEntityID=C.BusinessEntityIDAnd A.BusinessEntityID=D.BusinessEntityIDGo

Select

B.FirstName+' '+ B.MiddleName+' '+B.LastName,
A.JobTitle,C.PhoneNumber,D.EmailAddress
From HumanResources.Employee A, Person.Person B, Person.PersonPhone C, Person.EmailAddress DWhere A.BusinessEntityID=B.BusinessEntityIDAnd A.BusinessEntityID=C.BusinessEntityIDAnd A.BusinessEntityID=D.BusinessEntityID

And JobTitle Like '%Manager%'Go

Lets check the execution plan for the queries.

Select

[text], usecounts
From sys.dm_exec_cached_plans AS cp
Cross Apply sys.dm_exec_sql_text(plan_handle)
Where cp.cacheobjtype = N'Compiled Plan' And dbid=db_id ('AdventureWorks2012')

Lets try using DTA with Plan Cache as a workload.

Step 1 - Open DTA and go to General Tab and select

           - Workload option as "Plan Cache"

           - Database for workload analysis as "AdventureWorks2012"

           - Select database and tables to tune as "AdventureWorks2012"

Step 2 - Click on "Tunning Options" tab and select

        

           - Physical Design Structures (PDS) to use in database, I selected Indexes option only. You

              can use option as per your requirement.

           - Partitioning strategy to employe, I kept it default i.e. No Partiotioning.

           - Physical Design Structures (PDS) to keep in database, I selected "Keep all existing PDS" 

             option so that all existing structures remain intact. 

Step 3 - Click on "Advanced Options" button on Tunning Options tab and select

           - Include plan cache events from all databases. (will discuss this option in a while).
           - Generate online recommendations where possible.

Step 4 - Click on Start Analysis button. Once analysis completes it will provide recommendations as 
              below.

DTA Utility

The above analysis can also be done using the dta command line utility.

dta -E -D AdventureWorks2012 -ip -ipf -n 1100 -s TuneUsingPlanCache

Where in the option

ip:   is for plan cache workload
ipf:  is to analyze plan cache events for all database.
-n:   is to increase the number of cache events to be analyzed for from the default value of 1000 to
        1100.
        
Note:

* By default Plan Cache will use first 1000 event from the plan cache to use for analysis.
   We cannot change this limit in GUI, the only option to change it using DTA command line utility.

* Pausing Database Tunning Advisor is not supported. Once we click on "Start Analysis" the only 
   options we have is to "Stop Analysis" or "Stop Anlysis (with recomendations)".

        Stop Analysis : stops the tunning session without generating any recommendation.
        Stop Analysis (with recommendations) : stops the tunning session and prompts you to decide
        whether you want Database Engine Tunning Advisor to generate recommendations based on the
        analysis done till that point.


Include plan cache events from all databases option

As shown above we selected this option by click on "Advanced Option" button. The reason for selecting this button is:

Suppose we have a query which joins other tables of some different database and we want to analyze that query using DTA then this option will be helpful as it will use all the events from plan cache for analysis that will give good and more accurate results.

Hopefully this blog will be helpful. Please post your comments.

Regards
Gurpreet Singh Sethi

SQL 2012 Cluster Tempdb On Local Disks

Hi Friends,

Here by in this blog I am going to discuss a new feature of SQL Server 2012 cluster with regard to TempDB.

Lot of time we have seen that on a busy SQL Server Instance the Tempdb database possess high IO load. The IO load can be much high than other database if we are using row versioning. As we know TempDB is a shared resource and used by all toher databases so this put more stress on it.

Now in clustered instance of SQL this is requirement that all database files should reside in Shared Storage, this holds true for TempDB as well. Now on a busy SQL environment high IO on a TempDB can lead to slow response due to high burden on System's HBA and storage arrays. So a question arises....

Why should I keep TempDB on a Shared Disk, why not on a Local disk of the node which is part of cluster?

It will be easy as its a local drive and requesnsts need not to traverse via HBA, Storage arrays etc. etc.

Well SQL team has listen to this requst of users and comeup with a solution for that. Starting from SQL 2012, on a cluster instance we can keep TempDB on the local disk of the node.

How to do that?

First below is my disk layout on a Virtual Cluster and location of TempDB files for a SQL 2012 instance.

So I have 4 cluster drives ( E:, F:, X:, Y:). My TempDB files are on Y: drive (above screenshot).

Now lets move the files to local disk of specific cluster node.

In order to move the location of TempDB we will be using the same ALTER DATABASE commands.

Alter database tempdb modify file (name='tempdev',filename='C:\SQLData\tempdb.mdf')
Go
Alter database tempdb modify file (name='templog',filename='C:\SQLData\templog.ldf')
Go

After running above command in the message section we have below message written:

Local directory 'c:\sqldata\tempdb.mdf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server Service has read/write permission on it.
The file "tempdev" has been modified in the system catalog. The new patch will be used the net time the database is started.

Local directory 'c:\sqldata\templog.ldf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server Service has read/write permission on it.
The file "templog" has been modified in the system catalog. The new patch will be used the net time the database is started.

 Regards
Gurpreet Singh Sethi

Install SQL Server 2012 Books On Line On Local Disk

Hi Friends,

Here in this blog post I am going to share with you one of the strange thing (yes its strange atleast for me and I am sure you will also find the same).

SQL 2012 installation will not install ages old Books On Line on your local computer along with your SQL Instance. Yes.... this is true..... Not sure why MS thought of removing default installtion of Books On Line on local disk (may be they are going to promote Internet ... ;) ).

With default installation of SQL 2012 everytime one try to start Books On Line it will take him/her to internet page.

How to overcome this? How to install BOL on local machine?

1. Download book online local version from http://www.microsoft.com/download/en/details.aspx?id=347 and save "SQLServer2012Documentation.exe" on local disk.


2. Run SQLServer2012Documentation.exe and unzip the documents in some folder, say C:\SQL2012BOL.


3. Now, open "Manage Help Settings".

4. Select "Install Component From Disk".

5.Traverse to location where we unzip the BOL files i.e. C:\SQL2012BOL and select "HelpContentSetup.msha" file.

6. Click Add next to the documentation you want to install the click "Update" button.

                          

7. Go back to first page, click "Choose Online or Local help".

                           


8. Select "I want to use local help".

                           


Update: I had a talk with one of my friend in Microsoft Support, I asked him why MS not installing Books On Line along with SQL Server Engine in SQL 2012. As per him one reason is that people are not updating Books On Line patches as regulary as they do for DB Engine or other components. This leads to confusion as changes made to BOL are no updated and people still refer to old material which leads confusion... Interesting...isn't it?

Hope this post will help you.

Regards
Gurpreet Singh Sethi

SQL 2012 Restore Database Relocate and Timeline Option

Hi Friends,

I our daily DBA routine couple of time we restore our backups either on same instance  (with a new name) or to a different instance.

If we are performing a restore on the same instance (source of the backup) and restoring it with a new name then during restore we have to change the name or location of the files. Similarily if its different location then also we have to move the location of files as per that instance setting or as we desire.

Untill now i.e. till SQL Server 2008 we have to change the name of the files (if we are restore database on same instance with a new name) or we have to change the location of database files using OPTIONS tab of Restore Windows.

In above screenshot we have a database "Test_Restore" already present on my SQL Instance. I took a backup of the same and trying to restore it with name "Test_Restore2".

Once we clien on Options table we are seeing that the file names got changed to "Test_Restore2.mdf" and "Test_Restore2_1.ldf". This is because while restoring we gave the name as "Test_Restore2" for the database.

This is simple but when we try to restore the database from one instance to second instance there we have to change the location of the file in OPTIONS tab which is manual.

So what got changed in SQL 2012 for this...?

In SQL 2012 during restore we have some new option in place

1) TimeLine option
2) Rellocate all files to folder
3) Tail Log of Source Database

In above screen shot on General tab  we are trying to restore "Test_Restore" database as "Test_Restore2" on the same server. In the screen shot (arrow at the top) we can see it says "A tail log backup at the source database will be taken. View the settings at the option tab." also it automatically displays when was the last backup taken (which we are using for restore) and a TimeLine button (will discuss shortly).

Another feature is "Verify Backup Media" , one we click on this it will run "RESTORE VERIFYONLY" option on the backup file. It will run it with NOUNLOAD & NOREWIND option.

RESTORE VERIFYOLNY FROM DISK=N'Location and Backup File Name' WITH FILE=, NOUNLOAD, NOREWIND

In above screen shot on Files tab we are seeing 2 things

* Relocate all files to folder

A check box which if we select Data file folder and Log file folder will get activated and we can specify where we want to keep the files (in which folder). This is good feature as just one time we have to specify the location and if we have a database with multipl files then the location will be changed automatically.

* Restore As

As displayed SQL Server 2012 onward we have this Restore As field which will automatically point to default location of DATA folder for the instance and restored files will be placed in this folder.

Isn't this cool, no need to change the location..... :)

Also check the name of the database & log files the name changed to TestRestoreSQL2012 as that is the new name of the database that we have given while restore (same as in it is there till SQL 2008).

In above screenshot, Recovery state: RESTORE WITH RECOVERY (same as its in SQL 2005, 2008, 2008 R2). Main thing to discuss here is TAIL LOG BACKUP option.

If we are restoring database on the same sever from which it originate and if the source database is online, during restore it will try to take a tail log backup prior to restore and bring the source database into RESTORING mode.

In above screen we are taking TailLog backup of source database but we are not keeping the source database in NO RECOVERY as we are restoring the backup with a new name TestRestoreSQL2012.

TimeLine Option

To test timeline option please use below code to create a database TestTimeLine and create table TestTimeLine, then insert 1000 records and take a full backup, then insert 500 records take transaciton log backup and then at last insert another 500 records and take another transaction log backup.

Create Database TestTImeLine;

Go

Create Table TestTimeLine (ID Int Primary Key, Name Varchar(100))

Go

Declare @I Int

Set @I=1

While @I<1001

Begin

Insert Into TestTimeLine Values (@I,'Ujsol*9kKkmm')

Set @I=@I+1

End

Go

Backup Database TestTimeLine TO Disk ='F:\TestTimeLine.Bak'

Go

-- Inserted another 500 records and took transction log backup ---



Declare @I Int

Set @I=1001

While @I<1501

Begin

Insert Into TestTimeLine Values (@I,'Ujsol*9kKkmm')

Set @I=@I+1

End

Go

Backup Log TestTimeLine TO Disk ='F:\TestTimeLine_Log1.trn'

Go

-- Inserted another 500 records and took transction log backup ---



Declare @I Int

Set @I=1501

While @I<2001

Begin

Insert Into TestTimeLine Values (@I,'Ujsol*9kKkmm')

Set @I=@I+1

End

Go

Backup Log TestTimeLine TO Disk ='F:\TestTimeLine_Log2.trn'

Go

Now lets try to restore it using TimeLine option.

First lets select the Full, 2 Transaction Log backups which we took by running above script.

Next lets click on TimeLine button and see what we get?
Here in below screen we are seeing that we have backups taken till 22-Apr-2012 10:12:23 AM (in time filed its showing 10:10:23 AM - as that it when we took out Full backup) and it consist of Full and Transaction Log Backups (see the legends for color).

Dark Green - Full
Parrot Green - TransactionLog
Red Line - End of backup

Now lets select the Specific Date and Time button: Lets select the bar and move it to 10:11:04 AM and then restore it.

At options tab we are not going to select any Tail Log backup of source database (as we are doing a Time Line Recovery from our backups).

So restore is successfull. Now lets try to do a SELECT * FROM TESTTIMELINE table in this newly restore TESTTIMELINE2 database. In my output I got records till 1500 only as the timeline which I mentioned to restore at that time only 1500 recrods were present in the database and 1500 onwards records were still not added (were in process getting added).

Important: TailLog backup restore option will only work if we have the source database residing onthe same server where we are restoring the backup. Don't expect that it will connect to remote server and take a fresh tail log backup... ;)

Hopefully this post is helpful to you. Let me know your feedback.

Regards
Gurpreet Singh Sethi

Explore SQL Server 2012

Hi Friends,

From today onwards I am going to write blogs on SQL Server 2012 (Exsting enhancements, New Features, Settings, Options etc. etc.).

Thanks for visiting my page.

Regards
Gurpreet Singh Sethi

DBCC CHECKDB WITH WITH ESTIMATEONLY on MASTER Results 4 Rows

Hi All,

Today while working on something I figure out something. When we run DBCC CHECKDB (master) WITH ESTIMATEONLY the result set has 4 rows. Why?

Command:

DBCC CHECKDB (MASTER) WITH ESTIMATEONLY

Output:

Estimated TEMPDB space needed for CHECKALLOC (KB)

-------------------------------------------------
240

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
668

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
521

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
14535

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So in the above output we are seeing that there 2 entries for CHECKALLOC and CHECKTABLE.

WHY?

Anser is: The second entry is for RESOURCE DATABASE.

Try running DBCC CHECKDB on MASTER and in the output you will see that there will be 2 outputs (One for MASTER db and other for RESOURCE db). Even as per books online:

when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results. The command returns extra result sets when no options are set, or when either the PHYSICAL_ONLY or ESTIMATEONLY option is set.



 
Regards
Gurpreet Singh Sethi