Questions and Answers:


Jim Asked: What's a doubly-linked list (brief definiteion)?


Jeremy/LiveMeeting Asked: How do I see the demo in full-screen mode?


Jeremy/LiveMeeting Asked: How do I see the demo in full-screen mode?

Answered: Control + H


Jim Changes Question To: What's a doubly-linked list (brief definiteion)?


Jim Changes Question To: What's a doubly-linked list (brief definiteion)?


Bill Walter Asked: Is there a tool or method for monitoring a database for a while and identifying indexes that are rairly or never used that should be considered for removing?


Tim McKane Asked: In an OLTP server would clusterd indexes cause any performance issues?


Tim McKane Changes Question To: In an OLTP server would clusterd indexes cause any performance issues?


Tony Biz Asked: What do you mean by static and narrow?


Amy Farrington Asked: Is a unique key the same as a primary key?(I thought you said a primary key automatically creates a clustered key, so how can this be a non-clustered key?) thank you.


Tony Biz Changes Question To: What do you mean by static and narrow? Also, are Guid indexes as efficient as int indexes?


Tim McKane Changes Question To: In an OLTP server would clusterd indexes cause any performance issues?


Tim McKane Changes Question To: In an OLTP server would clusterd indexes cause any performance issues? do to the constant inserts and updates?


arunma@microsoft.com Asked: Kim, You might want to mention at some point that


arunma@microsoft.com Changes Question To: Kim, You might want to mention at some point that


chrisk Asked: could you plz give an example of foreign key


cathy chang Asked: What is ITW?


Max Ustinov Asked: Sorry, what is ITW?


arunma@microsoft.com Changes Question To: Kim, You might want to mention at some point that Database Tuning Advisor has a "drop-only" mode in SQL Server 2005 that allows you to "clean up" unused indexes. -Arun Marathe


Tony Biz Changes Question To: What do you mean by static and narrow? Also, are Guid indexes (primary key) as efficient as int indexes?


Emile Deen Asked: When you say volitile clustering key, do you mean the data or the column being changable?


Ted Wong Asked: On a very large table (32 GB, 20 mil rows) with both a single clustered index and ten non-clusterd indexes. If you could only do db reindex or indexdefrag on a few indexes at time per day, which ones would you do and in what order?


Tony Biz Changes Question To: What do you mean by static and narrow? Also, are Guid indexes (primary key) as efficient as int indexes?


Tony Biz Changes Question To: What do you mean by static and narrow? Also, are Guid indexes (primary key) as efficient as int indexes? If you want to know the value of the primary key prior to your insert statement, how can you use identity?


Chekib Kchouk Asked: If you create an ID field in a table just for the sake of creating a clustered index,how is this going to improve performance if most of the queries use other fields in the table?


Param R. Asked: Can I download the PPT from a website? Very useful information so far. opened my eyes :)


chrisk Changes Question To: could you plz give an example of foreign key


Paul Asked: How is a UK created? Explicitly or is it Implied via Indexes?


j scot verdin Asked: Can I view a recording of this webcast?


chrisk Changes Question To: could you plz give an example of foreign keys


j scot verdin Asked: Can I view a recording of this webcast?

Answered: Yes, in approx. 72 hours at www.microsoft.com/usa/webcasts/ondemand.


chrisk Changes Question To: could you plz give an example of foreign keys


cathy chang Changes Question To: What is ITW?


David Asked: Isn't there some issue with profiler where it does not recognize DBName but only the DBID from the sys tables?


David Changes Question To: Isn't there some issue with profiler where it does not recognize DBName but only the DBID from the sys tables?


Param R. Asked: Can I download the PPT from a website? Very useful information so far. opened my eyes :)

Answered Privately: You can download a PDF of the presentation right here by going to File --> Print to PDF.


chrisk Changes Question To: could you plz give an example of foreign keys


chrisk Changes Question To: could you plz give an example of foreign keys could you recommend the best book to be introduced to SQL


chrisk Changes Question To: could you plz give an example of foreign keys could you recommend the best book to be introduced to SQL


John Rogers Asked: Do I need to change my indexes if I use the like operator? I noticed that I get Index Scans instead of seeks when I use lastname like '%johnson%' vs lastname = 'johnson'


Harrison Asked: Will the ITW also recommend removing indexes ?


Adam Wiedenhaefer Asked: Is it true that the ITW is beneficial because it can predicte how the optimizer wil most benefit from the indexes it recommends?


