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 thanexample 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 alotMike 123Edited 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
|
HiA 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 myLoggingTableOrder By duration descYou 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.ORYou 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 bitDamian |
|
|
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? Thanksmike123 |
|
|
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 |
|
|
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} |
|
|
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... |
|
|
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 |
|
|
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} |
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-08-31 : 18:59:02
|
quote: Originally posted by Merkin Select top 20 * from myLoggingTableOrder 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 |
|
|
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,3700187Do you guys think it is too much?Thanksmk_garg |
|
|
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, TextDataFROM TraceTableNameWHERE TextData IS NOT NULL AND Duration > 5000ORDER BY Duration DESCYou'll probably want to start another thread on this. Post your questions in the Admin forum.Tara |
|
|
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 |
|
|
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 TraceTableNameGROUP BY TextDataThe 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 |
|
|
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 */ |
|
|
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.Thanksmk_garg |
|
|
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 |
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-31 : 22:42:43
|
TextData was NULL??? What event does it have listed?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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 |
|
|
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 |
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-09-01 : 18:12:51
|
Allright Thanks!mk_garg |
|
|
Next Page
|