Tuning Queries in Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Tuning Queries in Database

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 20:45:45

These are durations taken by my queries

2872970
1261606
1259813
1259436
1255454
1244063
1242390
1241750
1175720
1175703
878266
854063
833220
832704
798407
752826
712936
711623
666047
666047
665827
665407
628780
624843

I can see sql query that took this much time.
How i can find where is this query in my database.

Previous discussion on this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24317

Thanks

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 20:51:46

I would add WHERE TextData IS NOT NULL to your query. Here is what I usually run:

SELECT TOP 100 Duration, TextData
FROM TraceTableName
WHERE TextData IS NOT NULL AND Duration > 5000
ORDER BY Duration DESC

What kind of durations do you see with this?

I typically only get about 5 rows back when I run the above query and that's using a trace that ran for several hours. The query will show you the top 100 worst performing queries that took over 5 seconds.

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 20:54:09

I am running SQL Server 7.0 and client tools for sql server 2000.
Just checked in BOL these durations are in milliseconds.

mk_garg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 21:00:02

I have access application.
Data is stored in sql server 7.0.

After running your query

max duration :2872970 with query

("SELECT ""dbo"".""Job"".""JobNumber"" ,""dbo"".""Job"".""JobName"" ,""dbo"".""Job"".""ClientID"" ,""dbo"".""Client"".""ClientName"" FROM ""dbo"".""Company"",{oj ""dbo"".""Job"" LEFT OUTER JOIN ""dbo"".""Client"" ON (""dbo"".""Job"".""ClientID"" = ""dbo"".""Client"".""ClientID"" ) } WHERE (""dbo"".""Job"".""ClientID"" = ""dbo"".""Company"".""CiID"" ) ORDER BY ""dbo"".""Client"".""ClientName"" ,""dbo"".""Job"".""JobNumber"" ")

min duration :17593 with query

("SELECT ""dbo"".""Job"".""JobNumber"" ,""dbo"".""Job"".""JobName"" ,""dbo"".""Job"".""ClientID"" ,""dbo"".""Client"".""ClientName"" FROM ""dbo"".""Company"",{oj ""dbo"".""Job"" LEFT OUTER JOIN ""dbo"".""Client"" ON (""dbo"".""Job"".""ClientID"" = ""dbo"".""Client"".""ClientID"" ) } WHERE (""dbo"".""Job"".""ClientID"" = ""dbo"".""Company"".""CiID"" ) ORDER BY ""dbo"".""Job"".""ClientID"" ,""dbo"".""Job"".""JobNumber"" ")

How i can check where from these queries are executed in application.

Thanks

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 21:03:09

So you have some queries that are taking between 17 and 2900 seconds.

I don't understand your question.

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 21:06:11

i mean, i want to see where these queries are in my application or in db.

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 21:07:16

I suppose you would have to open the application and search for them.

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 21:09:40

quote:
Originally posted by tduggan

So you have some queries that are taking between 17 and 2900 seconds.
Tara

yes it is taking that long. I dont have very big db.
I inherited it. It is not designed very well.
But i am trying to improve it.

Thanks

mk_garg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 21:56:20

I generated sql script for all stored procedures and views.
Could not find above sql any where.
I tried to find in access application as well. no luck.
any other way to find it.
Thanks

mk_garg

jhermiz

3564 Posts

Posted - 2004-08-31 : 23:03:43

quote:
Originally posted by mk_garg20

I generated sql script for all stored procedures and views.
Could not find above sql any where.
I tried to find in access application as well. no luck.
any other way to find it.
Thanks

mk_garg

Hmm I'm not understanding this...you're running these queries but you don't even know whether they exist or not? Is it an access front end? Are these queries inside the front end hard coded

.
Can't you "search" for them ?

Jon
www.web-impulse.com

Can you dig it: http://www.thecenturoncompany.com/jhermiz/blog/

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 23:47:52

Yes, Access is front end application.
I searched in a application. i could not find anything.

Actually in start all the forms & reports were developed in access. But data increased quickly. Moved database to SQL server 7.0. All tables in access are linked to SQL Server using ODBC.

May be this can give you hint about the environment.

i am suspicious about text {oj in above shown quries.

Thanks

mk_garg

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-01 : 04:59:11

Did you include application name under Data Columns?

You can't really know, where exactly in your application those queries were executed but by knowing the name of the application, you can make a string search of the entire code using the text data column.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 18:15:32

i dont think so.
Application name is Microsoft Access. Any way i have only application.
Thanks

mk_garg

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-01 : 19:21:16

How was your DB created? Did someone 'upsize' the Access App to use a SQL Backend? If so, I suspect tables were migrated to SQL Server but the queries remain Access queries. This causes joins to be done on the client. This means huge amounts of data have to travel to the desktop where joins are performed. This will often result in an application that performs worse than it did when it was a standalone Access database.

You need to examine the Access queries and convert them to SQL Views where possible so they can be joined on the server. I suspect your query times have less to do with CPU or server resource utilization and more to do with network and client bandwidth limitations. Most time is probably spent waiting on NETWORK_IO.

MHO

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 19:46:27

Yes my application was developed in access and later moved all data table to sql server.

I will access queries as well.
Thanks

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources