Indexes and Deadlocks: sp3

Showing posts with label sp3. Show all posts

Monday, March 19, 2012

indexing image columns

Hi,
I'm building a search engine for a website using sql server 2000 (sp3).
My development invironment is Windows XP Pro and the production server
is Windows 2003, and I'm getting the same problem on both machines.
I have two tables in the cataolg, one contaning only text fields
(varchar and ntext). The results come out great for this table, no
problem here. The second table has an image field that can contain just
about any kind of documents: .txt, .doc, .pdf, .xls, .mpg, .zip, etc.
And this image column can sometimes be empty for cetain records. There's
also a column indicating the file type and I'm using the file extension
for this. I wasn't sure what to put here I tried the mime type, the
extension, with and without the period this never changed anything. I
read somewhere that it should be the file extension with the period so
I've set it back to that.
I've done a lot of searching and reading in the past few days but can't
find the problem. I've tryed searches on various types of documents,
including .txt, .doc and .pdf. I'm not absolutely sure but I think my
image column is't being indexed at all; this same table also has a
"title" and "description" field included in the index and if I search
for text contained in either of those 2 columns they turn up in the results.
Any ideas?
Any help would be much appreciated.
tia
Lucas,
Yes. First of all, could you post the full output of -- SELECT @.@.version --
as well as the table schema of both your tables (via sp_help <table_name>)
as will help in understanding your environment. The datatype, size and
nullablity of the "file extension" column is very important in getting this
to work correctly. You can include or exclude the "." period when populating
the values in your "file extension" column, but then you will need to define
it as a varchar(4) or you can use the sysname datatype.
As you second table contains an image column and the "file extension"
column, it can only hold binary file types, such as doc, .pdf, .xls, .mpg,
..zip, but not .txt. For text (.txt) files, you must store this type file in
a Text or NText datatype as the pure text will be FT Indexed without the
file extension. For non-MS Office file types, such as Adobe PDF, MPG and ZIP
files, you will need to install 3rd party IFilters that support these file
types, these can be downloaded from:
Adobe PDF IFilter v6.0:
http://www.adobe.com/support/downloa...11&fileID=2457
Zip IFilter:
http://www.ifiltershop.com/zipfilter.html
Zip IFilter:
http://www.4-share.com/
mp3 (MEPG) IFilter
http://www.meticulus.com/mp3filter.html
Microsoft Office file types (.doc, .xls, .ppt) and text (.txt) and HTML
(.htm) files are FT Indexed out-of-the-box by SQL Server 2000. See SQL
Server 2000 BOL title "Filtering Supported File Types" from more info.
Finally, you should always review the server's Application event log for
information on the success &/or failure of FT Indexing specific file types.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"lucas" <lucarc@.hotmail.qc> wrote in message
news:xgXDd.32709$Y61.1126448@.wagner.videotron.net. ..
> Hi,
> I'm building a search engine for a website using sql server 2000 (sp3).
> My development invironment is Windows XP Pro and the production server
> is Windows 2003, and I'm getting the same problem on both machines.
> I have two tables in the cataolg, one contaning only text fields
> (varchar and ntext). The results come out great for this table, no
> problem here. The second table has an image field that can contain just
> about any kind of documents: .txt, .doc, .pdf, .xls, .mpg, .zip, etc.
> And this image column can sometimes be empty for cetain records. There's
> also a column indicating the file type and I'm using the file extension
> for this. I wasn't sure what to put here I tried the mime type, the
> extension, with and without the period this never changed anything. I
> read somewhere that it should be the file extension with the period so
> I've set it back to that.
> I've done a lot of searching and reading in the past few days but can't
> find the problem. I've tryed searches on various types of documents,
> including .txt, .doc and .pdf. I'm not absolutely sure but I think my
> image column is't being indexed at all; this same table also has a
> "title" and "description" field included in the index and if I search
> for text contained in either of those 2 columns they turn up in the
results.
> Any ideas?
> Any help would be much appreciated.
> tia
|||Hi John,
Thanks for your response. I finally got it to work; it was nothing more
than an error in my query, I wasn't actualy ever searching my image
column.....d'oh!
It's working fine now.

Wednesday, March 7, 2012

Indexes Enabled or Disabled?

Hey folks,
SQL Server 2000 SP3/4
How can I determine if an index is enabled or disabled? The information must
be kept somewhere, but I can't seem to find it. Not in sysindexes,
information_schema, sp_helpindex.
Easy to get in SQL 2005 mind you.
--
Thanks,
Scott H.Well, it's been a while, but I don't think you can disable an index on SQL
Server 2000, can you?
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:D12CF217-6C70-4483-B07A-3E00F0A475AA@.microsoft.com...
> Hey folks,
> SQL Server 2000 SP3/4
> How can I determine if an index is enabled or disabled? The information
> must
> be kept somewhere, but I can't seem to find it. Not in sysindexes,
> information_schema, sp_helpindex.
> Easy to get in SQL 2005 mind you.
>
> --
> Thanks,
> Scott H.|||> Well, it's been a while, but I don't think you can disable an index on SQL
> Server 2000, can you?
Nope. That ability was introduced in SQL Server 2005.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uFrZOVhqIHA.552@.TK2MSFTNGP06.phx.gbl...
> Well, it's been a while, but I don't think you can disable an index on SQL
> Server 2000, can you?
>
>
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:D12CF217-6C70-4483-B07A-3E00F0A475AA@.microsoft.com...
>> Hey folks,
>> SQL Server 2000 SP3/4
>> How can I determine if an index is enabled or disabled? The information
>> must
>> be kept somewhere, but I can't seem to find it. Not in sysindexes,
>> information_schema, sp_helpindex.
>> Easy to get in SQL 2005 mind you.
>>
>> --
>> Thanks,
>> Scott H.
>|||Ok, that's what I thought. So that makes it easy.
SELECT *, Enabled = 1
FROM sysindexes;
:-)
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OOgQMfhqIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Well, it's been a while, but I don't think you can disable an index on
>> SQL Server 2000, can you?
> Nope. That ability was introduced in SQL Server 2005.
>|||On Tue, 29 Apr 2008 12:21:43 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>Well, it's been a while, but I don't think you can disable an index on SQL
>Server 2000, can you?
Doesn't look that way from the docs. ALTER INDEX had not been
introduced yet.
Roy Harvey
Beacon Falls, CT|||Good thinking, Aaron!
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZZRWhhqIHA.4476@.TK2MSFTNGP04.phx.gbl...
> Ok, that's what I thought. So that makes it easy.
> SELECT *, Enabled = 1
> FROM sysindexes;
> :-)
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:OOgQMfhqIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Well, it's been a while, but I don't think you can disable an index on SQL Server 2000, can you?
>> Nope. That ability was introduced in SQL Server 2005.
>>
>

indexes being ignored

