Entries:
Comments:
Posts:

Loading User Information from Channel 9

Something went wrong getting user information from Channel 9

Latest Achievement:

Loading User Information from MSDN

Something went wrong getting user information from MSDN

Visual Studio Achievements

Latest Achievement:

Loading Visual Studio Achievements

Something went wrong getting the Visual Studio Achievements

Back to Indexes: The Original Culprit

Download

Right click “Save as…”

Slides (view online)
Yes, indexes still remain the main bottleneck when it comes to performance; especially when your data grows big. In this demo-heavy session, Amit takes you through real-world scenarios of bad indexing designs and how they can be rectified to improve performance. Focus remains on clustered and non-clustered indexes and the esoteric combinations of them. If you think you know everything about indexes, think again! #TEDBI406
For more information, check out this course on Microsoft Virtual Academy:

Follow the Discussion

  • Simon LSimon L

    At best this session is level 200, partly due to advice given is incorrect and the benchmarking methodology is unorthodox. DBCC FREEPROCCACHE is NOT VERY SAFE to be ran on a production server. Testing, then yes. Paul Randal says the same as well.
    And executing DBCC DROPCLEANBUFFERS? I cant really think of much practical reason why you would want to flush clean pages out of the memory, even for testing?
    Finally, performance benchmark by datediff sysdatetime()? What happen to set statistics io\time? Benchmarking by execution time can be very inaccurate especially if you already having load on SQL at the time.
    Sorry to be harsh, but giving out such advice to such large audience is dangerous.

  • Probably, you did not hear what I had said before executing the DBCC statements - "If you want to lose your job, you can run these statements in prod enviornment". The message was clear that you should never run them in prod enviornment. I ran those statments only for the purpose of demo. Why? simply becuase I wanted to calculate the physical read time as well; not just logical read. I could have used STATISTICS IO; but I prefer the T-SQL code - doesnt matter for simple demos !!!

    I would love to hear which advice given is incorrect and what are modern benchmarking methodologies you suggest! Kindly be specific in your comments before you use terms like 'dangerous' Smiley

    Level 400 versus Level 200? For some it could be Level 100 and for some it could be Level 500 !!! all depends on your current knowledge and what you gain from the session ! Certainly, my session was not designed for Paul Randal Smiley

    HTH

    Amit

  • DineshDinesh

    Some of the advices given in the video is not uptomark and very absurd. As said benchmarking just by time makes no sense its just seeing half side of the coin.

    Yes it level 100 i will say.

    Do not want to be harsh Teched should improve and probe on quality before publishing to large audience.

  • Hi Dinesh, 2 things I want to know from you:

    1. which are the advices you term as 'absurd' and 'not uptomark'?

    2. what else do you want to benchmark in a quiescent SQL Server with a single user on simple update query? And what is the other side of the coin; if you can explain !

    Please be specific !

    Thanks

    Amit

  • Just viewed this and found it to be nice with simple and easy to understand demos. nothing inaccurate or absurd advice was offered ! In fact, I remeber one of the sessions of Kimberly Tripp where she uses similar benchmakring technique to record performance.

  • technically refreshing video ! nice job !

  • @Simon L:

    Probably, you did not hear what I had said before executing the DBCC statements - "If you want to lose your job, you can run these statements in prod enviornment". The message was clear that you should never run them in prod enviornment. I ran those statments only for the purpose of demo. Why? simply becuase I wanted to calculate the physical read time as well; not just logical read. I could have used STATISTICS IO; but I prefer the T-SQL code - doesnt matter for simple demos !!!

    I would love to hear which advice given is incorrect and what are modern benchmarking methodologies you suggest! Kindly be specific in your comments before you use terms like 'dangerous' Smiley

    Level 400 versus Level 200? For some it could be Level 100 and for some it could be Level 500 !!! all depends on your current knowledge and what you gain from the session ! Certainly, my session was not designed for Paul Randal Smiley

    HTH

    Amit

  • @Dinesh:

    Hi Dinesh, 2 things I want to know from you:

    1. which are the advices you term as 'absurd' and 'not uptomark'?

    2. what else do you want to benchmark in a quiescent SQL Server with a single user on simple update query? And what is the other side of the coin; if you can explain !

    Please be specific !

    Thanks

    Amit

  • @Dinesh:If I am not wrong you are one of those who put absurd comments without being specific and you have done that in past on Amit's blogs as well. Keep your personal issues aside my friend. this is a GREAT session as Amit put up things so well and the session was highly rated by the audience !

  • @AmitBansal:Sorry, but Amit did not say anywhere that you shoud run DBCC DROPCLEANBUFFERS in your prod enviornment; sadly you did not understand his pun !

  • Excellent session !

  • SreedharSreedhar

    Really Channel 9 should improve they way deliver videos online. I am trying to view this from India with a good connection and it has simply put me down.

    Just for everyones benefit first 6 to 10 minutes are all like lunch , CTO , building , miles ,deep breath , feedback , comedy airport jokes , bags india us, blah start.

  • Dandy WeynDandy Weyn

    From a content level perspective this session is a level 400 according to industry defined content level standards.
    The session requires certain prerequisites from the attendee that (knowledge and understanding of SQL Server and Join Mechanism, pre-assumed knowledge on physical /reads / logical reads, cardinality, understanding the cost based optimizer in SQL Server)
    Suggestions on how to improve TechED sessions, I gladly see ending up directly in my mailbox for which gives me maximum visibility, since I am the content owner for the DBI track. (or twitter @ilikesql). When demoing index performance, in any demo or showcase scenario to highlight these impact of what is shown in the demo you will free the cache and clean buffers. In this example, I do agree on SET STATISTICS IO, however I can come up with many reasons or justifications on why I would agree on time/comparisons using T-SQL.

  • @Sreedhar:Sreedhar, may be you can download the session and view offline; if that works for you ! (in case streaming does not work)

  • SreedharSreedhar

    I am doing the same Amit. Its a good session. The downloading takes lot of time.
    Thanks for sharing this.

  • Really well explained stuff and neat demos

  • Simon LSimon L

    Sorry if i've misheard about running the 2 DBCC in production. If advice is to extreme take care before running in prod, then yes thats a good advice.
    But for benchmarking performance, I'm not aware Kimberly Tripp also uses time base (how many seconds or milliseconds) to run her benchmark when doing performance tuning. If she's doing that, then I guess thats something I've not really considered when I'm doing my tuning. This is because I've not really heard of a DBA telling me they tune their query solely by measuring the execution time. How can they tell if its just luck that SQL cache was cleared and optimizer picks up a good plan?

  • @Simon L:

    Hi Simon,

    Thanks for your understanding. And I am glad you responded. Let me explain a few things.

    1. Yes, those DBCC statements should NEVER be run in prod enviornments. And that's why I said "you will lose your job if you do so". I couldnt have explained that better.

    2. It is only for the purpose of demos or in test enviornment, we do so, just to measure performance.

    3. Why did I run DBCC FREEPROCCACHE? Becuase I ran the upadte query twice. If you view the session again completely, you will see that I am running the update query twice, once before the non-clustered indexes are created and second time after creating the indexes. and update query has a plan in the cache after running it for the first time and I want to clear the plan before running it the second time; otherwise my performance numbers will not be correct since the time taken to generate the plan will not be counted (and that is becuase, I am using TSQL query to measure the numbers)

    4. Why did I run DBCC DROPCLEANBUFFERS? simply becuase I want to record the numbers including PHYSICAL READS. I dont want to assume that all the pages are there in the memory all the time.

    5. I do not know what Kimberly uses. Thats a comment from another user. I do have high regards for her knowledge.

    6. The last part of your comment is the most important here. You are assuming that I am telling the audience in the session to tune queries by just measuring execution time. NO. I am just using the execution time to record the update performance before and after indexing. The session is not about tuning queries "as such". Its about indexing. and query execution time is the most imporant parameter I want to record; simply to see if implenting the right indexes has decreased query resposne time or not? If yes, then good ! Its really as simple as that ! If you also observe the closing remarks of my session, I stated that "this session is not about troubleshooting SQL server performane or the metrics you should record". There could be dozens of perf mon counters, DMV data, etc that can be recorded. But this session simply takes the execution time to measure performance.

    6. Why not STATISTICS TIME? simply becuase it has lot of numbers.. compiple time, execution time, elapsed time, etc... its not bad... but for my session, it wasnt required... all I needed was simply the total time it takes to execute the update query. and the T-SQL code does that job pretty well. I had only 2 or 3 sessions running at that time with no activity at all. so there was no way that those number could get skewd and if those numbers are skwed, even the STATSITCS TIME numbers will be skewed.

    7. No there is no luck or something, when it comes to Optimizer. Its all cost based and there are rules. I just demonstrated some of them.

    Overall to summarize, I request you to watch this session again; I guess you havent done that Smiley - if you do, you will observe that neither have I offered any absurd advice nor have I used any orthodox mechanism - there is nothing orthodox or modern here. I wanted to connect with my audience in the most simple manner. Simple database, simple queries, tried to explain complicated concepts in the most simple manner possible. I had intercted with TechED audience before and I knew thats what they wanted. and thanks to them, they all appreciated the session very much making it land up in TOP 3 sessions in DB platofrm category Smiley

    Simon, hope this explanation helps and now my boss is calling me and I need to rush !!! I will be glad if you can edit your original comment and be less harsh Smiley

    Regards
    Amit

  • Simon LSimon L

    Thanks for explaination and congrat on being Top 3 session in DB platform. My comment were such because of the level 400. I do think you're technically competant, just thought some section could've been explained in much technically in-depth because again, its level 400. But i guess audience expectation are dis-similar.
    Apology again if my comments sounds harsh. If majority audience reckons you're doing a good job, then its just me :) My bad

  • All viewers - I blog at www.SQLServerGeeks.com and my twitter handle is www.twitter.com/A_Bansal

  • Paul SPaul S

    Good one ! (initially after reading the comments i was put off, but after watching the session i think its a good one)

  • dineshdinesh

    This is really funny Amit. Neither i know you , neither i know your blog. I do not want to be aware of your blog either.

    Your first demo itself put me off , i can bet that you have not shown the complete picture. You first statement that there are 3 physical operators is itself wrong ( if you wish i can point to MSDN where i can show you how many physical operators are there).

    Second you saying that hash comes because of memory shortage is again very novice. Come on hashing comes depending on the build and probe input ( yeah 20 years of working with RDBMS).Finally i can bet if my data is large that SORT will never come ahead.

    This is by experience. Just because i got a notification i commented. I appreciate what you do , but i can bet you have not given the complete picture.

    Bye i will not reply as you are thinking that you are under a impression that you have given some session which is like a perfect Steven speil movie and it is not.

  • Just after looking at the first comment, I got an impression that the session might not be upto the mark. Simon rightly marked that DBCC FREEPROCCACHE is not safe to run in production.

    But why do you need to comment such a crap on the very top of the comments section, when the Author clearly said before running the command that "If you are tired of your job, run this in production". This clearly makes sense.

    The demonstration which uses these statement is about "choosing right indexes", and the demo is absolutely fine and really fruitful to the DBA's.

    I really like this session the most, its absolutely a stunner from Amit. These important tricks will really guide me during my work.

    @Simon, Sorry, last time I didn't see your comment and responded. I would recommend to please edit this comment as Amit already explained. It actually gives bad impression to the thread when the first comment looks like this. I personally feel sad when I see such a nice session gets such criticisms. 

    @Dinesh
    Do you have a profile ? Can you show the content of your blog and your online activities before speaking something about the speaker. Well, people like Amit turns out to be a god in SQL server. Even when I speak about internals of CLR I always hide few things, and it makes perfect sense. If you have spoken anywhere before, you would have come accross to this too. It makes sense to speak in abstraction otherwise it would be difficult to catch up with the audience, but it is important not to speak false statement. I am quite aware that the speaker didnt made any false argument. Dinesh, I dont know why you need to hide yourself if you are sure about something... Smiley

    I really enjoyed this session and I appreciate Amit for spending time and delivering a session for TechEd NA. Because of these sessions, Teched gets its glory. Cheers mate.

  • @dinesh:You need to listen and watch carefully before commenting.

    1. I mentioned "3 physical JOIN operators", not "3 physical operators". There are only 3 physical join operators: NESTED, MERGE & HASH.

    2. I did not mention HASH comes becuase of memory shortage. My words say that HASH does the heavy lifting when both the inputs are "large". My session further says that "if memory is short, HASH tables data can get spilled down to worktables in tempdb" and happens mostly in DW enviornments.

    You are putting inaccurate comments misleading the audience. And there is no reason to get personal here based on someone else's comment. I am not a film maker. I am a SQL Server MVP and I am responding with full responsibility only in the interest of the community. Thanks !

  • DineshDinesh

    @Abhi You mean to say if i do not write articles and do not have a blog i do not have authority to comment. L Simon just because he said something bad his comments should be marked as SPAM. VOW , if you want, argue technically or go ahead with your part time blogging job. Do not use GOD word when you do not know the meaning of GOD. By the way no one can hide knowledge , if you are hiding you are indeed very very funny.

    @Amit

    I am so sorry for the physical operator thing. You said right.

    You said that you will see hash match "If your box is short of memory". You did say the rest of the words ( spill) but not sure it changes the meaning of "short of memory".Hash is used by SQL if he sees large data between two tables , again it depends completely on your environment , indexes etc.

    Second you stating if you are tired of your job and run two statements on production server is too much. Yes, you will loose your job if you do that. If you run that during peak times all your current request will go in a wait mode until a new query plan is generated.

    Second i would never clear cache to measure performance in that way , if i want fair results. I would first do a warm up , fill the cache , give equal chance to the database as well or else i am dead sure i am adding the warming up time with execution which is completely not fair.

    Second the way you have recorded time again is not fair. Why to record in a database , we are again adding noise. Show it in the window and record it a note pad. In Infy we use VSTS DB test from visual studio and its very fair and meant only for the same purpose.

    I do not want to carry this debate as it will just put me the bad side. Seeing comments like @Abhi here i think you have some kind of favored community and yes "can we debate with GOD ? " no we can not right.

    So i leave from here forever.

    my real email dineshsmadne@gmail.com

  • Nauzad KapadiaNauzad Kapadia

    @Dinesh - you are resorting to nitpicking and trying to find faults with one or two sentences here and there. I don't think you have ever got on stage and tried to present a topic to an audience with varying levels of knowledge in less than 60 minutes. The objective of the session was to "educate" about doing the right things with indexes and not get into things like what is the best way to measure performance and whether clearing the cache and buffers is necessary or not. If Amit had perhaps 2 entire days to present this vast topic, he could have gotten into the nitty gritties mentioned, but every session has an objective (look at the title) and a finite time to be completed in. Secondly whether a session is Level 200 or 400 is not dependent on an individual attendees knowledge and experience, but based on a fixed definition. Just because i know all the points presented in this session, doesnt mean i go off and label it as a level 200 session. Lastly majority of the audience will not be backed up with over 20 years of experience like you and in the end what matters is whether the majority of the folks benefited or not. The ratings received for this session prove that a majority of them did benefit. <EOD>

  • @Nauzad Kapadia:+1000

    @Dinesh:more personal comments than professional.Testing Database performance is big subject and this session was not about that. The speaker measured the execution time of update queries by clearing the cache and the buffer and rightly so to remove the update plan and the buffer pages from memory - so both the times, the queries are measure fairly. Simple & concise.

  • Jerald FernandesJerald Fernandes

    I was going through the comments posted on this site and just wanted to highlight some of the key takeaways from Amits session which I personally attended during this TechEd.

    1. The queries were crisp and easy to understand and can quickly help a DBA troubleshoot various performance bottlenecks. In fact, I felt this session was much more techincal than the many other sessions that I attended (which were only theory). To put it across to a variety of audience who would range from level 100 - infinity, I think the contents were perfectly presented.

    2. On the other hand to blow up a lighthearted joke into an issue is really not just on the presenter. That way all presentations would be boring and formal. I feel Amit met the audiences' expectations very well and I remember the hall burst into laughter at this one.

    3. Overall Amit is ginuenely a person who wants to help out. Keep it up Amit and I really appreciate the time and efforts you put into this presentation.

  • @Simon did not see Amits explanation and your follow up comment. Sorry about that. I have already edited my text on my comment, But I will request you to edit your first comment since it is not appropriate and looks bad when anyone visits this thread. I am glad to meet you online.

    @Dinesh

    Amit clearly said that in HASH operations, HASH table data gets spilled down to temDB if memory is short on box. You are hearing only what you want to hear. You are taking DBCC statements too far and everyone in this thread has clarified that so I would not like to comment on that. When you warm up the cache, how do you measure the time taken to read data from the disk? This is what I think the speaker meant by Physical IO. Even if I would have spoken to somebody this is what I would do to demonstrate actual physical IO. I cannot measure an IO when some page is loaded from cache.  He, in his session, wants to measure that time only. Thus, DBCC DROPCLEANBUFFERS is needed in his demo.

    And your testing with VSTS DB is really a not a good thought either. When I want to show query/index performance rather than application performance, why should I include VSTS?  If you test from VSTS DB (which DBAs dont generally do) you are adding more noise from the client & network latency. You need to test raw performance of SQL DB engine and this is what the session showed.

    What do you mean by show it in windows and record in notepad?? Funny it is that you work in Infy; very surprised !!! Really want to test SQL performance?? Use tools like SQLIO...

    Last but not the least, your email id is incorrect. I didnt want to spoil this thread and get this discussion in personal by sending a mail few minutes ago, but it bounced back. Fake users like yourself de-motivate all the good efforts of the MVP community. I request Channel 9 to delete your comments.

     

     

    @Nauzad Kapadia and  @Jerald Fernandes: +1000 from me. Practically speaking, I always try to motivate speakers on the community. And on this Demo, there is really no means of criticisms. Demo oriented session really touches mind of attendees. Even though theories are important, but as an audience, I generally like these kind of demo heavy sessions more than theories.

    My best wishes to Amit. Keep it up buddy.  


  • Anup WarrierAnup Warrier

    This was a very good Session.I was reading through some of the comments here,and it seems people are just passing on comments without actually listening to the session properly.I dont see any flaws in demos and Amit actually tried very well to explain some of the important things within the permitted time.

    Good job Amit !

  • @Dinesh:You have to be very specific when you have to correct something or point out something which went wrong with the session.What you just did here is passing on comments without listening to the session carefully.Thats a wrong move from your end and you have to accept it !

    Teched always produced good content and they keep delivering it well.

     

  • SriramSriram

    Nice Session

  • MeherMeher

    Good and Informative session Amit. Thank you.

  • SuhasSuhas

    Very Nice Session…!!!

  • RiteshRitesh

    Great demonstration and good insight Amit..... really helpful for experienced and novice dba's... keep up the good work... looking forward for more such sessions

  • Dinesh  vDinesh v

    nice session.

Remove this comment

Remove this thread

close

Comments Closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.