Include list
in [Server Configuration]
|
From: cbrichards via SQLMonster.com on 29 May 2010 23:48 Can somebody help enlighten me on the negatives of a large include list of an index? -- Message posted via http://www.sqlmonster.com
From: Dan Guzman on 30 May 2010 09:40 > Can somebody help enlighten me on the negatives of a large include list of > an > index? The main downsides that come to mind are: Increased space requirements possible lower buffer cache hit ratio Increased deadlock likelihood -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "cbrichards via SQLMonster.com" <u3288(a)uwe> wrote in message news:a8c252aad794a(a)uwe... > Can somebody help enlighten me on the negatives of a large include list of > an > index? > > -- > Message posted via http://www.sqlmonster.com >
From: cbrichards via SQLMonster.com on 30 May 2010 15:08 Thanks Dan. I was interested in the "increased deadlock likelihood". After considering this downside, I was not able to fully wrap my mind about it. Is the increased deadlock likelihood due to an index (with a large include list) taking longer to update in a DML operation? Dan Guzman wrote: >> Can somebody help enlighten me on the negatives of a large include list of >> an >> index? > >The main downsides that come to mind are: > > Increased space requirements > possible lower buffer cache hit ratio > Increased deadlock likelihood > >> Can somebody help enlighten me on the negatives of a large include list of >> an >> index? -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201005/1
From: Erland Sommarskog on 30 May 2010 15:59 cbrichards via SQLMonster.com (u3288(a)uwe) writes: > I was interested in the "increased deadlock likelihood". After considering > this downside, I was not able to fully wrap my mind about it. Is the > increased deadlock likelihood due to an index (with a large include list) > taking longer to update in a DML operation? I'm not exactly sure what Dan had in mind, but I would add that the more columns there are in the index, the more likely that an UPDATE will need to update an index page as well. That obviously takes more time, and it is conceivable that it also widens the window of oppurtunity for deadlocks. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Dan Guzman on 31 May 2010 11:32 > I was interested in the "increased deadlock likelihood". After considering > this downside, I was not able to fully wrap my mind about it. Is the > increased deadlock likelihood due to an index (with a large include list) > taking longer to update in a DML operation? Erland is correct about why I made this statement. The same data needs to be updated in multiple places so modifications will take a little longer and this can increase deadlock likelihood. Also, introducing any index change can affect the behavior of an existing system because data access paths are changed and this can sometimes introduce deadlocks that did not previously occur. I should add that INCLUDEd columns are quite useful when used appropriately. The few downsides often outweigh the performance gains. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/
|
Pages: 1 Prev: remote backup in local drive Next: synchronize sql 2008 r2 |