Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
David
Larry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Wednesday, March 21, 2012
Sunday, March 11, 2012
does linked server solve performance issue??
Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server?
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved?
Thanks in Advance,
Peter Van Wilrijk.
Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server?
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved?
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>
|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.
|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>
|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server?
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved?
Thanks in Advance,
Peter Van Wilrijk.
Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server?
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved?
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>
|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.
|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>
|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>
Friday, March 9, 2012
Does FTS in SQL 7 have known issues with not indexing records?
I've been testing using a full text index on a few columns in one of my
databases, and I'm having trouble with the index appearing to miss records.
I have stopped all updates on my database, and done a full population of the
SearchTitle field in my STK table. I have then waited until the full text
update has completed, and there are no errors in the event log. I then tried
the following queries:
SELECT STK.ID, STK.SearchTitle FROM STK WHERE
CONTAINS(STK.SearchTitle,'"being" and "jordan"')
Result is zero records. I also tried
SELECT STK.ID, STK.SearchTitle FROM STK WHERE
CONTAINS(STK.SearchTitle,'"being jordan"')
Again, zero records. I then tried
SELECT STK.ID, STK.SearchTitle FROM STK WHERE STK.SearchTitle LIKE '% being
%' and STK.SearchTitle LIKE '% jordan %')
and get 1 result, which is what I expect.
The SearchTitle field contains a stripped down version of book titles in my
table, all fields have a space followed by the words in the table followed
by an ending space (this is so that the current searches I do via the last
example work on whole words without ever finding partial matches). In the
above case the SearchTitle field contains just ' being jordan ' (without the
quotes).
SearchTitle is a varchar(255) field, and there are just under 365572 rows in
the table. The FT index shows 339632 items with 380112 unique words. I have
emptied the noise word files because they were causing problems with
searches, so I know it's not a noise word issue. This indicates that FTS has
skipped around 26000 records. I am currently running another full population
to see if the problem is a temporary one, but I was wondering if there are
known issues with FT indexing that I might be experiencing.
Dan
Being could be a noise word for the noise word list. Do you get the same
number of hits if you search on Jordan as you get if you search on Like '%
Jordan %'?
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:urZxCI5rEHA.1204@.TK2MSFTNGP12.phx.gbl...
> I've been testing using a full text index on a few columns in one of my
> databases, and I'm having trouble with the index appearing to miss
> records.
> I have stopped all updates on my database, and done a full population of
> the
> SearchTitle field in my STK table. I have then waited until the full text
> update has completed, and there are no errors in the event log. I then
> tried
> the following queries:
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE
> CONTAINS(STK.SearchTitle,'"being" and "jordan"')
> Result is zero records. I also tried
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE
> CONTAINS(STK.SearchTitle,'"being jordan"')
> Again, zero records. I then tried
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE STK.SearchTitle LIKE '%
> being
> %' and STK.SearchTitle LIKE '% jordan %')
> and get 1 result, which is what I expect.
> The SearchTitle field contains a stripped down version of book titles in
> my
> table, all fields have a space followed by the words in the table followed
> by an ending space (this is so that the current searches I do via the last
> example work on whole words without ever finding partial matches). In the
> above case the SearchTitle field contains just ' being jordan ' (without
> the
> quotes).
> SearchTitle is a varchar(255) field, and there are just under 365572 rows
> in
> the table. The FT index shows 339632 items with 380112 unique words. I
> have
> emptied the noise word files because they were causing problems with
> searches, so I know it's not a noise word issue. This indicates that FTS
> has
> skipped around 26000 records. I am currently running another full
> population
> to see if the problem is a temporary one, but I was wondering if there are
> known issues with FT indexing that I might be experiencing.
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u71T0k6rEHA.3076@.TK2MSFTNGP10.phx.gbl...
> Being could be a noise word for the noise word list. Do you get the same
> number of hits if you search on Jordan as you get if you search on Like '%
> Jordan %'?
No, instead of getting the expected 58 titles, I get 54. I have cleared the
noise word list before generating the index - SQL was throwing out errors if
1 noise word was passed into the search even if there were other non-noise
words, so I decided that rather than parsing out the noise words and getting
in-exact matches for what customers enter in their searches I'd just index
everything.
Dan
|||After running a full population again it appears to have now indexed
everything. I'll be doing some more preliminary testing before putting this
live though, last thing I want is for customers not to be able to find items
in our database (the example I gave of Being Jordan was the top selling book
a few weeks ago, not having that listed in search results would have been
disastrous for us.
Dan
|||"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> After running a full population again it appears to have now indexed
> everything. I'll be doing some more preliminary testing before putting
this
> live though, last thing I want is for customers not to be able to find
items
> in our database (the example I gave of Being Jordan was the top selling
book
> a few weeks ago, not having that listed in search results would have been
> disastrous for us.
Looking at the event logs I've noticed that despite the item count being
correct, in the information event at completion of an incremental update
there is:
"Detected end of incremental crawl for project <SQLServer SQL0002300005>.
Successfully processed 365614 documents, 0K. Failed to filter 13 documents.
Modified 282 documents."
Followed by a warning event with ID 3051 containing:
"Detected 13 URLs that could not be reached or were denied access in project
<SQLServer SQL0002300005>."
I assume that for some reason 13 items couldn't be accessed when running the
incremental search. I'm running another one now to see if I get the same
messages, as it's only taking around 20 mins to run the incremental compared
to 4 hours running the full population.
I've just looked back at the full population I ran yesterday, and have
noticed that it also logged a warning event (I have updated some records
since this was built, hence the difference in the item counts). Here's the
information one first:
"Detected end of crawl for project <SQLServer SQL0002300005>. Successfully
processed 365652 documents, 0K. Failed to filter 0 documents."
Followed by a warning event:
"Detected 365452 URLs that could not be reached or were denied access in
project <SQLServer SQL0002300005>."
Whereas the previous pair of errors made sense in that the information
message indicates that 13 records couldn't be indexed, and the warning seems
to confirm this, the pair for the full population are confusing in that they
don't match. Does this indicate a potential problem in the indexing system?
Or is the logging of mismatched item counts in the event log a normal
occurrence?
Dan
|||make sure your noise word list has a single space in it, otherwise it will
be using the noise word list found in %windir%\system32.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:urhcK2CsEHA.324@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u71T0k6rEHA.3076@.TK2MSFTNGP10.phx.gbl...
'%
> No, instead of getting the expected 58 titles, I get 54. I have cleared
the
> noise word list before generating the index - SQL was throwing out errors
if
> 1 noise word was passed into the search even if there were other non-noise
> words, so I decided that rather than parsing out the noise words and
getting
> in-exact matches for what customers enter in their searches I'd just index
> everything.
> Dan
>
|||I strongly suggest you move to SQL 2000 for performance and scalability
reasons.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> After running a full population again it appears to have now indexed
> everything. I'll be doing some more preliminary testing before putting
this
> live though, last thing I want is for customers not to be able to find
items
> in our database (the example I gave of Being Jordan was the top selling
book
> a few weeks ago, not having that listed in search results would have been
> disastrous for us.
> Dan
>
|||You can get these errors for a variety of reasons.
You get 0 rows could not be indexed typically for the below reasons.
1) the account SQL Server runs under is not registered with MSSearch. You
can get this when you change the SQL Server service account through control
panel instead of via Enterprise Manager. This will cause the entire table
not to be indexed.
2) verify that the login BUILTIN\Administrator exists in the login folder.
If it does not exist add it in.
You will get xxx rows could not be indexed typically for the below reasons
1) one or more rows were deleted since the last population
2) a row was locked
3) a row could contain a very large amount of data which could not be
extracted in the time MSSearch waits to extract each row.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:u4xryIDsEHA.1816@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> this
> items
> book
been
> Looking at the event logs I've noticed that despite the item count being
> correct, in the information event at completion of an incremental update
> there is:
> "Detected end of incremental crawl for project <SQLServer SQL0002300005>.
> Successfully processed 365614 documents, 0K. Failed to filter 13
documents.
> Modified 282 documents."
> Followed by a warning event with ID 3051 containing:
> "Detected 13 URLs that could not be reached or were denied access in
project
> <SQLServer SQL0002300005>."
> I assume that for some reason 13 items couldn't be accessed when running
the
> incremental search. I'm running another one now to see if I get the same
> messages, as it's only taking around 20 mins to run the incremental
compared
> to 4 hours running the full population.
>
> I've just looked back at the full population I ran yesterday, and have
> noticed that it also logged a warning event (I have updated some records
> since this was built, hence the difference in the item counts). Here's the
> information one first:
> "Detected end of crawl for project <SQLServer SQL0002300005>. Successfully
> processed 365652 documents, 0K. Failed to filter 0 documents."
> Followed by a warning event:
> "Detected 365452 URLs that could not be reached or were denied access in
> project <SQLServer SQL0002300005>."
> Whereas the previous pair of errors made sense in that the information
> message indicates that 13 records couldn't be indexed, and the warning
seems
> to confirm this, the pair for the full population are confusing in that
they
> don't match. Does this indicate a potential problem in the indexing
system?
> Or is the logging of mismatched item counts in the event log a normal
> occurrence?
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eitvdMFsEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I strongly suggest you move to SQL 2000 for performance and scalability
> reasons.
Unfortunately this is not an option at present due to cost - I would need
not only the SQL Server 2000 license, but also 2 SQL Server processor
licenses (dual processor server) so that the database is licensed for use on
my web sites. Last time I looked that was a hefty sum.
Dan
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eNpYhLFsEHA.2732@.TK2MSFTNGP09.phx.gbl...
> make sure your noise word list has a single space in it, otherwise it will
> be using the noise word list found in %windir%\system32.
Yes, I did leave a single space in the noise word files after reading a few
posts in here about it.
Dan
databases, and I'm having trouble with the index appearing to miss records.
I have stopped all updates on my database, and done a full population of the
SearchTitle field in my STK table. I have then waited until the full text
update has completed, and there are no errors in the event log. I then tried
the following queries:
SELECT STK.ID, STK.SearchTitle FROM STK WHERE
CONTAINS(STK.SearchTitle,'"being" and "jordan"')
Result is zero records. I also tried
SELECT STK.ID, STK.SearchTitle FROM STK WHERE
CONTAINS(STK.SearchTitle,'"being jordan"')
Again, zero records. I then tried
SELECT STK.ID, STK.SearchTitle FROM STK WHERE STK.SearchTitle LIKE '% being
%' and STK.SearchTitle LIKE '% jordan %')
and get 1 result, which is what I expect.
The SearchTitle field contains a stripped down version of book titles in my
table, all fields have a space followed by the words in the table followed
by an ending space (this is so that the current searches I do via the last
example work on whole words without ever finding partial matches). In the
above case the SearchTitle field contains just ' being jordan ' (without the
quotes).
SearchTitle is a varchar(255) field, and there are just under 365572 rows in
the table. The FT index shows 339632 items with 380112 unique words. I have
emptied the noise word files because they were causing problems with
searches, so I know it's not a noise word issue. This indicates that FTS has
skipped around 26000 records. I am currently running another full population
to see if the problem is a temporary one, but I was wondering if there are
known issues with FT indexing that I might be experiencing.
Dan
Being could be a noise word for the noise word list. Do you get the same
number of hits if you search on Jordan as you get if you search on Like '%
Jordan %'?
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:urZxCI5rEHA.1204@.TK2MSFTNGP12.phx.gbl...
> I've been testing using a full text index on a few columns in one of my
> databases, and I'm having trouble with the index appearing to miss
> records.
> I have stopped all updates on my database, and done a full population of
> the
> SearchTitle field in my STK table. I have then waited until the full text
> update has completed, and there are no errors in the event log. I then
> tried
> the following queries:
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE
> CONTAINS(STK.SearchTitle,'"being" and "jordan"')
> Result is zero records. I also tried
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE
> CONTAINS(STK.SearchTitle,'"being jordan"')
> Again, zero records. I then tried
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE STK.SearchTitle LIKE '%
> being
> %' and STK.SearchTitle LIKE '% jordan %')
> and get 1 result, which is what I expect.
> The SearchTitle field contains a stripped down version of book titles in
> my
> table, all fields have a space followed by the words in the table followed
> by an ending space (this is so that the current searches I do via the last
> example work on whole words without ever finding partial matches). In the
> above case the SearchTitle field contains just ' being jordan ' (without
> the
> quotes).
> SearchTitle is a varchar(255) field, and there are just under 365572 rows
> in
> the table. The FT index shows 339632 items with 380112 unique words. I
> have
> emptied the noise word files because they were causing problems with
> searches, so I know it's not a noise word issue. This indicates that FTS
> has
> skipped around 26000 records. I am currently running another full
> population
> to see if the problem is a temporary one, but I was wondering if there are
> known issues with FT indexing that I might be experiencing.
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u71T0k6rEHA.3076@.TK2MSFTNGP10.phx.gbl...
> Being could be a noise word for the noise word list. Do you get the same
> number of hits if you search on Jordan as you get if you search on Like '%
> Jordan %'?
No, instead of getting the expected 58 titles, I get 54. I have cleared the
noise word list before generating the index - SQL was throwing out errors if
1 noise word was passed into the search even if there were other non-noise
words, so I decided that rather than parsing out the noise words and getting
in-exact matches for what customers enter in their searches I'd just index
everything.
Dan
|||After running a full population again it appears to have now indexed
everything. I'll be doing some more preliminary testing before putting this
live though, last thing I want is for customers not to be able to find items
in our database (the example I gave of Being Jordan was the top selling book
a few weeks ago, not having that listed in search results would have been
disastrous for us.
Dan
|||"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> After running a full population again it appears to have now indexed
> everything. I'll be doing some more preliminary testing before putting
this
> live though, last thing I want is for customers not to be able to find
items
> in our database (the example I gave of Being Jordan was the top selling
book
> a few weeks ago, not having that listed in search results would have been
> disastrous for us.
Looking at the event logs I've noticed that despite the item count being
correct, in the information event at completion of an incremental update
there is:
"Detected end of incremental crawl for project <SQLServer SQL0002300005>.
Successfully processed 365614 documents, 0K. Failed to filter 13 documents.
Modified 282 documents."
Followed by a warning event with ID 3051 containing:
"Detected 13 URLs that could not be reached or were denied access in project
<SQLServer SQL0002300005>."
I assume that for some reason 13 items couldn't be accessed when running the
incremental search. I'm running another one now to see if I get the same
messages, as it's only taking around 20 mins to run the incremental compared
to 4 hours running the full population.
I've just looked back at the full population I ran yesterday, and have
noticed that it also logged a warning event (I have updated some records
since this was built, hence the difference in the item counts). Here's the
information one first:
"Detected end of crawl for project <SQLServer SQL0002300005>. Successfully
processed 365652 documents, 0K. Failed to filter 0 documents."
Followed by a warning event:
"Detected 365452 URLs that could not be reached or were denied access in
project <SQLServer SQL0002300005>."
Whereas the previous pair of errors made sense in that the information
message indicates that 13 records couldn't be indexed, and the warning seems
to confirm this, the pair for the full population are confusing in that they
don't match. Does this indicate a potential problem in the indexing system?
Or is the logging of mismatched item counts in the event log a normal
occurrence?
Dan
|||make sure your noise word list has a single space in it, otherwise it will
be using the noise word list found in %windir%\system32.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:urhcK2CsEHA.324@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u71T0k6rEHA.3076@.TK2MSFTNGP10.phx.gbl...
'%
> No, instead of getting the expected 58 titles, I get 54. I have cleared
the
> noise word list before generating the index - SQL was throwing out errors
if
> 1 noise word was passed into the search even if there were other non-noise
> words, so I decided that rather than parsing out the noise words and
getting
> in-exact matches for what customers enter in their searches I'd just index
> everything.
> Dan
>
|||I strongly suggest you move to SQL 2000 for performance and scalability
reasons.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> After running a full population again it appears to have now indexed
> everything. I'll be doing some more preliminary testing before putting
this
> live though, last thing I want is for customers not to be able to find
items
> in our database (the example I gave of Being Jordan was the top selling
book
> a few weeks ago, not having that listed in search results would have been
> disastrous for us.
> Dan
>
|||You can get these errors for a variety of reasons.
You get 0 rows could not be indexed typically for the below reasons.
1) the account SQL Server runs under is not registered with MSSearch. You
can get this when you change the SQL Server service account through control
panel instead of via Enterprise Manager. This will cause the entire table
not to be indexed.
2) verify that the login BUILTIN\Administrator exists in the login folder.
If it does not exist add it in.
You will get xxx rows could not be indexed typically for the below reasons
1) one or more rows were deleted since the last population
2) a row was locked
3) a row could contain a very large amount of data which could not be
extracted in the time MSSearch waits to extract each row.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:u4xryIDsEHA.1816@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> this
> items
> book
been
> Looking at the event logs I've noticed that despite the item count being
> correct, in the information event at completion of an incremental update
> there is:
> "Detected end of incremental crawl for project <SQLServer SQL0002300005>.
> Successfully processed 365614 documents, 0K. Failed to filter 13
documents.
> Modified 282 documents."
> Followed by a warning event with ID 3051 containing:
> "Detected 13 URLs that could not be reached or were denied access in
project
> <SQLServer SQL0002300005>."
> I assume that for some reason 13 items couldn't be accessed when running
the
> incremental search. I'm running another one now to see if I get the same
> messages, as it's only taking around 20 mins to run the incremental
compared
> to 4 hours running the full population.
>
> I've just looked back at the full population I ran yesterday, and have
> noticed that it also logged a warning event (I have updated some records
> since this was built, hence the difference in the item counts). Here's the
> information one first:
> "Detected end of crawl for project <SQLServer SQL0002300005>. Successfully
> processed 365652 documents, 0K. Failed to filter 0 documents."
> Followed by a warning event:
> "Detected 365452 URLs that could not be reached or were denied access in
> project <SQLServer SQL0002300005>."
> Whereas the previous pair of errors made sense in that the information
> message indicates that 13 records couldn't be indexed, and the warning
seems
> to confirm this, the pair for the full population are confusing in that
they
> don't match. Does this indicate a potential problem in the indexing
system?
> Or is the logging of mismatched item counts in the event log a normal
> occurrence?
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eitvdMFsEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I strongly suggest you move to SQL 2000 for performance and scalability
> reasons.
Unfortunately this is not an option at present due to cost - I would need
not only the SQL Server 2000 license, but also 2 SQL Server processor
licenses (dual processor server) so that the database is licensed for use on
my web sites. Last time I looked that was a hefty sum.
Dan
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eNpYhLFsEHA.2732@.TK2MSFTNGP09.phx.gbl...
> make sure your noise word list has a single space in it, otherwise it will
> be using the noise word list found in %windir%\system32.
Yes, I did leave a single space in the noise word files after reading a few
posts in here about it.
Dan
Sunday, February 26, 2012
does a view refresh itself when i reference it? (was "Question On Views")
guys, ive never worked with Views before so forgive me.
i know how to create one, and that it creates a virtual table in memory, but i've got one small question.
if i create a view:
CREATE view dbo.myView
as
select Distinct FirstName,LastName from SomeTable
When ever i reference that view, such as
Select FirstName,LastName
from myView
where LastName like 'Jo%'
does that View Refresh itself??
in other words does it run each time i Reference it? or is it static from when i created it.
Wouldnt it be easier just to use a #TempTable or some other Table thats used to hold a few values?
thanks for any help
rikDont worry man ,view is not static .It always fetch records from base table.It is uptodate unless u r using index on views|||You know - I've always thought that the definition of a view as something like a virtual table as very confusing. When I studied relational databases the chapter on views made an assumption that the reader would be very confused at what differentiated a view from a table and spent line after line emphasising the difference. "No " thought I "I can very well differentiate a view from a table - what the flip is the distinction between a view and a query?"
The way I tend to think of views (indexed views excluded) is that a view is best thought of as a persistent, optimised, stored query that can usually be treated in the same way that you would treat a table as far as viewing data is concerned. It is, however, just that - a view of the data. As such, it does not store any data itself. Every time you call it it views the data afresh.
HTH|||thank you guys so much. that's exactly the answer i was looking for.
thanks again
rik
i know how to create one, and that it creates a virtual table in memory, but i've got one small question.
if i create a view:
CREATE view dbo.myView
as
select Distinct FirstName,LastName from SomeTable
When ever i reference that view, such as
Select FirstName,LastName
from myView
where LastName like 'Jo%'
does that View Refresh itself??
in other words does it run each time i Reference it? or is it static from when i created it.
Wouldnt it be easier just to use a #TempTable or some other Table thats used to hold a few values?
thanks for any help
rikDont worry man ,view is not static .It always fetch records from base table.It is uptodate unless u r using index on views|||You know - I've always thought that the definition of a view as something like a virtual table as very confusing. When I studied relational databases the chapter on views made an assumption that the reader would be very confused at what differentiated a view from a table and spent line after line emphasising the difference. "No " thought I "I can very well differentiate a view from a table - what the flip is the distinction between a view and a query?"
The way I tend to think of views (indexed views excluded) is that a view is best thought of as a persistent, optimised, stored query that can usually be treated in the same way that you would treat a table as far as viewing data is concerned. It is, however, just that - a view of the data. As such, it does not store any data itself. Every time you call it it views the data afresh.
HTH|||thank you guys so much. that's exactly the answer i was looking for.
thanks again
rik
Tuesday, February 14, 2012
Do you really use SqlDataSource?
Hi,
I have an application w/ n-tier design so I've never used the SqlDataSource up to this point but having to do my own sorting for GridViews is not something I want to keep dealing with. I'd like to take advantage of some of the packaged features too.
My question is: are there any purists out there who opted to use SqlDataSource. If so, what do you think about it? It's nice that SqlDataSource makes things simple but having data classes and dealing w/ exceptions in those classes certainly make an application pretty robust. Should I entertain the idea of using SqlDataSource or stay as a purist and keep doing things the old fashioned way?
If you are the only person working on the app, then using a SqlDataSource for relatively simple databinding so you can take advantage of the benefits it brings is fine, in my opinion. Mind you, you will lose the soubriquet of "proper programmer" and your friends will no longer want to talk to you. On the other hand, you can retain your purity by developing your own sorting and paging classes which can be as reusable as the SqlDataSource.
Purists would never use the SqlDataSource. They wouldn't be purists if they did. Would they?
Having said that, the ObjectDataSource isn't too difficult to configure for paging and sorting:
http://www.asp.net/learn/data-access/tutorial-44-vb.aspx
http://davidgardiner.blogspot.com/2006/06/objectdatasource-and-sorting-part-2.html
and you get to keep your credibility.
Subscribe to:
Posts (Atom)