sql2k sp3
Two boxes. Same db's, tables, indexes, queries. Indexes
are being used by 1 but not the other. Any ideas?
Thanks in advance.I'd check the statistics on the tables in question (SELECT
STATS_DATE(id, indid) FROM sysindexes). If the table that
isn't using an index has older statistics, then update
them.
I'm assuming the data content of the table is identical
(same number of rows, same result set, etc.).
Bruce.
>--Original Message--
>sql2k sp3
>Two boxes. Same db's, tables, indexes, queries. Indexes
>are being used by 1 but not the other. Any ideas?
>Thanks in advance.
>.
>|||I tried updating the stats to no avail. I didnt realize
amount of data would have any effect. The one ignoring the
indexes has data. The one using the indexes is empty. Any
ideas?
>--Original Message--
>I'd check the statistics on the tables in question
(SELECT
>STATS_DATE(id, indid) FROM sysindexes). If the table
that
>isn't using an index has older statistics, then update
>them.
>I'm assuming the data content of the table is identical
>(same number of rows, same result set, etc.).
>Bruce.
>>--Original Message--
>>sql2k sp3
>>Two boxes. Same db's, tables, indexes, queries. Indexes
>>are being used by 1 but not the other. Any ideas?
>>Thanks in advance.
>>.
>.
>|||If the selectivity of the value is too low it may not use the index. If you
don't have any data then the selectivity is pretty high<g>.
--
Andrew J. Kelly
SQL Server MVP
"chris" <chrisr@.fingps.com> wrote in message
news:050601c3789d$d6ec6100$a101280a@.phx.gbl...
> I tried updating the stats to no avail. I didnt realize
> amount of data would have any effect. The one ignoring the
> indexes has data. The one using the indexes is empty. Any
> ideas?
>
> >--Original Message--
> >I'd check the statistics on the tables in question
> (SELECT
> >STATS_DATE(id, indid) FROM sysindexes). If the table
> that
> >isn't using an index has older statistics, then update
> >them.
> >
> >I'm assuming the data content of the table is identical
> >(same number of rows, same result set, etc.).
> >
> >Bruce.
> >
> >>--Original Message--
> >>sql2k sp3
> >>
> >>Two boxes. Same db's, tables, indexes, queries. Indexes
> >>are being used by 1 but not the other. Any ideas?
> >>
> >>Thanks in advance.
> >>.
> >>
> >.
> >|||Are the physical machines the same? Same number of CPUs
and Memory? If not then execution plans can be different.
>--Original Message--
>If the selectivity of the value is too low it may not use
the index. If you
>don't have any data then the selectivity is pretty
high<g>.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"chris" <chrisr@.fingps.com> wrote in message
>news:050601c3789d$d6ec6100$a101280a@.phx.gbl...
>> I tried updating the stats to no avail. I didnt realize
>> amount of data would have any effect. The one ignoring
the
>> indexes has data. The one using the indexes is empty.
Any
>> ideas?
>>
>> >--Original Message--
>> >I'd check the statistics on the tables in question
>> (SELECT
>> >STATS_DATE(id, indid) FROM sysindexes). If the table
>> that
>> >isn't using an index has older statistics, then update
>> >them.
>> >
>> >I'm assuming the data content of the table is identical
>> >(same number of rows, same result set, etc.).
>> >
>> >Bruce.
>> >
>> >>--Original Message--
>> >>sql2k sp3
>> >>
>> >>Two boxes. Same db's, tables, indexes, queries.
Indexes
>> >>are being used by 1 but not the other. Any ideas?
>> >>
>> >>Thanks in advance.
>> >>.
>> >>
>> >.
>> >
>
>.
>

indexes are slowing down my query

sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.com/composite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
*** note: someone mentioned yesterday (thank you) that the
results are due to the ITW's index being a covered index.
That makes sense. But I am still not getting why my query
takes 20 seconds longer after adding individual indexes?
***
TIA, ChrisRHi,
Here is a bit of information I found in BOL:
"
Covered queries can improve performance. Covered queries
are queries where all the columns specified in the query
are contained within the same index. For example, a query
retrieving columns a and b from a table that has a
composite index created on columns a, b, and c is
considered covered. Creating indexes that cover a query
can improve performance because all the data for the query
is contained within the index itself; only the index
pages, not the data pages, of the table must be referenced
to retrieve the data, thereby reducing overall I/O.
Although adding columns to an index to cover queries can
improve performance, maintaining the extra columns in the
index incurs update and storage costs.
"
It seems that your individual indexes DO NOT cover your
query. Each time you run your query, the optimizer is
using 4 different index (one at a time) and it has
increased disk i/o. Increased disk i/o means slow query
response.
hth
DeeJay
>--Original Message--
>sql2k sp3
>Ive got a query that takes 30 seconds to run:
>
> select top 50 customerkey as customerkey from
transdtl
> where MerchName in ('DTV*DIRECTV
SERVICE','DTV*DIRECTV
>SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey
> order by CustomerKey,min(TransDate)
>
>Index Tuning Wizard wants to create an Composite Index on
>MerchName, CustomerKey, TranCode, and TransDate. There is
>already an Composite Index on TransDate, TranCode(in this
>order) but for kicks I do the Wizards deal. Pretty cool
in
>that my query speeds up to only 1 second. Any yes, I
>cleared the cache. Ive always followed the advice from
>this article:
>http://www.sql-server-
performance.com/composite_indexes.asp
>and for the most part used single column Indexes instead
>of Composites. So, I drop the newly created Composite
>Indexes, create 4 single coulmn Indexes, and expect it to
>be about the same. Not only was it slower, but it was
>slower now than when there was no Indexes on these
>columns. 50 seconds. Ive tested this alot over the last
>couple of days and its very consistant. Im getting a
table
>scan on the TranCode coulmn I noticed. I used an Index
>Hint and have the same thing. Why is the Composite
soooooo
>much faster than the individuals? Why Is a Table Scan
>occuring? Why is it slower than before? All ideas are
>appreciated.
>*** note: someone mentioned yesterday (thank you) that
the
>results are due to the ITW's index being a covered index.
>That makes sense. But I am still not getting why my query
>takes 20 seconds longer after adding individual indexes?
>***
>TIA, ChrisR
>.
>|||>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?
>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>Covered queries can improve performance. Covered queries
>are queries where all the columns specified in the query
>are contained within the same index. For example, a query
>retrieving columns a and b from a table that has a
>composite index created on columns a, b, and c is
>considered covered. Creating indexes that cover a query
>can improve performance because all the data for the
query
>is contained within the index itself; only the index
>pages, not the data pages, of the table must be
referenced
>to retrieve the data, thereby reducing overall I/O.
>Although adding columns to an index to cover queries can
>improve performance, maintaining the extra columns in the
>index incurs update and storage costs.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>>--Original Message--
>>sql2k sp3
>>Ive got a query that takes 30 seconds to run:
>>
>> select top 50 customerkey as customerkey from
>transdtl
>> where MerchName in ('DTV*DIRECTV
>SERVICE','DTV*DIRECTV
>>SERVICER39')
>> and TranCode not in (7008,7023)
>> Group By CustomerKey
>> order by CustomerKey,min(TransDate)
>>
>>Index Tuning Wizard wants to create an Composite Index
on
>>MerchName, CustomerKey, TranCode, and TransDate. There
is
>>already an Composite Index on TransDate, TranCode(in
this
>>order) but for kicks I do the Wizards deal. Pretty cool
>in
>>that my query speeds up to only 1 second. Any yes, I
>>cleared the cache. Ive always followed the advice from
>>this article:
>>http://www.sql-server-
>performance.com/composite_indexes.asp
>>and for the most part used single column Indexes instead
>>of Composites. So, I drop the newly created Composite
>>Indexes, create 4 single coulmn Indexes, and expect it
to
>>be about the same. Not only was it slower, but it was
>>slower now than when there was no Indexes on these
>>columns. 50 seconds. Ive tested this alot over the last
>>couple of days and its very consistant. Im getting a
>table
>>scan on the TranCode coulmn I noticed. I used an Index
>>Hint and have the same thing. Why is the Composite
>soooooo
>>much faster than the individuals? Why Is a Table Scan
>>occuring? Why is it slower than before? All ideas are
>>appreciated.
>>*** note: someone mentioned yesterday (thank you) that
>the
>>results are due to the ITW's index being a covered
index.
>>That makes sense. But I am still not getting why my
query
>>takes 20 seconds longer after adding individual indexes?
>>***
>>TIA, ChrisR
>>.
>.
>|||On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:
>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 19, 2012

indexing image columns

Wednesday, March 7, 2012

Indexes Enabled or Disabled?

indexes being ignored

indexes are slowing down my query

indexes are slowing down my query

Indexes and Deadlocks

Blog Archive

About Me