Procedure Time out: leave

Showing posts with label leave. Show all posts

Wednesday, March 21, 2012

processor doesn't leave the 100%

I noticed the use of 100% of the processor in machines using Win2000,
independent of being in the application .mde or with the application in way
desing. In the with Win98 work usually. doesn't need to execute anything,
mecher is enough the mouse and ready, the processor is in picks of 100/80%.
Some months ago noticed this in a seek application in a server P4 of 3.0 gb,
1Gb mem, that I installed. When accessing the bank Sql Server in Win2000
Server the processor doesn't leave the 100% doing with that the stations are
very slow.
Did anybody already come across this problem?
And other still: To with the win2k in all the stations and Server in the
server. In intervals non sequence, the date and the hour of the personal
computer of the net reset for 1899, 1890 and for oh, nor for the date of the
bios it is.
I already passed anti-virus of the site of the simantec and doesn't have
anything.Sounds like you may have the Slammer virus. Are you running the latest
service packs?
http://www.aspfaq.com/show.asp?id=2441
--
Andrew J. Kelly SQL MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:egL3maQ8EHA.2124@.TK2MSFTNGP15.phx.gbl...
>I noticed the use of 100% of the processor in machines using Win2000,
> independent of being in the application .mde or with the application in
> way
> desing. In the with Win98 work usually. doesn't need to execute anything,
> mecher is enough the mouse and ready, the processor is in picks of
> 100/80%.
> Some months ago noticed this in a seek application in a server P4 of 3.0
> gb,
> 1Gb mem, that I installed. When accessing the bank Sql Server in Win2000
> Server the processor doesn't leave the 100% doing with that the stations
> are
> very slow.
> Did anybody already come across this problem?
> And other still: To with the win2k in all the stations and Server in the
> server. In intervals non sequence, the date and the hour of the personal
> computer of the net reset for 1899, 1890 and for oh, nor for the date of
> the
> bios it is.
> I already passed anti-virus of the site of the simantec and doesn't have
> anything.
>|||Win2000, Server and Sql Server with all the updated available SP's.
Anti-virus in all the equipments and updated. The Interesting is that also
noticed this in my machine using Access in way design and the same thing
happens. The processor is in the same 100% me not making anything, just
mecher the mouse already maintains the pick in the 100%. Any other
application works normal, I already tested. I can do from a document, a
graph in Corel, a presentation in the Power Point... it doesn't arrive in
the 50%, in the Access mecher in the mouse maintains the 100% of activity.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:uGDdVsQ8EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Sounds like you may have the Slammer virus. Are you running the latest
> service packs?
> http://www.aspfaq.com/show.asp?id=2441
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Dulk" <fdulk@.bol.com.br> wrote in message
> news:egL3maQ8EHA.2124@.TK2MSFTNGP15.phx.gbl...
> >I noticed the use of 100% of the processor in machines using Win2000,
> > independent of being in the application .mde or with the application in
> > way
> > desing. In the with Win98 work usually. doesn't need to execute
anything,
> > mecher is enough the mouse and ready, the processor is in picks of
> > 100/80%.
> > Some months ago noticed this in a seek application in a server P4 of 3.0
> > gb,
> > 1Gb mem, that I installed. When accessing the bank Sql Server in Win2000
> > Server the processor doesn't leave the 100% doing with that the stations
> > are
> > very slow.
> > Did anybody already come across this problem?
> >
> > And other still: To with the win2k in all the stations and Server in the
> > server. In intervals non sequence, the date and the hour of the personal
> > computer of the net reset for 1899, 1890 and for oh, nor for the date of
> > the
> > bios it is.
> > I already passed anti-virus of the site of the simantec and doesn't have
> > anything.
> >
> >
>|||Hi
For SQL Server, run SELECT @.@.VERSION from Query analyzer. If the value
returned is less than 8.00.760, you are susceptible.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:ezQHSho9EHA.3504@.TK2MSFTNGP12.phx.gbl...
> Win2000, Server and Sql Server with all the updated available SP's.
> Anti-virus in all the equipments and updated. The Interesting is that also
> noticed this in my machine using Access in way design and the same thing
> happens. The processor is in the same 100% me not making anything, just
> mecher the mouse already maintains the pick in the 100%. Any other
> application works normal, I already tested. I can do from a document, a
> graph in Corel, a presentation in the Power Point... it doesn't arrive in
> the 50%, in the Access mecher in the mouse maintains the 100% of activity.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
> news:uGDdVsQ8EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > Sounds like you may have the Slammer virus. Are you running the latest
> > service packs?
> >
> > http://www.aspfaq.com/show.asp?id=2441
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Frank Dulk" <fdulk@.bol.com.br> wrote in message
> > news:egL3maQ8EHA.2124@.TK2MSFTNGP15.phx.gbl...
> > >I noticed the use of 100% of the processor in machines using Win2000,
> > > independent of being in the application .mde or with the application
in
> > > way
> > > desing. In the with Win98 work usually. doesn't need to execute
> anything,
> > > mecher is enough the mouse and ready, the processor is in picks of
> > > 100/80%.
> > > Some months ago noticed this in a seek application in a server P4 of
3.0
> > > gb,
> > > 1Gb mem, that I installed. When accessing the bank Sql Server in
Win2000
> > > Server the processor doesn't leave the 100% doing with that the
stations
> > > are
> > > very slow.
> > > Did anybody already come across this problem?
> > >
> > > And other still: To with the win2k in all the stations and Server in
the
> > > server. In intervals non sequence, the date and the hour of the
personal
> > > computer of the net reset for 1899, 1890 and for oh, nor for the date
of
> > > the
> > > bios it is.
> > > I already passed anti-virus of the site of the simantec and doesn't
have
> > > anything.
> > >
> > >
> >
> >
>|||I'm experiencing the same or at least very similar problem.
After few days (time period before it happens again is random), at once
system process takes 100% of processor (or better = the rest what other
applications don't need ... i mean if you open something it works slower
than normally, because it has around 50% of processor - the rest is used
by system process).
Only thing that helps is restart of whole machine, i have no antivirus
software, but this computer doesn't communicate with internet so i'm
pretty sure there are no viruses on it.
I tried performance monitoring, but didn't have enough time, because it
is on a server that needs to be accessible 24/7 and must have very quick
responses. I found that there is a large number of active and passive
connections. (12000+ active and 84000+ passive). I didn't found any
extensive disk activity, memory usage is quite low ...
Does anyone have any clue ?
Thanks for any suggeestion.
Honza Novak
Frank Dulk wrote:
> Win2000, Server and Sql Server with all the updated available SP's.
> Anti-virus in all the equipments and updated. The Interesting is that also
> noticed this in my machine using Access in way design and the same thing
> happens. The processor is in the same 100% me not making anything, just
> mecher the mouse already maintains the pick in the 100%. Any other
> application works normal, I already tested. I can do from a document, a
> graph in Corel, a presentation in the Power Point... it doesn't arrive in
> the 50%, in the Access mecher in the mouse maintains the 100% of activity.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
> news:uGDdVsQ8EHA.3336@.TK2MSFTNGP11.phx.gbl...
>>Sounds like you may have the Slammer virus. Are you running the latest
>>service packs?
>>http://www.aspfaq.com/show.asp?id=2441
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Frank Dulk" <fdulk@.bol.com.br> wrote in message
>>news:egL3maQ8EHA.2124@.TK2MSFTNGP15.phx.gbl...
>>I noticed the use of 100% of the processor in machines using Win2000,
>>independent of being in the application .mde or with the application in
>>way
>>desing. In the with Win98 work usually. doesn't need to execute
> anything,
>>mecher is enough the mouse and ready, the processor is in picks of
>>100/80%.
>>Some months ago noticed this in a seek application in a server P4 of 3.0
>>gb,
>>1Gb mem, that I installed. When accessing the bank Sql Server in Win2000
>>Server the processor doesn't leave the 100% doing with that the stations
>>are
>>very slow.
>>Did anybody already come across this problem?
>>And other still: To with the win2k in all the stations and Server in the
>>server. In intervals non sequence, the date and the hour of the personal
>>computer of the net reset for 1899, 1890 and for oh, nor for the date of
>>the
>>bios it is.
>>I already passed anti-virus of the site of the simantec and doesn't have
>>anything.
>>
>>
>

Saturday, February 25, 2012

process cube but leave transaction open?

We have a massive cube. All but one of the fact tables are by 3am. But the final fact table isn't loaded in the SQL data warehouse until 11am. The users want the cube processed and available as immediately as possible. But the cube can't show ANY updated data until it shows ALL updated data including the fact table from 11am. Any thoughts on making this happen?

One option would be to process the cube at 3am in another database. Then process the final measure group at 11am. Then use the Synchronize command to get those changes to the live database soon after 11am. But there are two drawbacks... 1) This requires twice the amount of disk space as before and 2) I can't figure out a way to synchronize two databases on the same server.

Another option would be to process the cube at 3am but leave that transaction open (so end users can't see the new data) for 8 hours until you process the final measure group and then close the transaction. This doesn't seem like a good plan, but I wanted to hear people's thoughts.

I would certainly experiment with leaving transaction open for 8 hours. I don't see why it doesn't seem like a good plan to you. Note, that you will still have doubled usage of disk space, since two versions will be coexisting for the 8 hours.|||

Well, maybe it's not a dumb idea. I guess the reason I was worried about it is just my relational background and the idea to never leave transactions open. But I guess that doesn't matter in this case.

So my main concern with the technical feasibility is whether the server will clean up the idle session (in so doing cancel the transaction). Am I going to have to run some cheap discover function ever 5 mins for 8 hours to keep the session alive? Any other way?

Thanks for the feedback Mosha.

|||

If server canceling the idle session becomes a problem - you can always bump up the session expiration timeout.

|||

I've got this working. Cube processing is broken up into two parts with the connection being dropped in between. Just like you said, it works fine. Just had to bump up the IdleOrphanSessionTimeout setting.

There is one problem, though. If we get a failure on part 2 of the processing (like if there's an invalid dimension key in a fact table) then that failure rolls back the entire transaction, not just the stuff being processed during part 2. Wondering if there's a way around this. I can walk you through reproducing this against Adventure Works:

First, change the query binding of the Reseller_Sales_2004 partition of the Reseller Sales measure group to be:

SELECT [dbo].[FactResellerSales].[ProductKey],-999 as OrderDateKey,[dbo].[FactResellerSales].[DueDateKey],[dbo].[FactResellerSales].[ShipDateKey],[dbo].[FactResellerSales].[ResellerKey], [dbo].[FactResellerSales].[EmployeeKey],[dbo].[FactResellerSales].[PromotionKey],[dbo].[FactResellerSales].[CurrencyKey],[dbo].[FactResellerSales].[SalesTerritoryKey],[dbo].[FactResellerSales].[SalesOrderNumber],[dbo].[FactResellerSales].[SalesOrderLineNumber],[dbo].[FactResellerSales].[RevisionNumber],[dbo].[FactResellerSales].[OrderQuantity],[dbo].[FactResellerSales].[UnitPrice],[dbo].[FactResellerSales].[ExtendedAmount],[dbo].[FactResellerSales].[UnitPriceDiscountPct],[dbo].[FactResellerSales].[DiscountAmount],[dbo].[FactResellerSales].[ProductStandardCost],[dbo].[FactResellerSales].[TotalProductCost],[dbo].[FactResellerSales].[SalesAmount],[dbo].[FactResellerSales].[TaxAmt],[dbo].[FactResellerSales].[Freight],[dbo].[FactResellerSales].[CarrierTrackingNumber],[dbo].[FactResellerSales].[CustomerPONumber]
FROM [dbo].[FactResellerSales]
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'

(Note that "-999 as OrderDateKey" will cause a failure.)

Second, pull up the directory contains the 2004 partition for Internet Sales. (On your computer it should be something like c:\program files\Microsoft SQL Server\mssql.2\OLAP\data\Adventure Works DW.0.db\Adventure Works DW.18.cub\Fact Internet Sales 1.18.det\Internet_Sales_2004.16.prt) After part 1 completes, you'll see the number of files in this directory double. After part 2 fails, you'll see those new files disappear as the transaction was rolled back.

Then, compile a little VB.NET app, put a breakpoint on the Sleep command so you can see the number of files in that 2004 partition directory double. Then run it:

Public Shared Function Main(ByVal args As String()) As Integer

Dim sServer As String = "localhost"

Dim sDatabase As String = "Adventure Works DW"

ProcessPart1(sServer, sDatabase)

System.Threading.Thread.Sleep(5000) 'simulate a break in the program

ProcessPart2(sServer, sDatabase)

End Function

Public Shared Sub ProcessPart1(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

'save the session ID so we can retrieve it later and connect to this session later

'alternately, it could be saved to a SQL database instead

database.Annotations.SetText("ProcessCubeSessionID", server.SessionID)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.BeginTransaction()

Try

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Internet Sales")

mg.Process(ProcessType.ProcessFull)

'after processing has succeeded, change the orphan session timeout

'to 24 hours so that this session we're about to leave open won't timeout

'before we're done with it

server.ServerProperties("IdleOrphanSessionTimeout").Value = CStr(24 * 60 * 60)

server.Update()

'false means don't end session

'leave the session open so we can connect to it later

'and continue with the open transaction

server.Disconnect(False)

'don't commit the transaction now

'leave it open until we finish the processing later

Catch ex As Exception

server.RollbackTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.Disconnect(True)

Throw

End Try

End Sub

Public Shared Sub ProcessPart2(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

Dim oldSession As String = database.Annotations.GetText("ProcessCubeSessionID")

If String.IsNullOrEmpty(oldSession) Then

Throw New Exception("Was not able to find the existing session which " _

& "processed the other 95% of the cube and has the open transaction! " _

& "The cube will not be processed.")

End If

server.Disconnect(True)

Try

server.Connect(sServer, oldSession)

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Reseller Sales")

mg.Process(ProcessType.ProcessFull) 'BUG?: if this fails, it rolls back the transaction!!!

server.CommitTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

'set this property back to the default now that

'we're done needing orphan sessions to survive

server.ServerProperties("IdleOrphanSessionTimeout").Value = _

server.ServerProperties("IdleOrphanSessionTimeout").DefaultValue

server.Update()

server.Disconnect(True) 'true means to end session

Catch ex As Exception

'there was a problem with the last 5% of the processing...

'so leave the session open so the problem can be corrected and we can

'start with 95% of the processing from earlier in the morning

'being completed already

server.Disconnect(False) 'false means don't end session

Throw

End Try

End Sub

If you search for the line in the code which has a comment saying "BUG?" that's where the processing fails because of our tweak to the partition query. When that processing fails.

|||

I'd still like to hear if anyone knows a workaround, but I've posted this as a bug:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278000

|||I expect the bug that you filed to be resolved "By Design". The definition of the transaction is that it either fully succeed or fully fails. That's the letter "A" in the ACID definition - A for Atomicy. You have very long transaction, but it doesn't matter - if anything fails within transaction, the entire transaction is rolled back.

process cube but leave transaction open?

Thanks for the feedback Mosha.

|||

If server canceling the idle session becomes a problem - you can always bump up the session expiration timeout.

|||

First, change the query binding of the Reseller_Sales_2004 partition of the Reseller Sales measure group to be:

SELECT [dbo].[FactResellerSales].[ProductKey],-999 as OrderDateKey,[dbo].[FactResellerSales].[DueDateKey],[dbo].[FactResellerSales].[ShipDateKey],[dbo].[FactResellerSales].[ResellerKey], [dbo].[FactResellerSales].[EmployeeKey],[dbo].[FactResellerSales].[PromotionKey],[dbo].[FactResellerSales].[CurrencyKey],[dbo].[FactResellerSales].[SalesTerritoryKey],[dbo].[FactResellerSales].[SalesOrderNumber],[dbo].[FactResellerSales].[SalesOrderLineNumber],[dbo].[FactResellerSales].[RevisionNumber],[dbo].[FactResellerSales].[OrderQuantity],[dbo].[FactResellerSales].[UnitPrice],[dbo].[FactResellerSales].[ExtendedAmount],[dbo].[FactResellerSales].[UnitPriceDiscountPct],[dbo].[FactResellerSales].[DiscountAmount],[dbo].[FactResellerSales].[ProductStandardCost],[dbo].[FactResellerSales].[TotalProductCost],[dbo].[FactResellerSales].[SalesAmount],[dbo].[FactResellerSales].[TaxAmt],[dbo].[FactResellerSales].[Freight],[dbo].[FactResellerSales].[CarrierTrackingNumber],[dbo].[FactResellerSales].[CustomerPONumber]
FROM [dbo].[FactResellerSales]
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'

(Note that "-999 as OrderDateKey" will cause a failure.)

Then, compile a little VB.NET app, put a breakpoint on the Sleep command so you can see the number of files in that 2004 partition directory double. Then run it:

Public Shared Function Main(ByVal args As String()) As Integer

Dim sServer As String = "localhost"

Dim sDatabase As String = "Adventure Works DW"

ProcessPart1(sServer, sDatabase)

System.Threading.Thread.Sleep(5000) 'simulate a break in the program

ProcessPart2(sServer, sDatabase)

End Function

Public Shared Sub ProcessPart1(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

'save the session ID so we can retrieve it later and connect to this session later

'alternately, it could be saved to a SQL database instead

database.Annotations.SetText("ProcessCubeSessionID", server.SessionID)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.BeginTransaction()

Try

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Internet Sales")

mg.Process(ProcessType.ProcessFull)

'after processing has succeeded, change the orphan session timeout

'to 24 hours so that this session we're about to leave open won't timeout

'before we're done with it

server.ServerProperties("IdleOrphanSessionTimeout").Value = CStr(24 * 60 * 60)

server.Update()

'false means don't end session

'leave the session open so we can connect to it later

'and continue with the open transaction

server.Disconnect(False)

'don't commit the transaction now

'leave it open until we finish the processing later

Catch ex As Exception

server.RollbackTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.Disconnect(True)

Throw

End Try

End Sub

Public Shared Sub ProcessPart2(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

Dim oldSession As String = database.Annotations.GetText("ProcessCubeSessionID")

If String.IsNullOrEmpty(oldSession) Then

Throw New Exception("Was not able to find the existing session which " _

& "processed the other 95% of the cube and has the open transaction! " _

& "The cube will not be processed.")

End If

server.Disconnect(True)

Try

server.Connect(sServer, oldSession)

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Reseller Sales")

mg.Process(ProcessType.ProcessFull) 'BUG?: if this fails, it rolls back the transaction!!!

server.CommitTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

'set this property back to the default now that

'we're done needing orphan sessions to survive

server.ServerProperties("IdleOrphanSessionTimeout").Value = _

server.ServerProperties("IdleOrphanSessionTimeout").DefaultValue

server.Update()

server.Disconnect(True) 'true means to end session

Catch ex As Exception

'there was a problem with the last 5% of the processing...

'so leave the session open so the problem can be corrected and we can

'start with 95% of the processing from earlier in the morning

'being completed already

server.Disconnect(False) 'false means don't end session

Throw

End Try

End Sub

If you search for the line in the code which has a comment saying "BUG?" that's where the processing fails because of our tweak to the partition query. When that processing fails.

|||

I'd still like to hear if anyone knows a workaround, but I've posted this as a bug:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278000

Wednesday, March 21, 2012

processor doesn't leave the 100%

processor doesn't leave the 100%

processor doesn't leave the 100%

Saturday, February 25, 2012

process cube but leave transaction open?

process cube but leave transaction open?

Procedure Time out

Blog Archive

About Me