Finding the Missing Perfmon Counters in SQL Express

My post this weekend about slaying beasts with magical .dll incantations was my way of relieving some stress after having to work an entire Saturday unplanned. My goal with this post is to provide a little more insight into the problem and the solution that was discussed in a very abstract manner on Saturday.

First - to define the issue. I had a client who wanted me to investigate the performance of a server previously foreign to me. The reason for the analysis was to determine any value in upgrading the SQL Express instance to SQL Standard. This was a vendor supported environment that would move to the client's managed environment if they migrated to Standard edition. I gladly accepted the task and asked a co-worker to get the perfmon and sql trace set up to collect the data.

It wasn't long after I made the request that he called me and expressed that he couldn't find the performance counters. I had very little experience in SQL Express, so I immediately wondered if that was one of the features that was disabled. I could find nothing supporting that thought, so I logged onto the box and verified that indeed none of the SQL Server performance counters were available.

The counters were still loading into the sys.dm_os_performance_counters view, so I figured that the problem be between counters and the collector. I had seen this before on a cluster where one node would work and the other wouldn't, so I suggested we rebuild the counters using unlodctr and lodctr. I got a maintenance window (which was ironic considering that it was SQL Express, but I digress), unloaded and reloaded, and bounced the instance. Still nothing. Fortunately, I had not broken it worse - the values were still in the DMV.

I went through the very well documented troubleshooting procedures on the SQL Server CSS blog. I worked through Step 01 and decided to take a break to enjoy some of the new ice cream novelty that my wife had purchased for us all. Before moving on to Step 02, I had an epiphany - SQL Express is 32-bit only. That was a 64-bit box. I've experienced a similar problem before when setting up ODBC data sources - have to have both 32 and 64 bit set up for some applications to work.

So, I get back on the box and look and there is a perfmon.msc in the SysWow64 folder. I launch it and, like magic, the counters appear. I can't express the excitement - the kids wanted to know if I had discovered a new plant on Plants vs. Zombies. :-) I continued to build out the perfmon interactively and when I had everything in it I wanted, saved it out as a data collector. My excitement soon slipped into fear as I opened up the csv I was creating and realized that it was not collecting any SQL counters. Frustrated, I verified that the data collector definition was accurate. Everything seemed right...

