January 10, 2007 at 9:13 am
I've inherited a SQL 2000 instance on which there are a couple of databases that are having performance issues. The first thing that I've done is to attemtp to set up performance monitoring. However, in using the monitor: none of the SQL counters are available. When I look into the sysperfinfo system table - it's empty.
Is there some way to verify that the performance counters/module was initially installed with SQL? Additionally - is there something that I can do to install/invoke the counters?
This is such a critical instance/set up databases to our organization - I can't believe that no one has ever done any performance monitoring/tuning but....
Thanks for any help with this.
Glenn
January 10, 2007 at 10:08 am
January 11, 2007 at 7:49 am
I'm not surprised to be honest, in x years of being a DBA I don't think I've ever found anyone with baselines for their server performance, as I tune for a living it makes for interesting times! Missing counters can be a pain, but they can be excluded in the install.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 11, 2007 at 7:58 am
I had read the posting which John (above) had referenced and worked through the registry changes. The DWORD doesn't exist and the MS doc doesn't indicate where in the registry to put it.
I haven't yet installed the Windows 2000 resource Kit but I'm not confident that this will solve the issue.
Glenn
January 11, 2007 at 8:22 am
On a test box:
I've installed SQL 2000 (without the performance counters). This simulates the existing scenario. Then I went back and tried to install (update) SQL - hoping to install the component: performance counters.
When I attempted to install/update: the performance counters was already checked so I couldn't proceed (the update requires that your add something new).
So... if the performance counters were not initially installed (which I'm not certain - is there a way to verify this?) - how can the preformance counters be installed (without uninstalling/reinstalling SQL)?
Glenn
January 11, 2007 at 8:33 am
Glenn
This is one of those issues where you can waste days trying lots of fixes that don't work. Or you can spend half a day blitzing the whole thing and reinstalling from the start. It may be quicker, and better for your sanity, to do that.
John
January 11, 2007 at 8:40 am
John - could be... but as this is a core/critical database server... If there's an "easy out" - like installing the performance counters, fixing a dll, etc. It's worth the couple of days.
I'll probably contact MS.
Glenn
January 12, 2007 at 4:51 am
Hi,
What is the version of the SQL Server ?
There is a fix available. Please check the link.
http://support.microsoft.com/kb/812915/en-us
Hope that helps you.
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
January 12, 2007 at 5:04 am
apologies if I'm double posting - may have closed wrong window!
I've normally only had to do this
In command line, execute "unlodctr MSSQLServer" (Without quotation, the same as below)
Reloading the SQL Server Counters
(a) In command line, locate the current folder to C:\Program Files\Microsoft SQL Server\MSSQL\BINN
(b) Execute "lodctr sqlctr.ini"
Stop SQL Server Services and Restart the Services
Open Perforam Monitor to check if the counters appear
(a) In command line, execute "perfmon"
(b) Add and check if the counter appear
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 12, 2007 at 7:37 am
Colin the aritcle to which you refer:
http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
Also suggests registry settings...
In my case I checked the registry and found the MSSQLSERVER/Performance settings to be DWORD-identical to another server which is working fine (has SQL performance counters) - the performance counters simply aren't on available.
The search continues
btw - Minaz: this is SQL 2000 SP on a Windows 2000 SP4 server - and there are no errors (startup or otherwise)
Glenn
January 12, 2007 at 2:01 pm
How to manually rebuild Performance Counter Library values
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q300956
Troubleshooting Performance Monitor Counter Problems
http://support.microsoft.com/?id=152513
MohammedU
Microsoft SQL Server MVP
January 12, 2007 at 2:55 pm
Mohammed -
Interesting articles... thanks! They appear however to relate specifically to the windows performance counters versus the SQL Server counters. Since the windows performance counters are currently in place and working (just the SQL counters are missing) do you know if rebuilding the windows counters will restaore the SQL counters?
Thanks again.
Glenn
January 15, 2007 at 3:28 am
no it wasn't that article is was a microsoft publication - I usually copy ms kbs etc. into my own docs as ms have a habit of changing ref numbers so I build my own reference library of problems and solutions.
As I say I had this issue ona number of servers and that was all I ever did - never had to mess with the registry.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply