As mentioned in Part 01 of this tutorial series, one of the key responsibilities of SSMS was to replace the erstwhile SQL Server Enterprise Manager – the interface that is used to manage & maintain the SQL Server instance configuration. In addition to the tools and utilities available within the SSMS, there are other peripheral tools & utilities that work in tandem with SQL Server to help you develop T-SQL code, manage & maintain the SQL Server instance.

SQL Server Agent

The SQL Server agent needs no introduction. The SQL Server agent is actually a Windows service (separate from the SQL Server service) that executes scheduled administrative tasks, called jobs. Being a process external to the SQL Server, the Agent also performs a very important function of monitoring the SQL Server (thereby generating necessary logs) and processes SQL Server alerts.

To know more about the SQL Server Agent, please visit Books On Line at: http://msdn.microsoft.com/en-us/library/ms189089.aspx

SQL Server Profiler

There would hardly be a soul on the planet who would have worked with SQL Server and not used the Profiler, which is undoubtedly the 2nd most frequently used utility. The SQL Server Profiler integrates with the SSMS providing easy access.

One way of launching the Profiler from SSMS is by using the Tools menu. However, there is no difference between launching the Profiler externally or via the Tools menu.

Normally, when the Profiler is launched externally, one needs to login to the SQL Server instance and then choose the trace required. When launched from the query editor in SSMS, these two steps are bypassed, thereby improving general developer productivity.

For example, let’s say you have the following query open in SSMS and would like to trace it using the SQL Server Profiler.

USE AdventureWorks2008R2;
GO
SELECT VendorID, 
       [250] AS Emp1, 
       [251] AS Emp2, 
       [256] AS Emp3, 
       [257] AS Emp4, 
       [260] AS Emp5
FROM (SELECT PurchaseOrderID, EmployeeID, VendorID
      FROM Purchasing.PurchaseOrderHeader) p
PIVOT (COUNT (PurchaseOrderID)
       FOR EmployeeID IN ( [250], [251], [256], [257], [260] )
      ) AS pvt
ORDER BY pvt.VendorID;

On the Query editor, right-click and choose “Trace query in SQL Server Profiler” or use the key combination (Ctrl+Alt+P). You will find that the SQL Server Profiler launches, the trace chosen as default is launched and all that remains now is to run/execute the query in the query editor. The SQL Server Profiler will trace the execution of the query as required.

SQL Server Database Engine Tuning Advisor

The Database Engine Tuning Advisor (DTA) helps you select and create an optimal set of indexes, indexed views, and partitions for a given workload under evaluation. Provided that the hardware, SQL Server and it’s configuration, and the database itself mimic production environments, the DTA is fairly accurate in it’s suggestions. However, as is the case with all SQL Server utilities and recommendations, please validate whether they would be helpful in your particular scenario before rolling out into production use.

The DTA can be launched from the SSMS via the Tools menu (refer screenshot above). The other option is to right click in the query editor and choose “Analyze query in Database Engine Tuning Advisor”.

After adjusting the tuning options in the “Tuning options” tab, click on the “Start Analysis” button to start the analysis.

 

At the end of the tuning session, the recommendations are made available in the “Recommendations” tab. More details are available in the “Reports” tab.

Practice Exercises…

As practice, identify the most common workloads from your production application which you plan to optimize.

• Run a profiler trace against this trace. Are you able to identify the potential bottlenecks from the Profiler trace?
• Next, run the DTA against this workload. What are the recommendations that the DTA identifies? Using the Reports tab, decide whether you would accept these recommendations or not

Further reading…

• SQL Server Agent
• Database Engine Tuning Advisor Overview
• Understanding the Database Engine Tuning Advisor
• Using the Database Engine Tuning Advisor

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql