Finding "bad" Queries Using SQL Trace ??

May 1, 2000

I am optimising a application and need to find the queries which are taking lot of time to execute and which are scanning through many records (probably because indexing not proper).

I think that the tool to do this is SQL Trace. I created a trace but don't know how to interpret the data or what to look for.

Can someone please share their experience as to what info/data can one look at to find these "bad" queries.

Thanks!
- Manoj

View 2 Replies


ADVERTISEMENT

Trace Logs And Finding Error Source

Apr 19, 2006

Hi All! Looking for some help with SQL errors.
We are using SQL 2005. Our server has been failing (service just stops responding) several times a week. We don't have a DB admin, just me, the developer, so please be patient.
I can view all the errors in the existing trace through the Profiler, but not sure how to follow error to query source.
How do you use the trace logs to find the problem query?
I have two significant errors in my logs. One is Hash Warning and the other is Missing Join Operand. I'm not interested in the general causes of these types of errors, that info has been easy to find.
I don't know how to link or otherwise associate a particular instance of an error in the trace logs to the query that caused it to determine the query that is causing the trouble.
The traces are already on the server. I've never created a new trace. I believe they are generated automatically by the server...
Can someone shed some light on how these existing traces are being generated and how I can modify them to include the data I need? When I open an existing trace on the server and view the properties, events tab, there appears to be no way to select the column "TextData" (no checkboxes) for the two errors we are seeing (listed above).
If the TextData column for the errors will allow me to see the offending query, can someone please tell me how to enable that column in my traces that are currently being generated on the server?
Thank you!
 
Drew

View 4 Replies View Related

SQL 2012 :: How To Trace For Parallel Queries

Nov 6, 2015

what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.

MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.

I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in URL.... The BinaryData column says :

0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.- What does "parallel plan running in serial" mean ?

I see a lot of 0x01000000, and a few 0x08000000's in my trace.How can i determine whether one query is hogging CPUs and if reducing it to 4 will work?

View 4 Replies View Related

SQL 2012 :: Profiler - Trace Direct Queries Only?

Oct 21, 2015

Is there a way to setup a trace to show only direct TSQL statements triggered on my server? note I don't want to capture Procedure calls or the statements called within the procs.

Actually many people are firing direct SQL statements on server. And some are coming from entity framework as well. I just want to capture those.

View 1 Replies View Related

How Do I Trace In Sql 7?

Jan 5, 2000

I couldn't find a "sql trace" utility like in sql 6.5... Does anybody know how/if I can do this?

View 2 Replies View Related

SQL Trace

Dec 27, 2003

if i dont use the trace to record all the sql transaction. and someone execute the delete command to delete one of the table (ofcourse the person have high enough permissions to do that). is there anyway i can find out who that user's ID is to run the deleted ?? Thanks:D :p

View 2 Replies View Related

How To Trace SQL

Jun 21, 2004

Hi,

I'm trying to debug a vendor package and would like to turn on JDBC trace (either client or server side). The only information I found is DBCC TRACE which seems not very useful (I even don't know where the trace result located). Any help is appreciated!

Env: Windows Server 2003 & SQL Server 2000 8.00.818.


Jeff Li

View 5 Replies View Related

How To Trace MS-SQL ....

Feb 21, 2007

Guys,


How to trace/find out some one has dropped a database from my QA environment? Unfortunately we havent enabled the trace on this server.
We havent find any useful information from SQL Server logs also.

Can any one reply me how to find the details of who dropped the DB, when? is there any query/SP/command/tools avaialble?

Regards
BSR

With Regards
BSR

View 5 Replies View Related

Trace

May 17, 2007

hi all..
i have 2 tbl..

-- Prepare sample data
DECLARE@tbl1 TABLE (box varchar(10), loc varchar(5) )

INSERT@tbl1
SELECT'P1', 'aa' UNION ALL
SELECT'P1', 'bb' UNION ALL
SELECT'P1', 'aa' UNION ALL
SELECT'P3', 'cc'

DECLARE@tbl2 TABLE (box varchar(10), loc varchar(5) )

INSERT@tbl2
SELECT'P1', 'aa' UNION ALL
SELECT'P3', 'cc'

--expected result
SELECT 'P1' as box, 'aa' as Location, 'aa' as HeaderLoc UNION ALL
SELECT 'P1' as box, 'bb' as Location, 'aa' as HeaderLoc
how do i trace from @tbl1, that has 1 Box with more than 1 Location?
in this case P1 has 2 distinct location (aa & bb).. and left join in the loc from @tbl2 just to retrieve the loc for that box..

~~~Focus on problem, not solution ¯(º_o)/¯ ~~~

View 6 Replies View Related

Trace SQL

Jul 23, 2007

How do you set up the trace in SQL?

thanks


sorry, I am an Oracle guy trying to learn SQL

View 1 Replies View Related

Help With Trace

Jul 20, 2005

Hello:I am working with SQL Server 2000. I have a stored procedure that creates 3temporary tables (#temp1, #temp2, #temp3). When I view the trace of thestored procedure I see entries that have Object Created #temp1, ObjectCreated #temp2, Object Created #temp3. How can I fix the trace so it doesnot report any objects that get created in the tempdb.Any help will be appreciated.Alee

View 1 Replies View Related

ASP.Net Trace File

Jul 2, 2004

When we enable tracing for a web application where exactly the file or content of the file "Trace.axd" saves?

View 4 Replies View Related

Trace Flag Help

Apr 2, 2001

Hi,
I'm trying to use trace flag 1204 to get some detailed deadlock information
In EM, I add startup parameter -T1204, and then I stop and start the server.
I run two jobs that I have setup to deadlock, and they do. But no information about the deadlock is in my sql error log? anyone know what I am doing wrong? thanks.

View 1 Replies View Related

Trace Flags

May 1, 2001

Hi ,
I want to trace the deadlock information. I am enabling trace flags 1204 ,1205. Is there any difference in setting these trace flags by using DBCC
TRACEON and by setting them at command prompt by statrting the SQL server with SQLSERVR command. Actually i don't want to bring the server down. I want the information to be logged to Error log. Any help is greatly appreciated.

View 1 Replies View Related

Table Trace

May 29, 2001

Hi There,
Is there any way to identify how many times a table is accessed by different users in a day from a database.

Thanks,
Madhu.

View 3 Replies View Related

Trace On Deadlocking

Jun 21, 2001

Hi all,

I was wondering how much of an overhead would Running this
trace (1204) have over the system. Will my perfomance
degrade significantly. If yes, by what percentage ???

The trace is 1204 to keep a watch on DeadLocking

I am running this command
C:mssql7innsqlservr -T1204 /dc:mssql7datamaster.mdf

How much performance degrade are we talking about here ?
The Application is Peoplsoft and the db sizes is about 10
Gb !!


Regards

Girish

View 2 Replies View Related

Trace Out The USER

Jun 27, 2000

SUPPOSE A THIRD PERSON WHO (MODIFIES THE STRUCTURE OF A TABLE) OR (DOING SOME MODIFICATIONS ON A CERTAIN COLUMN), HOW THE SQL SERVER DBA WILL FIND WHO HAS DONE THIS. WHETHER THESE MODIFICATIONS WILL BE STORED IN SYSTEMS TABLE?. THIS IS MY QUESTION. CAN ANYBODY GIVE SOLUTION TO THIS PLEASE?. THANKS ---- Srinivasan.

View 2 Replies View Related

Can I Tell What Trace Flags Are On?

May 22, 2000

If I issue a DBCC TRACEON (1205) from a query window from a client machine.. Does it continue to run when I close that connection (spid).

Is there a way for me to tell what traceflags are currently on?

Thanks,

Dano

View 1 Replies View Related

Creating A Trace

Mar 1, 2001

Hello,
I have to create a trace to monitor how the stored proc.are performing.
The sp are for update/select/insert.
What all counters should I monitor?
The sp are called in from Java applications thru weblogic server and some of them are taking as long as 30 secs to fetch 80 records!
Any help/thoughts appreciated.

View 1 Replies View Related

Using Trace Flags

Sep 6, 2000

To solve a problem I encountered with Restoring from Backups in 6.5, I had to install a hotfix and thereafter do the load using Trace Flag 3282.I need help on using the trace flag (syntax etc.) Also there is no mention in books online of this particular Trace Flag.
Please help.

View 1 Replies View Related

DBCC TRACE

Apr 13, 1999

One of my SQL servers (v6.5 with SP5a) has begun to display 'DBCC TRACEON 208' in the Error Log. I have not been able to find any reference to the Trace Flag - 208. Can someone please help me?

View 2 Replies View Related

Performance Trace-off

Jun 4, 1999

Hi All,

I've just joined a project for improving performance issue on MS-SqlServer(6.5).
I was DBA Sybase a couple years ago I am unsure that MSSQL works in a manner as
Sybase. I want to change some configuration options (sp_compile) and modify
the table and indexes definition for improving the performance and avoid having
deadlocks.

Is true that :
When field, which has a default value assigned to NULL, is equal
at a variable-length field and when you execute an update on it you will
get 2 transactions : first a delete and then insert ?
(On Sybase there is the concept of Index Covering to avoid that overhead
what about Ms-Sqlserver 6.5).

To avoid having deadlock I want to use the FILLFACTOR I think this option
is usefull only on cluster indexes, that's right ?

For huge tables the order of fields is important :
first position put the primary key field
after put the FK, secondary key, short type field (tinyint,int,datetime...)
go on with the alpha fields (char) NOT NULL
at last the NULL fields
Is that design efficient for Ms-SQLserver ?

Many thanks,
Herve

View 1 Replies View Related

Trace Executed SQL

Jun 29, 2004

Hi folks

Is there any (easy!) way in which I can see the SQL that has been executed. I'm using stored procs that create & execute other stored procs(via sp_executesql). At the early stages there are often trivial errors in the created procs that cause rather general exception messages that do not give much of a clue as to where the error is. (It's tedious to find these in the debugger) ... and was wondering if there's any trace output type option I can turn on to see what sql has been presented for execution.

Thanks.

View 1 Replies View Related

SQL Trace Question

Jan 10, 2005

I ran a trace on my SQL Server and found that a single SELECT statement showed up the same number of times as the number of id numbers I was selecting on. In the hopes of optimizing my program, it ran worse :(

Instead of having:

SELECT * FROM table_name WHERE id='1'
SELECT * FROM table_name WHERE id='2'
SELECT * FROM table_name WHERE id='3'
...
SELECT * FROM table_name WHERE id='250'

for all the rows i need data from (sometimes up to 250 rows). I changed the select statement to:

SELECT * FROM table_name WHERE id='1' OR id='2' OR id='3' .. id='250'

When i run a SQL Trace if i have 250 id='num's in my SELECT, the same query shows up 250 times in the trace with high CPU and READS. Can Anyone explain and help me get the optimization Im looking for?

View 4 Replies View Related

Trace Info

Feb 19, 2004

Folks, I've inherited a SQL 2K server that has a trace running. I can see the trace file being generated(.trc), I can also see the standard templates(.tdf). But BOL is not helpful in finding information about the
trace when you have no information. I just want to shut the damn thing off.

I can open the trc file in SQL Profiler but I am not proficient in the tool's use. I also don't see a trace table anywere in the database.

If anyone can be of any help it would be much appreciated. Thanks.

View 7 Replies View Related

Trace Out Table Name

Apr 20, 2008

Hi,

I need to find the tables in which a particular column exists in the database. Please provide me an appropriate query.

Thanks in advance.......

View 1 Replies View Related

How To Trace The Table?

Apr 23, 2008

Hi,
I using SQL SERVER2000,i can see in one table.. often some data is duplicating.

I couldn,t find,how these data are populated.

Even in audit..i am unable to trace the action.

Any idea,how to find the root cause??

Thanks.

View 2 Replies View Related

How Can Trace SQL Server

Jun 9, 2008

How can trace SQL server without SQL profiler?

View 5 Replies View Related

SQL Trace Script

Jun 17, 2008

I have written a script to run profiler trace as far as I can. Guide me if i can add some more so i don't cause performance hog and which can be scheduled as well.

Here is the script:

--Stored Procedure to run trace for 30 minutes

Create Procedure dbo.Usp_Tracefor30minutes
as
set nocount on

declare @stop datetime
set @stop = dateadd(mi,30,getdate())
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
-- create traces and saves in folder specified
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @stop
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
-- 17 - Existing Connections
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
-- 10 - RPC:Completed
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
-- 43 - SP:Completed
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
-- 37 - SP:Recompile
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
-- 12 - SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
-- 13 SQL :BatchStarting
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler'
set @bigintfilter = 5000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

--Exec SP
Exec dbo.Usp_Tracefor30minutes

View 1 Replies View Related

How To Trace Sql Requests

Apr 5, 2006

Folks,

I have been hunting for a way to trace sql requests going to/from the sql express server (from VS.2005 using asp.net). I.E., I want to see the raw SQL commands that asp.net is generating.

Can anyone point me to a tool or article? Thanks,

--stuart

View 1 Replies View Related

Privileges To Run Trace

Oct 1, 2007

Is there any way I can give a user read only access to the database, yet have privilges to run a trace.

Any suggestions and inputs would help

Thanks

View 1 Replies View Related

Many Big Trace Files

Dec 6, 2007

Experts, I've run a profiler trace on a production 2k5 server for two days, resulting in more than 4GB of 50MB rollover files. Profiler was run with default settings plus some columns and a filter for just one database by name.

I want to run Tuning Advisor over this huge log, but it seems that this tool can take one file at a time, but I need only one report instead of more than 80 with different suggestions.

I'm trying to discard event records that aren't "RPC:Completed", "SQL:BatchCompleted" and "SP:StmtCompleted", in order to generate only one trc file that sumarizes all my capture.

Is there a way to do that? Any tool available?

I was thinking about to use the "fn_trace_gettable" method, but I don't want to run the Advisor over a database. BTW, is it possible to filter during the "select into" or "insert" with a "where"? If so, what should I filter?

I can write a file manipulation tool for this task if I know the trc file format, but I didn't find that info. Any hint?

Thanks a lot...

++Vitoco

View 15 Replies View Related

Trace @@Packet_Errors

Jun 5, 2007

I have a SQL 2000 enterprise edition single instance server on windows 2003 server, my server has been up for 53 days and I have 197 packet errors. Besides network card, cable and switch port what else could cause this? Is there a way to trace back to the source?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved