| Groups | Blog | Home |
sql server programming : indexes - best practices
We had a database that we upgraded to SQL2005. This is a highly transactional database and is about 20GB in size with about 200,000 rows in many tables. Our app is seeing serious performance issues due to queries and stored procs that take a long time to run.I am presuming these are lack of proper indexes. What is the best way to tune this database? Also, I had an elementary questions on indexes. If I have a foreign key in a table to another table. Will I still need to put an index on that column? TIA!b
inline...
[quoted text, click to view] <param@community.nospam> wrote in message news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... > Hi all, > > We had a database that we upgraded to SQL2005. This is a highly > transactional database and is about 20GB in size with about 200,000 rows > in many tables. Our app is seeing serious performance issues due to > queries and stored procs that take a long time to run.I am presuming these > are lack of proper indexes. What is the best way to tune this database? Have you updated statistics or rebuilt indexes after the upgrade? If not, it is fundamentally important to do this before you do anything else. [quoted text, click to view] > > Also, I had an elementary questions on indexes. If I have a foreign key in > a table to another table. Will I still need to put an index on that > column? Yes - otherwise during inserts, SQL Server will have no other option than to scan the foreign key entirely to determine whether a related row exists in the "parent" table. With an index, SQL Server only needs to seek, which is considerably less overhead. HTH Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood [quoted text, click to view] > > TIA!b >
Hi
More info http://www.sql-server-performance.com/ [quoted text, click to view] <param@community.nospam> wrote in message news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... > Hi all, > > We had a database that we upgraded to SQL2005. This is a highly > transactional database and is about 20GB in size with about 200,000 rows > in many tables. Our app is seeing serious performance issues due to > queries and stored procs that take a long time to run.I am presuming these > are lack of proper indexes. What is the best way to tune this database? > > Also, I had an elementary questions on indexes. If I have a foreign key in > a table to another table. Will I still need to put an index on that > column? > > TIA!b >
Hello,
I agree with Greg's suggestion. Also, you could try to use the Database Engine Tuning Advisor to tune whether an index is needed on your databases. Here is an article for your reference: Database Engine Tuning Advisor Tutorial http://msdn2.microsoft.com/en-us/library/ms166575.aspx Hope this will be helpful! Sincerely, Wei Lu Microsoft Online Community Support ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ================================================== (This posting is provided "AS IS", with no warranties, and confers no rights.)
No, I did not update the statistics or rebuild the indexes. Can I do that
while the database is running in production? [quoted text, click to view] "Greg Linwood" <g_linwood@hotmail.com> wrote in message news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... > inline... > > <param@community.nospam> wrote in message > news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >> Hi all, >> >> We had a database that we upgraded to SQL2005. This is a highly >> transactional database and is about 20GB in size with about 200,000 rows >> in many tables. Our app is seeing serious performance issues due to >> queries and stored procs that take a long time to run.I am presuming >> these are lack of proper indexes. What is the best way to tune this >> database? > > Have you updated statistics or rebuilt indexes after the upgrade? If not, > it is fundamentally important to do this before you do anything else. > >> >> Also, I had an elementary questions on indexes. If I have a foreign key >> in a table to another table. Will I still need to put an index on that >> column? > > Yes - otherwise during inserts, SQL Server will have no other option than > to scan the foreign key entirely to determine whether a related row exists > in the "parent" table. With an index, SQL Server only needs to seek, which > is considerably less overhead. > > HTH > > Regards, > Greg Linwood > SQL Server MVP > http://blogs.sqlserver.org.au/blogs/greg_linwood > >> >> TIA!b >> > >
So, in regards to your suggestion on adding an index on the FK column, will
that only benefit inserts/updates or will it benefit select statements as well? [quoted text, click to view] "Greg Linwood" <g_linwood@hotmail.com> wrote in message news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... > inline... > > <param@community.nospam> wrote in message > news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >> Hi all, >> >> We had a database that we upgraded to SQL2005. This is a highly >> transactional database and is about 20GB in size with about 200,000 rows >> in many tables. Our app is seeing serious performance issues due to >> queries and stored procs that take a long time to run.I am presuming >> these are lack of proper indexes. What is the best way to tune this >> database? > > Have you updated statistics or rebuilt indexes after the upgrade? If not, > it is fundamentally important to do this before you do anything else. > >> >> Also, I had an elementary questions on indexes. If I have a foreign key >> in a table to another table. Will I still need to put an index on that >> column? > > Yes - otherwise during inserts, SQL Server will have no other option than > to scan the foreign key entirely to determine whether a related row exists > in the "parent" table. With an index, SQL Server only needs to seek, which > is considerably less overhead. > > HTH > > Regards, > Greg Linwood > SQL Server MVP > http://blogs.sqlserver.org.au/blogs/greg_linwood > >> >> TIA!b >> > >
I am presuming this recommendation of adding an index on a column that is
also a foreign key applies to both 2000 and 2005? TIA! "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... [quoted text, click to view] > It will give the optimizer more reasonably costing ways (nested loop) of > doing joins (assuming you do join over these columns). whether the > optimizer find that those are better than the way it currently does > (possibly hash), you will find out by looking at execution time and the > execution plans. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > <param@community.nospam> wrote in message > news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >> So, in regards to your suggestion on adding an index on the FK column, >> will that only benefit inserts/updates or will it benefit select >> statements as well? >> >> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>> inline... >>> >>> <param@community.nospam> wrote in message >>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>> Hi all, >>>> >>>> We had a database that we upgraded to SQL2005. This is a highly >>>> transactional database and is about 20GB in size with about 200,000 >>>> rows in many tables. Our app is seeing serious performance issues due >>>> to queries and stored procs that take a long time to run.I am presuming >>>> these are lack of proper indexes. What is the best way to tune this >>>> database? >>> >>> Have you updated statistics or rebuilt indexes after the upgrade? If >>> not, it is fundamentally important to do this before you do anything >>> else. >>> >>>> >>>> Also, I had an elementary questions on indexes. If I have a foreign key >>>> in a table to another table. Will I still need to put an index on that >>>> column? >>> >>> Yes - otherwise during inserts, SQL Server will have no other option >>> than to scan the foreign key entirely to determine whether a related row >>> exists in the "parent" table. With an index, SQL Server only needs to >>> seek, which is considerably less overhead. >>> >>> HTH >>> >>> Regards, >>> Greg Linwood >>> SQL Server MVP >>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>> >>>> >>>> TIA!b >>>> >>> >>> >> >> >
Normally I have a IDENTITY Column as the PK of the table and make that the
CLUSTERED Index. Is that not advisable? "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... [quoted text, click to view] > Yep. Applies of all versions (so far) of SQL Server. In fact, clustering > over such a column can be beneficial. But there are many many other > factors to weigh in when considering what to cluster on. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > <param@community.nospam> wrote in message > news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>I am presuming this recommendation of adding an index on a column that is >>also a foreign key applies to both 2000 and 2005? >> >> TIA! >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >> in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>> It will give the optimizer more reasonably costing ways (nested loop) of >>> doing joins (assuming you do join over these columns). whether the >>> optimizer find that those are better than the way it currently does >>> (possibly hash), you will find out by looking at execution time and the >>> execution plans. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> >>> >>> <param@community.nospam> wrote in message >>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>> So, in regards to your suggestion on adding an index on the FK column, >>>> will that only benefit inserts/updates or will it benefit select >>>> statements as well? >>>> >>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>> inline... >>>>> >>>>> <param@community.nospam> wrote in message >>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>> Hi all, >>>>>> >>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>> transactional database and is about 20GB in size with about 200,000 >>>>>> rows in many tables. Our app is seeing serious performance issues due >>>>>> to queries and stored procs that take a long time to run.I am >>>>>> presuming these are lack of proper indexes. What is the best way to >>>>>> tune this database? >>>>> >>>>> Have you updated statistics or rebuilt indexes after the upgrade? If >>>>> not, it is fundamentally important to do this before you do anything >>>>> else. >>>>> >>>>>> >>>>>> Also, I had an elementary questions on indexes. If I have a foreign >>>>>> key in a table to another table. Will I still need to put an index on >>>>>> that column? >>>>> >>>>> Yes - otherwise during inserts, SQL Server will have no other option >>>>> than to scan the foreign key entirely to determine whether a related >>>>> row exists in the "parent" table. With an index, SQL Server only needs >>>>> to seek, which is considerably less overhead. >>>>> >>>>> HTH >>>>> >>>>> Regards, >>>>> Greg Linwood >>>>> SQL Server MVP >>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>> >>>>>> >>>>>> TIA!b >>>>>> >>>>> >>>>> >>>> >>>> >>> >> >> >
It will give the optimizer more reasonably costing ways (nested loop) of doing joins (assuming you
do join over these columns). whether the optimizer find that those are better than the way it currently does (possibly hash), you will find out by looking at execution time and the execution plans. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] <param@community.nospam> wrote in message news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl...
> So, in regards to your suggestion on adding an index on the FK column, will that only benefit > inserts/updates or will it benefit select statements as well? > > "Greg Linwood" <g_linwood@hotmail.com> wrote in message > news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >> inline... >> >> <param@community.nospam> wrote in message news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>> Hi all, >>> >>> We had a database that we upgraded to SQL2005. This is a highly transactional database and is >>> about 20GB in size with about 200,000 rows in many tables. Our app is seeing serious performance >>> issues due to queries and stored procs that take a long time to run.I am presuming these are >>> lack of proper indexes. What is the best way to tune this database? >> >> Have you updated statistics or rebuilt indexes after the upgrade? If not, it is fundamentally >> important to do this before you do anything else. >> >>> >>> Also, I had an elementary questions on indexes. If I have a foreign key in a table to another >>> table. Will I still need to put an index on that column? >> >> Yes - otherwise during inserts, SQL Server will have no other option than to scan the foreign key >> entirely to determine whether a related row exists in the "parent" table. With an index, SQL >> Server only needs to seek, which is considerably less overhead. >> >> HTH >> >> Regards, >> Greg Linwood >> SQL Server MVP >> http://blogs.sqlserver.org.au/blogs/greg_linwood >> >>> >>> TIA!b >>> >> >> > >
Yep. Applies of all versions (so far) of SQL Server. In fact, clustering over such a column can be
beneficial. But there are many many other factors to weigh in when considering what to cluster on. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] <param@community.nospam> wrote in message news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl...
>I am presuming this recommendation of adding an index on a column that is also a foreign key >applies to both 2000 and 2005? > > TIA! > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message > news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >> It will give the optimizer more reasonably costing ways (nested loop) of doing joins (assuming >> you do join over these columns). whether the optimizer find that those are better than the way it >> currently does (possibly hash), you will find out by looking at execution time and the execution >> plans. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> <param@community.nospam> wrote in message news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>> So, in regards to your suggestion on adding an index on the FK column, will that only benefit >>> inserts/updates or will it benefit select statements as well? >>> >>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>> inline... >>>> >>>> <param@community.nospam> wrote in message news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>> Hi all, >>>>> >>>>> We had a database that we upgraded to SQL2005. This is a highly transactional database and is >>>>> about 20GB in size with about 200,000 rows in many tables. Our app is seeing serious >>>>> performance issues due to queries and stored procs that take a long time to run.I am presuming >>>>> these are lack of proper indexes. What is the best way to tune this database? >>>> >>>> Have you updated statistics or rebuilt indexes after the upgrade? If not, it is fundamentally >>>> important to do this before you do anything else. >>>> >>>>> >>>>> Also, I had an elementary questions on indexes. If I have a foreign key in a table to another >>>>> table. Will I still need to put an index on that column? >>>> >>>> Yes - otherwise during inserts, SQL Server will have no other option than to scan the foreign >>>> key entirely to determine whether a related row exists in the "parent" table. With an index, >>>> SQL Server only needs to seek, which is considerably less overhead. >>>> >>>> HTH >>>> >>>> Regards, >>>> Greg Linwood >>>> SQL Server MVP >>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>> >>>>> >>>>> TIA!b >>>>> >>>> >>>> >>> >>> >> > >
[quoted text, click to view]
> Normally I have a IDENTITY Column as the PK of the table and make that the CLUSTERED Index. Is > that not advisable? Whoa, you asked two widely debated questions here. Don't expect simple answers, but do expect very different opinions. I suggest you Google on the subject and prepare for some reading. Regarding identity, you might want to search for things like "surrogate key vs. natural key". this is more of a data modeling/theoretical question. As for what you want to cluster over, that is a more physical level question. Clustering over the PK is default, so many people leave like that. However, the cl is very beneficial for search conditions with not so high selectivity (compared to nc indexes) and searches against the PK tend to be an equal search against something which you cannot have duplicates for (max 1 rows returned). But you also need to weigh in fragmentation aspect. And, also consider the option to create covering non-clustered indexes (which essentially work as a clustered index in the cases there that nc index covers a query). So, two big question. don't expect simple answers. The one about what to cluster over is easier as you can always try different schemes and test. It is not that simple with surrogate vs. natural key. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] <param@community.nospam> wrote in message news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl...
> Normally I have a IDENTITY Column as the PK of the table and make that the CLUSTERED Index. Is > that not advisable? > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message > news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >> Yep. Applies of all versions (so far) of SQL Server. In fact, clustering over such a column can >> be beneficial. But there are many many other factors to weigh in when considering what to cluster >> on. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> <param@community.nospam> wrote in message news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>I am presuming this recommendation of adding an index on a column that is also a foreign key >>>applies to both 2000 and 2005? >>> >>> TIA! >>> >>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message >>> news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>> It will give the optimizer more reasonably costing ways (nested loop) of doing joins (assuming >>>> you do join over these columns). whether the optimizer find that those are better than the way >>>> it currently does (possibly hash), you will find out by looking at execution time and the >>>> execution plans. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://www.solidqualitylearning.com/ >>>> >>>> >>>> <param@community.nospam> wrote in message news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>> So, in regards to your suggestion on adding an index on the FK column, will that only benefit >>>>> inserts/updates or will it benefit select statements as well? >>>>> >>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>> inline... >>>>>> >>>>>> <param@community.nospam> wrote in message news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>> Hi all, >>>>>>> >>>>>>> We had a database that we upgraded to SQL2005. This is a highly transactional database and >>>>>>> is about 20GB in size with about 200,000 rows in many tables. Our app is seeing serious >>>>>>> performance issues due to queries and stored procs that take a long time to run.I am >>>>>>> presuming these are lack of proper indexes. What is the best way to tune this database? >>>>>> >>>>>> Have you updated statistics or rebuilt indexes after the upgrade? If not, it is fundamentally >>>>>> important to do this before you do anything else. >>>>>> >>>>>>> >>>>>>> Also, I had an elementary questions on indexes. If I have a foreign key in a table to >>>>>>> another table. Will I still need to put an index on that column? >>>>>> >>>>>> Yes - otherwise during inserts, SQL Server will have no other option than to scan the foreign >>>>>> key entirely to determine whether a related row exists in the "parent" table. With an index, >>>>>> SQL Server only needs to seek, which is considerably less overhead. >>>>>> >>>>>> HTH >>>>>> >>>>>> Regards, >>>>>> Greg Linwood >>>>>> SQL Server MVP >>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>> >>>>>>> >>>>>>> TIA!b >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> > >
Re the CIX qn, I have been blogging some thoughts about this recently here
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx I also presented a session on how indexes work & managing them at the Australian SQL Server User Group in Melbourne a few days ago. You might find the slides useful: http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood [quoted text, click to view] <param@community.nospam> wrote in message news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... > Normally I have a IDENTITY Column as the PK of the table and make that the > CLUSTERED Index. Is that not advisable? > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >> Yep. Applies of all versions (so far) of SQL Server. In fact, clustering >> over such a column can be beneficial. But there are many many other >> factors to weigh in when considering what to cluster on. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> <param@community.nospam> wrote in message >> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>I am presuming this recommendation of adding an index on a column that is >>>also a foreign key applies to both 2000 and 2005? >>> >>> TIA! >>> >>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >>> in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>> It will give the optimizer more reasonably costing ways (nested loop) >>>> of doing joins (assuming you do join over these columns). whether the >>>> optimizer find that those are better than the way it currently does >>>> (possibly hash), you will find out by looking at execution time and the >>>> execution plans. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://www.solidqualitylearning.com/ >>>> >>>> >>>> <param@community.nospam> wrote in message >>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>> So, in regards to your suggestion on adding an index on the FK column, >>>>> will that only benefit inserts/updates or will it benefit select >>>>> statements as well? >>>>> >>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>> inline... >>>>>> >>>>>> <param@community.nospam> wrote in message >>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>> Hi all, >>>>>>> >>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>> transactional database and is about 20GB in size with about 200,000 >>>>>>> rows in many tables. Our app is seeing serious performance issues >>>>>>> due to queries and stored procs that take a long time to run.I am >>>>>>> presuming these are lack of proper indexes. What is the best way to >>>>>>> tune this database? >>>>>> >>>>>> Have you updated statistics or rebuilt indexes after the upgrade? If >>>>>> not, it is fundamentally important to do this before you do anything >>>>>> else. >>>>>> >>>>>>> >>>>>>> Also, I had an elementary questions on indexes. If I have a foreign >>>>>>> key in a table to another table. Will I still need to put an index >>>>>>> on that column? >>>>>> >>>>>> Yes - otherwise during inserts, SQL Server will have no other option >>>>>> than to scan the foreign key entirely to determine whether a related >>>>>> row exists in the "parent" table. With an index, SQL Server only >>>>>> needs to seek, which is considerably less overhead. >>>>>> >>>>>> HTH >>>>>> >>>>>> Regards, >>>>>> Greg Linwood >>>>>> SQL Server MVP >>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>> >>>>>>> >>>>>>> TIA!b >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> > >
I read your series of articles on clustered indexes and I could not
agree more. Just wanted to add that to my best knowledge SQL Server does not collect clustering factor statistics, while both DB2 and Oracle certainly do. Because of that SQL Server optimizer uses somewhat simplistic estimates and may miss the target altogether. Suppose you insert into a heap table and rows with the same EntryDate are very likely to be adjacent, on the same page. SQL Server optimizer will not know about it, while Oracle's optimizer will recognize and utilize this fact, coming up with a more efficient plan. ----------------------- Alex Kuznetsov http://sqlserver-tips.blogspot.com/ http://sqlserver-puzzles.blogspot.com/
These are not exactly the same, but very similar.
The index with fname as an included column will not have fname in any of the upper levels of the index, it will only be in the leaf. In addition, there will be no sorting by fname, in the event of duplicate lname values. -- HTH Kalen Delaney, SQL Server MVP http://sqlblog.com [quoted text, click to view] <param@community.nospam> wrote in message news:O1SKCTv7GHA.3828@TK2MSFTNGP03.phx.gbl... > So I noticed that your presentation recommends Covering Indexes. I also > noticed that in the Database Tuning Engine Advisor it recommends indexes > that have an include clause.So is:- > > create nonclustered index ix_index on dbo.customers (lname, fname) > > the same as > > create nonclustered index ix_index on dbo.customers (lname) include > (fname) > > ? > > TIA! > > "Greg Linwood" <g_linwood@hotmail.com> wrote in message > news:em8CjUn7GHA.788@TK2MSFTNGP05.phx.gbl... >> Re the CIX qn, I have been blogging some thoughts about this recently >> here >> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx >> >> I also presented a session on how indexes work & managing them at the >> Australian SQL Server User Group in Melbourne a few days ago. You might >> find the slides useful: >> http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ >> >> Regards, >> Greg Linwood >> SQL Server MVP >> http://blogs.sqlserver.org.au/blogs/greg_linwood >> >> <param@community.nospam> wrote in message >> news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... >>> Normally I have a IDENTITY Column as the PK of the table and make that >>> the CLUSTERED Index. Is that not advisable? >>> >>> >>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >>> in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >>>> Yep. Applies of all versions (so far) of SQL Server. In fact, >>>> clustering over such a column can be beneficial. But there are many >>>> many other factors to weigh in when considering what to cluster on. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://www.solidqualitylearning.com/ >>>> >>>> >>>> <param@community.nospam> wrote in message >>>> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>>>I am presuming this recommendation of adding an index on a column that >>>>>is also a foreign key applies to both 2000 and 2005? >>>>> >>>>> TIA! >>>>> >>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>>>> It will give the optimizer more reasonably costing ways (nested loop) >>>>>> of doing joins (assuming you do join over these columns). whether the >>>>>> optimizer find that those are better than the way it currently does >>>>>> (possibly hash), you will find out by looking at execution time and >>>>>> the execution plans. >>>>>> >>>>>> -- >>>>>> Tibor Karaszi, SQL Server MVP >>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>> http://www.solidqualitylearning.com/ >>>>>> >>>>>> >>>>>> <param@community.nospam> wrote in message >>>>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>>>> So, in regards to your suggestion on adding an index on the FK >>>>>>> column, will that only benefit inserts/updates or will it benefit >>>>>>> select statements as well? >>>>>>> >>>>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>>>> inline... >>>>>>>> >>>>>>>> <param@community.nospam> wrote in message >>>>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>>>> Hi all, >>>>>>>>> >>>>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>>>> transactional database and is about 20GB in size with about >>>>>>>>> 200,000 rows in many tables. Our app is seeing serious performance >>>>>>>>> issues due to queries and stored procs that take a long time to >>>>>>>>> run.I am presuming these are lack of proper indexes. What is the >>>>>>>>> best way to tune this database? >>>>>>>> >>>>>>>> Have you updated statistics or rebuilt indexes after the upgrade? >>>>>>>> If not, it is fundamentally important to do this before you do >>>>>>>> anything else. >>>>>>>> >>>>>>>>> >>>>>>>>> Also, I had an elementary questions on indexes. If I have a >>>>>>>>> foreign key in a table to another table. Will I still need to put >>>>>>>>> an index on that column? >>>>>>>> >>>>>>>> Yes - otherwise during inserts, SQL Server will have no other >>>>>>>> option than to scan the foreign key entirely to determine whether a >>>>>>>> related row exists in the "parent" table. With an index, SQL Server >>>>>>>> only needs to seek, which is considerably less overhead. >>>>>>>> >>>>>>>> HTH >>>>>>>> >>>>>>>> Regards, >>>>>>>> Greg Linwood >>>>>>>> SQL Server MVP >>>>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>>>> >>>>>>>>> >>>>>>>>> TIA!b >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> >> > >
Thanks Mate!
[quoted text, click to view] "Greg Linwood" <g_linwood@hotmail.com> wrote in message news:em8CjUn7GHA.788@TK2MSFTNGP05.phx.gbl... > Re the CIX qn, I have been blogging some thoughts about this recently here > http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx > > I also presented a session on how indexes work & managing them at the > Australian SQL Server User Group in Melbourne a few days ago. You might > find the slides useful: > http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ > > Regards, > Greg Linwood > SQL Server MVP > http://blogs.sqlserver.org.au/blogs/greg_linwood > > <param@community.nospam> wrote in message > news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... >> Normally I have a IDENTITY Column as the PK of the table and make that >> the CLUSTERED Index. Is that not advisable? >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >> in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >>> Yep. Applies of all versions (so far) of SQL Server. In fact, clustering >>> over such a column can be beneficial. But there are many many other >>> factors to weigh in when considering what to cluster on. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> >>> >>> <param@community.nospam> wrote in message >>> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>>I am presuming this recommendation of adding an index on a column that >>>>is also a foreign key applies to both 2000 and 2005? >>>> >>>> TIA! >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>>> It will give the optimizer more reasonably costing ways (nested loop) >>>>> of doing joins (assuming you do join over these columns). whether the >>>>> optimizer find that those are better than the way it currently does >>>>> (possibly hash), you will find out by looking at execution time and >>>>> the execution plans. >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://www.solidqualitylearning.com/ >>>>> >>>>> >>>>> <param@community.nospam> wrote in message >>>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>>> So, in regards to your suggestion on adding an index on the FK >>>>>> column, will that only benefit inserts/updates or will it benefit >>>>>> select statements as well? >>>>>> >>>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>>> inline... >>>>>>> >>>>>>> <param@community.nospam> wrote in message >>>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>>> Hi all, >>>>>>>> >>>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>>> transactional database and is about 20GB in size with about 200,000 >>>>>>>> rows in many tables. Our app is seeing serious performance issues >>>>>>>> due to queries and stored procs that take a long time to run.I am >>>>>>>> presuming these are lack of proper indexes. What is the best way to >>>>>>>> tune this database? >>>>>>> >>>>>>> Have you updated statistics or rebuilt indexes after the upgrade? If >>>>>>> not, it is fundamentally important to do this before you do anything >>>>>>> else. >>>>>>> >>>>>>>> >>>>>>>> Also, I had an elementary questions on indexes. If I have a foreign >>>>>>>> key in a table to another table. Will I still need to put an index >>>>>>>> on that column? >>>>>>> >>>>>>> Yes - otherwise during inserts, SQL Server will have no other option >>>>>>> than to scan the foreign key entirely to determine whether a related >>>>>>> row exists in the "parent" table. With an index, SQL Server only >>>>>>> needs to seek, which is considerably less overhead. >>>>>>> >>>>>>> HTH >>>>>>> >>>>>>> Regards, >>>>>>> Greg Linwood >>>>>>> SQL Server MVP >>>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>>> >>>>>>>> >>>>>>>> TIA!b >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> > >
So I noticed that your presentation recommends Covering Indexes. I also
noticed that in the Database Tuning Engine Advisor it recommends indexes that have an include clause.So is:- create nonclustered index ix_index on dbo.customers (lname, fname) the same as create nonclustered index ix_index on dbo.customers (lname) include (fname) ? TIA! [quoted text, click to view] "Greg Linwood" <g_linwood@hotmail.com> wrote in message news:em8CjUn7GHA.788@TK2MSFTNGP05.phx.gbl... > Re the CIX qn, I have been blogging some thoughts about this recently here > http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx > > I also presented a session on how indexes work & managing them at the > Australian SQL Server User Group in Melbourne a few days ago. You might > find the slides useful: > http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ > > Regards, > Greg Linwood > SQL Server MVP > http://blogs.sqlserver.org.au/blogs/greg_linwood > > <param@community.nospam> wrote in message > news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... >> Normally I have a IDENTITY Column as the PK of the table and make that >> the CLUSTERED Index. Is that not advisable? >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >> in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >>> Yep. Applies of all versions (so far) of SQL Server. In fact, clustering >>> over such a column can be beneficial. But there are many many other >>> factors to weigh in when considering what to cluster on. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> >>> >>> <param@community.nospam> wrote in message >>> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>>I am presuming this recommendation of adding an index on a column that >>>>is also a foreign key applies to both 2000 and 2005? >>>> >>>> TIA! >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>>> It will give the optimizer more reasonably costing ways (nested loop) >>>>> of doing joins (assuming you do join over these columns). whether the >>>>> optimizer find that those are better than the way it currently does >>>>> (possibly hash), you will find out by looking at execution time and >>>>> the execution plans. >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://www.solidqualitylearning.com/ >>>>> >>>>> >>>>> <param@community.nospam> wrote in message >>>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>>> So, in regards to your suggestion on adding an index on the FK >>>>>> column, will that only benefit inserts/updates or will it benefit >>>>>> select statements as well? >>>>>> >>>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>>> inline... >>>>>>> >>>>>>> <param@community.nospam> wrote in message >>>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>>> Hi all, >>>>>>>> >>>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>>> transactional database and is about 20GB in size with about 200,000 >>>>>>>> rows in many tables. Our app is seeing serious performance issues >>>>>>>> due to queries and stored procs that take a long time to run.I am >>>>>>>> presuming these are lack of proper indexes. What is the best way to >>>>>>>> tune this database? >>>>>>> >>>>>>> Have you updated statistics or rebuilt indexes after the upgrade? If >>>>>>> not, it is fundamentally important to do this before you do anything >>>>>>> else. >>>>>>> >>>>>>>> >>>>>>>> Also, I had an elementary questions on indexes. If I have a foreign >>>>>>>> key in a table to another table. Will I still need to put an index >>>>>>>> on that column? >>>>>>> >>>>>>> Yes - otherwise during inserts, SQL Server will have no other option >>>>>>> than to scan the foreign key entirely to determine whether a related >>>>>>> row exists in the "parent" table. With an index, SQL Server only >>>>>>> needs to seek, which is considerably less overhead. >>>>>>> >>>>>>> HTH >>>>>>> >>>>>>> Regards, >>>>>>> Greg Linwood >>>>>>> SQL Server MVP >>>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>>> >>>>>>>> >>>>>>>> TIA!b >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> > >
They can both be both covering indexes; in fact, the motivation for
introducing the included columns was to allow more indexes to be covering indexes, and include columns that would be too wide to fit into the key columns of a index. -- HTH Kalen Delaney, SQL Server MVP http://sqlblog.com [quoted text, click to view] <param@community.nospam> wrote in message news:ulCgpFx7GHA.4552@TK2MSFTNGP05.phx.gbl... > So for Covering Indexes it is recommended to use the first command and not > the second command correct? > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:%23mATGnv7GHA.140@TK2MSFTNGP05.phx.gbl... >> These are not exactly the same, but very similar. >> The index with fname as an included column will not have fname in any of >> the upper levels of the index, it will only be in the leaf. In addition, >> there will be no sorting by fname, in the event of duplicate lname >> values. >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> http://sqlblog.com >> >> >> <param@community.nospam> wrote in message >> news:O1SKCTv7GHA.3828@TK2MSFTNGP03.phx.gbl... >>> So I noticed that your presentation recommends Covering Indexes. I also >>> noticed that in the Database Tuning Engine Advisor it recommends indexes >>> that have an include clause.So is:- >>> >>> create nonclustered index ix_index on dbo.customers (lname, fname) >>> >>> the same as >>> >>> create nonclustered index ix_index on dbo.customers (lname) include >>> (fname) >>> >>> ? >>> >>> TIA! >>> >>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>> news:em8CjUn7GHA.788@TK2MSFTNGP05.phx.gbl... >>>> Re the CIX qn, I have been blogging some thoughts about this recently >>>> here >>>> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx >>>> >>>> I also presented a session on how indexes work & managing them at the >>>> Australian SQL Server User Group in Melbourne a few days ago. You might >>>> find the slides useful: >>>> http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ >>>> >>>> Regards, >>>> Greg Linwood >>>> SQL Server MVP >>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>> >>>> <param@community.nospam> wrote in message >>>> news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... >>>>> Normally I have a IDENTITY Column as the PK of the table and make that >>>>> the CLUSTERED Index. Is that not advisable? >>>>> >>>>> >>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>> wrote in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >>>>>> Yep. Applies of all versions (so far) of SQL Server. In fact, >>>>>> clustering over such a column can be beneficial. But there are many >>>>>> many other factors to weigh in when considering what to cluster on. >>>>>> >>>>>> -- >>>>>> Tibor Karaszi, SQL Server MVP >>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>> http://www.solidqualitylearning.com/ >>>>>> >>>>>> >>>>>> <param@community.nospam> wrote in message >>>>>> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>>>>>I am presuming this recommendation of adding an index on a column >>>>>>>that is also a foreign key applies to both 2000 and 2005? >>>>>>> >>>>>>> TIA! >>>>>>> >>>>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>>>> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>>>>>> It will give the optimizer more reasonably costing ways (nested >>>>>>>> loop) of doing joins (assuming you do join over these columns). >>>>>>>> whether the optimizer find that those are better than the way it >>>>>>>> currently does (possibly hash), you will find out by looking at >>>>>>>> execution time and the execution plans. >>>>>>>> >>>>>>>> -- >>>>>>>> Tibor Karaszi, SQL Server MVP >>>>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>>>> http://www.solidqualitylearning.com/ >>>>>>>> >>>>>>>> >>>>>>>> <param@community.nospam> wrote in message >>>>>>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>>>>>> So, in regards to your suggestion on adding an index on the FK >>>>>>>>> column, will that only benefit inserts/updates or will it benefit >>>>>>>>> select statements as well? >>>>>>>>> >>>>>>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>>>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>>>>>> inline... >>>>>>>>>> >>>>>>>>>> <param@community.nospam> wrote in message >>>>>>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>>>>>> Hi all, >>>>>>>>>>> >>>>>>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>>>>>> transactional database and is about 20GB in size with about >>>>>>>>>>> 200,000 rows in many tables. Our app is seeing serious >>>>>>>>>>> performance issues due to queries and stored procs that take a >>>>>>>>>>> long time to run.I am presuming these are lack of proper >>>>>>>>>>> indexes. What is the best way to tune this database? >>>>>>>>>> >>>>>>>>>> Have you updated statistics or rebuilt indexes after the upgrade? >>>>>>>>>> If not, it is fundamentally important to do this before you do >>>>>>>>>> anything else. >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Also, I had an elementary questions on indexes. If I have a >>>>>>>>>>> foreign key in a table to another table. Will I still need to >>>>>>>>>>> put an index on that column? >>>>>>>>>> >>>>>>>>>> Yes - otherwise during inserts, SQL Server will have no other >>>>>>>>>> option than to scan the foreign key entirely to determine whether >>>>>>>>>> a related row exists in the "parent" table. With an index, SQL >>>>>>>>>> Server only needs to seek, which is considerably less overhead. >>>>>>>>>> >>>>>>>>>> HTH >>>>>>>>>> >>>>>>>>>> Regards, >>>>>>>>>> Greg Linwood >>>>>>>>>> SQL Server MVP >>>>>>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> TIA!b >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
So for Covering Indexes it is recommended to use the first command and not
the second command correct? [quoted text, click to view] "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:%23mATGnv7GHA.140@TK2MSFTNGP05.phx.gbl... > These are not exactly the same, but very similar. > The index with fname as an included column will not have fname in any of > the upper levels of the index, it will only be in the leaf. In addition, > there will be no sorting by fname, in the event of duplicate lname values. > > -- > HTH > Kalen Delaney, SQL Server MVP > http://sqlblog.com > > > <param@community.nospam> wrote in message > news:O1SKCTv7GHA.3828@TK2MSFTNGP03.phx.gbl... >> So I noticed that your presentation recommends Covering Indexes. I also >> noticed that in the Database Tuning Engine Advisor it recommends indexes >> that have an include clause.So is:- >> >> create nonclustered index ix_index on dbo.customers (lname, fname) >> >> the same as >> >> create nonclustered index ix_index on dbo.customers (lname) include >> (fname) >> >> ? >> >> TIA! >> >> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >> news:em8CjUn7GHA.788@TK2MSFTNGP05.phx.gbl... >>> Re the CIX qn, I have been blogging some thoughts about this recently >>> here >>> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx >>> >>> I also presented a session on how indexes work & managing them at the >>> Australian SQL Server User Group in Melbourne a few days ago. You might >>> find the slides useful: >>> http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ >>> >>> Regards, >>> Greg Linwood >>> SQL Server MVP >>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>> >>> <param@community.nospam> wrote in message >>> news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... >>>> Normally I have a IDENTITY Column as the PK of the table and make that >>>> the CLUSTERED Index. Is that not advisable? >>>> >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>> wrote in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >>>>> Yep. Applies of all versions (so far) of SQL Server. In fact, >>>>> clustering over such a column can be beneficial. But there are many >>>>> many other factors to weigh in when considering what to cluster on. >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://www.solidqualitylearning.com/ >>>>> >>>>> >>>>> <param@community.nospam> wrote in message >>>>> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>>>>I am presuming this recommendation of adding an index on a column that >>>>>>is also a foreign key applies to both 2000 and 2005? >>>>>> >>>>>> TIA! >>>>>> >>>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>>> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>>>>> It will give the optimizer more reasonably costing ways (nested >>>>>>> loop) of doing joins (assuming you do join over these columns). >>>>>>> whether the optimizer find that those are better than the way it >>>>>>> currently does (possibly hash), you will find out by looking at >>>>>>> execution time and the execution plans. >>>>>>> >>>>>>> -- >>>>>>> Tibor Karaszi, SQL Server MVP >>>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>>> http://www.solidqualitylearning.com/ >>>>>>> >>>>>>> >>>>>>> <param@community.nospam> wrote in message >>>>>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>>>>> So, in regards to your suggestion on adding an index on the FK >>>>>>>> column, will that only benefit inserts/updates or will it benefit >>>>>>>> select statements as well? >>>>>>>> >>>>>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>>>>> inline... >>>>>>>>> >>>>>>>>> <param@community.nospam> wrote in message >>>>>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>>>>> Hi all, >>>>>>>>>> >>>>>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>>>>> transactional database and is about 20GB in size with about >>>>>>>>>> 200,000 rows in many tables. Our app is seeing serious >>>>>>>>>> performance issues due to queries and stored procs that take a >>>>>>>>>> long time to run.I am presuming these are lack of proper indexes. >>>>>>>>>> What is the best way to tune this database? >>>>>>>>> >>>>>>>>> Have you updated statistics or rebuilt indexes after the upgrade? >>>>>>>>> If not, it is fundamentally important to do this before you do >>>>>>>>> anything else. >>>>>>>>> >>>>>>>>>> >>>>>>>>>> Also, I had an elementary questions on indexes. If I have a >>>>>>>>>> foreign key in a table to another table. Will I still need to put >>>>>>>>>> an index on that column? >>>>>>>>> >>>>>>>>> Yes - otherwise during inserts, SQL Server will have no other >>>>>>>>> option than to scan the foreign key entirely to determine whether >>>>>>>>> a related row exists in the "parent" table. With an index, SQL >>>>>>>>> Server only needs to seek, which is considerably less overhead. >>>>>>>>> >>>>>>>>> HTH >>>>>>>>> >>>>>>>>> Regards, >>>>>>>>> Greg Linwood >>>>>>>>> SQL Server MVP >>>>>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>>>>> >>>>>>>>>> >>>>>>>>>> TIA!b >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Hi Alex
Thanks, & your info on how Oracle takes advantage of clustering factor looks interesting, but I don't quite understand what this means. Any chance you could elaborate further on what clustering factor statistics are & what type/s of efficiencies Oracle's optimiser takes advantage of in this scenario? I'd be very interested to learn more about this if you could share any more details. Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood [quoted text, click to view] "Alex Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1160764418.002260.27080@i42g2000cwa.googlegroups.com... >I read your series of articles on clustered indexes and I could not > agree more. Just wanted to add that > to my best knowledge SQL Server does not collect clustering factor > statistics, while both DB2 and Oracle certainly do. > Because of that SQL Server optimizer uses somewhat simplistic estimates > and may miss the target altogether. > Suppose you insert into a heap table and rows with the same EntryDate > are very likely to be adjacent, on the same page. > SQL Server optimizer will not know about it, while Oracle's optimizer > will recognize and utilize this fact, coming up with a more efficient > plan. > > ----------------------- > Alex Kuznetsov > http://sqlserver-tips.blogspot.com/ > http://sqlserver-puzzles.blogspot.com/ >
No worries, Maaate! (c:
Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood [quoted text, click to view] <param@community.nospam> wrote in message news:OPb9zEv7GHA.3644@TK2MSFTNGP03.phx.gbl... > Thanks Mate! > > "Greg Linwood" <g_linwood@hotmail.com> wrote in message > news:em8CjUn7GHA.788@TK2MSFTNGP05.phx.gbl... >> Re the CIX qn, I have been blogging some thoughts about this recently >> here >> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx >> >> I also presented a session on how indexes work & managing them at the >> Australian SQL Server User Group in Melbourne a few days ago. You might >> find the slides useful: >> http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ >> >> Regards, >> Greg Linwood >> SQL Server MVP >> http://blogs.sqlserver.org.au/blogs/greg_linwood >> >> <param@community.nospam> wrote in message >> news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... >>> Normally I have a IDENTITY Column as the PK of the table and make that >>> the CLUSTERED Index. Is that not advisable? >>> >>> >>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >>> in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >>>> Yep. Applies of all versions (so far) of SQL Server. In fact, >>>> clustering over such a column can be beneficial. But there are many >>>> many other factors to weigh in when considering what to cluster on. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://www.solidqualitylearning.com/ >>>> >>>> >>>> <param@community.nospam> wrote in message >>>> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>>>I am presuming this recommendation of adding an index on a column that >>>>>is also a foreign key applies to both 2000 and 2005? >>>>> >>>>> TIA! >>>>> >>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>>>> It will give the optimizer more reasonably costing ways (nested loop) >>>>>> of doing joins (assuming you do join over these columns). whether the >>>>>> optimizer find that those are better than the way it currently does >>>>>> (possibly hash), you will find out by looking at execution time and >>>>>> the execution plans. >>>>>> >>>>>> -- >>>>>> Tibor Karaszi, SQL Server MVP >>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>> http://www.solidqualitylearning.com/ >>>>>> >>>>>> >>>>>> <param@community.nospam> wrote in message >>>>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>>>> So, in regards to your suggestion on adding an index on the FK >>>>>>> column, will that only benefit inserts/updates or will it benefit >>>>>>> select statements as well? >>>>>>> >>>>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>>>> inline... >>>>>>>> >>>>>>>> <param@community.nospam> wrote in message >>>>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>>>> Hi all, >>>>>>>>> >>>>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>>>> transactional database and is about 20GB in size with about >>>>>>>>> 200,000 rows in many tables. Our app is seeing serious performance >>>>>>>>> issues due to queries and stored procs that take a long time to >>>>>>>>> run.I am presuming these are lack of proper indexes. What is the >>>>>>>>> best way to tune this database? >>>>>>>> >>>>>>>> Have you updated statistics or rebuilt indexes after the upgrade? >>>>>>>> If not, it is fundamentally important to do this before you do >>>>>>>> anything else. >>>>>>>> >>>>>>>>> >>>>>>>>> Also, I had an elementary questions on indexes. If I have a >>>>>>>>> foreign key in a table to another table. Will I still need to put >>>>>>>>> an index on that column? >>>>>>>> >>>>>>>> Yes - otherwise during inserts, SQL Server will have no other >>>>>>>> option than to scan the foreign key entirely to determine whether a >>>>>>>> related row exists in the "parent" table. With an index, SQL Server >>>>>>>> only needs to seek, which is considerably less overhead. >>>>>>>> >>>>>>>> HTH >>>>>>>> >>>>>>>> Regards, >>>>>>>> Greg Linwood >>>>>>>> SQL Server MVP >>>>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>>>> >>>>>>>>> >>>>>>>>> TIA!b >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> >> > >
As a very general rule, you should *index* columns which you expect to
provide row filtering & *include* columns that are only in the select list. Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood [quoted text, click to view] <param@community.nospam> wrote in message news:ulCgpFx7GHA.4552@TK2MSFTNGP05.phx.gbl... > So for Covering Indexes it is recommended to use the first command and not > the second command correct? > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:%23mATGnv7GHA.140@TK2MSFTNGP05.phx.gbl... >> These are not exactly the same, but very similar. >> The index with fname as an included column will not have fname in any of >> the upper levels of the index, it will only be in the leaf. In addition, >> there will be no sorting by fname, in the event of duplicate lname >> values. >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> http://sqlblog.com >> >> >> <param@community.nospam> wrote in message >> news:O1SKCTv7GHA.3828@TK2MSFTNGP03.phx.gbl... >>> So I noticed that your presentation recommends Covering Indexes. I also >>> noticed that in the Database Tuning Engine Advisor it recommends indexes >>> that have an include clause.So is:- >>> >>> create nonclustered index ix_index on dbo.customers (lname, fname) >>> >>> the same as >>> >>> create nonclustered index ix_index on dbo.customers (lname) include >>> (fname) >>> >>> ? >>> >>> TIA! >>> >>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>> news:em8CjUn7GHA.788@TK2MSFTNGP05.phx.gbl... >>>> Re the CIX qn, I have been blogging some thoughts about this recently >>>> here >>>> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx >>>> >>>> I also presented a session on how indexes work & managing them at the >>>> Australian SQL Server User Group in Melbourne a few days ago. You might >>>> find the slides useful: >>>> http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ >>>> >>>> Regards, >>>> Greg Linwood >>>> SQL Server MVP >>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>> >>>> <param@community.nospam> wrote in message >>>> news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... >>>>> Normally I have a IDENTITY Column as the PK of the table and make that >>>>> the CLUSTERED Index. Is that not advisable? >>>>> >>>>> >>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>> wrote in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >>>>>> Yep. Applies of all versions (so far) of SQL Server. In fact, >>>>>> clustering over such a column can be beneficial. But there are many >>>>>> many other factors to weigh in when considering what to cluster on. >>>>>> >>>>>> -- >>>>>> Tibor Karaszi, SQL Server MVP >>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>> http://www.solidqualitylearning.com/ >>>>>> >>>>>> >>>>>> <param@community.nospam> wrote in message >>>>>> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>>>>>I am presuming this recommendation of adding an index on a column >>>>>>>that is also a foreign key applies to both 2000 and 2005? >>>>>>> >>>>>>> TIA! >>>>>>> >>>>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>>>> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>>>>>> It will give the optimizer more reasonably costing ways (nested >>>>>>>> loop) of doing joins (assuming you do join over these columns). >>>>>>>> whether the optimizer find that those are better than the way it >>>>>>>> currently does (possibly hash), you will find out by looking at >>>>>>>> execution time and the execution plans. >>>>>>>> >>>>>>>> -- >>>>>>>> Tibor Karaszi, SQL Server MVP >>>>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>>>> http://www.solidqualitylearning.com/ >>>>>>>> >>>>>>>> >>>>>>>> <param@community.nospam> wrote in message >>>>>>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>>>>>> So, in regards to your suggestion on adding an index on the FK >>>>>>>>> column, will that only benefit inserts/updates or will it benefit >>>>>>>>> select statements as well? >>>>>>>>> >>>>>>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>>>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>>>>>> inline... >>>>>>>>>> >>>>>>>>>> <param@community.nospam> wrote in message >>>>>>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>>>>>> Hi all, >>>>>>>>>>> >>>>>>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>>>>>> transactional database and is about 20GB in size with about >>>>>>>>>>> 200,000 rows in many tables. Our app is seeing serious >>>>>>>>>>> performance issues due to queries and stored procs that take a >>>>>>>>>>> long time to run.I am presuming these are lack of proper >>>>>>>>>>> indexes. What is the best way to tune this database? >>>>>>>>>> >>>>>>>>>> Have you updated statistics or rebuilt indexes after the upgrade? >>>>>>>>>> If not, it is fundamentally important to do this before you do >>>>>>>>>> anything else. >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Also, I had an elementary questions on indexes. If I have a >>>>>>>>>>> foreign key in a table to another table. Will I still need to >>>>>>>>>>> put an index on that column? >>>>>>>>>> >>>>>>>>>> Yes - otherwise during inserts, SQL Server will have no other >>>>>>>>>> option than to scan the foreign key entirely to determine whether >>>>>>>>>> a related row exists in the "parent" table. With an index, SQL >>>>>>>>>> Server only needs to seek, which is considerably less overhead. >>>>>>>>>> >>>>>>>>>> HTH >>>>>>>>>> >>>>>>>>>> Regards, >>>>>>>>>> Greg Linwood >>>>>>>>>> SQL Server MVP >>>>>>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> TIA!b >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Nice blog btw Alex. Do you mind if I link to it from www.sqlserver.org.au?
Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood [quoted text, click to view] "Alex Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1160764418.002260.27080@i42g2000cwa.googlegroups.com... >I read your series of articles on clustered indexes and I could not > agree more. Just wanted to add that > to my best knowledge SQL Server does not collect clustering factor > statistics, while both DB2 and Oracle certainly do. > Because of that SQL Server optimizer uses somewhat simplistic estimates > and may miss the target altogether. > Suppose you insert into a heap table and rows with the same EntryDate > are very likely to be adjacent, on the same page. > SQL Server optimizer will not know about it, while Oracle's optimizer > will recognize and utilize this fact, coming up with a more efficient > plan. > > ----------------------- > Alex Kuznetsov > http://sqlserver-tips.blogspot.com/ > http://sqlserver-puzzles.blogspot.com/ >
Can I *include* columns on a PK? I have some tables that the only index is the PK (clustered). Good Day Maaate! :) P.S. My boss is actually in the Down Under right now!! :) [quoted text, click to view] "Greg Linwood" <g_linwood@hotmail.com> wrote in message news:%23ZtXH$z7GHA.1188@TK2MSFTNGP05.phx.gbl... > As a very general rule, you should *index* columns which you expect to > provide row filtering & *include* columns that are only in the select > list. > > Regards, > Greg Linwood > SQL Server MVP > http://blogs.sqlserver.org.au/blogs/greg_linwood > > <param@community.nospam> wrote in message > news:ulCgpFx7GHA.4552@TK2MSFTNGP05.phx.gbl... >> So for Covering Indexes it is recommended to use the first command and >> not the second command correct? >> >> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message >> news:%23mATGnv7GHA.140@TK2MSFTNGP05.phx.gbl... >>> These are not exactly the same, but very similar. >>> The index with fname as an included column will not have fname in any of >>> the upper levels of the index, it will only be in the leaf. In addition, >>> there will be no sorting by fname, in the event of duplicate lname >>> values. >>> >>> -- >>> HTH >>> Kalen Delaney, SQL Server MVP >>> http://sqlblog.com >>> >>> >>> <param@community.nospam> wrote in message >>> news:O1SKCTv7GHA.3828@TK2MSFTNGP03.phx.gbl... >>>> So I noticed that your presentation recommends Covering Indexes. I also >>>> noticed that in the Database Tuning Engine Advisor it recommends >>>> indexes that have an include clause.So is:- >>>> >>>> create nonclustered index ix_index on dbo.customers (lname, fname) >>>> >>>> the same as >>>> >>>> create nonclustered index ix_index on dbo.customers (lname) include >>>> (fname) >>>> >>>> ? >>>> >>>> TIA! >>>> >>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>> news:em8CjUn7GHA.788@TK2MSFTNGP05.phx.gbl... >>>>> Re the CIX qn, I have been blogging some thoughts about this recently >>>>> here >>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/25/387.aspx >>>>> >>>>> I also presented a session on how indexes work & managing them at the >>>>> Australian SQL Server User Group in Melbourne a few days ago. You >>>>> might find the slides useful: >>>>> http://www.sqlserver.org.au/meetings/Melb/2006/10_GregLinwood_IndexingForPerformance/ >>>>> >>>>> Regards, >>>>> Greg Linwood >>>>> SQL Server MVP >>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>> >>>>> <param@community.nospam> wrote in message >>>>> news:uvbDQoj7GHA.1248@TK2MSFTNGP03.phx.gbl... >>>>>> Normally I have a IDENTITY Column as the PK of the table and make >>>>>> that the CLUSTERED Index. Is that not advisable? >>>>>> >>>>>> >>>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>>> wrote in message news:OSy1kFj7GHA.4620@TK2MSFTNGP02.phx.gbl... >>>>>>> Yep. Applies of all versions (so far) of SQL Server. In fact, >>>>>>> clustering over such a column can be beneficial. But there are many >>>>>>> many other factors to weigh in when considering what to cluster on. >>>>>>> >>>>>>> -- >>>>>>> Tibor Karaszi, SQL Server MVP >>>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>>> http://www.solidqualitylearning.com/ >>>>>>> >>>>>>> >>>>>>> <param@community.nospam> wrote in message >>>>>>> news:%23jl%23SBj7GHA.728@TK2MSFTNGP04.phx.gbl... >>>>>>>>I am presuming this recommendation of adding an index on a column >>>>>>>>that is also a foreign key applies to both 2000 and 2005? >>>>>>>> >>>>>>>> TIA! >>>>>>>> >>>>>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >>>>>>>> wrote in message news:ufYDnYh7GHA.3916@TK2MSFTNGP04.phx.gbl... >>>>>>>>> It will give the optimizer more reasonably costing ways (nested >>>>>>>>> loop) of doing joins (assuming you do join over these columns). >>>>>>>>> whether the optimizer find that those are better than the way it >>>>>>>>> currently does (possibly hash), you will find out by looking at >>>>>>>>> execution time and the execution plans. >>>>>>>>> >>>>>>>>> -- >>>>>>>>> Tibor Karaszi, SQL Server MVP >>>>>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>>>>> http://www.solidqualitylearning.com/ >>>>>>>>> >>>>>>>>> >>>>>>>>> <param@community.nospam> wrote in message >>>>>>>>> news:OvjkNIh7GHA.4804@TK2MSFTNGP02.phx.gbl... >>>>>>>>>> So, in regards to your suggestion on adding an index on the FK >>>>>>>>>> column, will that only benefit inserts/updates or will it benefit >>>>>>>>>> select statements as well? >>>>>>>>>> >>>>>>>>>> "Greg Linwood" <g_linwood@hotmail.com> wrote in message >>>>>>>>>> news:%23JrwEjb7GHA.3620@TK2MSFTNGP04.phx.gbl... >>>>>>>>>>> inline... >>>>>>>>>>> >>>>>>>>>>> <param@community.nospam> wrote in message >>>>>>>>>>> news:uoncxJb7GHA.2364@TK2MSFTNGP02.phx.gbl... >>>>>>>>>>>> Hi all, >>>>>>>>>>>> >>>>>>>>>>>> We had a database that we upgraded to SQL2005. This is a highly >>>>>>>>>>>> transactional database and is about 20GB in size with about >>>>>>>>>>>> 200,000 rows in many tables. Our app is seeing serious >>>>>>>>>>>> performance issues due to queries and stored procs that take a >>>>>>>>>>>> long time to run.I am presuming these are lack of proper >>>>>>>>>>>> indexes. What is the best way to tune this database? >>>>>>>>>>> >>>>>>>>>>> Have you updated statistics or rebuilt indexes after the >>>>>>>>>>> upgrade? If not, it is fundamentally important to do this before >>>>>>>>>>> you do anything else. >>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Also, I had an elementary questions on indexes. If I have a >>>>>>>>>>>> foreign key in a table to another table. Will I still need to >>>>>>>>>>>> put an index on that column? >>>>>>>>>>> >>>>>>>>>>> Yes - otherwise during inserts, SQL Server will have no other >>>>>>>>>>> option than to scan the foreign key entirely to determine >>>>>>>>>>> whether a related row exists in the "parent" table. With an >>>>>>>>>>> index, SQL Server only needs to seek, which is considerably less >>>>>>>>>>> overhead. >>>>>>>>>>> >>>>>>>>>>> HTH >>>>>>>>>>> >>>>>>>>>>> Regards, >>>>>>>>>>> Greg Linwood >>>>>>>>>>> SQL Server MVP >>>>>>>>>>> http://blogs.sqlserver.org.au/blogs/greg_linwood >>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> TIA!b >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>&g |