DBCC pintable

Hi everyone,
I'm currently running sql 2k on win2k3, all with the
latest patches in a clustered/merge replication
environment. I'm considering running the dbcc pintable
command for one small (~38MB), but very hot table in our
database. I've never used this command before so I was
looking for any direction here or any "gotchas" I should
look out for. Any advice would be appreciated. Thanks.
Leon
Leon,
If the table is small and used a lot, you're not going to get any benefit
from pinning it; it will already be in memory anyway do to its constantly
being accessed. Remember that pinning doesn't tell SQL Server to load the
data into memory; rather, it tells it to keep it in memory once it's been
loaded by something else. And SQL Server will do that anyway if requests
keep coming in for the same data.
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k on win2k3, all with the
> latest patches in a clustered/merge replication
> environment. I'm considering running the dbcc pintable
> command for one small (~38MB), but very hot table in our
> database. I've never used this command before so I was
> looking for any direction here or any "gotchas" I should
> look out for. Any advice would be appreciated. Thanks.
> Leon
|||I agree 100% with Adam here. Pinning tables usually has a more negative
effect than a positive one since it will keep any data in memory even if it
has only been accessed a single time. SQL Server usually does a much better
job at managing the cache than a human can.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OB5TGSolEHA.3720@.TK2MSFTNGP12.phx.gbl...
> Leon,
> If the table is small and used a lot, you're not going to get any benefit
> from pinning it; it will already be in memory anyway do to its constantly
> being accessed. Remember that pinning doesn't tell SQL Server to load the
> data into memory; rather, it tells it to keep it in memory once it's been
> loaded by something else. And SQL Server will do that anyway if requests
> keep coming in for the same data.
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
>

DBCC pintable

Hi everyone,
I'm currently running sql 2k on win2k3, all with the
latest patches in a clustered/merge replication
environment. I'm considering running the dbcc pintable
command for one small (~38MB), but very hot table in our
database. I've never used this command before so I was
looking for any direction here or any "gotchas" I should
look out for. Any advice would be appreciated. Thanks.
LeonLeon,
If the table is small and used a lot, you're not going to get any benefit
from pinning it; it will already be in memory anyway do to its constantly
being accessed. Remember that pinning doesn't tell SQL Server to load the
data into memory; rather, it tells it to keep it in memory once it's been
loaded by something else. And SQL Server will do that anyway if requests
keep coming in for the same data.
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k on win2k3, all with the
> latest patches in a clustered/merge replication
> environment. I'm considering running the dbcc pintable
> command for one small (~38MB), but very hot table in our
> database. I've never used this command before so I was
> looking for any direction here or any "gotchas" I should
> look out for. Any advice would be appreciated. Thanks.
> Leon|||I agree 100% with Adam here. Pinning tables usually has a more negative
effect than a positive one since it will keep any data in memory even if it
has only been accessed a single time. SQL Server usually does a much better
job at managing the cache than a human can.
--
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OB5TGSolEHA.3720@.TK2MSFTNGP12.phx.gbl...
> Leon,
> If the table is small and used a lot, you're not going to get any benefit
> from pinning it; it will already be in memory anyway do to its constantly
> being accessed. Remember that pinning doesn't tell SQL Server to load the
> data into memory; rather, it tells it to keep it in memory once it's been
> loaded by something else. And SQL Server will do that anyway if requests
> keep coming in for the same data.
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> > Hi everyone,
> > I'm currently running sql 2k on win2k3, all with the
> > latest patches in a clustered/merge replication
> > environment. I'm considering running the dbcc pintable
> > command for one small (~38MB), but very hot table in our
> > database. I've never used this command before so I was
> > looking for any direction here or any "gotchas" I should
> > look out for. Any advice would be appreciated. Thanks.
> >
> > Leon
>sql

DBCC CHECKIDENT (jobs, RESEED, 30) does not work

Hi,

I'm trying to set up merge replication and when I try to synchrinize with the subscriber I get the error "invalid column name ROWGUIDCOL". Following an article it recomended to reseed the identity columns on the subscriber so that there is no conflict. Now the tables with the identity column have the "Not for replication "option on. Using DBCC CHECKIDENT (table_name, RESEED, some_value) does not change the SEED when I check. SQL server help file remarks on DBCC CHECKIDENT say that if the column was created with the "Not for replication " option on it cannot change the value.

From sql server help file:

Remarks

If necessary, DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement).

Any way around it or any help so I dont get that "invalid column name ROWGUIDCOL" error?

Thanks allot. Looking forword

George

I had a similar problem before, but my problem was that I add a column with the name ROWGUID but I did forget to mark this field as ROWGUIDCOL, then the replication process make another ROWGUID field but with other name. Check that this is not the case.

I hope this can help you.

|||

Can you turn on profiler and see if a query failed due to invalid column name ROWGUIDCOL?

Thanks.

|||

Thanks for the advice

Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes

This happens on the subscriber when I start the merge agent from the publisher.

George

|||

Hello George,

More questions:

1. Before you setup merge replication between 2 servers, does users table already available on both servers? Can I assume they have the same schema?

2. If tables available on both servers already, did you setup merge replication subscription using "NOSYNC" (do sp_addmergesubscription, and let @.sync_type = 'NONE')?

3. Do both tables, non of them has rowguidcol column, one has, both have?

4. Do you have a long table name?

5. I assume both publisher and subscriber are SQL server 2000.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Hello George,

Please try this, running the follow DDL on the subscriber database.

ALTER TABLE [table_need_to_be_merged] ADD rowguid uniqueidentifier ROWGUIDCOL

Drop the subscription and re-create it with "NOSYNC" option. (SELECT "No, the Subscriber already has the schema and data" from the "Push Subscription Wizard" on the "Initialize Subscription" Page).

The subscription is created and the error message "Invalid column name 'rowguidcol'" should be disappeared.

If this method is still not working for you, I may need to look at your table schema.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Hello Zhou,

Answers to your questions

1. Both databases are available on both servers with the same schema

2. I used NOSYNC frof the wizard

3. One of them (the publisher ) has rowguid column filled. On the subscriber I used your script to created it but is not filled. The merge agent says "No data needed to be merged"

4. The longest table name has 56 characters.

5. Both publisher and subscriber are SQL server 2000.

Thanks

|||

1. So you can create the merge sync by publication and subscription.

2. After you set up the sync and there is no data merged between the publisher and subscriber.

3. Try to update all your rowguid column on the subscriber with newid() and try to sync.

4. if no data sync from step3, try to do a dummy update such as update [table name] set column1 = column1 and do sync.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.