Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is 'Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is '
>|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Locking

Hi all,
SQL server doesnt provide any kind of configuration to
control granularity of locking on tables. As per books
online it is managed dynamically.
I have a major problem with my SQL Server, during
execution of one SP, on one table with 400+ rows it
acquires 600,000 locks. My profiler shows
whooping 'Lock:Acquired' on this particular table.
Can somebody please throw some light on this? Why SQL
server doesn't promote it to single table level lock? Is
there any bug in SQL?
thanks very much
Himanshu JaniYou probably want to keep working on why this happens in the first place (although there's not much
to go on for us), but here are a couple of options for immediate fix:
Specify lock hint in the query (TABLOCK, for instance).
Configure the index used to not allow row locks (sp_indexoption).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Himanshu Jani" <anonymous@.discussions.microsoft.com> wrote in message
news:2c84301c393cd$c746cab0$a601280a@.phx.gbl...
> Hi all,
> SQL server doesnt provide any kind of configuration to
> control granularity of locking on tables. As per books
> online it is managed dynamically.
> I have a major problem with my SQL Server, during
> execution of one SP, on one table with 400+ rows it
> acquires 600,000 locks. My profiler shows
> whooping 'Lock:Acquired' on this particular table.
> Can somebody please throw some light on this? Why SQL
> server doesn't promote it to single table level lock? Is
> there any bug in SQL?
> thanks very much
> Himanshu Jani

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is ?
Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is ?
>
|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Locking

I have a Stored Procedure serving an web application that returns data from
a
number of tables.
This dat resides on three seperate tables, two of which I join in a single
select statement, the third I use to lookup data dependent on some data
retrieved from the original select statement.
On some infrequent occasions the user will receive an error message stating
they are the victim of a deadlock etc, etc.
I have checked the logs on SQL but find no error message pertaining to this.
I then created a script in QA that access this Stored procedure in a loop at
1000 and then 10000 times, but whenever I run it, I receive no error
messages, just the recordset(s) returned correctly.
The data being accessed is live and updateable 24/7 by other users. This
Stored Procedure does not moify the data in any shape or form, simply reads
it.
Another developer I work with has suggested using the NOLOCK hint on all
reads, but I am loathed to use this as it may cause dirty reads.
1. Does the scenario indicate this is truly a deadlock situation or could
there be another cause for this error message (the web app is in .NET)
2. What is the general point of view on using the NOLOCK hint and should it
be used as a matter of course or very sparingly?
I am obliged for any assistance or guidance in this matter
(Note: This question has also been posted on SQLTeam Site)> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
Most probably. You didn't post the exact error message, but the way you phra
sed it sounds like the
error was indeed an SQL Server error message.

> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
Only use NOLOCK if you can foresee the consequences and accept them. But I s
uggest you look into
other possibilities first. These can include things like keeping locks for a
s short time as possible
(do not include things in the transaction that doesn't have to be there, tun
e queries etc),
accessing the objects in the same sequence, adding or removing indexes.
The reason you didn't see deadlocks in your loop is that you probably only h
ad one connection.
Deadlock (generally) occurs when you have two or more workers accessing data
in a way so that the
deadlock arises.
You can Google on deadlock (etc) and find plenty of text on the subject, inc
luding SQL server
specific text.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>I have a Stored Procedure serving an web application that returns data from
a
> number of tables.
> This dat resides on three seperate tables, two of which I join in a single
> select statement, the third I use to lookup data dependent on some data
> retrieved from the original select statement.
> On some infrequent occasions the user will receive an error message statin
g
> they are the victim of a deadlock etc, etc.
> I have checked the logs on SQL but find no error message pertaining to thi
s.
> I then created a script in QA that access this Stored procedure in a loop
at
> 1000 and then 10000 times, but whenever I run it, I receive no error
> messages, just the recordset(s) returned correctly.
> The data being accessed is live and updateable 24/7 by other users. This
> Stored Procedure does not moify the data in any shape or form, simply read
s
> it.
> Another developer I work with has suggested using the NOLOCK hint on all
> reads, but I am loathed to use this as it may cause dirty reads.
> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
> I am obliged for any assistance or guidance in this matter
> (Note: This question has also been posted on SQLTeam Site)|||The error message was thus...
Transaction (Process ID 99) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable
dataTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ...etc, etc
which strongly indicates that SQL generated it.
In respect of the loop test, I was attempting emulate the web application
when it requests the data to ascertain whether it was on the server or not.
I
recently read an artcle on MSDN that indicated deadlocking problems could be
caused by Applications opening dual threads that block each other - leading
to a deadlock situation - but it didn't state what error message would be
created in this case over and above the regular SQL 1204/1205 error.
Any further thoughts of where to look for this?
"Tibor Karaszi" wrote:

> Most probably. You didn't post the exact error message, but the way you ph
rased it sounds like the
> error was indeed an SQL Server error message.
>
> Only use NOLOCK if you can foresee the consequences and accept them. But I
suggest you look into
> other possibilities first. These can include things like keeping locks for
as short time as possible
> (do not include things in the transaction that doesn't have to be there, t
une queries etc),
> accessing the objects in the same sequence, adding or removing indexes.
> The reason you didn't see deadlocks in your loop is that you probably only
had one connection.
> Deadlock (generally) occurs when you have two or more workers accessing da
ta in a way so that the
> deadlock arises.
> You can Google on deadlock (etc) and find plenty of text on the subject, i
ncluding SQL server
> specific text.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
> news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>sql

LOCK Table necessary in stored procedure?

Hi,

I have some questions about locking tables in stored procedures. I got some excellent tips from my last post, but since it's sort of a different problem I figured I'd post it separately.

I have a large log table I need to do manual, periodic clean-up process on, which basically is purging unneccessary log-entries. The idea is to select out the 1-3% I need to another table, drop the old table, and rename the new table to the old one.

The problem is that I most likely will need to lock the entire table while I do all the clean-up stuff. If a client manages to add things inbetween this is going on, I could end up loosing data.

The table looks like this:
Logid PK
LogTypeID -- what category
LogValue --
LogTime -- when it occurred

My imaginary stored procedure looks something like this:

CREATE PROCEDURE ShrinkDB AS
-- 1)
"lock table log" -- do I have to do something like this?

-- 2)
select * into log_keep FROM Log where
(
logtypeid <> 2020 AND -- activity played
logtypeid <> 5020 AND -- database connected
-- ...etc et.... about 10 different things I don't need to keep
or logtime > dateadd(d, -1, getdate()) -- keep everything from last 24 hours
)
-- 3)
drop table log

-- 4)
EXEC sp_rename 'log_keep', 'log'
GO

I'm not able to figure out wether I need to run some sort of "Lock" command or not, or if everything inside a stored procedure automatically is locked. If so, I shouldn't worry about loosing any data I guess??

Hopefully it works that way, but if not I assume I'll run into these two problems:

- If a client logs immediately after the Selecet, could data be logged AFTER the select, but BEFORE the drop table-command? In which case I guess I would loose data?

- Immediately after the drop table log in step 3, there's no table named 'log' in my database. 'Log' will be "created" when I run step 4. This means I could perhaps loose data since the client for a brief moment can't log data to the 'log' table?

Hopefully someone can clearify this for me, I've read the documentation, but I don't feel too sure on this subject... :-)Hi,

