Entries for the 'CLR' Category

A SQLCLR Twitter Client

The product is TweetSQL. It is not out yet but you can see some details here and follow @rhyscampbell. You probably just uttered a profanity at the thought of it on production server. I did when I first heard of it. I even had a day dream moment where I pictured myself going all street fighter on a jr DBA who installed it prod box. Sure, install it on your test box and tweet when you do something cool like write a custom policy with ExecuteSQL() or write a script to only generate indexes. But production? Hell no.

Later, @AlanBarber made a point.

@statisticsio we're using twitter right now for status and error messages at my company. Surprisingly a nice tool to keep tabs on everything

Hhrmm, I do use twitter to communicate more than the telephone. Maybe not as much email and IM yet but if the bread and butter database fails over, some one better reach out an touch me. More ways the better. Email, SMS, and a phone call from the monitoring already happens. Why not a tweet too? This could be especially important if you do not have SMS capabilities by default. Of course, this is not something you want to go crazy with and pay attention to security. The launch of Yammer whose focus is the enterprise could bring about more usage like this.

I will tell you this… There is not much worse than having your boss call you to tell you there is a major SQL outage. The conversation goes so much better when you call him to let him know there is a problem with the SQL Server and you are on it like a hound dog on a pork chop.

email it! |   |   | 

Permalink     13 Comments

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     2 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 trustworthyon

 

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     4 Comments

Accessing OS performance counters from tsql through CLR, WMI, and WQL

A cool use of WMI data from within SQL that I have found is getting to OS perfmon data. There are several ways of doing it. I would say the best practice would be CLR if you need it in real time or SSIS if you are logging to a table for reporting purposes. You can also hit it through xp_cmdshell and powershell. This is what I do when I need it quick.

In this blog, we will look at an example of doing this in CLR.  We will be using % processor time but any perform counter is available. A list is here.

DISCLAIMER: I am definitely not a CLR guru. If you know of a better way to code this, please let me know.

Here are some uses. The first two I have in production.

• A server dashboard with reporting services.
• A resource governor to conditionally execute tasks like index\stats maintenance.
• Home grown monitoring apps
• Integration with your backup to dynamically choose drive based on space.
• Insert your idea here.

So let’s do this.

--Create Database

create database WMITest01;

--Enable CLR

exec sp_configure 'clr enabled', 1

reconfigure with override

--Enable trust worthy computing

--This allows us to create assemblies that access resources outside of the database.

alter database WMITest01

set trustworthy  on

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

use wmitest01

go

CREATE ASSEMBLY [System.Management]

AUTHORIZATION [dbo]

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

WITH PERMISSION_SET = UNSAFE

Now, we are going create a new VB(or C# but the sample code is VB) database project in Visual Studio 2005. Now do the following steps:

1.       Add a reference to the WMITest database

2.       Set the project properties to unsafe so we can add a reference to System.Management

3.       Add a database reference to System.Management

4.       Create a new stored procedure

5.       Replace the code with the follow code.

6.       Deploy

Imports System

Imports System.Data

Imports System.Management

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Public Class ProcTimeStoredProcedures

    Public Shared Sub pnetWMIProcTime()

        Dim searcher As New ManagementObjectSearcher( _

            "root\CIMV2", _

            "select PercentProcessorTime from Win32_PerfFormattedData_PerfOS_Processor where name = '_Total'")

        For Each queryObj As ManagementObject In searcher.Get()

            Dim record As New SqlDataRecord( _

            New SqlMetaData("PercentProcessorTime ", SqlDbType.VarChar, 100))

            SqlContext.Pipe.SendResultsStart(record)

            record.SetString(0, queryObj("PercentProcessorTime"))

            SqlContext.Pipe.SendResultsRow(record)

        Next

        SqlContext.Pipe.SendResultsEnd()

    End Sub

End Class

So now we can run pnetWMIProcTime and know what the current CPU usage is from within SQL.

email it! |   |   | 

Permalink     3 Comments

NTSSUG Presentation on WMI

Here are the demo files from my presentation at the North Texas SQL Server Users Group. It was a pretty fun experience. I was kind of hestitant to go with the topic because I thought it may be a little dry however I think it turned out pretty interesting.

Maybe I am a little biased but I think North Texas sets the bar high when it comes to knowledgable DBA's. We have a lot of of Microsoft people, MVP's and authors to spread the knowledge around. It was an honor to present and I look forward to attending the user group more often.

Download demo scripts.

email it! |   |   | 

Permalink     0 Comments