New Pluralsight course: SQL Server: Using SentryOne Plan Explorer

Jonathan’s latest Pluralsight course has been published: SQL Server: Using SentryOne Plan Explorer. It’s almost 3 hours long, and from the course description:

Query plan analysis is both a science and an art, and the best tool for the job is SentryOne Plan Explorer. In this course, SQL Server: Using SentryOne Plan Explorer, you will learn how to make the most of the tool to make query plan analysis and performance tuning much easier and more intuitive than using SQL Server Management Studio. First, you will gain an understanding of how Plan Explorer compares to Management Studio, demonstrating how Plan Explorer solves the problems and shortcomings that Management Studio has. Next, you will discover how to use the the Plan Explorer user interface, including how the various information displays can help with performance analysis and tuning, and the various methods for getting query plans into the tool. Finally, you will explore how to use the more advanced features of the tool like index and statistics analysis, and profiling query performance using live query statistics. When you are finished with this course, you will have the skills and knowledge to start using the powerful SentryOne Plan Explorer tool to greatly improve your query plan analysis and performance tuning capabilities.

It’s a complete update for his 2013 course, and covers the very latest release of SentryOne’s fantastic (and free!) tool.

The modules are:

  • Introduction
  • Comparing Plan Explorer to SSMS
  • Using the Plan Explorer UI
  • Getting Execution Plans to Plan Explorer
  • Using Index and Statistics Analysis
  • Profiling Query Performance

Check it out here.

We now have more than 180 hours of SQLskills online training available (see all our courses here), all for as little as $29/month through Pluralsight (including more than 6,000 other developer and IT training courses). That’s unbeatable value that you can’t afford to ignore.

Enjoy!

“Attempt to read or write protected memory” error from SSMS for System.Data

I just spent a couple of hours fruitlessly trying to solve a problem and thought I’d blog the solution in case anyone else hits the issue.

A few months back I got a new laptop and installed SQL Server 2014 on it and everything worked fine. A few weeks ago I installed SQL Server 2008 R2, SQL Server 2012, and Visual Studio 2013. After that, any time I tried to use the Dedicated Admin Connection (DAC) through SSMS, I got this error:

Untitled

I did some research online and couldn’t find any solutions that worked. So I tried repairing the installation, installing 2014 SP1 CU2 (latest build at time of writing), and a bunch of other things – to no avail. I was just about to give up and post a request for help when I thought I’d do one more search online.

When I did that, I noticed that all the solutions I’d tried had revolved around the assembly name in the error being something to do with SQL Server. In my case, it was System.Data, which is a .NET assembly. I added that into my search and found a bunch of new hits. Lo and behold, buried in the comments on a Stack Overflow question, I found the solution.

Turns out the problem was because of an upgraded .NET version, and the solution was to run the following from a command line and then reboot:

netsh winsock reset

And after that SSMS worked perfectly.

Hope this post helps others find the answer quickly in future!

Limiting error log file size in SQL Server 2012

It’s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server.

One thing that hasn’t been possible before is setting the maximum size of individual SQL Server error logs before SQL Server triggers cycling to a new error log. Well it turns out that in SQL Server 2012 you can!

While in my post-con workshop at SQL Intersection in Las Vegas last week, Jan Kåre Lokna (a former Immersion Event attendee from Norway) discussed some code he’s been experimenting with and I just heard from him that he got it to work.

The following code will set the maximum size of each error log file to 5MB on a SQL Server 2012 instance:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

I’ve tested this on SQL Server 2008 R2 and it does not work there, so the registry key must be new for SQL Server 2012. This is really useful to protect against gigantic error log files caused by repeated crash dumps, for instance when messing around with DBCC WRITEPAGE :-)

[Edit: 10/23/15] Also, for SQL Server 2014 the registry keys have changed again:

USE [master];
GO
-- Limit size of each file
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',
N'ErrorLogSizeInKb', REG_DWORD, 1024;
GO

-- Number of ErrorLog Files
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',
N'NumErrorLogs', REG_DWORD, 8;
GO

Note: I’ve had anecdotal reports that on SQL Server 2014, the SQL Server 2012 method is the only one that works. Please experiment and see which works in your situation. I imagine the difference may be due to fresh-install vs. upgraded-in-place.

You can read a more in-depth description on Jan’s blog here.