Why drop the table? why not delete the data you have just taken, then you will never lose any data|||...simply because it takes such an insane amount of time. A regular "delete from log where logtypeId = stuff-I-don't-need" takes forever. :( The complete post is here, if you want the details: http://www.dbforums.com/showthread.php?threadid=979910|||You can open a transaction, and then do SELECT * FROM TBL WITH (TABLOCK) WHERE 1=2 as you first statement. Then, instead of moving 1-3% of data to a different permanent table, you can put it into a temptable (whether # or @., doesn't matter,) then truncate the original (instead of dropping it,) and putting the data from your temptable back into the original.|||Even truncate takes quite a few seconds, allthough I guess that's related to some locking issues and not the truncate process in it self.

The method involving dropping the table takes about 3-5 seconds, and is much faster than select-away-and-truncate. It sort of works, the problem with that solution is that PK, indexes, rights and what not are gone too. This can of course be re-created with a number of SQL sentences, but it's not exactly ideal......

But I cannot understand why deleting takes so much time? I'm assuming the cause of this problem is that about 50 clients are constantly logging into the table while I'm deleting.

I'm not an expert sql-stored procedure maker, anyone who whould take a crack at setting up something for me with locking (and whatever else) that would work? Would help me a lot!! :-)|||I can send you a contract, and upon signing you'll never have to deal with it again :cool:sql

Lock on system table

Hi all,
One of my stored procedure is creating around 7 #tables.
This procedure comes out after 5 mins with error
1222 'Lock request time-out period exceeded'.
My @.@.lock_timeout is -1.
Analysis showed that this stored procedures holds numerous
shared and exclusive locks on sysobjects, sysindexes and
syscolumns on tempdb. It blocks all other processes and
nobody can do anything.
Can anybody suggests why this could be happening and way
out?
Thanks in advance
Himanshu JaniHi Himanshu ,
If your stored procedure uses SELECT ..INTO to create and populate the
temporary tables this can cause locking problems as it will take out a lock
on the system tables to create the temporary table, but will hold this lock
for the duration of the insert. You can solve this problem by creating the
temporary table explicitly with either CREATE TABLE or with SELECT ..
INTO... WHERE 1=0. You then have to insert the data into the temporary table
with a normal insert. If the table creation happens inside a transaction the
locks will be taken for the duration of the transaction, so it makes sense
to create the temporary tables outside a long running transaction. If you
use SQL Server 2000 you can also use table variables instead of temporary
tables, table variables do not participate in transactions, and so you won't
have any locks on the system tables in tempdb.
--
Jacco Schalkwijk
SQL Server MVP
"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
> Hi all,
> One of my stored procedure is creating around 7 #tables.
> This procedure comes out after 5 mins with error
> 1222 'Lock request time-out period exceeded'.
> My @.@.lock_timeout is -1.
> Analysis showed that this stored procedures holds numerous
> shared and exclusive locks on sysobjects, sysindexes and
> syscolumns on tempdb. It blocks all other processes and
> nobody can do anything.
> Can anybody suggests why this could be happening and way
> out?
> Thanks in advance
> Himanshu Jani|||It seems to me that SELECT INTO is just a single command
that combines both the CREATE TABLE and INSERT statements
(with the benefit of minimumally logging the INSERT).
This means that the X-lock(s) on the system tables in
tempdb is held just for the duration of the CREATE and not
for the duration of the entire SELECT INTO statement.
On SQL Server 2000, SP3, I just ran 'SELECT INTO' into a
#temp table from a 12 million record table and was able to
simultaneously run and complete a second SELECT INTO into
another #temp table while the first SELECT INTO was still
running.
Thoughts?
Thanks, -- Brian
>--Original Message--
>Hi Himanshu ,
>If your stored procedure uses SELECT ..INTO to create and
populate the
>temporary tables this can cause locking problems as it
will take out a lock
>on the system tables to create the temporary table, but
will hold this lock
>for the duration of the insert. You can solve this
problem by creating the
>temporary table explicitly with either CREATE TABLE or
with SELECT ..
>INTO... WHERE 1=0. You then have to insert the data into
the temporary table
>with a normal insert. If the table creation happens
inside a transaction the
>locks will be taken for the duration of the transaction,
so it makes sense
>to create the temporary tables outside a long running
transaction. If you
>use SQL Server 2000 you can also use table variables
instead of temporary
>tables, table variables do not participate in
transactions, and so you won't
>have any locks on the system tables in tempdb.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
>news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
>> Hi all,
>> One of my stored procedure is creating around 7 #tables.
>> This procedure comes out after 5 mins with error
>> 1222 'Lock request time-out period exceeded'.
>> My @.@.lock_timeout is -1.
>> Analysis showed that this stored procedures holds
numerous
>> shared and exclusive locks on sysobjects, sysindexes and
>> syscolumns on tempdb. It blocks all other processes and
>> nobody can do anything.
>> Can anybody suggests why this could be happening and way
>> out?
>> Thanks in advance
>> Himanshu Jani
>
>.
>

Lock identifying through SP code

We would have come across locks on rows, tables etc. How do we handle it
through code (SP)!!!

A job is executed at 12 pm midnight on a table. This table exists on an
24X7 accessed (users will be access the table anytime in the day/night)
database. We need to execute this job if this table is not locked at 12 pm.
Can we have the solution for the listed are two scenarios:

1. You have a normal user and do not have system privilege.
How would you design the database(Table)? Is it possible?
2. You have all the privileges on the system tables.
What would be the sql script to help us identify the lock? How will
it be used in your code? Or is there exception handling !!!You can see if a lock exists in syslockinfo table:

if exists (select 1 from master.dbo.syslockinfo (nolock) where rsc_objid=object_id('dbo.yourtable') print 'Someone is using the table'
else begin
print 'Table is not in use'
begin tran
select top 1 [lockit]=1 from dbo.yourtable
--do your thing
if @.@.error != 0 rollback tran
else commit tran
end

Lock Database

In my system I got a problem.
One of the database's Tables are not getting opened and I can not even alter a Stored Procedure which is related to the tables of that database.
How can I solve that?
SubhasishCheck permissions on the table and also run a DBCC check.|||What are the errors you're getting (if any)?|||Actually I am not getting any error message.
It simply is not running.
Even I can not open the database from Enterprise Manager.
I believe may be due to the overflow of data.
How can I resolve it?|||OK, even though confusion is my normal state of mind, it can't be happening that often! First you were talking about inability to open a table and a related stored procedure, and now you're saying you can't open the database from EM? Which one is it? And what else have you tried, - QA, OSQL, connecting to the server with terminal services and doing the same there, going physically to the server, etc..?sql

Location of logging for temp tables

I have a stored procedure that creates a temp table that is used to
insert data from a dynamic query. My question is does the logging of
the insert occur in the local database running the stored procedure or
does it occur in the tempdb database? My log file for local database
is growing significantly and I want to rule out that the temp table is
not writing to the local database log. I have done some testing and it
appears that is not but I just confirm my findings."Travis" <Travi424@.hotmail.com> wrote in message
news:1126279238.782948.112340@.z14g2000cwz.googlegr oups.com...
>I have a stored procedure that creates a temp table that is used to
> insert data from a dynamic query. My question is does the logging of
> the insert occur in the local database running the stored procedure or
> does it occur in the tempdb database? My log file for local database
> is growing significantly and I want to rule out that the temp table is
> not writing to the local database log. I have done some testing and it
> appears that is not but I just confirm my findings.

An INSERT into the temp table will be logged in tempdb; an INSERT from the
temp table into a permanent table will be logged in that database.

Simon|||Thanks!

Locating recently edited tables

Hi
Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer
application. This application keeps its data in a SQL 2000 database.
What would be the easiest way for me to figure out which table got updated
with the "Pennsylvania Av." string?`
I don't care whether the solution is to change some view settings in my SQL
GUI or if I can write some t-sql code and output the solution for me, I just
need to figure out where the application stores the darn addresses.
Thanks in advance for any input
IbUse Profiler. You can catch the SQL statements submitted by the app. You can even catch the
execution plan and from that determine what tables were hit (if that approach is better than working
with the SQL submitted).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ib Schrader" <ibschrader@.gmail.com> wrote in message news:ez2gcoqHHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi
> Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer application. This
> application keeps its data in a SQL 2000 database.
> What would be the easiest way for me to figure out which table got updated with the "Pennsylvania
> Av." string?`
> I don't care whether the solution is to change some view settings in my SQL GUI or if I can write
> some t-sql code and output the solution for me, I just need to figure out where the application
> stores the darn addresses.
> Thanks in advance for any input
> Ib
>|||Ib
Do you store "Pennsylvania Av." value in many tables?
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:ez2gcoqHHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi
> Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer
> application. This application keeps its data in a SQL 2000 database.
> What would be the easiest way for me to figure out which table got updated
> with the "Pennsylvania Av." string?`
> I don't care whether the solution is to change some view settings in my
> SQL GUI or if I can write some t-sql code and output the solution for me,
> I just need to figure out where the application stores the darn addresses.
> Thanks in advance for any input
> Ib
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVUvutqHHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Ib
> Do you store "Pennsylvania Av." value in many tables?
I don't know that. The exercise is to locate which tables got updated by the
application. I don't know how many that is, but I suspect it's just one
table that stores the data.
Thanks for the profiler idea..I'll look into that.
Ib|||The profiler helped me catch which table got updated.
Thank you very much.
Ib

Locating recently edited tables

Hi
Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer
application. This application keeps its data in a SQL 2000 database.
What would be the easiest way for me to figure out which table got updated
with the "Pennsylvania Av." string?`
I don't care whether the solution is to change some view settings in my SQL
GUI or if I can write some t-sql code and output the solution for me, I just
need to figure out where the application stores the darn addresses.
Thanks in advance for any input
IbUse Profiler. You can catch the SQL statements submitted by the app. You can
even catch the
execution plan and from that determine what tables were hit (if that approac
h is better than working
with the SQL submitted).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ib Schrader" <ibschrader@.gmail.com> wrote in message news:ez2gcoqHHHA.1248@.TK2MSFTNGP02.phx
.gbl...
> Hi
> Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer app
lication. This
> application keeps its data in a SQL 2000 database.
> What would be the easiest way for me to figure out which table got updated
with the "Pennsylvania
> Av." string?`
> I don't care whether the solution is to change some view settings in my SQ
L GUI or if I can write
> some t-sql code and output the solution for me, I just need to figure out
where the application
> stores the darn addresses.
> Thanks in advance for any input
> Ib
>|||Ib
Do you store "Pennsylvania Av." value in many tables?
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:ez2gcoqHHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi
> Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer
> application. This application keeps its data in a SQL 2000 database.
> What would be the easiest way for me to figure out which table got updated
> with the "Pennsylvania Av." string?`
> I don't care whether the solution is to change some view settings in my
> SQL GUI or if I can write some t-sql code and output the solution for me,
> I just need to figure out where the application stores the darn addresses.
> Thanks in advance for any input
> Ib
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVUvutqHHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Ib
> Do you store "Pennsylvania Av." value in many tables?
I don't know that. The exercise is to locate which tables got updated by the
application. I don't know how many that is, but I suspect it's just one
table that stores the data.
Thanks for the profiler idea..I'll look into that.
Ib|||The profiler helped me catch which table got updated.
Thank you very much.
Ib

Locate information

Hello,
How can i locate a information in any fields of any tables?
I was wondering to generate Txt about all tables in my database, but with
DTS i can only do that with a table.
Does anybody can help me?
Thanks
Leandro L S
See if this helps:
http://vyaskn.tripod.com/search_all_...all_tables.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Leandro Loureiro dos Santos" <nao@.falo.com.vc> wrote in message
news:%234kyqLnPEHA.3660@.TK2MSFTNGP11.phx.gbl...
Hello,
How can i locate a information in any fields of any tables?
I was wondering to generate Txt about all tables in my database, but with
DTS i can only do that with a table.
Does anybody can help me?
Thanks
Leandro L S

Locate information

Hello,
How can i locate a information in any fields of any tables?
I was wondering to generate Txt about all tables in my database, but with
DTS i can only do that with a table.
Does anybody can help me?
Thanks
Leandro L SSee if this helps:
http://vyaskn.tripod.com/search_all..._all_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Leandro Loureiro dos Santos" <nao@.falo.com.vc> wrote in message
news:%234kyqLnPEHA.3660@.TK2MSFTNGP11.phx.gbl...
Hello,
How can i locate a information in any fields of any tables?
I was wondering to generate Txt about all tables in my database, but with
DTS i can only do that with a table.
Does anybody can help me?
Thanks
Leandro L S

Locate information

Hello,
How can i locate a information in any fields of any tables?
I was wondering to generate Txt about all tables in my database, but with
DTS i can only do that with a table.
Does anybody can help me?
Thanks
Leandro L SSee if this helps:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Leandro Loureiro dos Santos" <nao@.falo.com.vc> wrote in message
news:%234kyqLnPEHA.3660@.TK2MSFTNGP11.phx.gbl...
Hello,
How can i locate a information in any fields of any tables?
I was wondering to generate Txt about all tables in my database, but with
DTS i can only do that with a table.
Does anybody can help me?
Thanks
Leandro L S

Locate Covered Indexes on Tables

I am looking for the stored procedure that have the covered indexes for
several indexes in my database.
Is there a script or way to determine the which stored procedures correspond
to the covered indexes?
Thank You,
Joe
Let me understand you. COVERING indexes are created on columns not on stored
procedures.
Can you elaborate a little bit what you are trying to accomplish?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D3FE0DDD-C12B-49EF-A02A-FFC8AA751E53@.microsoft.com...
> I am looking for the stored procedure that have the covered indexes for
> several indexes in my database.
> Is there a script or way to determine the which stored procedures
> correspond
> to the covered indexes?
> Thank You,

Locate Covered Indexes on Tables

I am looking for the stored procedure that have the covered indexes for
several indexes in my database.
Is there a script or way to determine the which stored procedures correspond
to the covered indexes?
Thank You,Joe
Let me understand you. COVERING indexes are created on columns not on stored
procedures.
Can you elaborate a little bit what you are trying to accomplish?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D3FE0DDD-C12B-49EF-A02A-FFC8AA751E53@.microsoft.com...
> I am looking for the stored procedure that have the covered indexes for
> several indexes in my database.
> Is there a script or way to determine the which stored procedures
> correspond
> to the covered indexes?
> Thank You,

Locate Covered Indexes on Tables

I am looking for the stored procedure that have the covered indexes for
several indexes in my database.
Is there a script or way to determine the which stored procedures correspond
to the covered indexes?
Thank You,Joe
Let me understand you. COVERING indexes are created on columns not on stored
procedures.
Can you elaborate a little bit what you are trying to accomplish?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D3FE0DDD-C12B-49EF-A02A-FFC8AA751E53@.microsoft.com...
> I am looking for the stored procedure that have the covered indexes for
> several indexes in my database.
> Is there a script or way to determine the which stored procedures
> correspond
> to the covered indexes?
> Thank You,

Locally Partitioned Tables for OLAP

Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard
In the current release of SQL Server, you can use partitioned views to
accomplish what you need. You'll need a partitioning column - enforced with
a check constraint - as part of the primary key in each member table. In
your case, it would be the relevant date. You then construct a view that
includes all members, hooked together with UNION ALL's. As you need to
add/drop a member table, simply alter the view to eliminate the old and add
the new from the definition. Then drop the un-needed table.
I don't believe you'll need the enterprise edition, since you're looking at
local partitioned views.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Corfield" <richard.corfield@.gmail.com> wrote in message
news:c58fd37c.0410190647.cd2e11c@.posting.google.co m...
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard

Locally Partitioned Tables for OLAP

Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- RichardIn the current release of SQL Server, you can use partitioned views to
accomplish what you need. You'll need a partitioning column - enforced with
a check constraint - as part of the primary key in each member table. In
your case, it would be the relevant date. You then construct a view that
includes all members, hooked together with UNION ALL's. As you need to
add/drop a member table, simply alter the view to eliminate the old and add
the new from the definition. Then drop the un-needed table.
I don't believe you'll need the enterprise edition, since you're looking at
local partitioned views.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Corfield" <richard.corfield@.gmail.com> wrote in message
news:c58fd37c.0410190647.cd2e11c@.posting.google.com...
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard

Locally Partitioned Tables for OLAP

Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- RichardIn the current release of SQL Server, you can use partitioned views to
accomplish what you need. You'll need a partitioning column - enforced with
a check constraint - as part of the primary key in each member table. In
your case, it would be the relevant date. You then construct a view that
includes all members, hooked together with UNION ALL's. As you need to
add/drop a member table, simply alter the view to eliminate the old and add
the new from the definition. Then drop the un-needed table.
I don't believe you'll need the enterprise edition, since you're looking at
local partitioned views.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Corfield" <richard.corfield@.gmail.com> wrote in message
news:c58fd37c.0410190647.cd2e11c@.posting.google.com...
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard