I have been working on code to auto trace anyone who logs in under the SA role that is not a job or system process. It would be filtered by spid and auto stop when they logout. The only thing I could come up with is two WMI alerts, two stored procedures and two jobs. It is a craptastic monstrosity at best.
It would be easier if their was a logout DDL trigger but I think a trace definition as a property of a login or role would be a better solution. Think of it as the “big brother” checkbox for the JR DBA.
I have filed a connect here. Vote if you would find this useful.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=404808
I always look for ways to access OS data from within SQL Server. As a DBA, this helps you make better decisions. In the Policy Based Management(PBM) realm, imagine a policy being run against all SQL Servers that checks for ERROR events in the event logs for the past 24 hours. Your morning report could include any servers having issues. Another scenario might be a one time report that checks the firmware revision of your raid controller. A WQL query and a couple clicks then you have a list of boxes that do not have the latest firmware. When you are managing a lot of SQL boxes, this could be powerful and time saving. You could also combine with ExecuteSQL() for more complex scripts.
Here is what is in BOL on ExecuteWQL():
ExecuteWQL()Function signature: Variant ExecuteWQL ( string returnType , string namespace , string wql )Function description: Executes the WQL script against the namespace that is provided. Select statement can contain only a single return column. If more than one column is provided, error will be thrown.Arguments and return type definitionreturnType - Specifies the return type of data that is returned by the WQL. The valid literals are Numeric, String,Bool, DateTime, Array, and Guid.namespace - Is the WMI Namespace to execute against.wql - Is the string that contains the WQL to be executed.Example: ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem') <> 0
Function signature: Variant ExecuteWQL ( string returnType , string namespace , string wql )
Function description: Executes the WQL script against the namespace that is provided. Select statement can contain only a single return column. If more than one column is provided, error will be thrown.
Arguments and return type definition
Example: ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem') <> 0
ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem') <> 0
I don’t know why I need to expand on a topic where the example verifies that the local server has at least one processor. :)
Here is a quick example of one of the cool things you can do with PBM in SQL Server 2008. It is pretty simple. We get the freespace for each drive and we fail evaluation if the drive has less than 10000MB free. You don’t want this to be your complete monitoring system but it could be a nice supplement.
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 Platform300BRKJason 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 Platform400BRKJason 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.
I'll be there Monday through Saturday. Hope to see you there!
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
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)
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'
It looks like they posted the sessions for Tech∙ED. Get ready for some hardcore SQL mayhem. All of the Database Platform are sessions are 300 and 400 level. Not a single 200. The line up is heavy weight and it looks like they are all pulling out new sessions. It is an honor just to be presenting with this crew. I was planning on taking in some sessions on VS2008, sharepoint and other topics I don't get to see much action in but I am afraid I would miss something if I did.
My sessions will be "Troubleshooting query plans gone wrong." and "WMI for the command line DBA". I hope to see you there. It is truly going to be a great year with the new products.
Two of my session submissions got accepted for the TechEd Developer conference database track.
I am really pumped up about this. I have seen the other sessions that they have selected so far and they look great. I highly suggest you check this out! Orlando over the Memorial Day Weekend followed by some hardcore tech goodness. That sounds like a great vacation.
There is not an example in books online and very little on the net. Let's look at this example that I worked through with a guy in the news groups.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert
@name =N'Test',
@enabled =1,
@delay_between_responses =0,
@include_event_description_in =0,
@wmi_namespace =N'\\.\ROOT\CIMV2',
@wmi_query =N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 100000'
This fires when any disk on the local server has less than 100000 bytes free. That is kinda boring huh? How about this?
@wmi_namespace =N'\\YourWebServer\ROOT\CIMV2',
@wmi_query =N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_PerfFormattedData_PerfOS_Processor" AND TargetInstance.PercentProcessorTime > 90'
Now, let's say you have an annoying web admin that always gleefully lets you know when there is a problem with SQL. With this alert, you can fire him an email if the web server is running hot.
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.
So let’s do this.
--Create Databasecreate database WMITest01; --Enable CLRexec sp_configure 'clr enabled', 1reconfigure with override--Enable trust worthy computing--This allows us to create assemblies that access resources outside of the database.alter database WMITest01set trustworthy on --This assemby allows us to use WMI in CLR functions and procs.use wmitest01goCREATE 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 database2. Set the project properties to unsafe so we can add a reference to System.Management3. Add a database reference to System.Management4. Create a new stored procedure5. Replace the code with the follow code.6. Deploy Imports SystemImports System.DataImports System.ManagementImports System.Data.SqlClientImports System.Data.SqlTypesImports 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 SubEnd Class So now we can run pnetWMIProcTime and know what the current CPU usage is from within SQL.
--Create Database
create database WMITest01;
--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
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
Public Class ProcTimeStoredProcedures
Public Shared Sub pnetWMIProcTime()
"root\CIMV2", _
"select PercentProcessorTime from Win32_PerfFormattedData_PerfOS_Processor where name = '_Total'")
New SqlMetaData("PercentProcessorTime ", SqlDbType.VarChar, 100))
record.SetString(0, queryObj("PercentProcessorTime"))
So now we can run pnetWMIProcTime and know what the current CPU usage is from within SQL.
Help the cause and vote for this a OLDB WMI "provider" that could be used in places like openrowset and dataflows in SSIS.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273782
**************snip***********************
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.