Processor Licensing question

If I remember correctly, SQL will automatically limit itself when using
the per processor license. If not, you can set the CPU affinity to 1
to make sure it only uses 1.
George Hester wrote:
> I have a dual processor system. One processor is used for boot operations
> and the other is used for Applications. If I were to install SQL Single
> Processior License on this machine would that be within the Licensing
> restrictions as there is the one processor for Applications or would I nee
d
> another license? Thanks.
> --
> George Hester
> _________________________________"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1150209890.143128.128400@.f14g2000cwb.googlegroups.com...
> If I remember correctly, SQL will automatically limit itself when using
> the per processor license. If not, you can set the CPU affinity to 1
> to make sure it only uses 1.
>
With Per-Proc licensing you must license SQL Server on all processers
visible to the OS instance where SQL is running.
If you want to run a single proc of SQL on a multi-proc box that is used for
multiple different workloads, you can use Virtual Server 2003 R2, install a
new OS that can only see one processor, and install a single-proc license of
SQL Server on that OS instance.
From:
Virtualization and Multiple Instances
http://www.microsoft.com/sql/howtob...einstances.mspx
Each virtual operating environment running SQL Sever 2005 must have a
processor license for each processor that the virtual machine accesses. If a
copy of SQL Server is running on a physical operating environment, then
processor licenses are required for all of the processors on that physical
server.
David|||I have a dual processor system. One processor is used for boot operations
and the other is used for Applications. If I were to install SQL Single
Processior License on this machine would that be within the Licensing
restrictions as there is the one processor for Applications or would I need
another license? Thanks.
George Hester
_________________________________|||If I remember correctly, SQL will automatically limit itself when using
the per processor license. If not, you can set the CPU affinity to 1
to make sure it only uses 1.
George Hester wrote:
> I have a dual processor system. One processor is used for boot operations
> and the other is used for Applications. If I were to install SQL Single
> Processior License on this machine would that be within the Licensing
> restrictions as there is the one processor for Applications or would I nee
d
> another license? Thanks.
> --
> George Hester
> _________________________________|||"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1150209890.143128.128400@.f14g2000cwb.googlegroups.com...
> If I remember correctly, SQL will automatically limit itself when using
> the per processor license. If not, you can set the CPU affinity to 1
> to make sure it only uses 1.
>
With Per-Proc licensing you must license SQL Server on all processers
visible to the OS instance where SQL is running.
If you want to run a single proc of SQL on a multi-proc box that is used for
multiple different workloads, you can use Virtual Server 2003 R2, install a
new OS that can only see one processor, and install a single-proc license of
SQL Server on that OS instance.
From:
Virtualization and Multiple Instances
http://www.microsoft.com/sql/howtob...einstances.mspx
Each virtual operating environment running SQL Sever 2005 must have a
processor license for each processor that the virtual machine accesses. If a
copy of SQL Server is running on a physical operating environment, then
processor licenses are required for all of the processors on that physical
server.
David

process is blocking itself

I have a stored proc created for report and now suddenly the report is takin
g
lot of time to run. The store proc is having simple select statement with
multiple case statements.
I checked the sysprocesses table and noticed that the process is blocking
itself.
What could be reason and how to solve this issue'
ThanksSo you are saying that when you run sp_who the spid that appears in
the "blk" column is the SAME as the value in the "spid" column of the
same line?
Roy Harvey
Beacon Falls, CT
On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
<TSQL@.discussions.microsoft.com> wrote:

>I have a stored proc created for report and now suddenly the report is taki
ng
>lot of time to run. The store proc is having simple select statement with
>multiple case statements.
>I checked the sysprocesses table and noticed that the process is blocking
>itself.
>What could be reason and how to solve this issue'
>Thanks|||You're running SQL 2000 with SP4 installed:
http://support.microsoft.com/defaul...KB;EN-US;906344
TSQL wrote:
> I have a stored proc created for report and now suddenly the report is tak
ing
> lot of time to run. The store proc is having simple select statement with
> multiple case statements.
> I checked the sysprocesses table and noticed that the process is blocking
> itself.
> What could be reason and how to solve this issue'
> Thanks|||Yes Exactly.
I ran -
select * from sysprocesses
where physical_io>25 or cpu>15 or memusage>15
order by blocked desc
"Roy Harvey" wrote:

> So you are saying that when you run sp_who the spid that appears in
> the "blk" column is the SAME as the value in the "spid" column of the
> same line?
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
> <TSQL@.discussions.microsoft.com> wrote:
>
>|||seems wierd. Try it with
OPYION (MAXDOP 1) and
and recompile the stored procedure.
Can you post the script?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||SQL 2000 latches quite often block other latch request from the same
SPID by design (which Tracy also acknowledged; see
http://support.microsoft.com/default.aspx/kb/906344). With SP4
Microsoft have started displaying those latch blocks (in addition to the
lock blocks) in the blocked column of sysprocesses, so now it looks like
a SPID is blocking itself (which, I guess, technically, it is) but in
fact it's usually just waiting on a page to be read into memory due to
slow I/O.
*mike hodgson*
http://sqlnerd.blogspot.com
Omnibuzz wrote:

>seems wierd. Try it with
>OPYION (MAXDOP 1) and
>and recompile the stored procedure.
>Can you post the script?
>|||Thanks Mike, Tracy. Went through the article. Makes sense. Makes a lot of
sense.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

Process blocking itself since SP4

Hi,

Since installing SP4 we seem to encounter issues where a process seems to block itself. Has anyone seen this issue. Is this a feature?

Thanks,
Dave

Please refer to the following for more details. This may be the reason

The original posting can be found in many places, for example: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/23496/SP4-reports-queries-blocking-on-themselves

There is also a KB article on this: http://support.microsoft.com/default.aspx?scid=KB;EN-US;906344
Thanks

Process blocking itself

Im ahving a problem running a sql server 2000 query, in that the
process is blocked by itself and doesn't finish, even after waiting 20
hours for it.
The table has 40 million records and im running something like :
UPDATE table SET field2 = db.myfunction(field1)
The function isnt doing anthing with tables, it justs does some string
manipulation on the parameter string.
Someone said it might be dur to parallelism (multiple processes
running at the same time causing the process to lock up / block
itself), but even if i try :
UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
it doesnt make any difference.
Hope someone can help, thanks,
PaulHow do you know that the process is "blocked by itself"? Are you seeing
this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
and of itself, is not a cause for concern, it is just a change in the way
sysprocesses reported waits (I think it started working this way in 2000
SP4). Please see the following KB article for more information:
http://support.microsoft.com/kb/906344
"puma75" <paulroskilly@.gmail.com> wrote in message
news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
> Im ahving a problem running a sql server 2000 query, in that the
> process is blocked by itself and doesn't finish, even after waiting 20
> hours for it.
> The table has 40 million records and im running something like :
> UPDATE table SET field2 = db.myfunction(field1)
> The function isnt doing anthing with tables, it justs does some string
> manipulation on the parameter string.
> Someone said it might be dur to parallelism (multiple processes
> running at the same time causing the process to lock up / block
> itself), but even if i try :
> UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> it doesnt make any difference.
> Hope someone can help, thanks,
> Paul
>|||On 8 Oct, 19:42, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How do you know that the process is "blocked by itself"? Are you seeing
> this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
> and of itself, is not a cause for concern, it is just a change in the way
> sysprocesses reported waits (I think it started working this way in 2000
> SP4). Please see the following KB article for more information:
> http://support.microsoft.com/kb/906344
> "puma75" <paulroski...@.gmail.com> wrote in message
> news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
>
> > Im ahving a problem running a sql server 2000 query, in that the
> > process is blocked by itself and doesn't finish, even after waiting 20
> > hours for it.
> > The table has 40 million records and im running something like :
> > UPDATE table SET field2 = db.myfunction(field1)
> > The function isnt doing anthing with tables, it justs does some string
> > manipulation on the parameter string.
> > Someone said it might be dur to parallelism (multiple processes
> > running at the same time causing the process to lock up / block
> > itself), but even if i try :
> > UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> > it doesnt make any difference.
> > Hope someone can help, thanks,
> > Paul- Hide quoted text -
> - Show quoted text -
Thanks.
In Enterprise Manager under Management > Current Activity > Locks/
Process Id it says "spid 73 (blocked by 73)"
It just seems very slow that it hasn't finished after 20 hours and it
didnt seem to be doing anything, little cpu usage, so thinking that it
was deadlocked seemed like a good explanation, must maybe not.|||> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
It's certainly not deadlocking because otherwise SQL Server would have
detected it and terminated one of the deadlock participants.
Perhaps, the UPDATE is not the only SQL statement, and there is still
runaway parallelism going on. I'd check the query plan to be see whether
there is any prallelism.
Linchi
"puma75" wrote:
> On 8 Oct, 19:42, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> > How do you know that the process is "blocked by itself"? Are you seeing
> > this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
> > and of itself, is not a cause for concern, it is just a change in the way
> > sysprocesses reported waits (I think it started working this way in 2000
> > SP4). Please see the following KB article for more information:
> >
> > http://support.microsoft.com/kb/906344
> >
> > "puma75" <paulroski...@.gmail.com> wrote in message
> >
> > news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
> >
> >
> >
> > > Im ahving a problem running a sql server 2000 query, in that the
> > > process is blocked by itself and doesn't finish, even after waiting 20
> > > hours for it.
> >
> > > The table has 40 million records and im running something like :
> >
> > > UPDATE table SET field2 = db.myfunction(field1)
> >
> > > The function isnt doing anthing with tables, it justs does some string
> > > manipulation on the parameter string.
> >
> > > Someone said it might be dur to parallelism (multiple processes
> > > running at the same time causing the process to lock up / block
> > > itself), but even if i try :
> >
> > > UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> >
> > > it doesnt make any difference.
> >
> > > Hope someone can help, thanks,
> >
> > > Paul- Hide quoted text -
> >
> > - Show quoted text -
> Thanks.
> In Enterprise Manager under Management > Current Activity > Locks/
> Process Id it says "spid 73 (blocked by 73)"
> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
>
>|||On Oct 8, 1:32 pm, puma75 <paulroski...@.gmail.com> wrote:
> Im ahving a problem running a sql server 2000 query, in that the
> process is blocked by itself and doesn't finish, even after waiting 20
> hours for it.
> The table has 40 million records and im running something like :
> UPDATE table SET field2 = db.myfunction(field1)
> The function isnt doing anthing with tables, it justs does some string
> manipulation on the parameter string.
> Someone said it might be dur to parallelism (multiple processes
> running at the same time causing the process to lock up / block
> itself), but even if i try :
> UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> it doesnt make any difference.
> Hope someone can help, thanks,
> Paul
a few suggestions:
- check if you have triggers on that table.
- try to do your updates in batches, of, say, 10K rows.|||Aaron Bertrand [SQL Server MVP] wrote:
> > How do you know that the process is "blocked by itself"? Are you
> > seeing this in sysprocesses, or are you guessing? Usually a spid
> > self-blocking, in and of itself, is not a cause for concern, it is
> > just a change in the way sysprocesses reported waits (I think it
> > started working this way in 2000 SP4). Please see the following KB
> > article for more information:
> >
> > http://support.microsoft.com/kb/906344
puma75 wrote:
> In Enterprise Manager under Management > Current Activity > Locks/
> Process Id it says "spid 73 (blocked by 73)"
> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
In this case, I think it's exactly what Aaron said: you are seing that
the process is waiting for I/O or network latches (belonging to that
process). I bet the query is accessing a lot of data and the HDD led is
light-up almost contiuously.
Try to rephrase the query (to optimize it) or try adding appropriate
indexes (if necessary). Also, try updating the statistics before
running the query.
--
Razvan Socol
SQL Server MVP

process block itself

Hi to everybody,

i have this problem.

sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from

select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'

resorce type : OBJECT

resource id : 218899226

request mode : Sch-M

request status WAIT

request_session id : 51

request life time : 33554432

or from

SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51

waiting task address : 0x0000000000C2F198

session id : 51

wait type : LCK_M_SCH_M

resource address : 0x0000000201C71300

blocking task address : 0x0000000000C2F198

blocking session id = 51

resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226

I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process

I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)

So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server

thanks in advance

Luca

Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||

No, it isn't a DTS package but I have found where is problem...

I use a procedure like this to retry a block of istructions when i have an error

Procedure 2

.....

create table #tmp.....

declare @.n int

declare @.maxcicle int

declare @.cicle int

set @.n = 100

set @.maxcicle = 10

set @.cicle = 1

while @.n = 100

begin

begin try

begin transaction

--

-- instructions that use #tmp

--

commit tran

set @.n = 0

end try

begin catch

set @.cicle = @.cicle + 1

set @.n = 100

rollback transaction

if @.clicle > @.maxcicle

begin

raise error

end

waitfor delay '00:00:10'

end catch

end

end

I call this procedure from Procedure 1

procedure 1

begin try

begin transaction

exec procedure 2

commit transaction

end try

begin catch

rollback transaction

end catch

end

when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.

Only to restart SQL Server service process was destroy becouse tempdb restart.

Thanks

Luca

|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.

|||

Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.

-Sue

process block itself

Hi to everybody,

i have this problem.

sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from

select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'

resorce type : OBJECT

resource id : 218899226

request mode : Sch-M

request status WAIT

request_session id : 51

request life time : 33554432

or from

SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51

waiting task address : 0x0000000000C2F198

session id : 51

wait type : LCK_M_SCH_M

resource address : 0x0000000201C71300

blocking task address : 0x0000000000C2F198

blocking session id = 51

resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226

I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process

I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)

So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server

thanks in advance

Luca

Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||

No, it isn't a DTS package but I have found where is problem...

I use a procedure like this to retry a block of istructions when i have an error

Procedure 2

.....

create table #tmp.....

declare @.n int

declare @.maxcicle int

declare @.cicle int

set @.n = 100

set @.maxcicle = 10

set @.cicle = 1

while @.n = 100

begin

begin try

begin transaction

--

-- instructions that use #tmp

--

commit tran

set @.n = 0

end try

begin catch

set @.cicle = @.cicle + 1

set @.n = 100

rollback transaction

if @.clicle > @.maxcicle

begin

raise error

end

waitfor delay '00:00:10'

end catch

end

end

I call this procedure from Procedure 1

procedure 1

begin try

begin transaction

exec procedure 2

commit transaction

end try

begin catch

rollback transaction

end catch

end

when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.

Only to restart SQL Server service process was destroy becouse tempdb restart.

Thanks

Luca

|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||

Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.

-Sue

process block itself

Hi to everybody,

i have this problem.

sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from

select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'

resorce type : OBJECT

resource id : 218899226

request mode : Sch-M

request status WAIT

request_session id : 51

request life time : 33554432

or from

SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51

waiting task address : 0x0000000000C2F198

session id : 51

wait type : LCK_M_SCH_M

resource address : 0x0000000201C71300

blocking task address : 0x0000000000C2F198

blocking session id = 51

resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226

I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process

I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)

So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server

thanks in advance

Luca

Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||

No, it isn't a DTS package but I have found where is problem...

I use a procedure like this to retry a block of istructions when i have an error

Procedure 2

.....

create table #tmp.....

declare @.n int

declare @.maxcicle int

declare @.cicle int

set @.n = 100

set @.maxcicle = 10

set @.cicle = 1

while @.n = 100

begin

begin try

begin transaction

--

-- instructions that use #tmp

--

commit tran

set @.n = 0

end try

begin catch

set @.cicle = @.cicle + 1

set @.n = 100

rollback transaction

if @.clicle > @.maxcicle

begin

raise error

end

waitfor delay '00:00:10'

end catch

end

end

I call this procedure from Procedure 1

procedure 1

begin try

begin transaction

exec procedure 2

commit transaction

end try

begin catch

rollback transaction

end catch

end

when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.

Only to restart SQL Server service process was destroy becouse tempdb restart.

Thanks

Luca

|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||

Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.

-Sue