Does SQL Server move records between partitions when updating the partition key column?

If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?

ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?

It will move to the other partition.

Thanks,|||Thank you. That's the kind of behavior I was looking for.

Does SQL have a function that return "null" for records which dont exist in a FK realation

Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.

Thank you very much.

when doing select with 'join's over two tables it would return null.

For example, you have customers and customerdetails table

if you do

select *

from customers

left join customerdetails on customers.customerId = customerdetails.customerId

if there is no customer details for a customer the fields corresponding to the customer details table would be null.

Check the different types of joins 'left join', 'inner join', 'outer join', 'cross join'...i think that's all :)

Cheers,

Yani

|||

Thank you very much indeedYani Dzhurov.

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

Does it store all the results to tempdb database when I query against a large table which joins

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

It doesn't store all the results - but it may store some intermediate results if it needs to.

You really shouldn't be putting tempdb on your local system drive. tempdb always has a lot of activity (typically the busiest database in any system), and ideally should be on its own disk - a fast one.

If you have over 4 million records in a single table, 1GB doesn't sound too big for tempdb. But I appreciate this doesn't fix your immediate problem.

Rob|||

Hi, Rob,

Thank you for your kind advices.

But how can we change the storage location of the tempdb database? It looks like tempdb database can not be detached.

I am looking forward to hearing from you further for your kind advices.

With best regards,

Yours sincerely,

|||

And also, though I have moved the data files of tempdb to other places, but when I restarted the database engine, the system automatically generates the data files of tempdb again back to its root data files?

Please give me some more advices for this.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||I believe moving tempdb is as simple as altering the file paths and restarting SQL Server. It'll create the new files automatically. I'm not sure if it removes the old ones automatically, so you may have to to some brief housekeeping once the database is up and running in its new location.

ALTER DATABASE tempdb MODIFY FILE (NAME = logical_filename, FILENAME = 'new_physical_file_path')

Just make sure you do the log in addition to all the data files you have.
|||

Hi, davidbrit2,

Thank you for your kind attention.

But I failed to modify the files paths for the data files of the tempdb database, I did restart the service, but the data files of tempdb again went back to its original file paths. Therefore I does not seem like we are able to change the file paths of the data files of tempdb.

Any more ideas? Thanks a lot in advance.

With best regards,

Yours sincerely,

|||You need to alter the database to tell it to have its files in a different location. But then you need to restart SQL Server (ie, the service - use SQL Configuration Manager) to actually have it start using those new locations. Once you've restarted SQL, you should see the new files start to be used. Then you can delete the old ones.

Hope this helps,

Rob|||

Hello Helen999888,

Let me start with giving you a brief of what is tempdb.
The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

User Objects

User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions
|||

Hi, HS_DJ,

Thanks a lot for your very kind and helpful advices. Got it done now.

With best regards,

Yours sincerely,

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

does enyone know...

Does enyone know how to get all byte[] from records in database.
When user click on the button to give him all record in some arhive??

Some code?(C#)

Thanx

OgaCould you ask your question in a different way, giving more explanation?|||In datatable I have field with type: "binary data", and I put into it some jpeg, doc, etc files. Then I have a web form with download button, when user click it i wont to give him all filesfrom that datatable.

I hope this explanation is better|||You might find this blog post (as well as the links it contains) to be helpful:

How to serve binary resouces from a database

Does DB Size decrease when I delete a huge table ?

Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??The size will only decrease if you use the shrinkdb command.|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

When and why should we use Shrink DB ??|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

Would the deleted data be still on the database physical file ?|||The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??|||Originally posted by rokslide
The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??

Can u give a quick example of how I use ShrinkDB ?|||Originally posted by forXLDB
Can u give a quick example of how I use ShrinkDB ?
How do I know what size to reduce to ?
(If the size doesnt decrease even after deleting tables, how do i know what must be the exact size occupied by all the tables)...
I would have deleted and created lots of huge files involving around a million records atleast 10 times..

and above all, does the size effect the sql server performance in any way ?|||straight from the books online

This example decreases the size of the files in the UserDB user database to allow 10 percent free space in the files of UserDB.

DBCC SHRINKDATABASE (UserDB, 10)
GO

or you can do it through the EM|||Originally posted by rokslide
straight from the books online

or you can do it through the EM

i just did use for another test database, it shrunk more than half the size...

thx anyway !!1

Still looking for the answer on any performance degradation if the db file size is more ?|||Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.|||Originally posted by rokslide
Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.
thx again...

lemme know any functions !!!|||Since the log file takes the big size of the database, you can use the following script to reduce the file size:

USE DatabaseName
GO
DBCC SHRINKFILE ('DatabaseName_Log', Size)
GO

Here "Size" is the number of MB, i.e., if you put 100, that means the file will be shrinked to 100 MB.|||Since you are already working in em - when you right click on the database go to all task ->shrink database ->files... Under database file, look at the data file and log file. What is the current size/space used ? Are you backing up the database/transaction log ? What is the recovery model ?

What is the performance issue ?|||You can use "dbcc showcontig" to display fragmentation info. Yes performance will be affected - you need to either drop/recreate the indexes, use dbcc dbreindex or dbcc indexdefrag. Since you deleted so many records (more than half), fragmentation is a good start for performance issues.|||Do you have access to Books Online (BOL) ?|||An addition to rnealejr's post on dbcc indexdefrag and recreating indexes. If you have tables with a large amount of data input or modification take a good look at the indexes. If you use a non-seed value for your index then increasing the fillfactor number can help to reduce fragmentation in the future. I.E if your customer table uses first four characters of the name plus the zipcode for a customerID then you would be inserting new records into the middle of the table frequently. Once the fillfactor is used up then the next record inserted will create a new page and now your data is getting fragmented which does impact performance as mentioned be other posters.

Now that you've opened this pandoras box, you could spend a week in BOL learning how to tweak each tables index based on usage, calculating how many records fit on a page, use of varchar over char, benifits/penalties of seed indexes...............|||Originally posted by rnealejr
Do you have access to Books Online (BOL) ?
Yes. I can read BOL

Does COUNT(*) use clusterd index?

Hi folks,
I have a large table which has approximately 150 million records. I am
running a SELECT COUNT(*) query to find out the exact number of rows
present in that table. It is running for more than an hour, yet to
complete. Estimated execution plan shows that it uses clusted index
scan. But it does seem it is using the index. What is the best way to
find out the exact number of records present in that table quickly.
Thanks in advance.
--
*** Sent via Developersdex http://www.examnotes.net ***COUNT(*) is the best way but it sounds like you are being blocked. What
does sp_who2 say? And by the way SQL Server will choose the best index with
a count(*) and it does not have to be the clustered index.
Andrew J. Kelly SQL MVP
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:evTgGq$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||This might help.
http://toponewithties.blogspot.com/...count-them.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:evTgGq$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||If you don't ned to be up to the second, you can check sysindexes. To be
more accurate, you can issue DBCC UPDATEUSAGE against the table first, but
this will take time just like the scan does...
What's probably happening is that there is activity against the table. So,
another way to make the count return quicker (though, again, it may be out
of date by the time your brain processes it) is to use SELECT COUNT(*) FROM
table WITH (NOLOCK);
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:evTgGq$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Ideally, you would have an non-clustered index on a narrow column so you wou
ld fit many rows on such
an index page. SQL Server would now scan that index instead of scanning the
data pages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:evTgGq$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||If you don't require an exact answer, it isn't necessary use a
SELECT count(*) query on the rows in a table to get the row
count. SQL Server keeps the row count in sysindexes and it
can be retrieved there. The key is to select the correct
record from sysindexes.
Sysindexes is a system table that exists in every database.
SQL Server maintains at least one row in sysindexes for every
user table. A few of the most important columns are:
Column Data Type Description
-- -- ---
id int ID of the table referred to by this row
indid int See the text that follows...
rowcnt bigint Number of rows in the index
The indid column tells us what part of the table structure this
row of sysindexes is referring to:
indid value Description
-- ----
0 Table data when there is no clustered index
1 Refers to the clustered index
2 - 254 Non-clustered indexes
255 Text or Image data pages
A table will only have an entry in sysindexes with an indid value
of for 0 or 1, never both. That's the entry that we're
interested in because its rowcnt field gives is the number of
rows in the table. There's a query that shows the table, index
and indid from the pubs database:
/-- Copy From Below this line --USE pubs
GO
SELECT so.[name] as [table name]
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]
\-- Stop copying above this line --/
(Results)
table name Index Name indid
-- -- --
authors UPKCL_auidind 1
discounts NULL 0
employee employee_ind 1
jobs PK__jobs__117F9D94 1
pub_info UPKCL_pubinfo 1
publishers UPKCL_pubind 1
roysched NULL 0
sales UPKCL_sales 1
stores UPK_storeid 1
titleauthor UPKCL_taind 1
titles UPKCL_titleidind 1
As you can see from the results, most of the indexes are
clustered (indid=1) but a few tables such as discounts
don't have a clustered index (indid=0).
I started this newsletter with "If you don't need an exact
answer..." That's because there are times when rowcnt is
not the exact number of records in the table. This can
be corrected by updating statistics on the table with:
dbcc updateusage
go
Here's the CREATE FUNCTION script for udf_Tbl_RowCOUNT:
/-- Copy From Below this line --
CREATE FUNCTION dbo.udf_Tbl_RowCOUNT (
@.sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
/*
* Returns the row count for a table by examining sysindexes.
* This function must be run in the same database as the table.
*
* Common Usage:
SELECT dbo.udf_Tbl_RowCOUNT ('')
* Test
PRINT 'Test 1 Bad table ' + CASE WHEN SELECT
dbo.udf_Tbl_RowCOUNT ('foobar') is NULL
THEN 'Worked' ELSE 'Error' END
* ) Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
****************************************
***********************/
AS BEGIN
DECLARE @.nRowCount INT -- the rows
DECLARE @.nObjectID int -- Object ID
SET @.nObjectID = OBJECT_ID(@.sTableName)
-- Object might not be found
IF @.nObjectID is null RETURN NULL
SELECT TOP 1 @.nRowCount = rows
FROM sysindexes
WHERE id = @.nObjectID AND indid < 2
RETURN @.nRowCount
END
GO
GRANT EXECUTE ON [dbo].[udf_Tbl_RowCOUNT] TO PUBLIC
GO
\-- Stop copying above this line --/
Let's use it:
/-- Copy From Below this line --
use pubs -- assuming the UDF was created in pubs
go
SELECT [name]
, dbo.udf_Tbl_RowCOUNT ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [name]
GO
\-- Stop copying above this line --/
(Results)
name Row Count
-- --
authors 24
discounts 3
employee 43
jobs 14
pub_info 8
publishers 8
roysched 86
sales 21
stores 6
titleauthor 25
titles 18
That's all there is to it.
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Venkat" wrote:

> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***
>