February 2, 2009 at 7:15 am
Hello everyone,
I have been writing SPs for my work for a long time. I just used the basic knowledge that I have. The application that we are building is causing performance issues. Can someone help me to check that the kind of SPs that I have written are according to standards?
I need some tutorial/tips articles on how to write SQL statements that run faster. How the query is made and how to write joins? how to handle multiple user access to same SPs? inserting and updating and selecting at the same time?
I hope my question is clear and I have big hopes that this forum will help me!
February 2, 2009 at 7:28 am
I'm sure there are lots that will help on this forum. The first thing to do would be to try to grab the worst performing procedure and work your way from there providing the sql from that procedure for review. The other thing that would be good to consider would be to look at the overall design to ensure that your indexing strategy is solid and allowing the procedures to perform properly. So, my recommendation is to use SQL Server Profiler and configure it to look for those statements that have a high CPU duration. Set your filter at > 3000 and see what you get. Hopefully that won't yield too much and you can start working your way through the procedures from there. You could also get the execution plan from the execution of that procedure and post that up for review as well.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 2, 2009 at 7:41 am
Thanks for the quick reply.
Actually I find it always difficult to even understand the topics by which people post their problems. The content is so technical or specific that I cant benefit much. I just found one article so far where the author has listen 15 points to do! but then I see criticism at the end on many point so I cant use take those points as it is.
Frankly I dont think I have ever indexed anything as yet! Which is bad I know but thats why I am here! Can you tell some articles that list common mistakes? best practices that are simple to do but gives much improvement?
As per you suggestion on using SQL Profiler! the situation is that we are hosting our Site on a dedicated server but we are using the SQL Server (shared) that our hosting company offers to avoid licensing primarily! Is there anyway we can still use SQL Profiler? and if yes then is there any tutorial on that...that I can simply follow? and if I need to purchase SQL Server to use SQL Profiler, is there anyother way out?
Thank you so much for your response once again!
February 2, 2009 at 7:52 am
idrees.butt (2/2/2009)
Thanks for the quick reply.Actually I find it always difficult to even understand the topics by which people post their problems. The content is so technical or specific that I cant benefit much. I just found one article so far where the author has listen 15 points to do! but then I see criticism at the end on many point so I cant use take those points as it is.
Frankly I dont think I have ever indexed anything as yet! Which is bad I know but thats why I am here! Can you tell some articles that list common mistakes? best practices that are simple to do but gives much improvement?
As per you suggestion on using SQL Profiler! the situation is that we are hosting our Site on a dedicated server but we are using the SQL Server (shared) that our hosting company offers to avoid licensing primarily! Is there anyway we can still use SQL Profiler? and if yes then is there any tutorial on that...that I can simply follow? and if I need to purchase SQL Server to use SQL Profiler, is there anyother way out?
Thank you so much for your response once again!
You don't actually want to run Profiler against your production system, but instead set up a server-side trace (a scripted version of what Profiler does through the gui). That aside, yes, if you have access to the SQL Server client tools, then you can set up & run stuff on a server where you have appropriate permissions.
As to the basics & best practices for TSQL, you also mentioned indexes. It sounds like you're really just getting going on this. You probably need a lot more than 10 basic tips such as fully qualify your table names, etc. Instead of an article or two, I'd suggest several books. The Inside SQL Server series, specifically T-SQL Querying and The Storage Engine are must reads. I honestly hesitate to do this, but I've got a book coming out in March that seems to be geared specifically for your question. Take a look here: http://www.apress.com/book/view/1430219025
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2009 at 7:59 am
I'd also recommend T-SQL Fundamentals by Itzik Ben-Gan. I've only glanced through it, but it looks good.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 8:01 am
One thing you could do is post specific stored procedures on this forum, and ask for help with them. Make it clear in the post that you're new to performance tuning, and we'll make sure to explain a bit more carefully when we suggest improvements. There's an article on best practices when posting such things: http://www.sqlservercentral.com/articles/Best+Practices/61537.
As far as running profiler/traces on a server you don't own, you probably can, but check with the hosting company before you do. Profiler can slow the whole server down if you use it incorrectly. Also, they might already be running certain traces, and then you won't have to run those, since you can get the data from them.
Since it sounds like you're pretty much new to performance tuning and (perhaps) to database design, it might be best if you did something like get your company to hire a good contractor to fix things up for you. That'll definitely be faster than waiting while you learn the ropes. It'll depend on whether your situation is more sensitive to time (it'll take longer for you to learn how to speed them up) or money (it'll cost more to hire a contractor).
One of the best resources for learning how to write faster T-SQL code will be the Inside SQL Server series of books from Microsoft Press. That'll get your started quite nicely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 8:06 am
GilaMonster (2/2/2009)
I'd also recommend T-SQL Fundamentals by Itzik Ben-Gan. I've only glanced through it, but it looks good.
I agree. This is also a very good resource. Thinking about it some more, I'd also add Joe Sack's books, although they focus primarily on SQL Server 2008, most code will work in the same way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2009 at 8:07 am
GSquared (2/2/2009)
Since it sounds like you're pretty much new to performance tuning and (perhaps) to database design, it might be best if you did something like get your company to hire a good contractor to fix things up for you. That'll definitely be faster than waiting while you learn the ropes. It'll depend on whether your situation is more sensitive to time (it'll take longer for you to learn how to speed them up) or money (it'll cost more to hire a contractor).
If you went that way it would be a great way to get a jumpstart on tuning as you could watch over their shoulder and learn providing you make that clear to them that you would be doing that as part of the contract.
I'll second Gail's recommendation as well. Not much for performance tuning but good for solid fundamentals in T-SQL programming.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 2, 2009 at 8:32 am
Well thats alot for me. I am feeling so good. Thats perhaps exactly what I was looking for.
I really wana read the books but they will be expensive and I dont think that I will be able to buy them all!! I will look through libraries and if I find anyone of them I will start reading. Any online resource for these/related books? am I supposed to read the whole book or just the relavant chapters? which are relavant chapters? at the end may be I will be able to buy one book online, which one is the most comprehensive one to start with?
One very general question, how long would it take for an expert to 'tune' the framework if there are lets say 175+ SPs? with only less than 40 relatively complex Selecting SPs? The rest are all either insert, update or delete. There are 40 tables in the DB at the moment.
Also is there anyway on this forum that I can communicate on phone directly to experts to explain the situation and get quick help on that medium?
I will sure post SPs as soon as I identify which one to post! Any suggestion? Should I post one from each that contain insert, update, delete statements and then temp table and complex select?
Appologies for anoying questions!!! 🙁
February 2, 2009 at 8:49 am
idrees.butt (2/2/2009)
Well thats alot for me. I am feeling so good. Thats perhaps exactly what I was looking for.I really wana read the books but they will be expensive and I dont think that I will be able to buy them all!! I will look through libraries and if I find anyone of them I will start reading. Any online resource for these/related books? am I supposed to read the whole book or just the relavant chapters? which are relavant chapters? at the end may be I will be able to buy one book online, which one is the most comprehensive one to start with?
One very general question, how long would it take for an expert to 'tune' the framework if there are lets say 175+ SPs? with only less than 40 relatively complex Selecting SPs? The rest are all either insert, update or delete. There are 40 tables in the DB at the moment.
Also is there anyway on this forum that I can communicate on phone directly to experts to explain the situation and get quick help on that medium?
I will sure post SPs as soon as I identify which one to post! Any suggestion? Should I post one from each that contain insert, update, delete statements and then temp table and complex select?
Appologies for anoying questions!!! 🙁
Of the books I've seen listed, probably reading most of the book is what is needed.
It's hard to say how long an effort like that would take. Assuming the structure doesn't need to be adjusted in any way, 175 procs... probably no more than four weeks, but that's a serious SWAG.
Which proc to post first, learn how to run a server-side trace and then find the one that causes you the most pain. Also, if you want a quick hit, you could try running a query against sys.dm_exec_query_stats to see which of the procedures that are currently in cache are the most costly. It's not a perfect measure, but it gives you a moment in time.
Phone calls... it's not a service Steve provides that I'm aware of. You do know that everyone posting here is just doing this on their own time for fun or as a learning experience or to give back to the community or all of the above?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2009 at 10:35 am
idrees.butt (2/2/2009)
am I supposed to read the whole book or just the relavant chapters? which are relavant chapters?
Since you're new to this, read them all. The books are expensive, but they're an investment. Ask your boss if he's willing to buy them for the company?
One very general question, how long would it take for an expert to 'tune' the framework if there are lets say 175+ SPs? with only less than 40 relatively complex Selecting SPs?
If I were doing that, I'd probably focus on the worst 5 of the complex ones. No point in tuning stuff that's not contributing to the problem. I'd probably say a week to do a proper job on the 5. That's everything from identifying to analysing to recommendations on code and indexes.
Also is there anyway on this forum that I can communicate on phone directly to experts to explain the situation and get quick help on that medium?
Sure. How much are you willing to pay?
To elaborate, I do this kind of work professionally. I answer here in my spare time, because it's challenging and keeps my skills sharp. If you want more than this, it can be arranged, but it will cost. (to give you a idea, I bill $100/hour. The US guys will probably bill more)
I will sure post SPs as soon as I identify which one to post! Any suggestion? Should I post one from each that contain insert, update, delete statements and then temp table and complex select?
Start with the one that you identify as the worst performing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 10:41 am
GilaMonster (2/2/2009)
idrees.butt (2/2/2009)
Also is there anyway on this forum that I can communicate on phone directly to experts to explain the situation and get quick help on that medium?
Sure. How much are you willing to pay?
To elaborate, I do this kind of work professionally. I answer here in my spare time, because it's challenging and keeps my skills sharp. If you want more than this, it can be arranged, but it will cost. (to give you a idea, I bill $100/hour. The US guys will probably bill more)
Or less. I'd charge less as I would do it off hours to supplement my income.
February 2, 2009 at 11:46 am
I thank you all for being so much helpful.
Well $100/hour is much more for me. I think I will prefer to get your free time 🙂 but I will keep that in mind for worst scenario! as I believe this forum wont let my problems exist!
I will start reading the books then!
I will be right back with updates after some time. Meanwhile if someone has any valuable suggestions please feel free to reply.
Thank you once again!
February 2, 2009 at 12:20 pm
GSquared (2/2/2009)
One thing you could do is post specific stored procedures on this forum, and ask for help with them. Make it clear in the post that you're new to performance tuning, and we'll make sure to explain a bit more carefully when we suggest improvements. There's an article on best practices when posting such things: http://www.sqlservercentral.com/articles/Best+Practices/61537.
Don't forget this advice either. I'm sure all that have posted to this thread would be happy to provide some suggestions on what you have in place currently. Follow the instructions in the link for the best help though.
Enjoy!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 2, 2009 at 12:31 pm
DavidB (2/2/2009)
I'm sure all that have posted to this thread would be happy to provide some suggestions on what you have in place currently.
Oh, absolutely! The comment about prices wasn't saying we won't help. We're happy to help.
It was just saying that if you want telephonic assistance, or x days of work from someone here, you're going to have to pay for it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply