January 31, 2013 at 2:42 am
I seriously recommend a lot of reading on the main topics of SQL.
Start with the stairways section of the site and read up on all the topics covered.
Gail also has some good topics on indexes, which you can find on her columnist page - http://qa.sqlservercentral.com/Authors/Articles/Gail_Shaw/148127/ - Introduction to indexes parts 1 2 and 3.
Also her website covers a lot of topics which I would suggest reading as well - http://sqlinthewild.co.za/
Also whats with all the NOLOCKS, you are aware of the implications of using the hint arn't you?
If its all to much to take in and this is a cirtical thing that needs sorting ASAP, then as Gail said, hire someone with experience of tuning SQL who can divote all their time to your problem.
January 31, 2013 at 3:23 am
anthony.green (1/31/2013)
I seriously recommend a lot of reading on the main topics of SQL.Start with the stairways section of the site and read up on all the topics covered.
Gail also has some good topics on indexes, which you can find on her columnist page - http://qa.sqlservercentral.com/Authors/Articles/Gail_Shaw/148127/ - Introduction to indexes parts 1 2 and 3.
Also her website covers a lot of topics which I would suggest reading as well - http://sqlinthewild.co.za/
Thanks. Seem to be really usefull resources :D. I'll check it further...
Also whats with all the NOLOCKS, you are aware of the implications of using the hint arn't you?
I think I'm aware of it, but I lack experience in snapshot isolation mode. The question is, if it is usefull and good for the performance to still use ROWLOCKS or NOLOCKS, or if it omits the mechanism of snapshot isolation mode. The next thing is the question : if I use ROWLOCK in snapshot isolation mode could this lead to lock escalation (and perf problems) anyway?
If its all to much to take in and this is a cirtical thing that needs sorting ASAP, then as Gail said, hire someone with experience of tuning SQL who can divote all their time to your problem.
As you surely would agree, the whole SQL Server subject is getting bigger and bigger growing ever faster and sometimes your job leaves you no time to read all you should read. I think I'm not a noob anymore, after several years of experience, but you live and learn and I still got enough gaps. So as I wan't to reach the next level, I'd like to solve the problem by myself and develop myself, so I could help others too as you did. So as this seems to be a nice forum where forum noobs get treated well, I think I'd like to stay here a while and get proactive.
Greetz KSC
PS: The included column changed the execution plan now, that the Key Lookup only costs me 70% and the Index Seek with my now changed Index takes 13%. So I think I have to play around with i a little bit and maybe also optimize the other queries...
Greetz
Query Shepherd
January 31, 2013 at 3:33 am
SQL MOD (1/31/2013)
The question is, if it is usefull and good for the performance to still use ROWLOCKS or NOLOCKS, or if it omits the mechanism of snapshot isolation mode.
Nolock is not a performance tuning technique.
The next thing is the question : if I use ROWLOCK in snapshot isolation mode could this lead to lock escalation (and perf problems) anyway
Well, snapshot isolation doesn't use locks...
Locking hints are not for casual use. There are very few times when you need locking hints on well tuned queries.
So as I wan't to reach the next level, I'd like to solve the problem by myself and develop myself, so I could help others too as you did.
Sure, but if it's a critical issue that's causing your company problems, lost business and the rest, is it responsible to use it to learn how to fix things? You typically learn a lot faster when there's someone showing you what to do.
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
January 31, 2013 at 3:47 am
Nolock is not a performance tuning technique.
No...it's just a dirty technique to avoid deadlocks.
Well, snapshot isolation doesn't use locks...
Locking hints are not for casual use. There are very few times when you need locking hints on well tuned queries.
I know snapshot isoaltion doesn't use locks, but all the queries used it before i switched to snapshot isolation mode, because we had deadlock problems. So the question is if it (especially the rowlocks) troubles now or not, because the snapshot isolation mode ignores it
Sure, but if it's a critical issue that's causing your company problems, lost business and the rest, is it responsible to use it to learn how to fix things? You typically learn a lot faster when there's someone showing you what to do.
Thanks for this helpful advice...sometimes you don't hear on other peoples opinion and after you leared the lessons on your own, you mostly think I could have saved me this. So I totally agree with you. I'm already searching for a good consultant and in the meantime I try my best.
How did you become so good in the SQL Server topic and how many years of experience do you have? Waht is the key to a knowledge like yours?
Greetz
Query Shepherd
January 31, 2013 at 3:51 am
SQL MOD (1/31/2013)
How did you become so good in the SQL Server topic and how many years of experience do you have? Waht is the key to a knowledge like yours?
I learned from a top SQL consultant that I worked with for several months, studied by myself, tried stuff, broke stuff, read everything I could find answered as much on these forums as I could.
About 12 years now.
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
January 31, 2013 at 4:26 am
I learned from a top SQL consultant that I worked with for several months, studied by myself, tried stuff, broke stuff, read everything I could find answered as much on these forums as I could.
About 12 years now.
Wow...respect! So know I know what I got to do :D!
So as i can outline : tuning indexes is nothing that you can automize...right?
Greetz
Query Shepherd
January 31, 2013 at 4:52 am
SQL MOD (1/31/2013)
So as i can outline : tuning indexes is nothing that you can automize...right?
Yes
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 31, 2013 at 6:12 am
So now as I tuned all my CPU-extensive queries, I must state, that SQL Server is still taking almost 100%.
What else can I do? How can I see explicitly how sql server uses or claims the CPU? Any ideas?
Greetz
Query Shepherd
January 31, 2013 at 6:25 am
If SQL is still taking 100% of the CPU, then you haven't tuned all the CPU consuming queries. Keep going, not something you're going to solve in 5 minutes, usually takes me a week a month for 2-3 months to get a badly performing server under control.
No silver bullet, no magical instant solution.
What else can I do? How can I see explicitly how sql server uses or claims the CPU? Any ideas?
Chapter 3 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
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
January 31, 2013 at 7:16 am
If SQL is still taking 100% of the CPU, then you haven't tuned all the CPU consuming queries. Keep going, not something you're going to solve in 5 minutes, usually takes me a week a month for 2-3 months to get a badly performing server under control.
So you're investigating 2-3 months the long duration queries with profiler and tune em up? That's all respectively the secret?
Greetz
Query Shepherd
January 31, 2013 at 7:20 am
SQL MOD (1/31/2013)
If SQL is still taking 100% of the CPU, then you haven't tuned all the CPU consuming queries. Keep going, not something you're going to solve in 5 minutes, usually takes me a week a month for 2-3 months to get a badly performing server under control.
So you're investigating 2-3 months the long duration queries with profiler and tune em up? That's all respectively the secret?
Yes. It's no secret, those two articles on Simple Talk describe my tuning methods, so do some on my blog, articles for SSC, talks I've given, etc.
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 5, 2013 at 7:49 am
Just a last question to this topic : Now I know more about indexing and reading maintenance plans. But how do I start my "performance tuning weeks"? IS it the best practice to delete all old indexes and recreate them one by one as needed? You know what I mean?
Greetz
Query Shepherd
February 5, 2013 at 9:08 am
SQL Pizza (2/5/2013)
IS it the best practice to delete all old indexes and recreate them one by one as needed?
Do you want to start by crippling the server and making already poor performance horrific? 🙂
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 5, 2013 at 9:15 am
No, I don't think so...but how do i get an order respectively get rid of unused and poor indexes having only the "best" indexes left?
Greetz
Query Shepherd
February 5, 2013 at 9:30 am
http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/
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 - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply