My SQLRally 2012 presentation materials are available

Posted on 2012-05-11 by

I have posted the materials from my PASS SQLRally 2012 Dallas session, Deadlock Detection, Troubleshooting, and Prevention (DBA-201). The download contains the slide deck, a handout that is full of links to resources, demonstration code, and a few sample files. I’d love feedback on the contents, or the presentation itself!

More information about the session can be found on the presentation’s web page, including:

  • The session’s abstract and goals
  • The current release of the session materials, along with all past versions
  • A list of past deliveries of the presentation

See you this afternoon!

Posted in SQL Server | Tagged , , , , , , , , , , | Leave a comment

Tutorial – Installing PowerShell

Posted on 2011-06-04 by

Unfortunately there are a few different ways to install PowerShell, depending on the version of Windows. This tutorial provides the installation steps that are appropriate for your operating system.

Note: Before attempting to install PowerShell on a computer, confirm that it isn’t already installed!

PowerShell version 1.0 was available for installation by itself. PowerShell version 2.0 is installed as part of the Windows Management Framework. Don’t worry… WinRM is disabled by default, so this update can be safely installed without affecting the security of Windows.

Steps Common to Each Windows Operating System

There are a few requirements for installing PowerShell on Windows:

  1. Confirm that you have sufficient security privileges for installing software on Windows
  2. Verify that the pre-requisite—version 2.0 of the .NET Framework—is installed (and patched)
  3. Make sure that your system is up-to-date with its Service Pack and patches:

    Windows Version Minimum Service Pack
    Windows Server 2008 Service Pack 2
    Windows Vista Service Pack 2
    Windows Server 2003 R2 Service Pack 2
    Windows Server 2003 Service Pack 2
    Windows XP Service Pack 3

Installation on Windows 7 or Windows Server 2008 R2

PowerShell should already be installed and enabled. Inability to run PowerShell on a Windows 7 or Windows Server 2008 R2 machine indicates a problem, or it has been removed/locked down by your system administrator.

Installation on Windows Server 2008 or Windows Vista

  1. Review the information on the Windows Management Framework Core update
  2. Download the update package (“Windows6.0-KB968930-*.msu”) that is appropriate for your computer’s processor architecture
  3. Double-click on the “Windows6.0-KB968930-*.msu” file (that you just downloaded) to install it

Installation on Windows Server 2003

  1. Verify that version 2.0 of the .NET Framework is installed:

    1. Open the Windows® Run dialog box

      1. Press the Windows key + R key keys
    2. Type Cmd.exe into the Open text box and press the Enter key key

    3. At the command prompt, type the following command line and press the Enter key key:

      dir %SYSTEMROOT%\Microsoft.NET\Framework\ /AD /B

    4. The .NET Framework pre-requisite is satisfied if the dir output of the command includes a substring of “v2.0.50727″. Otherwise, download and install version 2.0 or later of the .NET Framework

      • You could (and probably should) install the latest version (each release of the .NET Framework includes each of the ones that precede it)
      • Be sure to check for and apply Windows updates afterward; there are Service Packs and patches for each version of the .NET Framework!
  2. Review the information on the Windows Management Framework Core update
  3. Download the installation program (“WindowsServer2003-KB968930-*-ENG.exe”) that is appropriate for your computer’s processor architecture
  4. Double-click on the “WindowsServer2003-KB968930-*-ENG.exe” file (that you just downloaded) to install it

Installation on Windows XP

  1. Verify that version 2.0 of the .NET Framework is installed:

    1. Open the Windows® Run dialog box

      1. Press the Windows key + R key keys
    2. Type Cmd.exe into the Open text box and press the Enter key key

    3. At the command prompt, type the following command line and press the Enter key key:

      dir %SYSTEMROOT%\Microsoft.NET\Framework\ /AD /B

    4. The .NET Framework pre-requisite is satisfied if the dir output of the command includes a substring of “v2.0.50727″. Otherwise, download and install version 2.0 or later of the .NET Framework

      • You could (and probably should) install the latest version—each release of the .NET Framework includes each of the ones that precede it
      • Be sure to check for and apply Windows updates afterward; there are Service Packs and patches for each version of the .NET Framework!
  2. Review the information on the Windows Management Framework Core update
  3. Download the installation program (“WindowsXP-KB968930-x86-ENG.exe”)
  4. Double-click on the “WindowsXP-KB968930-x86-ENG.exe” file (that you just downloaded) to install it

Installation on Windows 2000

PowerShell cannot be installed on Windows 2000… it’s time to upgrade!

