Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0134 - SQL Server – SSMS – Tutorial – 09 - Integrating with peripheral services & other SQL Server tools (L200)

Feb 13 2012 12:00AM by Nakul Vachhrajani   

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:

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;
       [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…

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

Tags: Development, Administration, T-SQL, Performance Tuning, Tips, SQL Server, #SQLServer, #TSQL, Guidance, DBA, SSMS, Tools and Utilities, Community, Series, Debugging,

Nakul Vachhrajani
4 · 36% · 11648


Your Comment

Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]