mssql monitoring?

[mconigliaro]

im curious as to whether anyone has been able to monitor mssql server statistics with zabbix. my first thought was to use proc_info (and possibly perf_counter) items, but im interested in what other people have done. any suggestions?

[cameronsto]

I believe MS SQL Server exposes values via SNMP. You could use those to monitor it. Sorry, I can't provide more information, I was looking into this recently but got sidetracked before I found much information.

-cameron

[bytesize]

# Microsoft SQL Server 2000
PerfCounter = [\SQLServer:Buffer Manager()\Database pages]
PerfCounter = [\SQLServer:Buffer Manager()\Free pages]
PerfCounter = [\SQLServer:_Databases(DATABASENAME)\Data File(s) Size (KB)]
PerfCounter = [\SQLServer:_Databases(DATABASENAME)\Log File(s) Size (KB)]
PerfCounter = [\SQLServer:General Statistics()\User Connections]
PerfCounter = [\SQLServer:Memory Manager()\Total Server Memory (KB)]

[claytronic]

I use Bytesize's example to setup PerfCounter monitoring of our SQL 2005 server. Notice that "_SQLServer" has been edited to "SQLServer" in the two lines used for reading the database file sizes.

MSSQL = 2005 SP1
OS = Windows 2003 R2 SP1
Zabbix Agent = 1.1.3 Build of Oct 23 2006


PerfCounter = db_pages,"\SQLServer:Buffer Manager()\Database pages",60
PerfCounter = db_free_pages,"\SQLServer:Buffer Manager()\Free pages",60
PerfCounter = db_data_file_size,"\SQLServer:Databases(DATABASE_NAME)\Data File(s) Size (KB)",60
PerfCounter = db_log_file_size,"\SQLServer:Databases(DATABASE_NAME)\Log File(s) Size (KB)",60
PerfCounter = db_userconns,"\SQLServer:General Statistics()\User Connections",60
PerfCounter = db_server_mem,"\SQLServer:Memory Manager()\Total Server Memory (KB)",60
PerfCounter = db_cpu_load,"\Process(sqlservr)\% Processor Time",60

I hope this helps anyone looking to monitor their MSSQL servers.

- Claytronic

[samwise]

Hi,

I've added these perfmons to my .conf:

PerfCounter = db_pages,"\SQLServer:Buffer Manager()\Database pages",60
PerfCounter = db_free_pages,"\SQLServer:Buffer Manager()\Free pages",60
PerfCounter = db_data_file_size,"\SQLServer:Databases(BTD)\Data File(s) Size (KB)",60
PerfCounter = db_log_file_size,"\SQLServer:Databases(BTD)\Log File(s) Size (KB)",60
PerfCounter = db_userconns,"\SQLServer:General Statistics()\User Connections",60
PerfCounter = db_server_mem,"\SQLServer:Memory Manager()\Total Server Memory (KB)",60
PerfCounter = db_cpu_load,"\Process(sqlservr)\% Processor Time",60

I've used db_pages, data_file_size and db_userconns.

data_file_size is working fine. The other two keep showing 'Not Supported' in their item status.

Data was recieved for a bit and then dropped off, then for another short time and stopped.

I set them to active, but they revert to not supported - after polling happens I assume.

I have these options set for the item

db_pages
zabbix agent
db_pages
numberic(integer 64bit)
per/sec
custom multiplier
.01
30
90
365
Not supported
as is
as is
none

Thanks for any help.
Sam

[phredbroughton]

So far every time I have seen an item work and then suddenly stop, it has been because I had the data type set to numeric(integer) and the value was either returned as a numeric(float) or with some of my UserParameter definitions, I found that it would occasionally return a leading blank (whitespace). Any time the value returned doesn't match up exactly with the item definition it flags itself as not supported. It would be nice if we could change this to alert that the value received was X and X doesn't match the defined data type ;-)

[samwise]

this was the problem, thanks! This also happens with other problems in the .conf file. Missing () after object names, no leading backslash before object.

[gpostaire]

Do you know a way to monitor scheduled jobs in MSSQL ?

[claytronic]

Do you know a way to monitor scheduled jobs in MSSQL ?

That's a great idea, gpostaire. The maintenance plan success/failures are logged in the msdb.dbo.sysmaintplan_log table. An agent check script could be built around the following SQL query. I'll tool around with this today if I have some extra time.

The query below returns any jobs that have failed in the last 24 hours of the query execution time.

SELECT
s.name,
sp.subplan_name,
l.start_time,
l.end_time,
l.succeeded
FROM
msdb.dbo.sysmaintplan_log AS l
INNER JOIN msdb.dbo.sysmaintplan_plans AS s ON l.plan_id=s.id
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id
WHERE
DATEDIFF(d, l.end_time, GETDATE()) < 1
AND
succeeded = 0

[claytronic]

I added this UserParameter to the Zabbix Agent running on the SQL server to monitor for maintenance jobs that have failed in the last 24 hours.

Set your triggers to fire for any value greater than zero.

Note Make sure to edit the ##SQL_USER## and the ##SQL_PASSWORD## to a SQL account that has select access in the MSDB database.


UserParameter = db_maint_jobs,sqlcmd -d Master -U ##SQL_USER## -P ##SQL_PASSWORD## -h -1 -W -Q "SELECT COUNT(*) FROM msdb.dbo.sysmaintplan_log AS l INNER JOIN msdb.dbo.sysmaintplan_plans AS s ON l.plan_id=s.id INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id WHERE DATEDIFF(d, l.end_time, GETDATE()) < 1 AND succeeded = 0"

[boschertjd18]

Claytronic,

I took what you suggested and put it in the userparameter, except that I removed the -U and -P options. Therefore, I believe, the user under which the zabbix windows agent runs executes the sql statement. The user does have rights to run the statement against the db

However, when I run the zabbix_get command from the linux box against that parameter, I receive a ZBX_NOTSUPPORTED error. This happens for both your command and a new one I created:

UserParameter = sql_version,sqlcmd -d Master -h -1 -W -Q "SELECT @@version"

Do you have any ideas why this may be?

Thanks,
Joseph

[Alexei]

Specify full paths to all binaries you use.

[boschertjd18]

Alexei,

Thanks for you quick response. Unfortunately, plugging in the full path for the binaries still does not work. I restarted the ZABBIX 1.4 Client for Windows and I'm still returning the ZBX_NOTSUPPORTED from the zabbix_get command in linux. The commands run fine from command prompt in Windows.

Here is what I have in the .conf file:
UserParameter = ipconfig,c:\windows\system32\ipconfig

UserParameter = sql_hello,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -h -1 -W -Q "SELECT 'hello'"

UserParameter = sql_version,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -h -1 -W -Q "SELECT @@version"

Thanks,
Joseph

[claytronic]

boschertjd18,

Your SQL commands do not have the -E switch which forces NT Authentication. Have you set the Zabbix service to run under an NT account that has user access in your MSSQL server? This is why my queries use the -U <user> and -P <password> arguments. My Zabbix service runs under the local system account which does not have access to MSSQL. I can then lock down the zabbix MSSQL user account since I'm storing the password in plain text. (encrypted config files, Alexei?)

--
Claytronic

[boschertjd18]

Claytronic,

I did indeed make sure the zabbix service is running as a user who has access to the Master db. I also updated the command to run as -E, but no luck. Finally, I created a user and tried via sql server authentication, but again no luck.

Again, all the commands run fine from the Windows command prompt. In all instances, I received a ZBX_NOTSUPPORTED from the ./zabbix_get command. The log file reads "Parameter [sql_hello2] is not supported by agent on host..."

I seem to be running out of ideas. Could this be a bug within the Zabbix Windows Agent 1.4.0.1? Here is the User-Def section:


UserParameter = ipconfig,"c:\windows\system32\ipconfig"

UserParameter = sql_hello,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -E -h -1 -W -Q "SELECT 'hello'"

UserParameter = sql_hello2,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -U Master_RO -P r0_mas+er -h -1 -W -Q "SELECT 'hello'"

UserParameter = sql_version,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -E -h -1 -W -Q "SELECT @@version"

UserParameter = db_maint_jobs,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -E -h -1 -W -Q "SELECT COUNT(*) FROM msdb.dbo.sysmaintplan_log AS l INNER JOIN msdb.dbo.sysmaintplan_plans AS s ON l.plan_id=s.id INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id WHERE DATEDIFF(d, l.end_time, GETDATE()) < 1 AND succeeded = 0"


Regards,
Joseph

[dratliff]

Joseph,

Have you tried an older client? I have reverted back to the 1.1.7 agent.

Just a thought.

[claytronic]

Joseph,

I have not tested the SQL commands with the 1.4.x agent series. We are still running 1.1.7 in production. I'll post our results when we begin evaluating on our tier 2 production servers.

--
Claytronic

[tronite]

Well ... I would update my database and search on the host for anything to do with mssql monitoring?

Sorry if that's not much help! :o

[tronite]

I thought I already posted on this list, must have gotten side tracked, but I had wanted to ask what happens if you just locate what you are looking for and grep for the specifics?

[mrogers]

Clay,
Would you mind kind of explaining the different parts of the query you wrote. I understand the UserParam and the key and a few other pieces however i am not familiar with all the switches. I'm kind of new to DB monitoring and would appreciate the help.

Thanks,

Matt

[claytronic]

Clay,
Would you mind kind of explaining the different parts of the query you wrote. I understand the UserParam and the key and a few other pieces however i am not familiar with all the switches. I'm kind of new to DB monitoring and would appreciate the help.

Thanks,

Matt

Matt,

The MS SQL performance counters that I previous posted are the basic gages for monitoring the health of a your SQL server. The are quite a few more that the ones that I use so take some time to identify what is important to your applications.

Here's a general explanation of each:

\SQLServer:Buffer Manager()\Database pages Gages how often the SQL server can perform a transaction without having to perform disk I/O.
\SQLServer:Databases(DATABASE_NAME)\Data File(s) Size (KB) Measure of how large a particular database is physically growing
\SQLServer:Databases(DATABASE_NAME)\Log File(s) Size (KB) The physical size of the SQL transaction log. This one is very important since by default a SQL server will not automatically truncate the database log. If someone performs large update or deletes this log file can grow very quickly. Use this in conjunction with your disk space triggers.
\SQLServer:General Statistics()\User Connections This shows how many concurrent connections are utilizing the SQL server itself. A sudden rise in these may point to a hung application that isn't properly closing connections. A .Net application SQL connection pool can often spike the connection count when a transaction has caused a large number of table locks.
\SQLServer:Memory Manager()\Total Server Memory (KB) How much OS memory the SQL service is using.
\Process(sqlservr)\% Processor Time Tracks how hard the SQL process is working. This is a great way to identify performance bottlenecks before they become big pains in your neck.
\SQLServer:Locks(_Total)\Number of Deadlocks/sec If you see these happening then check with your DBA or application engineer because deadlocks are a SQL performance killer.
\SQLServer:Locks(_Total)\Lock Waits/sec This measures how much time the database is having to wait to deliver its data because of other transactions locking tables.


Hope this helps.

--
Clayton

[clubbing80s]

Hi
I'm running zabbix-agent 1.4.2 on windows 2003 server with msql sql server 2005

I keep getting not supported .. i have tried adding an removing "()" etc .. what is that exact correct syntax for the following ?

PerfCounter = db_pages,"\SQLServer:Buffer Manager()\Database pages",60

PerfCounter = db_free_pages,"\SQLServer:Buffer Manager()\Free pages",60

PerfCounter = db_data_file_size,"\SQLServer:Databases(BTD)\Data File(s) Size (KB)",60

PerfCounter = db_log_file_size,"\SQLServer:Databases(BTD)\Log File(s) Size (KB)",60

PerfCounter = db_userconns,"\SQLServer:General Statistics()\User Connections",60

PerfCounter = db_server_mem(),"\SQLServer:Memory Manager()\Total Server Memory (KB)",60

PerfCounter = db_cpu_load(),"\Process(sqlservr)\% Processor Time",60

[swaterhouse]

Since you are on 1.4 you can add perf counters to the web interface without adding them to the config file on the agent.

type: Zabbix agent
key: perf_counter["\SQLServer:Buffer Manager\Database pages"]
Type of information: float



I started to put together a template for SQL on Friday. I have a couple of more things to add to it and I will post it to the wiki when complete.

[clubbing80s]

Thanks was a quick response .. many thanks, look forward to seeing your template ..
G

[clubbing80s]

ok winning with most but the following are still not behaving ..

perf_counter["\SQLServer:_Databases(WEBCOLTECH\Data File(s) Size (KB)"]
perf_counter["\SQLServer:_Databases(WEBCOLTECH)\Log File(s) Size (KB)"]
where WEBCOLTECH is the database name (what if i wanted the total foot print of all the databases ?)

still getting not supported what have i missed
Thanks for you time ..
G

[claytronic]

Since you are on 1.4 you can add perf counters to the web interface without adding them to the config file on the agent.

I like your idea of moving all of the perfmon counters into the database. Having everything in a template would save a bunch of time and centralize the configuration. Is there any 1.4 / 1.6 Win32 Agent differences I should know about between perf_counter keys?

--
Claytronic

[claytronic]

ok winning with most but the following are still not behaving ..

perf_counter["\SQLServer:_Databases(WEBCOLTECH\Data File(s) Size (KB)"]
perf_counter["\SQLServer:_Databases(WEBCOLTECH)\Log File(s) Size (KB)"]
where WEBCOLTECH is the database name (what if i wanted the total foot print of all the databases ?)

still getting not supported what have i missed
Thanks for you time ..
G

Try executing this from the Windows command prompt:

typeperf -qx | find "WEBCOLTECH"

It should echo back all of the performance counters with your database's name. Then just do a good old cut'n paste from the command prompt window. Perhaps you've got a simple whitespace problem. This has happened twice to me while setting up my Zabbix perfmon. :)

--
Claytronic

[clubbing80s]

thanks for the input .. it also answered my question on getting info on terminal server ..

[swaterhouse]

I like your idea of moving all of the perfmon counters into the database. Having everything in a template would save a bunch of time and centralize the configuration. Is there any 1.4 / 1.6 Win32 Agent differences I should know about between perf_counter keys?

--
Claytronic

Not that I have found - but you need the 1.4.2 version or later because the perf counters were pretty broken in 1.4.0 and 1.4.1.

I will upload my template later today. I dont have many triggers defined. I am struggling to figure out where I should set the thresholds. Right now my server has more than enough power so I am really just using the items for trending data.

[Jarno]

Hey guys! First of all i'm new to this forum and zabbix, this is my first post :). I'm testing with zabbix for a few weeks now and it works great!

Currently i'm looking into perf counters. I was wondering where you uploaded this template swaterhouse. I've checked the wiki xml contrib page but didn't find anything. My plan is to make loads of these default templates for various services, and yes, i'm willing to share them :)

--

Jarno

[swaterhouse]

I totally forgot about this - I thought I already uploaded it.

Its on the XML Templates page now on the wiki.