I returned to my good friend Bing and now that I was sure it was a 32/64 bit issue (just as sure as I was that it was a corrupt counters issue, but I'll not linger there) and searched on "perfmon data collector x86 x64". The 2nd hit on Bing produced a winner - Creating a custom Data Collector Set with SQL x86 counters on an x64 OS. It's an article from a technical lead at Microsoft that appears to be in Romania. His issue was specifically with SBS, but the situation was similar. I was able to copy out the SQLCtrs.dll file from a 64-bit SQL Standard of the same build and place it into the C:\Windows\System32 folder on the Express box and now the counters for the data collector started to work.

The jury is still out on whether Standard will help this box perform, but I know one thing, we should now have the information necessary to determine that answer. I can only say I have the excellent SQL Server community to thank for getting this resolved. If it were not for the sharing nature of the world wide SQL Server community, I'm certain that this DBA Warrior would still be running from the Beast at the Well.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

SQL Express and the Curse of the Perfmon Counters

On a dark and spooky night in the land of SQL Server make-believe, a young warrior was asked to slay a beast preventing those in the land of SQL Server Express from collecting water from the Well of the Perfmon Counters.

While this warrior was only vaguely familiar with SQL Server Express, he thought - "It's a beast still in the land of SQL Server, just an outlying province." This warrior was not unfamiliar with slaying beasts in the land of SQL Server. He had slain hundreds of Dragons of Performance and had silenced the Horrid Beast of SSPI, so how could this beast in such a small province pose any threat.

Confidently, he stepped into the magical transport at Port 3389 and was quickly whisked away to the land of SQL Express. Upon arrival, everything seemed normal. The absence of the familiar SQL Agent was disheartening, yet expected, so he continued to the Well of the Perfmon Counters.

Other warriors had gone before to survey the land. They reported on the size and viciousness of the beast in the well, but he foolishly thought those were nothing more than exaggerations. So, at the well, he started to pull out the water. On the rope were labels that read "% Processor" and "Avg Disk Sec / Transfer", and many others as the rope continued to pull from the well. However, when he expected to find the bucket marked SQLServer:BufferManager, the rope was frayed. Just then, out of the Well came a beast the warrior had never before seen in the land of SQL Server Enterprise or even SQL Server Standard. This beast would surely require special magic to defeat. Sword and spear alone would not be sufficient.

Before being bitten by the hundreds of razor sharp teeth in the beast's mouth, the warrior fled into the woods to collect his thoughts and ask the all knowing, Sir BingsALot. Sir BingsALot offered a suggestion - restring the rope in the well by using the bark from the magical trees of Unlodctr and Lodctr. So, the warrior toiled to take this bark and weave it into a rope. First, the bark of the Unlodctr, then the bark of the Lodctr. Day and night, for days on end, without rest, food or water - Ok, enough of that - for 15 minutes with Pepsi and McDouble in hand.

With the new rope in hand, he ran back to the Well and dropped the rope. Once again, several labels appeared on the rope as he pulled it up, but just as he thought he could see the SQLServer:BufferManager bucket, the beast slashed the rope with its razor sharp claws and leapt from the well with more ferocity than before, frustrating the warrior, and sending him back to the woods for some more advice from SirBingsAlot. Try and try again, suggestions from the wisdom of the masses would just not work. Every time the rope would get close, the beast would appear, each time with more teeth and sharper claws, ready to pounce on the warrior.

The warrior, not defeated, but feeling weakened, decided to journey back home, closing the door on Port 3389, and enjoy some ice cream with the SQL Server family. It was while enjoying the wonderful ice cream Mrs. Warrior had purchased for just an occasion that he had an epiphany. SQL Express has never signed the 64-bit treaty with the other lands of SQL Server. SQL Express was still running under the magical laws of the 32-bit treaty.

The warrior thought - "I wonder if using the 64-bit bucket to retrieve the water is making the beast angry? I shall use the 32-bit bucket!". With speed rivaled only by the great runners in the #SQLRun at #SQLPass, this warrior jumped to Port 3389 where he was once again transported away to the land of SQL Express. This time, armed with the 32-bit bucket, he tied the 32-bit Perfmon bucket he collected from the "C:\Windows\SysWow64" folder to the rope and dropped it into the well. With great trepidation, he started to pull the bucket up. Once again the collection of counters, which he started to collectively call "OS" for no apparent reason, started to appear. The SQLServer:BufferManager bucket appeared... no beast. Victory! The warrior had done it.

As the warrior started to dump the bucked into the "Data Collector" barrel to save the water for drinking later, the bucket disappeared. Instantly, the beast appeared and knocked the warrior off his feet. How could this be! As the warrior ran off to safety, he got a detailed glimpse of the beast. This beast was not the same. This beast was nothing more than a copy of the original. While the looks were deceiving, the original was vanquished, this one was indeed new.

Armed with new key words, err, details to ask of SirBingsAlot, the warrior received his answer from a warrior in Microsoft Romania. Yes, buried deep in a tome from 2010, a bold warrior had shared his experience in slaying a similar beast in the land of SBS. Armed with the magical SQLctr90.dll spell collected from the province of SQL Server Standard, the warrior collected the water from the Well of Perfmon and saved it into the Data Collector barrel. While he could still hear the beast breathing heavily in the background, he failed to raise his head and allowed the water to be carried away. The water from the Well of Perfmon can lives on to energize the other warriors as they seek to slay the Dragons of Performance!

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist.

Follow Me on Twitter

Delivering Three PTI SQL 2012 Firestarter Webcasts

I have been given the opportunity to deliver a few webcasts about some of the new features of SQL Server 2012 over the next couple of months for Perpetual Technologies.

You'll get to hear me share the greatness that is SQL Server during the following sessions:

2/16/2012
Throwing T-SQL 2012 Enhancements Against the Wall
Just like your grandma tested spaghetti to tell if it was done, we'll be throwing several of the new T-SQL enhancements against the wall to see what's good and what falls off. We'll silence the lion's RaiseRror by throwing some meaty exceptions. We'll bring together the old lovers "Coalesce" and "+" for one last jaunt before they are banished by the Concat function. We'll even open the window on some of the new aggregate and analytical functions as well as some other t-sql enhancements introduced in SQL 2012.

3/8/2012
SSIS 2012 – New and Improved
SQL Server Integration Services has been used by many a DBA through the "Import and Export Wizard" or through the use of Maintenance Plans. However, SSIS has been adopted by many organizations as the platform of choice for moving data to and from disparate data repositories. SQL 2012 introduces several enhancements to SSIS for both the administrator and the development team. These enhancements will be explored as we compare and contrast SSIS in previous versions with SQL 2012.

3/29/2012
Extended Events – The Death Knell of SQL Trace
Extended Events has been around for a couple of versions of SQL Server, but beginning in 2012 has an important role – replacing SQL Trace. We'll dive into how to use Extended Events just as you would use Profiler to create a trace. We'll also be exploring the new APIs and Powershell enhancements that make Extended Events a 2012 feature to be learned, loved, and utilized.

Over this same time frame, my colleagues will be delivering webcasts on topics such as MDS, DQS, Reporting Services, and Analysis services:

2/9/2012
Utilizing Knowledge with Data Quality Services in SQL Server 2012
Data operations are not only about getting data in and out of a given system but also ensuring that the data is suitable for usage. We've all heard of the phrase "Bad data in, bad data out" and what good is data to us if we cannot ensure that it is correct. Most of the time we write lots of code within stored procedures and functions to compensate for our lack of a system that can help us out with this part of the data food chain. Lucky for us, Microsoft SQL Server 2012 comes with a product known as Data Quality Services (DQS), whose main goals are not only improve your data quality but also to make it much easier to implement and maintain. In this session, we will be looking at what it takes to get DQS up and running in your environment, how to set up Knowledge Bases, and how to utilize the system with tools like SSIS and Master Data Services.
Presented By: Arie Jones

2/23/2012
Enhancements to Reporting Services in SQL Server 2012
Reporting is one of the best ROI features of any business intelligence project because it allows the end user to view the results of all of your hard work. In SQL Server 2012, Microsoft has provided a couple of useful upgrades to our tried and true Reporting Services framework. More importantly, is the release of Power View (formerly known as Project Crescent) which allows you end users to visualize, interact, and perform sophisticated analyses on their own. In this session, we will cover all of the upgrades involved in the new version of SSRS as well as demo some of the slickness of the Power View tool.
Presented By: Shaun Watts

3/1/2012
AlwaysOn – Way to get your 9's?
We are always being told 9's, 9's and more 9's, but when implementation time comes to take action, technology/budget limitations get in the way, especially in the High Availability and Disaster Recovery (HA/DR) technology space. Clustering and Mirroring are great and meet many of our needs, but there are some things we have wished for, such as a mirror database that is usable. AlwaysOn solves this limitation by merging both Mirroring and Multi-Node Multi-Subnet Clustering (True Geo-Clustering Support!!!). AlwaysOn also allows the replica database to be used in a Read-Only capacity, so we can use it for reporting purposes. (Imagine that!) Join us in providing a warm welcome to this new functionality with extensive demos of setting up, configuring and testing AlwaysOn and all of it in an effort to afford the 9's we are always being asked.
Presented By: Warren Sifre

3/15/2012
PowerPivot & BISM in SQL Server 2012 OH MY!
PowerPivot is one of Microsoft's self-service Business Intelligence tools released with R2 but it was a serious v 1.0 type of environment. Now with its second release Microsoft has ramped up the capabilities and given us a new toy known as the BI Semantic Model. In this presentation we will delve into all the new goodies and show you how these tools will change the way that you develop and implement your business intelligence offerings.
Presented By: Hope Foley

3/22/2012
SSAS 2012 – What BISM Means to You!
The data revolution continues and with revolutions come evolutions. Implementing and using SSAS has always been synonymous with cost and expertise. With the newest evolution of the SSAS platform from UDM to BISM there is an enhanced focus on data proliferation amongst the non-BI specialist of the world. The new BI Semantic Model introduces new components such as the Tabular Data Model and Vertipaq, which allows for quick SSAS DB creations with the intent on having widely available content originating from a Pivot source. We will discuss the impact the BISM will have in the BI architecture as well as review some of the new options within SSAS Configurations.
Presented By: Warren Sifre

You can click here or visit http://www.pti.net/Firestarter/ to sign up.

Verifying Last Successful CheckDB with Powershell and SMO - sorta

A few years ago, I wrote a script that was published on SQLServerCentral.com about how to verify the last successful CheckDB for all the databases on an instance. This was a tremendous help to me and other DBAs as we could now tell when the integrity was valid on our databases. If we kept record of this, we could know exactly to when we would have to restore to be guaranteed a consistent database.

I recently worked on a project where I needed to have Powershell collect this date. Much to my chagrin, SMO does not expose this property. I can get the LastBackupDate, LastLogBackupDate, and LastDifferentalBackupDate as properties of the database class, however LastSuccessfulIntegrityCheck is just not there.

I filed a Microsoft Connect in December, 2011 that requested to have this feature added to the SMO database class - - but that has yet to be responded to by Microsoft.

I knew I had to do something so that Lord Vader wouldn't smack me with the dark side of the force. I eventually just reverted to T-SQL and the ExecuteWithResults database method to return the DBCC DBINFO result set to Powershell for each database. The script below will give you the last successful checkdb for all databases in any instances that you provide in a text file. This script uses a very simple custom object - $iDatabase - and loads these objects up into an array for further processing later. This array could be piped to the ConvertTo-HTML or the ConvertTo-CSV cmdlet to create a more flexible output or even through the Where-Object to only report on those that were out of spec. In this example, however, it just sends all the rows unfiltered to the Format-Table cmdlet.

Be certain to vote for the Microsoft Connect suggestion if you think that this would be a valuable addition to the SMO database class.

#************************
#     LastSuccessfulCheckDB
#    Author:  Kyle Neier, Perpetual Technologies
#    Reports on the last successful checkdb for all databases within all instances provided
#    
#    $InstanceList file should be a file of SQL instances in either server, server,port or server\instancename
#    
#************************

param
(
    [string]$InstanceList = "C:\Users\kneier\Documents\Powershell\InstanceList.txt"
)

# Load SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null;

#Initialize Array to hold new database objects
$iDatabases = @()

#Loop over each instance provided
foreach ($instance in $(get-content $InstanceList))
{
     try
    {
        "Connecting to $instance" | Write-Host -ForegroundColor Blue
        
        $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $instance;
        #How many seconds to wait for instance to respond
        $srv.ConnectionContext.ConnectTimeout = 5
        $srv.get_BuildNumber() | out-Null
    }
    catch
    {
        "Instance Unavailable - Could Not connect to $instance." | Write-Host -ForegroundColor Red
        continue
    }
    
    $srv.ConnectionContext.StatementTimeout = $QueryTimeout
    
    foreach($Database in $srv.Databases)
    {
        #create object with all string properties
        $iDatabase = "" | SELECT InstanceName, DatabaseName, LastSuccessfulCheckDB
        #populate object with known values
        $iDatabase.InstanceName = $srv.Name
        $iDatabase.DatabaseName = $database.Name
        
        try
        {
            #Get date of last successful checkdb
            #executes dbcc dbinfo on database and narrows by dbi_dbcclastknowngood
            $database.ExecuteWithResults('dbcc dbinfo() with tableresults').Tables[0] | `
            ?{$_.Field -eq "dbi_dbccLastKnownGood"}| `
            %{$iDatabase.LastSuccessfulCheckDB = [System.DateTime]$_.Value} -ErrorAction Stop
        }
        catch
        {
            "CheckDB could not be determined for $instance.$database" | Write-Host -ForegroundColor Red
        }
        
        #add the iDatabase object to the array of iDatabase objects
        $iDatabases += $iDatabase
    }
}

#output all the databases as a table for viewing pleasure
$iDatabases | ft

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter