Another one about blocking

This is weird, but I hope someone can help me out:
MSSQLServer 2000, SP 2, W2K Server
Dedicated server with one DB, 60 concurrent users
Locktimeout is set
The DB is accesses via my app only.
About once a week the DB runs into a blocking situation, wich locks up all
other users. Although the lock timeout is set, no timeout occurs. I
monitored the DB for the blockees and got grip of the blocking statement:
select count(*) as c FROM TBLMWTRACE M WITH (NOLOCK) WHERE m.datum = Convert(datetime, '20031120', 112) and ((GELESEN IS NULL) or (GELESEN = 'N')) AND (M.HAUSCD = 'BRI')
I researched my app and I am quite sure, that there are no transactions left
open. If lengthy select statements were the cause, they would have been
captured by the monitor.
My assumption is, that if SQLServer gets under load, he starts issuing
x-locks without releasing them. But this can´t be true, of course.
What can I do to track down what the causes the blocks?
Thanks BerndHi Bernd !
Seems, that the option "with (NOLOCK)" does not work as expected. This
option is identical to transaction mode set to uncommmitted, that means,
that no locks are set, incoinsitencies may occurr. Have you considered
the "lock escalation" ? SQL Server 2000 automatically switches to table
lock, whenever too many users try to access a database.
My advice. Use databases with MVTO, MVRO, your problems will disappear.
look for it in google groups.
regards, Guido Stepken
Bernd Maierhofer (dato) wrote:
> This is weird, but I hope someone can help me out:
> MSSQLServer 2000, SP 2, W2K Server
> Dedicated server with one DB, 60 concurrent users
> Locktimeout is set
> The DB is accesses via my app only.
> About once a week the DB runs into a blocking situation, wich locks up all
> other users. Although the lock timeout is set, no timeout occurs. I
> monitored the DB for the blockees and got grip of the blocking statement:
> select count(*) as c FROM TBLMWTRACE M WITH (NOLOCK) WHERE m.datum => Convert(datetime, '20031120', 112) and ((GELESEN IS NULL) or (GELESEN => 'N')) AND (M.HAUSCD = 'BRI')
> I researched my app and I am quite sure, that there are no transactions left
> open. If lengthy select statements were the cause, they would have been
> captured by the monitor.
> My assumption is, that if SQLServer gets under load, he starts issuing
> x-locks without releasing them. But this can´t be true, of course.
> What can I do to track down what the causes the blocks?
> Thanks Bernd
>

anormal increase of rowmodctr for a table

