Is there a High water Mark in sql server

If you delete rows in a table and do a full table scan...
Is that supposed to read up to the highest block/extent that the
table ever attended.(like in some databases I use)
If so what is the best way to take care of such tables in sql server.

I appreciate your responses

VinceVincento Harris (wumutek@.yahoo.com) writes:
> If you delete rows in a table and do a full table scan...
> Is that supposed to read up to the highest block/extent that the
> table ever attended.(like in some databases I use)
> If so what is the best way to take care of such tables in sql server.

I'm afraid that I don't understand the question. Could you exemplify what
you are looking for?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94018565C8FYazorman@.127.0.0.1>...
> Vincento Harris (wumutek@.yahoo.com) writes:
> > If you delete rows in a table and do a full table scan...
> > Is that supposed to read up to the highest block/extent that the
> > table ever attended.(like in some databases I use)
> > If so what is the best way to take care of such tables in sql server.
> I'm afraid that I don't understand the question. Could you exemplify what
> you are looking for?

Sorry
I hope this is in some clarifies the earlier post.

Example

My table contains 10000 rows

If a query is run that requires a full table scan all the blocks with
data are visited.

Now

9000 rows are deleted

Next Step
Run a query that requires a full table scan and all the blocks that
ever contained data are visited (Not only the one thousand now
present)

Remedy

If the table is exported ,truncated,imported
the highest point that ever contained data is readjusted at least in
Oracle

Does this work the same in sql server?...

Will appreciate your responses

Vince|||Vincento Harris (wumutek@.yahoo.com) writes:
> Example
> My table contains 10000 rows
> If a query is run that requires a full table scan all the blocks with
> data are visited.
> Now
> 9000 rows are deleted
> Next Step
> Run a query that requires a full table scan and all the blocks that
> ever contained data are visited (Not only the one thousand now
> present)
>
> Remedy
> If the table is exported ,truncated,imported
> the highest point that ever contained data is readjusted at least in
> Oracle
> Does this work the same in sql server?...

It sounds like what you are looking for is DBCC DBREINDEX. This commands
rebuilds all or the selected indexes for a table. Note that if you
have a clustered index on a table, the data pages are the leaf level
of that index, so DBREINDEX also caters for these. For a table that
does not have a clustered index, there is no command as far as I know
that takes care of the data pages. But it is recommendable to always
have a clustered index on a table.

An alternative is DBCC INDEXDEFRAG which can be run without locking out
other users.

Both commands are described in Books Online.

The reason that your question confused me, is that you used Oracle
terminology. In SQL Server you never talk about high-water marks,
for instance.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The reason that your question confused me, is that you used Oracle
> terminology. In SQL Server you never talk about high-water marks,
> for instance.

Right. SQL Server has a totally different internal structure than
Oracle has. In SQL Server, a table is basically a big linked-list and
the data blocks are essentially the leaf-blocks of the cluster index.
Those blocks are then doubly-linked back and forth so you can traverse
the table in a full table scan or in an index range scan. In fact, an
index range scan of the whole table is essentially (physically) the
same as a full table scan. I'm not sure exactly why, but SQL Server
has always had trouble with corruption of these link-list pointers.
Run DBCC to check (and fix) problems with these pointers.

Is SqlTransaction overhead ?

hi,

Is it over head to use SqlTransaction(begin, commit, rollback) for a single transaction.

am not using application block or enterprise library.

only a single insert statement.

Yes, SQL Transactions are an overhead to SQL Server whether you are writing a single query or a batch of queries, but on the other side you are rest assured that in case of any errors you can always rollback the transaction and on successful execution only you will commit transaction.

Have a look at Trasactions in SQL BOL.

Hope this will help.

|||

hi

i dont think that using the sqlTransaction causes any overhead ...

regards,

Is Service Broker Enabled?

Hello,

I've been trying for two days now to get SQL Cache Dependencies to work. So far, nothing has worked, and I have been around the block a few times now on this one. So now I'm going back to basics, as I think my code and queries are fine. My first question is how to confirm that I have a Service Broker that is up and running. I am using SSX as my database engine. Other posts mention how the "look at the service broker folder", and I don't see a folder anywhere. Can someone tell me what to look for? I've added various SP's based on tutorials on web sites, so I can't tell if those SP's are mine or theirs at this point (I've been at this too long). Where is the "folder" the other posts have mentioned? Do I need to do anything special to get it? Did I install all the right files?

Mike

Service Broker is built in into the SQL Server engine, is not possible to install/uninstall the broker compenent independently of the engine itself. Is has no external files/folders whatsowever. So if you have a SQL Server 2005 instanc eup and running, then Service Broker is up and running too. That being said, individual databases can have the broker in them disabled. To check this, look into the is_broker_enabled column in sys.databases. I guess the 'Broker folder' adice you found refers to the Object Explorer view in SQL Server Management Studio.

One place I would look first to troubleshoot SqlDependency issues is the sys.transmission_queue view in your database. If notifications were fired but cannot be delivered, you will find them there, pending delivery. The transmission_status column should indicate the reason why they cannot be delivered.

HTH,
~ Remus

|||

Remus,

Does this apply to SQL Server Express as well?

Thanks for you response,

Michael

|||

Yes. Note that the Express version of the Management Studio doesn't have a 'Service Broker' folder in it's Object Explorer.

HTH,
~ Remus

|||

Remus,

There is nothing in the sys.transmission_queue. I'm not even sure messages are GETTING to the database. In my event log, I just noticed a message I haven't seen before. It says:

Service Broker needs to access the master key in the database 'EventSystem'. Error code:25. The master key has to exist and the service master key encryption is required.

I'm not sure what this means. I suppose it's a starting point. Have you seen that one before?

Overall, I'm VERY frustrated with the Service Broker and cache dependencies. I've added more stored procedures and granted more permissions than should be necessary for a feature that is "built in" and "easy to use". So far, the exact OPPOSITE is true.

Thanks for all the help!

Michael

|||

I found a post that suggested I needed to create a master key. I ran the following statement:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1ComplexPassword!'

Next, I ran my code again, and saw the following error in the logs:

The query notification dialog on conversation handle '{A1AFE272-1391-DB11-B928-0004230B9AA7}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-15eb7e6d-e2b3-4355-b082-8f0235f5fe3f&apos; because it does not exist.</Description></Error>'.

I thought I had run all procs that were necessary after going through literally a dozen pages on how to set this all up, but perhaps I missed one? I sure wish a single web site had steps that I could follow to get this all to work, start to finish. I promise to post all my steps when I am done, because I am sure others are having the same problem as me.

Michael

|||

I had similar problems.

The reason for my problem was that the database was in SQL Server 2000 compatibility mode.
Changing that to SQL Server 2005 helped.

is rollback possible here?

hi friends,

is it possible to roll back a transaction here? i heard that it will be possible only if we wrote the code only in certain block.please tell me the block.

vinodcan you post the sample query

Roll back can happen
1. if you put your statements inside a 'BEGIN TRAN ...ROLLBACK TRAN ' statements

2. if you stop the running Query before it completes