Using Profiler Traces To Troubleshoot
Aug 14, 2007
Hey guys,
I need some help knowing what to look for in Profiler to troubleshoot an issue.
I've got an application that accesses a SQL Server database that has suddenly started timing out when users launch and attempt to log in, and I'm trying to find out where and why the application might be timing out (whether it's a server issue, a stored procedure or SQL query from the application that could be optimized, a table that could be truncated or archived, etc.). All I have to work with from troubleshooting the database side are a series of trace files from Profiler that were run for a total of about 5 minutes while the application was launched and then timed out. Of course, there are a whole lot of statements being issued, hundreds of tables being accessed, lots of stored procedures and even more ad-hoc queries coming straight from application source code.
So my question is, what do I need to look for in these trace files that might be a red flag to an issue? I'm no DBA, but I know that really long durations might be a tip-off. I'm only seeing these on the occasional Event:Audit Logout (which I read in another thread could potentially be very normal). Anything else that I might want to filter for?
I appreciate any help!
Mandy
View 2 Replies
ADVERTISEMENT
Jun 12, 2000
Can you run 6.5 traces through the SQL 7.0 profiler. If so, I am getting a login error. I do have the same db name, login, and perms.
Andy
View 1 Replies
View Related
Jul 20, 2007
Hi
I have a procedure in a history database that does insert into 3 tables inside a transaction. users complaint that the proc sometimes takes too long during heavy usage. I did some traces to see what is taking up the time, I found that the rpc duration was averaging > 500 ms (should only take 50ms). I checked to see if one of that statements were taking too much time, but only see the commit transaction statement taking around 500 ms). I check the avg disk queue to be around 30. ( this is on a single local disk) .
So is this definitely a disk issue, or is there something else I need to check
thanks
P
View 1 Replies
View Related
Sep 26, 2005
I am debating whether to go to all the trouble of setting up on-demand Profiler traces on some test servers for the developers here. Really just tracing RPC:Completed and SQL:BatchCompleted, so the developers can at least try to catch a performance problem before going to production. The question I have, though, is just how useful is this sort of information to mid- to low-level (i.e. experience) developers. One of the bigger concerns is over Java applications, which like to hide their queries behind a lot of "sp_cursorfetch" calls.
My question to the forum is if you are a developer, have you ever dreamed of having this sort of information available? How useful is it?
I am going to try to post a poll along with this, but I am not sure it will work..
View 3 Replies
View Related
Aug 25, 2006
Hi There
I have been profiler tracing my service broker setup while testing.
Now i have a strange scenario.
I get the following error on Server1 every 65 seconds:
An error occurred while receiving data: '64(The specified network name is no longer available.)'.
Now i am familiar with this error, it normally has to do with my routing.
BUT
Every 10 minutes or so i get the following errors:
An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.
10053(An established connection was aborted by the software in your host machine.)
An error occurred while receiving data: '10053(An established connection was aborted by the software in your host machine.)
Now i have no ieda where this is coming from, the queue, sys.transmission queue and sys.endversation_endoints are clear, if i trace the initiator and forwarder for this target i get nothing, so basically i have no idea what is causing these errors? I cannot find any active messages or conversations from either side?
I cannot find out what remote host the errors are refering to? I am not sure what to trace for to get more detailed infromation.
Sql Server Log has nothing.
When i do test communications between target and initaitor both ways every seems fine, the messages are recieved, conversations are ended properly the queues, sys_transmission and conversation endpoints all clear 100%.So my routing etc all is 100%.
But these errors keep coming up on server 1. On profiler these errors happen under Broker:connection event, but there is no other info like hostname etc.
This is really irritating because i am profiling testing and i dont know when and error is from what i am testing or this ghost error that keeps happening constantly.
My normal investigation with profiler and sys_transmission_queue and conversation_endpoints gets my no information.
I am stumped.How can i track down the problem?
Thank You
View 4 Replies
View Related
Jan 3, 2006
I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:
Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2
Could not find Type 'DINEServiceProc' in assembly 'DINEService'
Here is the VB code to create the class:
<code>
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class DINEServiceProc
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)
Dim conn As SqlConnection
'Create an in-process connection to the instance of SQL Server
conn = New SqlConnection("Context Connection=True")
Dim DINEService As New DINEService
Try
conn.Open()
DINEService.ProcessStartRequest(iMsg, iMsgType)
Catch sqe As SqlException
'Console.WriteLine(sqe.Message)
Return
Finally
conn.Close()
End Try
End Sub
End Class
</code>
And here is the code to create the assembly and the procedure:
<code>
USE [ServiceBrokerTest]
GO
/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/
CREATE ASSEMBLY [DINEServiceProc]
AUTHORIZATION [dbo]
FROM 'D:EHITServiceBrokerDINEServiceDINEServiceinDebugDINEService.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE dbo.DINEServiceProc
(
@msg nvarchar(MAX),
@msgType nvarchar(MAX)
)
AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;
</code>
What am I doing wrong here?
View 11 Replies
View Related
Jul 25, 2006
Hi There
Ok i have done the following on my target instance i have created an endpoint, xml schema, message types, contact, activation sp , queue and service.
On the initiator i have created an endpoint, a route to the target service, same xml schema, same message types, same contact, queue and service.
When i try test this by doing the following:
SET @msg = CAST(@body AS XML)
BEGIN DIALOG CONVERSATION @dialogHandle
FROM SERVICE
[http://ewx.co.za/DemoInitiatorService]
TO SERVICE
'http://ewx.co.za/DemoService'
ON CONTRACT
[http://ewx.co.za/DemoContract];
SEND ON CONVERSATION @dialogHandle
MESSAGE TYPE [http://ewx.co.za/DemoMessage]
(@msg);
END CONVERSATION @dialogHandle;
END
================================================================================
Nothing happens and i dont know where to troubleshoot, if i check both queues they are empty ? I have altered the queues so that retention is ON.
I have checked the sql log of both instances and nothing.
When i execute the above sql it executes succesfully, so basically i am stuck i can execute the dialog sql, and set the activation on the target queue to OFF , so if i message gets there it should stay in the queue, but both que's are empty and no errors ? Not sure where to even start checking the issue ? For all i know the contract could be incorrect or port 4022 blocked or the endpoints not working, but i dont know where to find these errors other than the sql log and it has no errors ?
PLEASE HELP.
Thanx
View 7 Replies
View Related
Aug 21, 2006
Any suggestions on how I can monitor the following without using traces? I am a dba/developer working as a developer on a contract, and I'm supposed to be tuning. However, I can't run traces. I've got my own procs that monitor locking, etc. But I would like to get at least i/o and cpu throughout the day. It would also be nice to get the query executed. Basically, the type of stuff you'd normally use traces for.
I know about @@cpu, @@io etc., but these are basically useless (no?) since they only record since the server was started. There is a stored proc but it only monitors these things since the last time it was run.
Does anyone know how I could utilize the above? I tried to write a script but I couldn't get it to work. :(
I realize that in general this is a ridiculous request, but I thought I would ask anyway.
View 4 Replies
View Related
Jan 31, 2002
This one has stumped me. Hopefully somebody can help.
A while ago, I setup a trace that posted the log to the desktop. I needed to stop the trace this morning, so I went into the profiler and deleted the traces. There was a private and shared trace. Now every time I start up something that has to do with sql server, the log pops up on the desktop. I'm not sure why the trace wasn't deleted or stopped. The trace includes what program accessed sql. Whether it is EM or Query analyzer or ISQL. It gets posted in the log. Any suggestions? I need to remove this because the log fills up the drive and causes the server to crash.
Thanks
View 1 Replies
View Related
Mar 20, 2008
I want to use ShowPlanXML in my traces. Information witch is given by this event is quite interesting but I dunno how to bound it with other events. For example I have SqlBatchComplete event and want to get XML plan of it but I have no idea how to do it.
View 3 Replies
View Related
Nov 29, 2011
When I restart the server that hosts the database engine SQL Server 2005 Standard Edition SP4, the trace gets up mytrace-5.trc with a size of 100 MB and id is 2, leaving the hard disk space, then the SQL Server stops execution of the trace due to lack of space. I do not know how to erase the trace, because I do not know where is it.
The archive log looks like : SQL Trace ID 2 was started by login "sa"
Trace ID '2' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.
I only know how delete a trace, knowing id.
EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus 2, 2
Which configuration file I can disable all SQL Server traces. If I run select * from sys.traces, I just get the default trace:
id = 1
status = 1
path= D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGlog_32736.trc
max_size = 20
stop_time = NULL
max_files = 5
is_rowset = 0
is_rollover = 1
is_shutdown = 0
[code]....
View 4 Replies
View Related
Jul 18, 2007
I have a simple parent package that calls 3 small simple child packages (child1, child2, and child3). Each child package does nothing but contains a FlatFile connection. I run these 3 child packages in parallel inside parent package. The parent package fails sometimes (i.e. sometimes success, but sometimes failed). And the failure is replicable.
I would like to send you all of 4 simplest packages via email if anyone is interested in helping out.
Here is the error info
Code Snippet
SSIS package "parent.dtsx" starting.
Executing ExecutePackageTask: C:SSISDasLoaderchlid3.dtsx
Executing ExecutePackageTask: C:SSISDasLoaderchld2.dtsx
Executing ExecutePackageTask: C:SSISDasLoaderchild1.dtsx
Error: 0xC0014005 at : The connection type "FLATFILE" specified for connection manager "_MedicalClaimServiceLine_070713_161742152820" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
Error: 0xC0010018 at : Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">_MedicalClaimServiceLine_070713_161742152820</DTS:Property><DTS:Property DTS:Name="DTSID">{CA12C" from node "DTS:ConnectionManager".
Error: 0xC00220DE at child2: Error 0xC0010014 while loading package file "C:SSISDasLoaderchld2.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.
Task failed: child2
Warning: 0x80019002 at parent: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Information: 0x40016042 at chlid3: The package is attempting to configure from the parent variable "z".
Information: 0x40016042 at child1: The package is attempting to configure from the parent variable "x".
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
View 5 Replies
View Related
Apr 18, 2008
I have 2 Excel sheets ( Sheet1 and Summary) in an excel output file.
Sheet1 is created and loaded with data fine.
Summary sheet is getting the following error:
Error: 0xC0202009 at Write Counts and Percentages to Summary Sheet, Excel Destination [337]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error: 0xC02020E8 at Write Counts and Percentages to Summary Sheet, Excel Destination [337]: Opening a rowset for "Summary" failed. Check that the object exists in the database.
I do have an execute SQL task to create the summary sheet before the data flow task.
The execute SQL task has
CREATE TABLE `Summary` (
`Counts_and_Percentages` LongText
)
Please advise on what I can do to troubleshoot/correct the error. Thanks
More details on the error
DTS.Pipeline] Error: "component "Excel Destination" (337)" failed validation and returned validation status "VS_ISBROKEN".
My Excel file name is an expression
@[User::FullFilePath] + (DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) + "-" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) + "-" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + " " +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) + " CLIENT=" +
@[User::ACCOUNT_NAME] + " output.xls"
View 4 Replies
View Related
May 27, 2015
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]...
View 6 Replies
View Related
Mar 20, 2008
Hello frnds Can Anybody explai what does it mean by question itself and how to resole it ?
View 2 Replies
View Related
Apr 26, 2001
Can only member in the System Administrators role use the SQL Profiler’s? Is there any other role or way to allow user to use this tool other then adding them to this role? Thanks you
View 1 Replies
View Related
Dec 23, 1999
Does anybody have any idea if there is a bug in SQL 7.0 where running multiple traces causes the SQL 7.0 to crash ??
This is running in a clustered environment.
Thanks in advance.
Ajay
View 2 Replies
View Related
Mar 10, 1999
I've looked high and low for information regarding this problem to no avail. Profiler works fine from the local server where SQL7 is running,
(using NT security). But even though my client is multiprotocol, I cannot connect from my desktop using SQL security (ODBC error) or NT
authentication. NT security gives me ConnectionOpenRcpBindingSetAuthInfo(). I do have a valid id on the NT machine as well and am not
getting any errors in the security events log.
Can/How do you connect remotely to the profiler using TCP/IP or Mulitprotocol client?
Why doesn't NT authentication work?
Thanks in advance for any help.
View 1 Replies
View Related
Aug 1, 2003
Hi Everybody,
Can anyone tell me what is a profiler in SQL server 7.0? What I exactly want to know is, in which practical situation u will find the profiler useful. Explaination with example will be appreciated.
Regards,
Samir.
View 3 Replies
View Related
Jul 7, 2004
How do I write a process in Profiler to categorise the performance of Stored procedures into buckets of <4 secs,>4secs,between 4 and 10 secs
View 4 Replies
View Related
Feb 28, 2007
Hello,
My qn is related to profiler.
my app calls an sp, say, sp_DisplayResults. in profiler, i can see multiple entries for this sp call eventhough it is invoked only once from the UI.
no other process/users is invoking the sp.
could any one please let me know why the profiler is displaying it multiple times.
im using MSSQL2k
View 2 Replies
View Related
Apr 28, 2008
I have a small doubt through profiler how can we find out the long running queries.specifically which events we have to capture.
View 2 Replies
View Related
Apr 30, 2008
Hi
I need to track the connections hitting to my server through application???
I m doing some work in Profiler but i m not sure from where to start it..I m using SQL SERVER 2005...I m accesing few pages on application and that i want to track that activity involving at server side
T.I.A
View 9 Replies
View Related
May 16, 2008
I have a small doubt.
where is the ideal place to run a profiler .
can we run a profiler on production server
or we have to run profiler on a seperate instance and connect to production server
View 2 Replies
View Related
Feb 28, 2006
Lets say I have a username & password , I dont want people to see it being passed on to the SQL Server.
What are the ways I can prevent the parameters being passed on .
Thanks,
Regards
Kidddddddddddddddddd
View 10 Replies
View Related
Apr 20, 2006
Any one show me the basic guide or tutorial about SQL Profiler for begginer .
Thank in advance !
View 1 Replies
View Related
Sep 12, 2006
hello friends sorry for this basic question.
i heard a word sql profiler.
i tried through BOL, but not got any good information how to start and from where to start. can anybody give me some brief about this
thank you very much
View 7 Replies
View Related
Oct 27, 2006
1/When I create a new trace, it gives me sevral events including the SQL server s own functions and provcedures. How do I tell profiler to display only the requests we send SQL server from a VB program or other company made programs instead of including sql server own events as well. That will help me filter the requests and locate the problem easily.
2/ also ,. sometimes, my text data column description gets truncated and as a result I can't see the whole text of a request.
What causes that and how can I fox it pls?
Thank you, i am not familiar with profiler.
View 3 Replies
View Related
Nov 22, 2006
Hi hi, We had a sql express version running fine untill recently.
Now it seems cause high processor use, high memory load, etc.
So the websites being dependant on SQL start performing slower.
Is there some way to monitor what is causing this.
I can't seem to read any log files, there is no profiler in express, etc. So what options do I have?
View 2 Replies
View Related
Feb 8, 2007
im trying to use the profiler to find out which tables are still being accesses as there seems to be alot of suspected tables lying around and stored objects (sps, functions etc) can the trace profiler be used in this situation?
View 12 Replies
View Related
Oct 17, 2007
When running a profiler trace for long running queries during peak usage what is a typically expected additional load the process of running it will create on that Database?
Future guru in the making.
View 4 Replies
View Related
Oct 24, 2007
Hi
Can anyone tell me what is SQL profiler?its function and all.
Thanks in Advance
RKNAIR
View 4 Replies
View Related
Feb 29, 2008
Dear All,
where can i found sql profiler in sql server 2005?
i'm using
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
