TechEd! Let's do this.

I am pumped up about TechEd. Bill Gates's last technical keynote. He has to have a bomb to drop... The launch of Windows\VS\SQL Server 2008. All of the speakers are reaching deep into their bag of tricks and bringing new hardcore sessions. The universal party. The blogger's lounge. Twitter, FriendFeed, MSDN, and Technet are all buzzing with posts. 

I found out a couple of days ago that a speaker would not be able to make it due to a family emergency. I offered to present his session on DMV's so I will be presenting these sessions:

Using Dynamic Management Views to Improve Your Development

Dynamic Management Views were added to SQL Server 2005 and have been enhanced in SQL Server 2008. While they provide great functionality and usefulness, it appears they are not widely understood or implemented as yet. This session provides an overview of the Dynamic Management Objects available (both Views and Functions) and describes those considered the most useful. It shows how they can be utilized in monitoring, troubleshooting, and ongoing management of SQL Server systems and in gaining an understanding of SQL Server’s operation. The session also illustrates how they can be used to improve the database development experience and how they can be used in custom reports within SQL Server Management Studio.

Database Platform
300
BRK
Jason Massie

Windows Management Instrumentation (WMI) for the Command Line DBA

This is a demo filled session on how a command line DBA can unleash the power of WMI. WMI allows the DBA access to information that was previously hard to get or unavailable like the application event logs, OS perfmon counters, and OS events. This session covers WQL queries from SSIS, accessing OS performance counters from SQL, automated reactions to WMI events, WMI from Windows PowerShell and much more.

Database Platform
400
BRK
Jason Massie

Troubleshooting Query Plans Gone Wrong

Have you ever flushed the procedure cache to resolve a performance problem? Have you ever had to use a join or index hint when you should not have to? Have you ever updated statistics to fix a performance problem and wondered why it worked? In this session, we get deep into how the query optimizer decides how to execute a SQL statement. We look at common problems that cause the optimizer to choose the wrong access paths like underestimation. We go over a detailed demo on parameter sniffing, problems with local variables and statistics. We also look at solutions to these problems in several areas including good design at the application, schema, and query levels, proactive maintenance and reactive fixes. This session applies to Microsoft SQL Server 2000 through 2008 but we pay special attention to new features in SQL Server 2008 like plan freezing, new hints, filter indexes, and statistics among others.

Database Platform
400
BRK
Jason Massie

I'll be there Monday through Saturday. Hope to see you there!

email it! |  |   |   |   |   | 

Permalink     1 Comments

A generic WMI perfmon CLR procedure

I am working on my demo's for TechED and I wrote this proc and I thought I would share. Well, updated an old proc I previously posted. It allows you to pass a computer name, a WQL query and a perfmon counter. It must only return a single value although it could easily be modified to return a record set.

You have to do the following in SQL before you deploy:

--Enable CLR

exec sp_configure 'clr enabled', 1

reconfigure with override

--Enable Trustworthy computing

--This allows us to create unsupported assemblies

alter database DemoDB

set trustworthy  on

use master

go

GRANT unsafe ASSEMBLY TO [WIN2K3R2EE\Administrator]

ALTER AUTHORIZATION on database::demodb to [WIN2K3R2EE\Administrator]

--This assembly allows us to use WMI in CLR functions and procs.

use DemoDB

go

CREATE ASSEMBLY [System.Management]

AUTHORIZATION [dbo]

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

Copy and add this code to a VB database project stored proc in VS2005\2008.

 Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Management

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

     _

    Public Shared Sub pnetWMI(ByVal sComputer As String, ByVal sWQL As String _ 
, ByVal sCounter As String)

        Try

            Dim searcher As New ManagementObjectSearcher( _

                "\\" & sComputer & "\root\CIMV2", _

                sWQL)

            For Each queryObj As ManagementObject In searcher.Get()

                Dim record As New SqlDataRecord( _

                New SqlMetaData(sCounter, SqlDbType.VarChar, 100))

                SqlContext.Pipe.SendResultsStart(record)

                record.SetString(0, queryObj(sCounter))

                SqlContext.Pipe.SendResultsRow(record)

            Next

            SqlContext.Pipe.SendResultsEnd()

        Catch ex As Exception

            Dim sp As SqlPipe = SqlContext.Pipe()

            sp.Send(ex.Message)

        End Try

    End Sub

End Class

exec pnetWMI 'localhost', 'select AvgDiskQueueLength from Win32_PerfFormattedData_PerfDisk_PhysicalDisk where name = ''_Total''', 'AvgDiskQueueLength'

email it! |  |   |   |   |   | 

Permalink     1 Comments

Tech∙ED database platform track