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
Subscribe to:
Post Comments (Atom)
1 comment:
Thanks a lot this helps!
Post a Comment