Posted in PowerShell | Tagged , , , , , , , , , , , , , | Leave a comment

Tutorial – Determining whether PowerShell is installed

Posted on 2011-06-04 by

The first step in using PowerShell is making sure that it’s installed on the computer(s) that need it. As time passes, it will become as ubiquitous as Cmd.exe and VBScript. However, it’s currently important to verify that PowerShell is available when getting started.

Beginning with Windows 7 and Windows Server 2008 R2, PowerShell is installed and enabled by default. As people realize its significance, more and more administrators and power users are installing PowerShell on earlier operating systems. There’s a good chance that PowerShell is already installed on your computer, even if you didn’t put it there yourself!

Here are the steps for verifying that PowerShell is ready to use:

  1. Open the Windows® Run dialog box

    1. Press the Windows key + R key keys
  2. Type PowerShell.exe into the Open text box and press the Enter key key

  • If PowerShell is installed, a console window will be displayed:

  • If PowerShell is not installed, you will see the following error message:

    Windows cannot find 'PowerShell.exe'. Make sure you typed the name correctly, and then try again.

    That means it’s time install PowerShell!

Posted in PowerShell | Tagged , , , , | 1 Comment

Reflecting on my first CodeStock session

Posted on 2011-06-03 by

Today I gave my first of two presentations at the 2011 CodeStock conference, my “An Introduction to Profiler and SQL Trace” talk. The session went really well! It had good attendance, the audience was engaged and asked great questions, and I am pleased with my delivery. I also received the kind of feedback that justifies all of the work invested… an audience member, Stephen Horne (blog, Twitter), said the following (in-person and on Twitter):

I really enjoy seeing people talking passionately & with significant knowledge about #SQL Server. @SQLServerSleuth #codestock.

During the session, I state that the following two ideas are the most important things to take away from the presentation:

I spend a long time during the presentation warning against using Profiler (in production) and explaining its performance impact (as well as the overhead of tracing). Still, Profiler can be a useful tool for defining trace sessions and analyzing trace data. Here is the proper workflow for reaping its benefits without shooting yourself in the foot:

  1. On your workstation, use Profiler to define the trace (event classes, columns, and filters)
  2. Use Profiler to generate a Transact-SQL script file with the system stored procedure calls for a server-side trace:

  3. Edit the script, including placing limits on the size and duration of the trace (using the @maxfilesize and @stoptime parameters of the [sys].[sp_trace_create] system stored procedure, as demonstrated in the presentation’s “Demo 07_Create a server-side trace to provide information on the next deadlock(s),2005+.sql” file)
  4. Execute the script on the server that is under investigation
  5. If the trace automatically stops more quickly than expected, refine the trace definition to be more conservative
  6. Once you have several trace files with useful data, compress them
  7. Transfer the archive of compressed trace files from the target server to your workstation
  8. Analyze the trace data using the [sys].[fn_trace_gettable] system function and/or using Profiler
Posted in SQL Server | Tagged , , , , , , , , , | Leave a comment

I am speaking at CodeStock 2011

Posted on 2011-06-03 by

I will be delivering two sessions at this year’s CodeStock conference in Knoxville, Tennessee:

If you happen to be attending, please stop by and visit!

Posted in PowerShell, SQL Server | Tagged , , , , , , , | Leave a comment

Dealing with full transaction logs

Posted on 2011-06-01 by

In a recent post, I mentioned a customer that takes some perverse pleasure in undermining point-in-time-recoverability of their databases. Here are some more details on the bad practice and what I wish they would do instead.

The customer has a two-node failover cluster with two instances of SQL Server 2005. Each clustered instance has dozens of databases, nearly all of them with identical schema (they have a Data-Dependent Routing/shard architecture), and each is in the full recovery model. They don’t have enough disk space allocated for the instances and, much too frequently, they encounter 9002 errors from SQL Server (when one or more of the transaction log files fill up and don’t have room to grow):

Msg 9002, Level 17, State 1, Line 1
The transaction log for database '____' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The customer reacts by looping through all of their databases and running BACKUP LOG … WITH NO_LOG against each. Let’s walk through the steps that they should be taking, instead of this knee-jerk overreaction.

The first problem is that, despite the hint in the 9002 error message, the customer doesn’t use the [log_reuse_wait_desc] column of the [sys].[databases] catalog view to determine which databases need attention. Instead, they resort to shotgun troubleshooting and truncate the transaction logs of all databases.

Next, the customer should perform appropriate capacity planning and provision enough disk space for their databases (now and in the future).