Tony Biz Changes Question To: What do you mean by static and narrow? Also, are Guid indexes (primary key) as efficient as int indexes? If you want to know the value of the primary key prior to your insert statement, how can you use identity? Also, can you recommend a good source for information on optimizing full-text indexes and searches?


Eric Asked: Thanks!


Melanie Oliveros Asked: Does SHOWCONTIG keep a table lock?


Jyoti Jacob Asked: I was told not to use DBCC showcontig on a production server as it can adversely affect performance. Is that true?


Jyoti Jacob Changes Question To: I was told not to use DBCC showcontig on a production server as it can adversely affect performance. Is that true?


Jyoti Jacob Changes Question To: I was told not to use DBCC showcontig on a production server as it can adversely affect performance. Is that true?


Doug Asked: 1) What is the best way to analyze both the space used and fragmentation of each table? Ie. is there one procedure that will check all tables in a db and show output the results?


Jyoti Jacob Asked: I was told not to use DBCC showcontig on a production server as it can adversely affect performance. Is that true?


adurandt Asked: why the SQLServer.exe service grop up the mem. ram fast, i need to stop the service to grop down but in short time its the same ?


Doug Changes Question To: 1) What is the best way to analyze both the space used and fragmentation of each table? Ie. is there one procedure that will check all tables in a db and show output the results and show me which tables and indexes are foobar and need some TLC?


Doug Changes Question To: 1) What is the best way to analyze both the space used and fragmentation of each table? Ie. is there one procedure that will check all tables in a db and show output the results and show me which tables and indexes are foobar and need some TLC?


Doug Changes Question To: 1) What is the best way to analyze both the space used and fragmentation of each table? Ie. is there one procedure that will check all tables in a db and show output the results and show me which tables and indexes are foobar and need some TLC? 2) Where can we download the scripts?


Ernesto Asked: Where are the sample scripts going to be posted?


Tejaswi Redkar Asked: Where can I get the script


Daniel DBA Asked: Could you tell us how the fill factor effects performance?


Doug Changes Question To: 1) What is the best way to analyze both the space used and fragmentation of each table? Ie. is there one procedure that will check all tables in a db and show output the results and show me which tables and indexes are foobar and need some TLC? 2) Where can we download the scripts? 3) Do you have a blog?


Max Ustinov Asked: Wow, this is a really great presentation. Thanks!


pchang Asked: Can I get the Powerpoint file?


Ernesto Changes Question To: Where are the sample scripts going to be posted?


adurandt Asked: your email please !!!


Nassif Asked: can the presentation(content) be posted or emailed by request?


arunma@microsoft.com Changes Question To: Kim, You might want to mention at some point that Database Tuning Advisor has a "drop-only" mode in SQL Server 2005 that allows you to "clean up" unused indexes. -Arun Marathe


Tony Biz Changes Question To: What do you mean by static and narrow? Also, are Guid indexes (primary key) as efficient as int indexes? If you want to know the value of the primary key prior to your insert statement, how can you use identity? Also, can you recommend a good source for information on optimizing full-text indexes and searches?


Natasha Asked: Thank you very much.


krishna Asked: Thanks


arunma@microsoft.com Changes Question To: Kim, You might want to mention at some point that Database Tuning Advisor has a "drop-only" mode in SQL Server 2005 that allows you to "clean up" unused indexes. -Arun Marathe


Tony Biz Asked: Also, can you recommend a good source for information on optimizing full-text indexes and searches?


Jaime Medina Asked: thank you


Martin Brys Asked: We are unable to save to PDF, please advise.


David Changes Question To: Isn't there some issue with profiler where it does not recognize DBName but only the DBID from the sys tables?


Rhonda Lewis Asked: how can i get a copy of the presentation?


Marco Asked: Do you speak in other events for more Advanced tuning topics?


Adam Wiedenhaefer Changes Question To: Is it true that the ITW is beneficial because it can predicte how the optimizer wil most benefit from the indexes it recommends?


Doug Changes Question To: 1) What is the best way to analyze both the space used and fragmentation of each table? Ie. is there one procedure that will check all tables in a db and show output the results and show me which tables and indexes are foobar and need some TLC? 2) Where can we download the scripts? 3) Do you have a blog?


Ernesto Changes Question To: Where are the sample scripts going to be posted?


Adam Wiedenhaefer Changes Question To: Is it true that the ITW is beneficial because it can predicte how the optimizer wil most benefit from the indexes it recommends? Sometimes I find that the ITW recommends statistics that hinder the query performance. Do you feel comfortable using it for the most part?


Marco Asked: Do you speak in other events for more Advanced tuning topics?

Answered: Yes! Check out www.SQLskills.com for information on both public and private events as well as consulting/mentoring!


arunma@microsoft.com Changes Question To: Kim, You might want to mention at some point that Database Tuning Advisor has a "drop-only" mode in SQL Server 2005 that allows you to "clean up" unused indexes. -Arun Marathe


Rufus Kellam Asked: On www.sqlskills.com, where will I find the scripts that you were using today? I didn't find the under "Scripts"


John Rogers Changes Question To: Where do we see the answers to our questions?


Doug Asked: 1) What is the best way to analyze both the space used and fragmentation of each table? Ie. is there one procedure that will check all tables in a db and show output the results and show me which tables and indexes are foobar and need some TLC? 2) Where can we download the scripts? 3) Do you have a blog?

Answered: www.SQLskills.com is my website and www.SQLskills.com/blogs/Kimberly is my blog!


arunma@microsoft.com Asked: Kim, You might want to mention at some point that Database Tuning Advisor has a "drop-only" mode in SQL Server 2005 that allows you to "clean up" unused indexes. -Arun Marathe


Rufus Kellam Changes Question To: On www.sqlskills.com, where will I find the scripts that you were using today? I didn't find the under "Scripts"


Rufus Kellam Changes Question To: Thanks!


arunma@microsoft.com Asked: Kim, You might want to mention at some point that Database Tuning Advisor has a "drop-only" mode in SQL Server 2005 that allows you to "clean up" unused indexes. -Arun Marathe

Answered: hey there arun... good point... wasn't spending much time on yukon and ran a bit short at the end.... I will add this into my blog as well as my Q&A on my website


cathy chang Asked: What is ITW?

Answered: Index tuning wizard but I bet you got that one :)


John Rogers Asked: Where do we see the answers to our questions?

Answered: they should be here... but I didn't get to them until now?! :)


arunma@microsoft.com Asked: Sure, no problems. I saw that people asked that a few time. And hi to you as well ... :-)


Adam Wiedenhaefer Asked: Is it true that the ITW is beneficial because it can predicte how the optimizer wil most benefit from the indexes it recommends? Sometimes I find that the ITW recommends statistics that hinder the query performance. Do you feel comfortable using it for the most part?

Answered: i do... and the statistics should be based on real data so I'm having a bit of a hard time with this one... statistics don't usually hinder query perf. Can you give me any more details?


Max Ustinov Asked: The BPA is on the front page http://www.microsoft.com/sql


Rufus Kellam Asked: Thanks!

Answered: your welcome! :)


Rhonda Lewis Asked: how can i get a copy of the presentation?

Answered: it will be on msdn within a couple of days... and I'll make sure to create links from my website and my blog as well!


Rufus Kellam Asked: Actually, I still can't see how to navigate to the sample scripts you were showing today...


Rhonda Lewis Asked: Thanks you very much.


Rufus Kellam Asked: Actually, I still can't see how to navigate to the sample scripts you were showing today...

Answered: whooopppss ! they might not be posted yet. Give me another hour or so....


Rufus Kellam Asked: Don't you have a share on the Microsoft corpnet site with most of your scripts?


Daniel DBA Asked: Could you tell us how the fill factor effects performance?

Answered: it typically helps IMPROVE performance in that it creates free space which minimizes splits and then minimizes the need to defrag as often


Rhonda Lewis Asked: i didn't get your name.


pchang Asked: Can I get the Powerpoint file?

Answered: I don't usually give these away... but msdn does :)... so it will be posted on the msdn website within 72 hours!


Tejaswi Redkar Asked: Where can I get the script

Answered: give me a bit but I will have them BOTH with the webcast (on msdn) as well as on my website (www.SQLskills.com, current events - soon-to-be past events :)


Bill Walter Asked: Is there a tool or method for monitoring a database for a while and identifying indexes that are rairly or never used that should be considered for removing?

Answered: ITW helps with that and in SQL 2005 ITW is being replaced by DTA (Database Tuning Advisor)... the DTA will even have an ability to do ONLY this check!


Adam Wiedenhaefer Asked: When a statistic exists on some of my client tables, if forces the optimizer to us an index that is less benificial (even after we update stats with full scan) We've had to force the index to use. Becuase of this, we disable "AUTO CREATE" and create indexes when performance issues arise and are needed... granted this isn't the best method but when tables become large over time we find out that some queries work fine until then :-)


Daniel DBA Asked: Right but a fill factor that is higher would create less pages and improve reads and a lower fill factor would improve writes. So if that is true then is there a set point that should be watched for when setting a fill factor?


Max Ustinov Changes Question To: The BPA is on the front page http://www.microsoft.com/sql


Max Ustinov Changes Question To: The BPA is on the front page http://www.microsoft.com/sql


Adam Wiedenhaefer Asked: When a statistic exists on some of my client tables, if forces the optimizer to us an index that is less benificial (even after we update stats with full scan) We've had to force the index to use. Becuase of this, we disable "AUTO CREATE" and create indexes when performance issues arise and are needed... granted this isn't the best method but when tables become large over time we find out that some queries work fine until then :-)

Answered: sounds like you might be hitting some bugs... OR - maybe the indexes that exist are not "perfect" for the query so it's not doing as good of a job as it could... I have a class where I get into more details on statistics (a whole module on just statistics :) that explains a lot more details... It's hard to sum up in a sentence or two BUT I would guess that a bit more tuning (i.e. more indexes) might help solve the problem... Really, hard to say in a quick chat but that really shouldn't be the majority of the time.


Max Ustinov Changes Question To: The BPA is on the front page http://www.microsoft.com/sql Talking about DTA in SQL2005, when do you think it will come out?


Nassif Asked: can the presentation(content) be posted or emailed by request?

Answered: It will be posted on msdn within 72 hours!


Melanie Oliveros Asked: Does SHOWCONTIG keep a table lock?

Answered: ah ha... I will put more details into a q&a but - yes - it does a scan so it can create some blocking... there is a trick to minimize this though. Check out the WITH FAST option on DBCC SHOWCONTIG. This will ONLY do a scan (returning less data) but it's a pretty good one!


Adam Wiedenhaefer Asked: Thanks for your feedback! See you at PASS! :-D


Adam Wiedenhaefer Asked: Thanks for your feedback! See you at PASS! :-D

Answered: Cool!


Emile Deen Asked: When you say volitile clustering key, do you mean the data or the column being changable?

Answered: yes!


arunma@microsoft.com Asked: Sure, no problems. I saw that people asked that a few time. And hi to you as well ... :-)

Answered: Thanks! See you sometime soon!


pchang Asked: Thank you. I also attended several of your session in San Diego TechEd, excellent work!


Max Ustinov Asked: The BPA is on the front page http://www.microsoft.com/sql Talking about DTA in SQL2005, when do you think it will come out?

Answered: well... with SQL Server really, not before (if that's what you're wondering)... The expectation for Yukon is mid-next year!


arunma@microsoft.com Asked: Kim: More stats can "hurt" in the following way. The optimizer has to load more stats when compiling a query and then has to figure out which one to base card estimation on. So query compilation time increases. But usually, stats don't hurt perf. -Arun


Daniel DBA Asked: Right but a fill factor that is higher would create less pages and improve reads and a lower fill factor would improve writes. So if that is true then is there a set point that should be watched for when setting a fill factor?

Answered: ah ha... see that's a great question. What I usually look for is the frequency with which you can rebuild and the rate at which your table becomes fragmented between rebuilds. The more fragmented the lower the fillfactor... the more continguous the higerh the fillfactor... so over time you can find the most optimal setting... but it does take a bit of work!


pchang Asked: Thank you. I also attended several of your session in San Diego TechEd, excellent work!

Answered: Thanks! See you again!


arunma@microsoft.com Asked: Kim: More stats can "hurt" in the following way. The optimizer has to load more stats when compiling a query and then has to figure out which one to base card estimation on. So query compilation time increases. But usually, stats don't hurt perf. -Arun

Answered: Good point - I'll make sure to add this to my other statement. I'm wondering if they really have that many... apparently though?! THANKS!!!


Daniel DBA Asked: You said in the power point slides that generally a few wide indexes are better than several narrow; However, later you said that one narrow clustered index on an id column + non clustered indexes is the way to go. So which is it? Or did I not really understand?


arunma@microsoft.com Asked: Ok, I am logging off now. Great presentation, thanks! Hope to see you some time soon. -Arun

(Print or Save this page, or copy the page contents to the clipboard and paste it into a file on your computer.)