In the past, I've combined server side traces with Perfmon successfully, which is pretty useful, I know that. I would like to do the same with Extended Events, so I can correlate and analyze the server side as well.
View 4 RepliesAny suggestions on getting them back? Reinstalling SQL didn't do anything. And yes, I am server administrator.
Thanks!
The SQL server perfmon counters are missing from one of my MSSQL 7.0 servers - they do appear in others just fine.
Can anyone tell me what is wrong and/or how I can add the counters? I have seen archieve notes on this topic but can't access them for some reason (page appears blank when I click on link)
I am using perfmon to remotely monitor the problem server. My login has sysadmin permissions and is an NT admin. on both the server and the monitoring client. The login account for MSSQL server and SQL Agent services are both sysadmins.
Many thanks
Im missing all SQL Server counters in the Performance Monitor
and have looked at article: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q196/6/57.ASP&NoWebContent=1
but this article refers to SQL 7...I have 2K with SP3 and MS03-031
How can I get my counters ? thanks
Does any one know, how I can add SQLServer performance monitoring counters back on my NT server. For some reason I am missing these counters. I really do not want to reinstall either the NT OS or SQL OS if there is anyway to aviod this.
Thanks
On some of my database servers, there are SQL Server counters in Performance Monitor. On other servers these are not present. How can I turn these on?
I know with the disc counters you need to explicitly enable them:
diskperf -yv
Is there a parallel for SQL Server? I don't remember doing anything to get them working on the servers that have them. All online articles seem to assume that they just exist.
TIA!
I've always assumed SQL Server performance monitor counters get automatically installed when SQL Server is installed. I recently installed SQL Server on a remote server and noticed the SQL Server performance monitor counters do not appear in Perfmon. Aren't these counters automatically installed during setup?
Dave
SQL 6.5 had counters for monitoring SQL licenses. Where did Microsoft hide them in SQL 7.0? Or did they just disappear? If they are gone, is anyone doing anything to monitor the licenses with something like perfmon? User Connections doesn't help because the application takes several and not always the same number of user connections for one license.
View 2 Replies View RelatedI have 2 SQL Server 7 databases installed on 2 Windows 2000 servers. One box shows all the SQL performance counters with in performance monitor, the other one doesn't. Is there a way to register those SQL counters in performance monitor?
View 1 Replies View RelatedThis is related to an earlier post, but I found out that I was able to see SQL Server counters in Performance monitor before applying the patch, but the counters were not present afterwards....
do I need to edit the registry ?
I've setup a deadlock monitor using extended events like this.
CREATE EVENT SESSION [deadlock] ON SERVER
ADD EVENT sqlserver.lock_deadlock(
ACTION(package0.process_id,sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle)
[Code] ....
Deadlock happened couple of days ago. I'm trying to determine the cause of deadlocks. What script should I use to pull that information to see what objects/processes caused deadlock?
I want to gather (and filter by) client ip address in an extended events session. So far, I have the sqlserver.login event added, but I can find no way to get client ip address added to the action or predicate.
I can filter by the pred_source field "session_nt_domain" or "nt_domain" but this is not always populated by all logins and also, this domain only lists machine name, such as FRED, as opposed to the full domain FRED.foo.bar.
Is it possible to gather login client ip address in an extended events session? Surely internal SQL Server processes have access to it because the login audit can populate client ip address.
I have an existing EE setup that captures all failing queries (see code below). The problem is that I also want to somehow capture RPC_starting so that I can see which parameters are passed in whenever a query fails. Is there a way to somehow capture those two events (error_reported & rpc_starting), but only capture rpc_starting when there is actually an error reported?Or maybe just an event on rpc_starting and somehow filter to only capture when there is an error?
Existing error_reported EE code:
CREATE EVENT SESSION [what_queries_are_failing] ON SERVER ADD EVENT sqlserver.error_reported (
ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.session_id,
package0.collect_system_time, sqlserver.transaction_id, sqlserver.username, sqlserver.client_hostname)
[code]....
I do have the following Extended Event session on my Dev box;
CREATE EVENT SESSION [sp_showplan] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlserver.plan_handle)
WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[equal_i_sql_unicode_string]([object_name],N'MyStoreProcedure')))
ADD TARGET package0.event_file(SET filename=N'E:DBA_AuditSP_Exec.xel',metadatafile=N'E:DBA_AuditSP_Exec.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
The idea is being able to capture the execution plan when the program invokes the store procedure, regardless of the database.
This works on my Dev box. When I manually trigger "MyStoreProcedure" from database A , the event is saved. The same thing happens when I do that from database B. Ok ... so far, so good.
So I went to the live production environment and setup my Extended Events session. But it's saving nothing. I was able to check that the store procedure was executed on several databases but my extended events session never grabbed the plan.
What could be the reason for this? Memory starvation maybe? Is there something I am doing wrong?
When you view the Extended Events "Watch Live Data" is the duration in milliseconds or microseconds? I'm assuming it's milliseconds, but if you look at the timestamp difference from start to complete it doesn't add up to the duration amount? It looks like it's just the difference between the timestamps?
View 5 Replies View RelatedI use this code in a utility procedure (for performance testing) but it is really slow.
For example, a session with three events is taking 5 seconds to complete this query:
DECLARE @xml xml=
(
SELECT CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = @name
);
with data as
(
select
convert(varchar(128),convert(varbinary(128),'0x'+n.value('(action[@name="context_info"]/value/text())[1]','varchar(128)'),1)) context
, n.value('(data[@name="duration"]/value/text())[1]','int')/1000.0 duration
, n.value('(data[@name="cpu_time"]/value/text())[1]','bigint')/1000.0 cpu_time
, n.value('(data[@name="physical_reads"]/value/text())[1]','bigint') physical_reads
[code].....
So, I was wondering (considering the buffer is usually only holding a few hundred events)
1. Is this the wrong way to query data from a ring buffer?
2. Is there any way to make this code quicker?
3. Is it better to target a file store rather than a ring buffer for this?
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003D
D8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000
[Code] .....
I wanted to demonstrate something about CXPACKET wait type. For the purpose of this demo I've created a query in AdventureWorks database that uses a parallel query plan, an extended events session that captured the wait statistics for a single session and a query that shows the extended event's data. I ran it and it worked fine. Then I dropped and recreated the event session (to clear the data), in a new window I wrote a transaction that updated the table fallowed by waitfor statement so the first query will be blocked for few seconds and ran the whole thing again. The select statement was blocked as expected (ran for 9 seconds instead on 1 second as it ran without the blocking), but the wait stats that I got were almost identical to the those that I got without blocking the query.
--Query that uses parallel query plan
With MyCTE as (
select top 50 * from Sales.SalesOrderHeader)
select top 10000 * from Sales.SalesOrderHeader, MyCTE
order by newid()
[code]....
I am trying to use xquery to get event data back from extended events. I am trying to use some sample data from Grant Fritchey but I am getting null records back. Below is the xml - I just want to retrieve a distinct list of the client_hostname and client_app_name.
<event name="login" package="sqlserver" timestamp="2014-02-19T23:53:54.299Z">
<data name="is_cached"><value>true</value></data><data name="is_dac">
<value>false</value></data><data name="database_id"><value>7</value>
</data><data name="packet_size"><value>8000</value></data><data name="options">
[Code] ....
The query I have that doesn't work is :
WITH xEvents AS
(SELECT object_name AS xEventName,
CAST (event_data AS xml) AS xEventData
FROM sys.fn_xe_file_target_read_file
('C:LoginTraceShared_0*.xel', NULL, NULL, NULL))
SELECT distinct top 1000 xEventName,
xEventData.value('(/event/data[@action_name=''Client_APP_Name'']/value)[1]','varchar') Client_APP_Name,
xEventData.value('(/event/data[@action_name=''Client_Host_Name'']/value)[1]','varchar') Client_Host_Name
FROM xEvents
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 64, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 21, @on
[code]...
I am using this code for LongRunning Queries.
CREATE EVENT SESSION LongRunningQuery
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
WHERE sqlserver.sql_statement_completed.duration > 60000
[Code] ...
Here Instead of writing to XML file how can send an EMAIL if a query runs more than 1 minute in my server ?
SQL Server 2014 BI
Windows 2008 R2
Every time machine is rebooted performance counters are missing.
select * from sys.dm_os_performance_counters returns no records. I execute "lodctr" command and get it back every time.
I would like to know the route cause of this issue, why it gets removed every time the machine is rebooted.
If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)”
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.
I can only do one match at a time -- Like can only do either the sql_statement_(start and end), or sp_statement_(start or end). Is there any way to capture both in the same session? Or since I am adding both the events in the ADD EVENT section, can I query it somehow to get unmatched SP or SQL?
Code:
USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT *
FROM sys.server_event_sessions
WHERE name='TimedOutSQL')
[code]...
I am wanting to get/filter on all queries and procs that take longer than 2 seconds to run (I'll balance real values later) but I'm not sure which Action out of the XE that I need.
I am using SQL Server 2014 and thought I had used sqlserver.sql_statement_completed.duration > 2000 in a previous version.
I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.
View 4 Replies View RelatedI need to find any stored procedures that have not been used over a certain time period.
I have set up an extended events session to gather sp_statement_starting and _completed.
The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.
Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?
I have created a session in extended events and want to frequently monitor the events that i have filtered .The problem i am facing is i am not able to clear the previous data as we do it in SQL trace. i am able to see all the data that is occupied till now.whenever i start the test i want the current data only to be displayed.The work around is either delete the session and create a new one every-time which i do not want to do .I am able to see the CLEAR DATA FROM TABLE button but it is in Disabled state all the time.
View 2 Replies View RelatedI am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?
I've got the files configured to be 20MB before rolling over, the XE is running all the time.
So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?
I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.
We know we can use the event lock_deadlock and xml_deadlock_report to capture the deadlock info, however I also want to capture the execution plans for all of the SPIDs in the deadlock graph, how to output the execution plans to the extended events trace results either ? such as if there is an action for execution plan or workaround for it ?If there is no built in action for execution plan , may I know if we can add the customized info to the extended events results file also ? Such as when the deadlock related event happens , then we can run a query to get some info ,then added the info along with other info such as sql_text, dbname etc to the events trace results file either ? The reason is if we also know the execution plans when the deadlock happens, it is useful to turning the query based on the execution plans to reduce deadlock happening .
View 5 Replies View RelatedI am running MSSQL v7.0 with Service Pack 2. I just realized that I have no sql server performance counters on this machine. Does anyone know how to install these performance counters without reinstalling the SQL Server software?
View 1 Replies View RelatedIn the Performance object drop down list of Perfmon.
I don't see the below counter
SQLServer:AccessMethods
SQLServer:BufferManager
SQLServer:Databases
SQLServer:GeneralStatistics
SQLServer:Latches
SQLServer:Locks
SQLServer:MemeoryManager
SQLServer:SQLStatistics.
How to enable these?
Thx
Venu
Hi all,
I have sql 2000 enterprise. From performance monitor, the sql counters are missing. During installation I've made sure that the sqlcounters are installed. The sqlctr80.dll is installed. But still the counters are not showing.
Has anybody come across this, not sure what to do next
On 32 bit SSIS installations, both of the following performance counter objects are visible in perfmon.
SQLServer:SSIS Service
SQLServer:SSIS Pipeline
On 64 bit SSIS installations, only the following is available.
SQLServer:SSIS Service
The SQlServer:SSIS Pipeline counters are nowhere to be found.
Should I re-install? Is this a known issue with 64 bit SSIS?
P.S. Remote or local access administrative access with perfmon makes no difference, the "SQLServer:SSIS Pipeline" performance counters don't appear in the listbox when connecting to Windows 2003 x64 server.