HI,
We have 5 GIG OLTP database with 25 users.
Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
days
I cannot explain why rowmodcrt increase so rapidly for a specific table.
It look like 500,000 modifications are done on this table evweryday.
After 4 days, rowmodctr = 2,000,000
I f check witth PROFILER, around 200 upgrades are done on this table
everyday.
Since fews days, I decided to scedule every nignt the Maintenace Plan that
Update Statistic
Any idea ?
Thank you
danny
(P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
on the table since the last time the statistics were updated or created.Hi Danny
Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
does?
It is not a supplied procedure, so somebody must have added it to your
system.
Profiler will show you the number of update commands, but rowmodctr counts
every row. How many rows are being updated in each update statement?
How many rows are in the table?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Thank you Kalen

answer: each update statement update only 1 row
answer : 22,000 rows
CREATE PROCEDURE Usp_dba_ShowMe_TableStats @.dbname sysname=NULL, @.option
char (1) = NULL
AS
BEGIN
Declare @.what char(1),
@.qry varchar(2000)
set @.what = @.option
IF @.what = 'I'
Begin
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
ELSE IF @.what = 'A'
Begin
Print space(10)+' Run the Update Statistics on the following Tables'
SET @.qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @.dbName + ' SELECT Distinct
''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
JOIN SYSINDEXES i ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
--ORDER BY O.NAME'
exec (@.qry)
End
ELSE
Begin
Print space(10)+'Please pass in the right parameters : DBName and option
"I" for Information or "A" Action"'
PRINT
'----
----'
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
END
"Kalen Delaney" <replies@.public_newsgroups.com> a crit dans le message de
news:O9cu6uPAEHA.3048@.tk2msftngp13.phx.gbl...
> Hi Danny
> Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
> does?
> It is not a supplied procedure, so somebody must have added it to your
> system.
> Profiler will show you the number of update commands, but rowmodctr counts
> every row. How many rows are being updated in each update statement?
> How many rows are in the table?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
> news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
(clustered)
every
that
changes
>|||HI,
I think I found what happens
A process is modifiing rowmodctr associated with a _WA_Sys
I think I identify the user associated with this process.
danny
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a crit dans le message de
news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Hi Danny
Good catch! It is a interesting procedure you have, but you can add another
condition to ignore column stats. The number may be high as those are
automatically dropped if no one uses the stats for a while and then maybe
rebuilt later. There is little overhead.
To have your proc ignore stats only rows in sysindexes, you can make this
change:
SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
and indexproperty(i.id, indid, 'IsStatistics') = 0 -- <=======
additional condition
ORDER BY i.rowmodctr DESC
Please read about INDEXPROPERTY in the BOL for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:O9AoXHhAEHA.3308@.TK2MSFTNGP10.phx.gbl...
> HI,
> I think I found what happens
> A process is modifiing rowmodctr associated with a _WA_Sys
> I think I identify the user associated with this process.
> danny
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a crit dans le message de
> news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
(clustered)
every
that
changes
>

anormal increase of rowmodctr for a table

HI,
We have 5 GIG OLTP database with 25 users.
Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
days
I cannot explain why rowmodcrt increase so rapidly for a specific table.
It look like 500,000 modifications are done on this table evweryday.
After 4 days, rowmodctr = 2,000,000
I f check witth PROFILER, around 200 upgrades are done on this table
everyday.
Since fews days, I decided to scedule every nignt the Maintenace Plan that
Update Statistic
Any idea ?
Thank you
danny
(P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
on the table since the last time the statistics were updated or created.Hi Danny
Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
does?
It is not a supplied procedure, so somebody must have added it to your
system.
Profiler will show you the number of update commands, but rowmodctr counts
every row. How many rows are being updated in each update statement?
How many rows are in the table?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Thank you Kalen
>>How many rows are being updated in each update statement
answer: each update statement update only 1 row
>>How many rows are in the table
answer : 22,000 rows
CREATE PROCEDURE Usp_dba_ShowMe_TableStats @.dbname sysname=NULL, @.option
char (1) = NULL
AS
BEGIN
Declare @.what char(1),
@.qry varchar(2000)
set @.what = @.option
IF @.what = 'I'
Begin
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
ELSE IF @.what = 'A'
Begin
Print space(10)+' Run the Update Statistics on the following Tables'
SET @.qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @.dbName + ' SELECT Distinct
''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
JOIN SYSINDEXES i ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
--ORDER BY O.NAME'
exec (@.qry)
End
ELSE
Begin
Print space(10)+'Please pass in the right parameters : DBName and option
"I" for Information or "A" Action"'
PRINT
'----
----'
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
END
"Kalen Delaney" <replies@.public_newsgroups.com> a écrit dans le message de
news:O9cu6uPAEHA.3048@.tk2msftngp13.phx.gbl...
> Hi Danny
> Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
> does?
> It is not a supplied procedure, so somebody must have added it to your
> system.
> Profiler will show you the number of update commands, but rowmodctr counts
> every row. How many rows are being updated in each update statement?
> How many rows are in the table?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
> news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> > HI,
> >
> > We have 5 GIG OLTP database with 25 users.
> > Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k
(clustered)
> >
> > Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'"
every
> > days
> > I cannot explain why rowmodcrt increase so rapidly for a specific table.
> > It look like 500,000 modifications are done on this table evweryday.
> > After 4 days, rowmodctr = 2,000,000
> > I f check witth PROFILER, around 200 upgrades are done on this table
> > everyday.
> > Since fews days, I decided to scedule every nignt the Maintenace Plan
that
> > Update Statistic
> >
> > Any idea ?
> >
> > Thank you
> > danny
> > (P.S. The rowmodctr column in SYSINDEXES value shows the number of
changes
> > on the table since the last time the statistics were updated or created.
> >
> >
>|||HI,
I think I found what happens
A process is modifiing rowmodctr associated with a _WA_Sys
I think I identify the user associated with this process.
danny
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a écrit dans le message de
news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Hi Danny
Good catch! It is a interesting procedure you have, but you can add another
condition to ignore column stats. The number may be high as those are
automatically dropped if no one uses the stats for a while and then maybe
rebuilt later. There is little overhead.
To have your proc ignore stats only rows in sysindexes, you can make this
change:
SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
and indexproperty(i.id, indid, 'IsStatistics') = 0 -- <=======additional condition
ORDER BY i.rowmodctr DESC
--
Please read about INDEXPROPERTY in the BOL for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:O9AoXHhAEHA.3308@.TK2MSFTNGP10.phx.gbl...
> HI,
> I think I found what happens
> A process is modifiing rowmodctr associated with a _WA_Sys
> I think I identify the user associated with this process.
> danny
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a écrit dans le message de
> news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> > HI,
> >
> > We have 5 GIG OLTP database with 25 users.
> > Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k
(clustered)
> >
> > Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'"
every
> > days
> > I cannot explain why rowmodcrt increase so rapidly for a specific table.
> > It look like 500,000 modifications are done on this table evweryday.
> > After 4 days, rowmodctr = 2,000,000
> > I f check witth PROFILER, around 200 upgrades are done on this table
> > everyday.
> > Since fews days, I decided to scedule every nignt the Maintenace Plan
that
> > Update Statistic
> >
> > Any idea ?
> >
> > Thank you
> > danny
> > (P.S. The rowmodctr column in SYSINDEXES value shows the number of
changes
> > on the table since the last time the statistics were updated or created.
> >
> >
>

Analysis Services: Data source provider error

Trying to go through the Analysis Services tuturial. Logged in as Administrator on a 64 bit W2K server. SQL Server 2000 AS and SP4.

As I come to the Design Storage step I get a message saying
Data source provider error: ; Time:2006-04-10 19:51:47
There are no further details given and the Event Logger has nothing.

Grateful for any help.

Screenshot: http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem.png

And the version Screenshot:
http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem2.pngReason was probably that I defined the data source as a User DSN. Should have been System DSN.

I opted to use Jet instead or the Access driver - solved it.|||I'm just starting out with Analysis Services. Also new working with databases. A friend of mine sent me a couple of Databases and they are files with .mdf file extensions. When I try and setup datasource in the control panel wizard, there are no drivers with the .mdf extensions. Just .dbo and others.

Could you explain to me what I need to do to get AS to work with my databases?

Thanks in advance,
Milfredo|||No idea how the drivers are distributed, but I guess installing MS Access will do it. Probably instaling Analys Services includes driver installation.

I am more of a unix guy, so do not trust me. :D