SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Where to start on tuning sql server
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 03/06/2003 :  03:41:32  Show Profile
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!

Australia
4970 Posts

Posted - 03/06/2003 :  04:46:05  Show Profile  Visit Merkin's Homepage
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
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 03/06/2003 :  19:36:54  Show Profile

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!

Australia
4970 Posts

Posted - 03/06/2003 :  19:52:55  Show Profile  Visit Merkin's Homepage
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 03/07/2003 :  07:24:00  Show Profile
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

USA
15663 Posts

Posted - 03/07/2003 :  07:29:23  Show Profile  Visit robvolk's Homepage
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!

Australia
4970 Posts

Posted - 03/07/2003 :  07:29:29  Show Profile  Visit Merkin's Homepage
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 03/07/2003 :  07:45:03  Show Profile
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

Australia
343 Posts

Posted - 08/31/2004 :  18:59:02  Show Profile
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

Australia
343 Posts

Posted - 08/31/2004 :  19:45:44  Show Profile
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

USA
36845 Posts

Posted - 08/31/2004 :  19:48:35  Show Profile  Visit tkizer's Homepage
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

Edited by - tkizer on 08/31/2004 19:49:10
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

Australia
343 Posts

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


mk_garg
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 08/31/2004 :  20:17:04  Show Profile  Visit tkizer's Homepage
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

Edited by - tkizer on 08/31/2004 20:18:06
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

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


rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Edited by - rockmoose on 08/31/2004 20:28:19
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

Australia
343 Posts

Posted - 08/31/2004 :  20:42:20  Show Profile
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

USA
36845 Posts

Posted - 08/31/2004 :  20:45:31  Show Profile  Visit tkizer's Homepage
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

Australia
343 Posts

Posted - 08/31/2004 :  20:48:56  Show Profile
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

USA
4184 Posts

Posted - 08/31/2004 :  22:42:43  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
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

Australia
343 Posts

Posted - 08/31/2004 :  23:41:55  Show Profile
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

USA
36845 Posts

Posted - 09/01/2004 :  11:58:44  Show Profile  Visit tkizer's Homepage
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

Australia
343 Posts

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

mk_garg
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000