I recently had the opportunity to view some of the newest courses from Pluralsight, an organization that offers online training courses for developers. One of the courses that I viewed was the SQL Server Questions & Answers (http://www.pluralsight-training.net/microsoft/Courses/TableOfContents?courseName=sql-server-qa) by Pinal Dave (blog) and Vinod Kumar (blog), both of whom have been a source of inspiration to the entire SQL Server community and enthusiasts like myself.

Today, I take the opportunity to write a review on the course.

Book or Online course? Are they the same?

Pinal & Vinod recently authored a book “SQL Server Interview Questions and Answers”, which I reviewed in my post here. My review was “With real world questions and practical answers, this is one book you simply cannot afford to miss.”

So, the question now is – both the book and the online course have similar names. Are they the same? My answer: No.

While the online course is based a highly filtered content from the book, the online course is much more than the book. Pinal Dave takes the place of the garden-variety SQL Server developer & administrator whose mind is full of myths surrounding SQL Server. Vinod Kumar spreads light by demonstrating the problems caused by these common misconceptions and also demonstrates the right way to do things.

The online course is more of a “myth-buster”, and is definitely not the same as the Interview Questions book.

Overall feel of the course

When I first looked at the course index, I thought – there’s no way such a laundry list of concepts can be covered in a time that’s just above 120minutes (2 hours). But, the simplicity by which Pinal & Vinod explained the most complicated concepts left me hungry for more. I took the entire course in one, uninterrupted session and it’s quite possible that almost everyone who views the course would have a similar experience.

The course is all about eliminating some of the most common misconceptions in the developer community around SQL Server. Here are some of the best:

• Are stored procedures pre-compiled?
• What is the difference between temporary tables & temporary variables?
• Does the order of columns in the WHERE clause matter?
• What is the real difference between DELETE & TRUNCATE?

In addition, the course also enlightens us on some of the basic elements like – how long should a transaction be? or, are locks maintained throughout the duration of the transaction?

At one point in the course, Vinod says – "Dynamic SQL is really not bad, it’s the way in which we prepare & execute SQL that matters." – It just can’t get any better than this!

Some surprises….

Pinal & Vinod never cease to surprise me. The final part of the session is not about questions and answers, but about how the various Tools & utilities within SQL Server can help us make working with SQL Server a breeze. While I have written about them in the past and use them in my day-to-day work, I was surprised to note a couple of things that Vinod had up his sleeve.

1. Activity Monitor
2. Object explorer details
• An Introduction to the query editor details window
• Searching within the object explorer details window
3. Filtering within Object Explorer details
4. Template Explorer
• Vinod demonstrates some really cool tricks here (and my post does not cover them)
5. Query Editor & Query Editor results tab (I won’t share any links here – watch the course!)

My Recommendation

Every SQL Server developer must take the course. If you are into DB design, this course has elements that you would definitely enjoy, especially the part which demystifies DENY v/s GRANT operations.

Do not miss the course for a highly productive work environment! So, this week-end, register with Pluralsight and take the course.

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

As you know, I recently completed a 10-part tutorial series on the SQL Server Management Studio (SSMS). As I finished writing the tutorial, I remembered that one of the issues most SQL Server developers encounter are the slow load times of the SSMS. Sometimes, SSMS is just not fast enough for a quick session. In this post, I will share with you 2 simple tweaks I use to improve the launch performance of SSMS.

Simplify the SSMS interface

Almost each dedicated pane in SSMS that allows you to directly interact with your SQL Server instance establishes a connection to the SQL Server. The Object Explorer (which normally opens by default when you launch SSMS) and the Object Explorer Details window launch 2 separate connections to the SQL Server instance, which are in addition to the connection opened by the Query Editor window. Read my post: http://beyondrelational.com/blogs/nakul/archive/2011/01/17/the-multiple-connections-of-ssms.aspx – for details on this fact.

Each new connection means an increase in the resources consumed and additional time taken to establish the connection. Therefore, the first rule of optimizing SSMS launch times has to be to keep the SSMS interface simple and close out any unused panes/monitors open within SSMS.

Controlling the startup windows

To configure what windows should be opened up by SSMS at startup, simply navigate out to Tools –> Options within an SSMS session. In the “Options” window, go to the “Startup” options under “Environment” node. (For SQL Server 2005, 2008 and 2008R2 users, you would go to the “General” page)

You may want to select either “Open new query window” or “Open empty environment” for some of the fastest SSMS launch times.

Adjust the Visual Experience based on the client performance (SQL 2012/”Denali” and up)

Starting SQL Server 2012 (code named “Denali”), the “General” page of the Options window has a section that helps you tweak the visual experience of the SSMS based on the client performance. By default the SSMS automatically adjusts itself based on the client performance, and I have found that if I leave it as-is, things work just fine for me.

Automate the launching of SSMS and connecting to your preferred SQL Server instance

Many a times, improving the load times may not just be about launching a particular application faster. In fact, for me, launch time for any application is the time the application takes to load up + the time it takes for me to actively use the application. In case of the SQL Server management studio, the launch time would therefore be a summation of:

1. Launching the SSMS
2. Selecting the SQL Server instance
3. Entering connection information
4. Clicking OK to the connection dialog

Wouldn’t it be much faster and simpler for a developer to have all these steps rolled up into one?

Using the SSMS command line options

SSMS allows us to specify a few command line options that eliminate the need for a user to select the required SQL Server instance and enter the connection information every time they need to use SSMS. Most developers need to connect to only a handful of SQL Server instances. These instances would not change for the duration of the project they are actively working on and therefore using customized shortcuts for these instances makes sense.

The parameters that I generally use are:

In addition, you may also use the parameters [scriptfile], [projectfile], [solutionfile] to specify a script, project or solution to open by default.

I therefore changed my SSMS shortcut to be the following. The shortcut connects to the AdventrueWorks2008R2 database on my test instance. The SSMS would use Windows authentication.

"C:Program FilesMicrosoft SQL Server110ToolsBinnManagementStudioSsms.exe" -S W2K8DENALISQL11 -d AdventureWorks2008R2 -E –nosplash

As can be seen from the screenshot below, the SSMS launched with windows authentication and connected directly to the AdventureWorks2008R2 database by default.

References:

• SSMS Utility: http://msdn.microsoft.com/en-us/library/ms162825.aspx

Do you use any methods to speed up the SSMS launch times? If yes, do share your method in the discussion section below.

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

All systems are susceptible to failure. No software is perfect, and therefore it is natural that SSMS too will fail. Today, I have put together some of the most common failures that I have experienced with SSMS and their known workarounds.

The reason I have kept this part of the tutorial as a L200 is because the workarounds and tips from this post should be used with caution.

General troubleshooting

The Object Explorer is not visible/hidden

The most common scenario when my Object Explorer sometimes goes missing is when I switch back to my standard laptop resolution after projecting using a projector or on a larger screen.

Obviously, the first step in troubleshooting a hidden object explorer is to make sure the Object Explorer is indeed enabled by toggling it using the F8 key or by using the View menu.

If this does not resolve your issue, then the problem starts becoming a bit ugly. Save all your work within the SSMS, and restart the SSMS. In most cases, restarting SSMS restores the Object Explorer.

An important criteria for any recovery mechanism is to make as little collateral damage as possible. Restarting a system allows the system to preserve user data, while repairing the point of failure successfully. It is therefore that standard protocols like restarting systems are developed. However, in some cases, it becomes impossible to recover the system completely. Over the many years of my experience with SQL Server, I have faced such a situation with SSMS only once. If the components of SSMS simply fail to become visible on screen, the last option left is to give the system a “shock” by removing all customizations (repositioning, resizing, etc) applied. This can be done by using Windows –> Reset window layout option.

Using the “Reset Window Layout” clears out customizations to the default window positions and sizes and takes the SSMS back to the default state.

Possibly large number of connections when only one SSMS instance is connected to the SQL Server

When working with SSMS, you might find that while only one instance of SSMS is connected to the SQL Server, the number of connections being opened up is not proportional. The reason is that SSMS does a tradeoff between features and additional connections. The Object Explorer, Object Explorer details and other such “heavy-weight” windows open their own dedicated connection to the SQL Server instance.

You can read about my little experiment regarding the multiple connections of SSMS here: http://beyondrelational.com/blogs/nakul/archive/2011/01/17/the-multiple-connections-of-ssms.aspx

Avoid the urge to use all features at the same time on a production server, or during troubleshooting – not only will it confuse you, but also might slow down the SQL Server (if the server is under pressure of some sort).

Finally, if the SQL Server has a database which is in single-user mode, it will be available to only one of the multiple sessions that SSMS starts. The trouble is that which connection will come first is unknown. Hence, it is always a good practice to have nothing but the query editor open when working with either single-user databases or single query modes like the DAC (Dedicated Admin connection).

So, configure your environment wisely, and the power of SSMS will be with you.

Clearing the SSMS History

For those of you who work with multiple SQL servers with multiple logins, the “Connect to Server” window seems to fill up very quickly. Sometimes, these servers might not even be in existence (e.g.. if you frequently refresh your virtual RND/test environments).

For those using the yet-to-be-released SQL Server 2012 (code named “Denali”), you can select the value and hit the delete key just as you would delete the entry from any other drop-down. However, if you are using SSMS for SQL Server 2005 (in which case you should upgrade) or SQL Server 2008/2008 R2, you can refer my post on clearing SSMS history here: http://beyondrelational.com/blogs/nakul/archive/2011/02/17/clearing-ssms-history.aspx.

Advanced Troubleshooting – Troubleshooting the T-SQL Debugger

In 2011, I presented a session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. One of the items that I demoed in the VTD was the T-SQL debugger. The T-SQL Debugger exists at least since SQL Server 2000 and as the versions progress, we can see a lot of changes being made to the debugger. While preparing my environment for the T-SQL debugger, I encountered multiple issues related to firewall security and sequence of the Visual Studio versions installed. You can find my posts on each of these errors here:

• Enabling T-SQL Debugger in SSMS
• T-SQL Debugging – Connection Errors & Firewall settings
• T-SQL Debugging – SSMS Errors – MSDBG32.dll

By the way, you can find a recording of my VTD session here:

• PPT here: http://beyondrelational.com/media/p/12497.aspx
• Webcast Download:http://download.microsoft.com/download/6/0/C/60CBF7F0-5558-4C00-ACC0-ABFF25A92244/Day5_SQLServer webcast.wmv

Concluding the series

This concludes my series of tutorials on SSMS. Last year, a lot of engineers who had never worked with SQL Server came up to me looking for some guidance on the SSMS interface. My goal therefore was to focus on features that would make their lives easier and make them feel comfortable within the confines of the SSMS IDE. I hope that these tutorials will help all the budding SQL Server enthusiasts.

If these tutorials helped you, or if you have any suggestions, do let me know. Your feedback is most welcome.

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

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

One of the most important aspects of SQL Server administration and maintenance is the ability to review and changing the existing configuration of the server. In Part 01 of this tutorial series, I had mentioned that 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. Today, we will look at some of the windows and wizards that can be used to configure your SQL Server instance.

Reviewing & Changing Instance-level configuration

Using the Object Explorer, you can review the instance-level configuration (provided you have sufficient privileges, of course) for all servers that you are connected to. Simply right-click on the instance name and select “Properties” from the pop-up menu to launch the SQL Server instance properties window.

The base or “general” view of the window is a read-only view that provides details on the SQL Server build, hose operating system version, number of processors and memory, SQL Server collation and high-availability configuration values.

Navigating out to any of the child nodes (“Database settings” node visible in the screenshot below), allows us to view two sets of values – the “configured” values, and the “running” values.

• Configured values: These are editable values, and display the configured values for the options on this pane. If you change these values, click Running Values to see whether the changes have taken effect. If they have not, the instance of SQL Server must be restarted first.
• Running values: View the currently running values for the options on this pane. These values are read-only

Below is a summary of the various child nodes in the Server Properties window:

Reviewing & Configuring database-level configuration

Similar to the instance-level properties page, simply right-click on the database name and select “Properties” from the pop-up menu to launch the database properties window. Please note that this window does not have the concept of “configured” and “running” values. Any changes you make are in effect for ever new connection to the database going forward.

The screenshot below shows the “General” page, which displays the following information:

• Last backup information
• Database creation date, status
• Database owner
• Space available in the database
• Current Number of concurrent users connected to the database
• Database collation

Below is a summary of the various child nodes in the Database Properties window:

Scripting changes for future reference

Whenever a SQL Server configuration change is required, it needs to be planned and scheduled for execution during an outage. There too, the team implementing the change may or may not be the same as those recommending the change. In such cases, it becomes essential to script the changes made so that the change can be supplied to the implementation team for:

1. Record keeping – for future reference
2. Scheduled deployment – the required change has been scripted, so it can be applied whenever the outage window permits

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 2
GO

Practice Exercises…

The properties page is available for every distinct object visible within the Object Explorer.

• Explore the properties for your SQL login
• Define an idle CPU condition for the SQL Server agent using the using the SQL Server Agent properties window

Further reading…

• Server properties F1 Help
• Database properties F1 Help

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