Since the customer won’t acquire sufficient disk space, I have asked whether their databases really belong in the full recovery model. They expect point-in-time-recoverability, but their frequent and ill-advised reaction to 9002 errors trashes the databases’ backup log chains. It will be an upsetting day when they need to restore to a certain time and cannot do so. It would be better to review the Recovery Point Objective (RPO) for each database (or define it for the first time) and then place the majority of the databases in the simple recovery model, saving the resources and administrative overhead associated with transaction log backups. Differential backups could be used to reduce data loss to levels consistent with the RPOs (YMMV).

As bad as it is to rely on sabotaged point-in-time-recoverability, the bigger issue is that the customer will follow-up the transaction log truncations with a round of shrinking the transaction log files! I don’t have time right now to delve into the perils of shrinking transaction log files, so here are some great posts from others in the community:

If, despite careful planning and database administration, the transaction log for one of your databases fills up (and you receive a 9002 error), take a deep breath and simply back up the transaction log for that database. It takes slightly longer than throwing away all of the committed transaction log records (via BACKUP LOG … WITH NO_LOG/TRUNCATE_ONLY) but it also avoids the problems discussed in this post and the last one. If absolutely necessary, discard the committed transactions using the following process (which still works even after the wretched BACKUP LOG … WITH NO_LOG/TRUNCATE_ONLY functionality was removed from SQL Server):

  1. Put the database in simple recovery model:

    ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE;
  2. Wait for the transaction log to automatically truncate… or force it:

    USE [YourDatabase];
GO
CHECKPOINT;
GO
  3. If the database truly belongs in full recovery model:

    1. Change the recovery model back:

      ALTER DATABASE [YourDatabase] SET RECOVERY FULL;
    2. Immediately perform a full backup of the database to reestablish the backup chain

Note: While performing research for this post, I came across a similar one from Brent Ozar (blog, Twitter). Despite the overlap, I decided to publish anyway, hoping that we rank higher in search engine results than all of the bad Internet advice that advocates forcibly clearing the transaction log!

Posted in SQL Server | Tagged , , , , , , , , , , , , | Leave a comment

Query – Finding a DMV based on column names

Posted on 2011-05-22 by

I really enjoyed the NTSSUG meeting on Thursday. Bob Ward (blog, Twitter), a major role model of mine, presented Inside SQL Server Wait Types. As with the SQLskills Immersion Events, I spent the whole time taking copious notes as the priceless information flowed.

While discussing THREADPOOL waits, I missed the name of a helpful DMV for identifying a lack of worker threads. I did hear the name of the relevant column though… “work_queue_count.” Thanks to SQL Server’s catalog views, it was easily to track down the DMV in question, [sys].[dm_os_schedulers]:

SELECT [_Objects].[object_id] AS [ObjectID],
    [_Objects].[name] AS [ObjectName],
    [_Objects].[type_desc] AS [ObjectType],
    [_Columns].[column_id] AS [ColumnID],
    [_Columns].[name] AS [ColumnName]
FROM [master].[sys].[system_objects] AS [_Objects]
INNER JOIN [master].[sys].[system_columns] AS [_Columns]
ON ([_Objects].[object_id] = [_Columns].[object_id])
WHERE (
    ([_Objects].[name] LIKE N'dm_%')
    AND
    ([_Objects].[type] IN ('IF', 'TF', 'V'))
    AND
    ([_Objects].[schema_id] = Schema_ID('sys'))
    AND
    ([_Objects].[is_ms_shipped] = 1)
    AND
    ([_Columns].[name] LIKE N'%work%queue%count%')
)
ORDER BY [_Objects].[name] ASC,
    [_Columns].[name] ASC;

This type of query is also useful when searching for gems in SQL Server’s meta data:

SELECT [_Objects].[object_id] AS [ObjectID],
    [_Objects].[name] AS [ObjectName],
    [_Objects].[type_desc] AS [ObjectType],
    [_Columns].[column_id] AS [ColumnID],
    [_Columns].[name] AS [ColumnName]
FROM [master].[sys].[system_objects] AS [_Objects]
INNER JOIN [master].[sys].[system_columns] AS [_Columns]
ON ([_Objects].[object_id] = [_Columns].[object_id])
WHERE (
    ([_Objects].[type] IN ('IF', 'TF', 'V'))
    AND
    ([_Objects].[schema_id] = Schema_ID('sys'))
    AND
    ([_Objects].[is_ms_shipped] = 1)
)
ORDER BY [_Objects].[name] ASC,
    [_Columns].[column_id] ASC;
Posted in SQL Server | Tagged , , , , , , , , , | Leave a comment