help on Indexes

Hi all,
I haven't add indexes to a database for a long long time so I've
completely forgotten how to go about designing an index.
Say I have the following query (which is run often):
SELECT
bl.TotalRooms, bt.TypeName, u.UserWorkTel,
p.DisplayAddress, p.idList, cy.CountyCityName, d.DistrictPostcode,
p.idPublicDetails,
p.PriceVal, p.Description, fr.FrequencyName, p.ModifyDate, p.ListRef,
u.idUserType, lt.idAdType,
pt.PTypeName, lcl.OccasionalWends, lcl.WendPrice,
lc.idLetCategory, lc.CategoryName,
c.CountryName, d.DistrictName, p.idPropStatus, pp.PicPath, (al.PicPath)
AS 'Logo', al.idOffice,
FROM Property p
JOIN PropertyType pt
ON p.idPType = pt.idPType
JOIN Country c
ON p.idCountry = c.idCountry
JOIN District d
ON p.idDistrict = d.idDistrict
JOIN CountyCity cy
ON p.idCountyCity = cy.idCountyCity
JOIN Frequency fr
ON p.idFrequency = fr.idFrequency
LEFT OUTER JOIN BedroomList bl
ON p.idList = bl.idList
LEFT OUTER JOIN BedroomType bt
ON bl.idBedroomType = bt.idBedroomType
LEFT OUTER JOIN PropertyPicture pp
ON p.idList = pp.idList AND pp.Thumbnail = 1
LEFT OUTER JOIN LetCategoryList lcl
ON p.idList = lcl.idList
LEFT OUTER JOIN LetCategory lc
ON lcl.idLetCategory = lc.idLetCategory
LEFT OUTER JOIN Agent a
ON a.idUser = p.idCreatedBy
LEFT OUTER JOIN AgencyLogo al
ON al.idOffice = a.idOffice
JOIN Users u ON
u.idUser = p.idCreatedBy
LEFT OUTER JOIN ListingType lt
ON lt.idList = p.idList
JOIN PropContactList pc
ON pc.idList = p.idList
JOIN Users us
ON us.idUser = pc.idMainContact
WHERE p.idCRule = 1
GROUP BY p.idList, bl.TotalRooms, p.ListRef,
al.PicPath, bl.FiveDayPrice, bl.SevenDayPrice, bt.TypeName,
p.DisplayAddress, p.PriceVal, p.Description, p.ModifyDate, u.idUserType,
fr.FrequencyName, d.DistrictPostcode,
pt.PTypeName, lcl.OccasionalWends, lcl.WendPrice,
lc.idLetCategory, lc.CategoryName, p.idPublicDetails, u.UserWorkTel,
c.CountryName, d.DistrictName, p.idPropStatus, cy.CountyCityName,
pp.PicPath, al.idOffice, lt.idAdType
ORDER BY lt.idAdType, p.ModifyDate desc, p.idList desc
... where do I begin looking to add indexes for this query '
(note that I
have primary keys on each table on the columns begining with 'id' so
therefore I already have one clustered index on each table)
Any advice much appreciated!
Peter
--
fiddlewidawiddumIs your p.IDCRule greatly disparate? In other words, are there more
than a couple distinct values in it (1-10 as opposed to 1-3)? If so,
it might be a candidate for an index. I'd also look at indexing the
foreign key columns on each table (e.g., p.idCreatedBy)
Do you know how to work with execution plans? They'll reveal a lot
about what indexes are being used, and how the addition of an index
will alter the performance.
Be aware that adding an index can impact performance on INSERTs and
UPDATEs, so be careful and don't over-do it.
HTH,
Stu|||Stimp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
"Stimp" <ren@.spumco.com> wrote in message
news:slrne3206s.4or.ren@.carbon.redbrick.dcu.ie...
> Hi all,
> I haven't add indexes to a database for a long long time so I've
> completely forgotten how to go about designing an index.
> Say I have the following query (which is run often):
> SELECT
> bl.TotalRooms, bt.TypeName, u.UserWorkTel,
> p.DisplayAddress, p.idList, cy.CountyCityName, d.DistrictPostcode,
> p.idPublicDetails,
> p.PriceVal, p.Description, fr.FrequencyName, p.ModifyDate, p.ListRef,
> u.idUserType, lt.idAdType,
> pt.PTypeName, lcl.OccasionalWends, lcl.WendPrice,
> lc.idLetCategory, lc.CategoryName,
> c.CountryName, d.DistrictName, p.idPropStatus, pp.PicPath, (al.PicPath)
> AS 'Logo', al.idOffice,
> FROM Property p
> JOIN PropertyType pt
> ON p.idPType = pt.idPType
> JOIN Country c
> ON p.idCountry = c.idCountry
> JOIN District d
> ON p.idDistrict = d.idDistrict
> JOIN CountyCity cy
> ON p.idCountyCity = cy.idCountyCity
> JOIN Frequency fr
> ON p.idFrequency = fr.idFrequency
> LEFT OUTER JOIN BedroomList bl
> ON p.idList = bl.idList
> LEFT OUTER JOIN BedroomType bt
> ON bl.idBedroomType = bt.idBedroomType
> LEFT OUTER JOIN PropertyPicture pp
> ON p.idList = pp.idList AND pp.Thumbnail = 1
> LEFT OUTER JOIN LetCategoryList lcl
> ON p.idList = lcl.idList
> LEFT OUTER JOIN LetCategory lc
> ON lcl.idLetCategory = lc.idLetCategory
> LEFT OUTER JOIN Agent a
> ON a.idUser = p.idCreatedBy
> LEFT OUTER JOIN AgencyLogo al
> ON al.idOffice = a.idOffice
> JOIN Users u ON
> u.idUser = p.idCreatedBy
> LEFT OUTER JOIN ListingType lt
> ON lt.idList = p.idList
> JOIN PropContactList pc
> ON pc.idList = p.idList
> JOIN Users us
> ON us.idUser = pc.idMainContact
> WHERE p.idCRule = 1
> GROUP BY p.idList, bl.TotalRooms, p.ListRef,
> al.PicPath, bl.FiveDayPrice, bl.SevenDayPrice, bt.TypeName,
> p.DisplayAddress, p.PriceVal, p.Description, p.ModifyDate, u.idUserType,
> fr.FrequencyName, d.DistrictPostcode,
> pt.PTypeName, lcl.OccasionalWends, lcl.WendPrice,
> lc.idLetCategory, lc.CategoryName, p.idPublicDetails, u.UserWorkTel,
> c.CountryName, d.DistrictName, p.idPropStatus, cy.CountyCityName,
> pp.PicPath, al.idOffice, lt.idAdType
> ORDER BY lt.idAdType, p.ModifyDate desc, p.idList desc
>
> ... where do I begin looking to add indexes for this query '
> (note that I
> have primary keys on each table on the columns begining with 'id' so
> therefore I already have one clustered index on each table)
> Any advice much appreciated!
> Peter
> --
> fiddlewidawiddum|||The rule of thumb which I've found useful with indexes is to create the
ones that allow the optimiser to quickly bound the size of the result
set. So for example if you were passing a parameter such as a daterange
into your where clause, this would be a good candidate for the index as
it would probably cut that table down to a few rows, such that all
other operations on that result set from then on would be trivial.
in your case however you don't seem to be passing anything to really
cut down on the size of the results, so as Stu said, it's the execution
plans which will really show you where the best improvements can be
made (CTRL-K in query analyser (SQL 2000) and CTRL-M in SQL 2005)
Cheers
Will|||Run the profiler with output to a file.
and run this query along with any other query that you run on these tables.
Take the result file and pass it to the index tuning wizard.
You should get a fair idea on what indexes to use.|||On Mon, 3 Apr 2006 Omnibuzz <Omnibuzz@.discussions.microsoft.com> wrote:
> Run the profiler with output to a file.
> and run this query along with any other query that you run on these tables
.
> Take the result file and pass it to the index tuning wizard.
> You should get a fair idea on what indexes to use.
this seems to do the trick alright.. mucho gracias y'all!
--
fiddlewidawiddum|||Stimp
I'd not rely on ITW ,because on more complex (involved many tables)
queries it does not provide a good advice. Instead , examine an execution
plan of the query , see if the optimier was available to use idexes.
"Stimp" <ren@.spumco.com> wrote in message
news:slrne32cpm.c9o.ren@.carbon.redbrick.dcu.ie...
> On Mon, 3 Apr 2006 Omnibuzz <Omnibuzz@.discussions.microsoft.com> wrote:
> this seems to do the trick alright.. mucho gracias y'all!
> --
> fiddlewidawiddum|||On Mon, 3 Apr 2006 Uri Dimant <urid@.iscar.co.il> wrote:
> Stimp
> I'd not rely on ITW ,because on more complex (involved many tables)
> queries it does not provide a good advice. Instead , examine an execution
> plan of the query , see if the optimier was available to use idexes.
Hi Uri,
I looked at the execution plan and couldn't make head-or-tail of it, and
since I was on a tight schedule I didn't have time to research how to
translate execution plans.
Is there a tutorial on how to read execution plans, and so use them to
derive indexes?
Cheers,
Peter

>
> "Stimp" <ren@.spumco.com> wrote in message
> news:slrne32cpm.c9o.ren@.carbon.redbrick.dcu.ie...
>
fiddlewidawiddum|||Hi
Vist at http://www.sql-server-performance.c...performance.asp and
you'll find lots of good info
"Stimp" <ren@.spumco.com> wrote in message
news:slrne34eqg.n0v.ren@.carbon.redbrick.dcu.ie...
> On Mon, 3 Apr 2006 Uri Dimant <urid@.iscar.co.il> wrote:
> Hi Uri,
> I looked at the execution plan and couldn't make head-or-tail of it, and
> since I was on a tight schedule I didn't have time to research how to
> translate execution plans.
> Is there a tutorial on how to read execution plans, and so use them to
> derive indexes?
> Cheers,
> Peter
>
>
> --
> fiddlewidawiddum

help on INDEX

Hi,
I'm a beginner and so perhaps this is a dumb question,
but here goes.
here's two query statements:
SELECT *
FROM FewUniques WITH(index(0))
WHERE Status = 'Inactive'
SELECT *
FROM FewUniques WITH(index(inFewUniquesStatus))
--inFewUniquesStatus is a predefined INDEX
WHERE Status = 'Inactive'
what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
Is index() a function or not?
I thank you very much,for your assistance:)
fridaydreamThis are directives for the optimizer (called optimizer hints) which tells t
he optimizer which index
to use for the query. 0 means a table scan. These should be avoided in produ
ction code, let the
optimizer pick the best execution plan it can.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<fridaydream@.163.com> wrote in message news:uP0rO$TJFHA.3196@.TK2MSFTNGP15.phx.gbl...darkred">
> Hi,
> I'm a beginner and so perhaps this is a dumb question,
> but here goes.
> here's two query statements:
> SELECT *
> FROM FewUniques WITH(index(0))
> WHERE Status = 'Inactive'
> SELECT *
> FROM FewUniques WITH(index(inFewUniquesStatus))
> --inFewUniquesStatus is a predefined INDEX
> WHERE Status = 'Inactive'
>
> what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
> Is index() a function or not?
>
> I thank you very much,for your assistance:)
> fridaydream
>
>
>|||Not a dumb question.
INDEX() is not a function. In this context, it is a table hint. When
you specify "WITH(INDEX(x))" where x is either the index ID or the index
name, you are telling the query engine to use that specific index to
retrieve the data from that table.
99% of the time you shouldn't specify table hints but should just trust
the query optimiser to pick the best indexes for the query plan - it
usually gets it right.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
fridaydream@.163.com wrote:

>Hi,
>I'm a beginner and so perhaps this is a dumb question,
>but here goes.
>here's two query statements:
>SELECT *
>FROM FewUniques WITH(index(0))
>WHERE Status = 'Inactive'
>SELECT *
>FROM FewUniques WITH(index(inFewUniquesStatus))
> --inFewUniquesStatus is a predefined INDEX
>WHERE Status = 'Inactive'
>
>what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
>Is index() a function or not?
>
>I thank you very much,for your assistance:)
>fridaydream
>
>
>
>|||Thanks a lot!
so I guess this is not a standard SQL Grammar,like WITH(INDEX(xx)),Is it?
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '
news:u%236OOHUJFHA.3132@.TK2MSFTNGP12.phx.gbl...
Not a dumb question.
INDEX() is not a function. In this context, it is a table hint. When you
specify "WITH(INDEX(x))" where x is either the index ID or the index name,
you are telling the query engine to use that specific index to retrieve the
data from that table.
99% of the time you shouldn't specify table hints but should just trust the
query optimiser to pick the best indexes for the query plan - it usually
gets it right.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
fridaydream@.163.com wrote:
Hi,
I'm a beginner and so perhaps this is a dumb question,
but here goes.
here's two query statements:
SELECT *
FROM FewUniques WITH(index(0))
WHERE Status = 'Inactive'
SELECT *
FROM FewUniques WITH(index(inFewUniquesStatus))
--inFewUniquesStatus is a predefined INDEX
WHERE Status = 'Inactive'
what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
Is index() a function or not?
I thank you very much,for your assistance:)
fridaydream|||What do you mean by "standard SQL Grammar"? If you refer to the ANSI SQL sta
ndard, then you are
correct. There's nothing like that in the ANSI SQL standard. Actually ANSI S
QL doesn't say a work
about indexes in the first place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fridaydream" <fridaydream@.163.com> wrote in message news:ev1TTaUJFHA.3340@.TK2MSFTNGP12.phx
.gbl...
> Thanks a lot!
> so I guess this is not a standard SQL Grammar,like WITH(INDEX(xx)),Is it?

> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '
> news:u%236OOHUJFHA.3132@.TK2MSFTNGP12.phx.gbl...
> Not a dumb question.
> INDEX() is not a function. In this context, it is a table hint. When you
> specify "WITH(INDEX(x))" where x is either the index ID or the index name,
> you are telling the query engine to use that specific index to retrieve th
e
> data from that table.
> 99% of the time you shouldn't specify table hints but should just trust th
e
> query optimiser to pick the best indexes for the query plan - it usually
> gets it right.
> --
> mike hodgson | database administrator | mallesons stephen jaques
> T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
> E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
> fridaydream@.163.com wrote:
> Hi,
> I'm a beginner and so perhaps this is a dumb question,
> but here goes.
> here's two query statements:
> SELECT *
> FROM FewUniques WITH(index(0))
> WHERE Status = 'Inactive'
> SELECT *
> FROM FewUniques WITH(index(inFewUniquesStatus))
> --inFewUniquesStatus is a predefined INDEX
> WHERE Status = 'Inactive'
>
> what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
> Is index() a function or not?
>
> I thank you very much,for your assistance:)
> fridaydream
>
>
>
>
>

help me get rid of the noise words please!

Hi,
I am a programmer with no SQLServer dba experience and have set up a full
text index on two columns in the same table. The index is working fine
except for when a noise word is used in a search. My problem is that I have
been unable to remove the noise words. I have searched the drive and deleted
everything from all of the noise.enu files - they are zero length. I have
gone to a command prompt and typed "net stop mssearch" and then "net start
mssearch". After stopping and starting mssearch, I have repopulated my
indexes by going to the table where the indexes are and using the
edit-full-text indexing wizard. I'm sure there is a better way but at the
completion of the wizard it drops the index and rebuilds it. I do have a
scheduled repopulation that runs every morning. When I open this job it has
the following code:
use [central] exec sp_fulltext_table N'[dbo].[Product]', N'start_full'.
This has been running successfully for quite a while.
What am I doing incorrectly that I can't get rid of the noise words?
Your help would be greatly appreciated!
[code generated from ASP page]
select * from Product where contains(Desc1, 'dvd AND r') or contains(Desc2,
'dvd AND r')
[System Summary]
Item Value
OS Name Microsoft Windows 2000 Advanced Server
Version 5.0.2195 Service Pack 4 Build 2195
OS Manufacturer Microsoft Corporation
System Manufacturer Hewlett-Packard
System Model HP NetServer
System Type X86-based PC
Processor x86 Family 6 Model 8 Stepping 10 GenuineIntel ~933 Mhz
BIOS Version 10/15/01
Windows Directory C:\WINNT
System Directory C:\WINNT\system32
Boot Device \Device\Harddisk0\Partition2
Locale United States
Time Zone Eastern Standard Time
Total Physical Memory 1,310,188 KB
Available Physical Memory 30,864 KB
Total Virtual Memory 4,435,520 KB
Available Virtual Memory 1,987,148 KB
Page File Space 3,125,332 KB
Page File C:\pagefile.sys
you have several options
1) use a freetext query, but this may return results that are too fuzzy for
you.
2) strip the noise words out by using a client side script - search this
newsgroup for searchpage1.htm
3) using tsql parsing on the server possibly a UDF
4) write an extended proc to parse them out
5) I'm wondering if you got the correct noise file. noise.eng is for British
English, noise.enu is for American English. Change the noise word lists you
find in
C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config
You will have to stop and start the mssearch service to make these changes.
"woodysapsucker" <woody@.rohland.org> wrote in message
news:OMbPJgoEEHA.3344@.tk2msftngp13.phx.gbl...
> Hi,
> I am a programmer with no SQLServer dba experience and have set up a full
> text index on two columns in the same table. The index is working fine
> except for when a noise word is used in a search. My problem is that I
have
> been unable to remove the noise words. I have searched the drive and
deleted
> everything from all of the noise.enu files - they are zero length. I have
> gone to a command prompt and typed "net stop mssearch" and then "net start
> mssearch". After stopping and starting mssearch, I have repopulated my
> indexes by going to the table where the indexes are and using the
> edit-full-text indexing wizard. I'm sure there is a better way but at the
> completion of the wizard it drops the index and rebuilds it. I do have a
> scheduled repopulation that runs every morning. When I open this job it
has
> the following code:
> use [central] exec sp_fulltext_table N'[dbo].[Product]', N'start_full'.
> This has been running successfully for quite a while.
> What am I doing incorrectly that I can't get rid of the noise words?
> Your help would be greatly appreciated!
>
> [code generated from ASP page]
> select * from Product where contains(Desc1, 'dvd AND r') or
contains(Desc2,
> 'dvd AND r')
>
> [System Summary]
> Item Value
> OS Name Microsoft Windows 2000 Advanced Server
> Version 5.0.2195 Service Pack 4 Build 2195
> OS Manufacturer Microsoft Corporation
> System Manufacturer Hewlett-Packard
> System Model HP NetServer
> System Type X86-based PC
> Processor x86 Family 6 Model 8 Stepping 10 GenuineIntel ~933 Mhz
> BIOS Version 10/15/01
> Windows Directory C:\WINNT
> System Directory C:\WINNT\system32
> Boot Device \Device\Harddisk0\Partition2
> Locale United States
> Time Zone Eastern Standard Time
> Total Physical Memory 1,310,188 KB
> Available Physical Memory 30,864 KB
> Total Virtual Memory 4,435,520 KB
> Available Virtual Memory 1,987,148 KB
> Page File Space 3,125,332 KB
> Page File C:\pagefile.sys
>
|||Thanks Hilary,
I am trying to still use the noise words in the queries. I would like the
query to be on "dvd r" not just "dvd". That is why I've been trying to empty
out the noise word files.
I am from the US but also emptied noise.eng files - just incase - and reran
the indexing.
Any other suggestions?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eYFPX1oEEHA.688@.tk2msftngp13.phx.gbl...
> you have several options
> 1) use a freetext query, but this may return results that are too fuzzy
for
> you.
> 2) strip the noise words out by using a client side script - search this
> newsgroup for searchpage1.htm
> 3) using tsql parsing on the server possibly a UDF
> 4) write an extended proc to parse them out
> 5) I'm wondering if you got the correct noise file. noise.eng is for
British
> English, noise.enu is for American English. Change the noise word lists
you
> find in
> C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config
> You will have to stop and start the mssearch service to make these
changes.
> "woodysapsucker" <woody@.rohland.org> wrote in message
> news:OMbPJgoEEHA.3344@.tk2msftngp13.phx.gbl...
full
> have
> deleted
have
start
the
> has
> contains(Desc2,
>