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
 Old Forums
 CLOSED - General SQL Server
 Where to start on tuning sql server

Author  Topic 

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-06 : 03:41:32
My dedicated sql server (just 1 db) is starting to slow down, I need to speed it up. I have about 150 stored procedures in my db and was wondering what the best way to find out which ones to tune first are. I dont have any sp's that are extremely long running, I just want to find out which are the most and how long they take. Basically which ones are taking the most cpu usage overall. (I am assuming this is a major concern, not sure about where hd read/writes come into this but I am assuming this is important as well) I probably need some type of overall cost if there is such a thing.

I want to have some sort of ranking, so I can pick which SPROC's in order of most costly to work thru.

example sproc: runs 0.1 seconds and run a million times a day

is more costly than

example sproc: runs 1 second and run a ten thousand times a day




I plan on tuning the queries as well as using object caching in my asp.net application to cut down the amount the queries are run. Is there a simple way to do this?

I havent done anything like this before. Any suggestions /insight are greatly appreciated as always.

Thanks alot

Mike 123







Edited by - mike123 on 03/06/2003 03:43:14

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-06 : 04:46:05
Hi

A good place to start might be the profiler :

Open up profiler.
Connect to the database.
Use the default trace template.
Specify logging to a table (one NOT in your main database)
Go to the filters tab, and filter so you only log for your main database.

Start tracing. Log an "amount" of data (whatever is a good measure of your database usage).

When it is done, you can see the results in the table you specified. You can find out from that what is taking the most time by doing a query like :


Select top 20 *
from myLoggingTable
Order By duration desc


You can get text of the slower queries from the logging table and run it in query analyzer with execution plan turned on.
That will show you what bits of your queries are not optimised and you can start looking into some indexing.

OR

You can run the index tuning wizard from Enterprise Manager, point it at the logging table and have it suggest and create some indexes.

Most likely, a combination of both of those solutions will be the best bet.

Hope that helps a bit

Damian
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-06 : 19:36:54

thanks merkin I will try that.

My main concern however is that I have queries that are run probably 2 million times per day, and others that are only run a few dozen time per day. Is there some way to figure out which queries are the most expensive factoring in how many times they are run?

Thanks
mike123

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-06 : 19:52:55
I think the index tuning wizard takes that into account (it will figure out what is most common from the trace file). But to be most effective you will have to use your own judgement and knowledge of the database.

Damian
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-07 : 07:24:00
I'm going to make one of those comments where you will probably say, "get of your soapbox" ...

But I think the real place to start tuning sql server is with the logical design and how that is mapped to the physical schema. If your database is poorly designed, adding indexes will be akin to using duct tape to patch a leaking nuke reactor.

Jay White
{0}
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-07 : 07:29:23
quote:
adding indexes will be akin to using duct tape to patch a leaking nuke reactor.
Worked in Chernobyl...

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-07 : 07:29:29
get off your soapbox Jay

Even with a well designed database, you can still have performance problems.
I've been dealing with a VERY normalised database with a client, it is running like crap because of poor index choices.
Going to deal wth that problem on Tuesday...

Damian
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-07 : 07:45:03
quote:

get off your soapbox Jay

Even with a well designed database, you can still have performance problems.
I've been dealing with a VERY normalised database with a client, it is running like crap because of poor index choices.
Going to deal wth that problem on Tuesday...

Damian



100% agree ... but if the question is "where to start" I still think my preaching has value ...

If I were givin a poor perf. query that was extracting lineage info from a 5000 level deep hierarchy ... I'd look at how the data was modeled first and then play with indexes second.

Jay White
{0}
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 18:59:02
quote:
Originally posted by Merkin



Select top 20 *
from myLoggingTable
Order By duration desc



Above query gives me SQL queries that takes maximum time.
How i can get number of times a query is executed.

mk_garg
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 19:45:44
Here are durations of my queries.

71517077,7954173,7064220,6883453,6883377,6528673,5485343,4361020,4361003,4274246,4274233,4227813,4188470,4073717,3978140,3975497,3707674,3700187

Do you guys think it is too much?
Thanks

mk_garg
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 19:48:35
Oh geez! I don't like to see any durations over 5000. 5000 means 5 seconds. You've got huge durations. And yes this is bad.

My query for the trace is usually like this:

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

You'll probably want to start another thread on this. Post your questions in the Admin forum.


Tara
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 20:08:16
Thanks Tara.
How can i get number of times a query is executing?


mk_garg
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 20:17:04
I guess you would do a GROUP BY on TextData. But you are going to have to play around with it. Something like this though:

SELECT TextData, COUNT(TextData)
FROM TraceTableName
GROUP BY TextData

The thing though with this is that TextData will include data, so you aren't going to get correct results if the stored procedure is called with different parameters. You'd have to play around with substring to get this to work.

I'd be more concerned with your durations though. Your highest duration is 71517 seconds! That's almost a whole day that a query took.

How long did your trace run for?

P.S. Start another thread on this. You'll possibly have more people contributing to it if you start your own thread in the Admin section. It's usually best not to hijack an old thread.

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-31 : 20:27:38
How much does this one cost ???
[url]http://www.xprime.com/products.html[/url]


rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 20:42:20
I will start new thread as you suggested.
quote:

I'd be more concerned with your durations though. Your highest duration is 71517 seconds! That's almost a whole day that a query took.

How long did your trace run for?



Actually i ran my trace for 1 hour only.

Thanks


mk_garg
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 20:45:31
Are you running SQL Server 2000? If so, check in SQL Server Books Online for what Duration means. In 2000, it means milliseconds, so 5000 is 5 seconds.

Tara
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 20:48:56
Just checked for that query TextData is Null and start time of that was previous day.

Started new thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39288

mk_garg
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-31 : 22:42:43
TextData was NULL??? What event does it have listed?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 23:41:55
There are many records with TextData NULL. It is from my MS-Access applicaton.
I can not figure out why it is NULL.


mk_garg
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-01 : 11:58:44
I've seen TextData NULL for logins and logouts and other non-TSQL queries.

Tara
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 18:12:51
Allright Thanks!

mk_garg
Go to Top of Page
    Next Page

- Advertisement -