MS SQL Server optimization

May 8, 2008 at 9:37 AM
Edited May 8, 2008 at 9:43 AM
Hi!

I've analyzed queries that performed to database, and found that there are no indexes. Most of queries looks as uneficient, which can downgrade performance for large websites. Here is some indexes, i've created to optimize mssql 2005 database (i assume that apply for mssql 2000 too).

create clustered index IX_n2AllowedRole_ItemID on dbo.n2AllowedRole (ItemID)
create clustered index IX_n2Detail_ItemID on dbo.n2Detail (ItemID)
create clustered index IX_n2DetailCollection_ItemID on dbo.n2DetailCollection (ItemID)
create nonclustered index IX_n2Detail_Name_IntValue on dbo.n2Detail (Name, IntValue)
create nonclustered index IX_n2Item_VersionOfID on dbo.n2Item (VersionOfID)
create nonclustered index IX_n2Item_ParentID on dbo.n2Item (ParentID)

To create clustered index for tables n2AllowedRole, n2Detail and n2DetailCollection, you should unset 'clustered' property on primary key, or modify install.sqlserver2005.sql, as folowing:

...
create table n2Detail (
....
Value VARBINARY(MAX) null,
primary key nonclustered (ID)
)
create table n2DetailCollection (
....
Name NVARCHAR(50) not null,
primary key nonclustered (ID)
)
create table n2AllowedRole (
...
Role NVARCHAR(50) not null,
primary key nonclustered (ID)
)
Coordinator
May 8, 2008 at 2:09 PM
This is cool, and also interesting...

Could you mail me the complete sql script please?

Do you have any measurements you can share (# of items, time before/after, etc.)?
May 9, 2008 at 7:54 PM
Edited May 9, 2008 at 7:55 PM
Hi

Don't have your email. Here is complete scripts http://www.maqdev.com/file.axd?file=install.sqlserver.zip (mssql 2000 & 2005)

I've created 6000 items (for 10 web sites), then using profiler recorded all sql queries when navigating through one of web site.
There are 6 typical queries, each one repeated many times with diferent parameters.

Then i played that recorded queries using original DB and DB with indexes, and got the folowing results:
83 seconds for unoptimized version vs 19 seconds for optimized, that is performance increased about 2.45 times over original version.

When there is no heavy load, and there are no many items (e.g. < 500), optimization will show no significant diference vs original version.
Here is the typical queries, that i've optimized for:

exec spexecutesql N'SELECT authorized0.ItemID as ItemID1, authorized0.ID as ID1, authorized0.ID as ID00,
authorized0.ItemID as ItemID00, authorized0.Role as Role00 FROM n2AllowedRole authorized0_ WHERE
authorized0_.ItemID=@p0',N'@p0 int',@p0=145

exec spexecutesql N'SELECT children0.ParentID as ParentID1, children0.ID as ID1, children0.ID as ID20,
children0.Updated as Updated20, children0.Name as Name20, children0.ZoneName as ZoneName20_,
children0.Title as Title20, children0.Created as Created20, children0.Published as Published20_,
children0.Expires as Expires20, children0.SortOrder as SortOrder20, children0.Visible as Visible20_,
children0.SavedBy as SavedBy20, children0.VersionOfID as Version1320, children0.ParentID as ParentID20,
children0.Type as Type20_ FROM n2Item children0_ WHERE children0_.ParentID=@p0 ORDER BY
children0_.SortOrder',N'@p0 int',@p0=3

exec spexecutesql N'SELECT contentite0.ID as ID21, contentite0.Updated as Updated21, contentite0.Name as
Name21, contentite0.ZoneName as ZoneName21, contentite0.Title as Title21, contentite0_.Created as
Created21, contentite0.Published as Published21, contentite0.Expires as Expires21, contentite0_.SortOrder as
SortOrder21, contentite0.Visible as Visible21, contentite0.SavedBy as SavedBy21, contentite0_.VersionOfID as
Version1321, contentite0.ParentID as ParentID21, contentite0.Type as Type21, authorized1.ItemID as
ItemID3, authorized1.ID as ID3, authorized1.ID as ID00, authorized1.ItemID as ItemID00, authorized1.Role
as Role00 FROM n2Item contentite0_ left outer join n2AllowedRole authorized1_ on
contentite0.ID=authorized1.ItemID WHERE contentite0_.ID=@p0',N'@p0 int',@p0=3

exec spexecutesql N'SELECT detailcoll0.ItemID as ItemID1, detailcoll0.ID as ID1, detailcoll0.Name as Name1_,
detailcoll0.ID as ID30, detailcoll0.ItemID as ItemID30, detailcoll0.Name as Name30_ FROM n2DetailCollection
detailcoll0_ WHERE detailcoll0_.ItemID=@p0',N'@p0 int',@p0=3

exec spexecutesql N'SELECT details0.ItemID as ItemID2, details0.ID as ID2, details0.Name as Name2_,
details0.ID as ID11, details0.ItemID as ItemID11, details0.DetailCollectionID as DetailCo411,
details0.Name as Name11, details0.BoolValue as BoolValue11, details0.IntValue as IntValue11_,
details0.LinkValue as LinkValue11, details0.DoubleValue as DoubleVa911, details0.DateTimeValue as
DateTim1011, details0.StringValue as StringV1111, details0.Value as Value11, details0.Type as Type11_,
contentite1.ID as ID20, contentite1.Updated as Updated20, contentite1.Name as Name20, contentite1.ZoneName
as ZoneName20, contentite1.Title as Title20, contentite1.Created as Created20, contentite1_.Published as
Published20, contentite1.Expires as Expires20, contentite1.SortOrder as SortOrder20, contentite1_.Visible as
Visible20, contentite1.SavedBy as SavedBy20, contentite1.VersionOfID as Version1320, contentite1.ParentID
as ParentID20, contentite1.Type as Type20_ FROM n2Detail details0_ left outer join n2Item contentite1_ on
details0.LinkValue=contentite1.ID WHERE (details0.DetailCollectionID IS NULL) and details0.ItemID=@p0',N'@p0
int',@p0=3

exec spexecutesql N'SELECT TOP 10 x00_ FROM (SELECT ROWNUMBER() OVER(ORDER BY _hibernatesortexpr1_ DESC) as
row, query.x00, query._hibernatesortexpr1__ FROM (select contentite0.ID as x00_, Published as
_hibernatesortexpr1__ from n2Item contentite0_ where (VersionOfID Is Null )and((contentite0_.ID in(select
contentdet1.ItemID from n2Detail contentdet1 where (contentdet1.BoolValue=@p0 )AND(contentdet1.Name=@p1 )))))
query ) page WHERE page.row > 0 ORDER BY _hibernatesortexpr1__ DESC',N'@p0 bit,@p1
nvarchar(9)',@p0=1,@p1=N'Syndicate'
Coordinator
May 9, 2008 at 9:31 PM
Thank you =) I've updated the web site with your improvements.