SQL Server – Performance Counter MSSQLInstance\SQL Errors\Errors/sec\Info Errors

perfmonsql server

Im wondering what kind of errors would show up in the:

SQL Errors\Errors/sec\Info errors

performance counter. I read the TechNet article it just states:

Information related to error messages that provide information to
users but do not cause errors.

Anyone can shed some light on this, examples of what errors might show up here is more than welcome.

Best Answer

I tested this counter and for info errors the following increased the counter (not an exhaustive list): connect, shrink, backup,script database, modify stored proc, select top 1000 rows, new query, using autosense, starting profiler, stopping trace, activity monitor, new login, pulling up properties for a login, database, or the server.

User Errors: Messages in the query results when there is incorrect syntax, or table doesn't exist, that sort of thing. Taking a database offline or bringing a database online. Killing a process.

Interesting notes: starting Job Activity Monitor generated both User and Info. Starting a job generated both User and Info. Changing a server option (max server memory) did not increase the counter.

Related Solutions

Sql-server – sp_ExecuteSQL, performance and table variables

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement.

If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that accounts for 136 of your missing 137 reads.

(Script with #temp table)

EXEC sp_executesql N'
   CREATE TABLE #T 
  ( 
     x INT 
  ) 

INSERT INTO #T 
            (x) 
SELECT TOP 10 number 
FROM   master..spt_values 

SELECT * 
FROM   #T 
UNION 
SELECT * 
FROM   #T 

DROP TABLE #T
'

Sql-server – High CXPACKET and LATCH_EX waits

CXPACKET can be accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the case (and I believe it is, based on the question) then the MAXDOP value should be lowered to fit your hardware.

Besides this, here are some more recommended steps in diagnosing the cause of high CXPACKET wait stats values (before changing something on SQL Server):

Do not set MAXDOP to 1, as this is never the solution
Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is normally working correctly
Check the indexes and statistics on tables used by the query and make sure they are up to date
Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system
Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is the case, then parallelism is not the bottleneck. Troubleshoot those wait stats to find the root cause of the problem and solution

If you really need to understand the CXPACKET wait type in depth, I would advise reading the Troubleshooting the CXPACKET wait type in SQL Server article

Best Answer

Related Solutions

Sql-server – sp_ExecuteSQL, performance and table variables

Sql-server – High CXPACKET and LATCH_EX waits

Related Question