ERROR: Subreport: sp4

Showing posts with label sp4. Show all posts

Thursday, March 29, 2012

estimation the buffer cache hit ratio

I have trouble with MSSQL2000 SP4 (without any hotfixes). During last two
weeks it start works anormally. After last optimalization (about few months
ago) it works good (fast, without blocks). Its buffer cache hit ratio was
about 99.7-99.8. Last day it starts work slow, there was many blocks and
dedlocks. There are no any queries, jobs and applications was added. Now
buffer cache hit ratio oscilate about 95-98. I try update statistics and
reindex some hard used tables, but there is no effect or effect is wery
short (after few hours problem return).

Mayby somene know what it could be?

Is it possible to estimate how each table (using DBCC SHOW_STATISTICS or
DBCC SHOWCONTIG or others) how the table affect on total buffer cache hit
ratio?

Marek

--
www.programowanieobiektowe.plMarek Wierzbicki (marek.wierzbickiiiii@.azymuttttt.pl) writes:

Quote:

Originally Posted by

I have trouble with MSSQL2000 SP4 (without any hotfixes). During last
two weeks it start works anormally. After last optimalization (about few
months ago) it works good (fast, without blocks). Its buffer cache hit
ratio was about 99.7-99.8. Last day it starts work slow, there was many
blocks and dedlocks. There are no any queries, jobs and applications was
added. Now buffer cache hit ratio oscilate about 95-98. I try update
statistics and reindex some hard used tables, but there is no effect or
effect is wery short (after few hours problem return).
>
Mayby somene know what it could be?

I would run Profiler and look for long-running queries. As your amount of
data grows and statistics changes, the optimizer may go for a new plan.

Theoretically, you could also run into that when the amount of data
increases over a threshold value, the memory does no longer suffice for the
typical mix of queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Tuesday, March 27, 2012

Estimated cost shows 300% in query plan

Hello all,
On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
(Windows Server 2003), most of the query plans I see are showing, on the
estimated cost of each step of the plan, more than 100% (e.g. 300% for a
seek).
Does anyone have an explanation or has already seen that ?
tia,
Rudi Bruchez
MCDBAIt's a bug in the graphical query plans when dealing with parallel query
plans. I have seen percentage reaching 4 figures even. I don't think it will
be fixed, considering it wasn't fixed in SP4.
--
Jacco Schalkwijk
SQL Server MVP
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:138syoixibdyn$.14zc2fkwrr1j5.dlg@.40tude.net...
> Hello all,
> On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
> (Windows Server 2003), most of the query plans I see are showing, on the
> estimated cost of each step of the plan, more than 100% (e.g. 300% for a
> seek).
> Does anyone have an explanation or has already seen that ?
> tia,
> Rudi Bruchez
> MCDBA|||On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
> It's a bug in the graphical query plans when dealing with parallel query
> plans. I have seen percentage reaching 4 figures even. I don't think it will
> be fixed, considering it wasn't fixed in SP4.
Hi,
Thanks for the feedback. My problem is that I've set the maximum degree of
parallelism to 1 at the server level, and there's no mention of parallelism
on the graphical plan. Should this still appear ?
Btw, even with a maxdop to 1, I still see some "degree of parallelism"
event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
Is it explanable ?
thanks again,
Rudi Bruchez|||On Thu, 27 Oct 2005 17:05:35 +0200, Rudi Bruchez
<rudi#no-spam#at.babaluga.com> wrote:
>On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
>(Windows Server 2003), most of the query plans I see are showing, on the
>estimated cost of each step of the plan, more than 100% (e.g. 300% for a
>seek).
>Does anyone have an explanation or has already seen that ?
It happens.
:)
How about the after-query plan?
J.|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>> It's a bug in the graphical query plans when dealing with parallel
>> query plans. I have seen percentage reaching 4 figures even. I don't
>> think it will be fixed, considering it wasn't fixed in SP4.
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:
> Rudi Bruchez wrote:
>> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>> It's a bug in the graphical query plans when dealing with parallel
>> query plans. I have seen percentage reaching 4 figures even. I don't
>> think it will be fixed, considering it wasn't fixed in SP4.
>> Hi,
>> Thanks for the feedback. My problem is that I've set the maximum
>> degree of parallelism to 1 at the server level, and there's no
>> mention of parallelism on the graphical plan. Should this still
>> appear ?
>> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
>> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
>> Is it explanable ?
>> thanks again,
>> Rudi Bruchez
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

Estimated cost shows 300% in query plan

How about the after-query plan?
J.
|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

Estimated cost shows 300% in query plan

How about the after-query plan?
J.|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.sql

Wednesday, March 21, 2012

Errors with SP4 upgrade... Please Help

I am having problems accessing DTS after install SP4 and was wondering
if someone could offer some advice.

I installed SP4 and got the following error after it competed.

Unable to write to response file 'U:\WINDOWS\setup.iss' during
recording. Please ensure enough space is available on target drive.

I got the error 3 times (3 pop-ups).

After the install I could not access DTS. I was getting 2 error
pop-ups.
1. 'rebbrui.rll is missing'
2. Snap-In failed to initialize: Meta Data Services

I read the groups and tried several things.

I tried to reinstall MDAC and it did not help.

I tried to register several dlls and it did not help.

I found this post and it seemed to help.
Repair Script:
http://www.experts-exchange.com/Dat...Q_21445589.html

I was then able to access DTS using the profile (which is an
administrator) which
I used to install SP4 and run the above repair script. However, no
other profile can open or run DTS.

I tried running the repair script under the other profiles and it did
not help.

I was receiving 2 errors for the other profiles. I only documented one
and I am no longer getting the other. Now it just goes to a white
screen and hangs.

1st error:
DTS Designer Error
The specified module could not be found.

2nd error:
???

I tried reinstalling client tools and SP4 and it did not help. Now I
get one additional error.

Before I can expand the server node in enterprise manager I get the
following error.
Snap-in failed to initialize
Name: Meta Data Services
CLSID: {1DBA4DD4-EB97-4FD2-AB80-9D0D4BA74034}

Does anyone have a suggestion on how I might fix this?

I am running SQL Server 2000 Enterprise on Windows 2000 Server with 4
GB RAM.

I did notices that someone has set the /3GB switch. I know this is not
supported on this OS but it was not causing major problems before the
upgrade. Could it be the culprit?Finally got this fixed. It was a real pain!

The registry on this server looked all hacked up to me. It looks like
many of the keys referenced U:/ instead of C:/

Some of the necessary dlls were pointing to U: also.

I had to manually uninstall SQL Server. Then reinstall. Then uninstall
via add remove programs. Then I did another uninstall/reinstall
(because i got a few errors).

I reinstalled, upgraded to sp4, stopped the service, swapped my master,
msdb, tempdb with the originals and everything is working great.

Sunday, February 26, 2012

Error:Initializing SQL Server Reconciler has failed.

My exploit environment:

VS.net 2003, SQL Server 2000 SP4, SQL Server CE 2.0 SP4

I write next codes:

Private cn As New SqlCeConnection("data source=\my documents\SQLCECF.sdf")

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try

If Not System.IO.File.Exists("\my documents\SQLCECF.sdf") Then

Dim en As New SqlCeEngine("data source=\my documents\SQLCECF.sdf")

en.CreateDatabase() 'create a blank database to subscribe

Synch()

End If

Catch err As SqlCeException

ShowErrors(err)

Catch err As Exception

MsgBox("There was an error:" & err.ToString())

End Try

End Sub

Sub Synch()

Try

Dim rep As SqlCeReplication = New SqlCeReplication

With rep

.Publisher = "192.168.0.222"

.PublisherDatabase = "CFLab"

.PublisherLogin = "sa"

.PublisherPassword = "sa"

.Publication = "CFLab"

.Subscriber = "SQLCENETCF"

.SubscriberConnectionString = "Provider=Microsoft.SQLServer.OLEDB.CE.2.0;Data Source=\My Documents\SQLCECF.sdf"

.InternetUrl = "http://192.168.0.222/SQLCE/sscesa20.dll"

' Create the Local SSCE Database subscription

rep.AddSubscription(AddOption.CreateDatabase)

rep.Synchronize() 'subscribe

End With

Catch err As SqlCeException

ShowErrors(err)

Finally

End Try

End Sub

Public Sub ShowErrors(ByRef e As SqlCeException)

Dim errorCollection As SqlCeErrorCollection = e.Errors

Dim bld As System.Text.StringBuilder = New System.Text.StringBuilder

Dim inner As Exception = e.InnerException

If Not inner Is Nothing Then

MessageBox.Show(("Inner Exception:" & inner.ToString()))

End If

For Each err As SqlCeError In errorCollection

bld.Append("/n Error Code: " + err.HResult.ToString("X", System.Globalization.CultureInfo.CurrentCulture))

bld.Append("/n Message : " + err.Message)

bld.Append("/n Minor Err.: " + err.NativeError.ToString())

bld.Append("/n Source : " + err.Source)

For Each numPar As Int32 In err.NumericErrorParameters

If 0 <> numPar Then

bld.Append("/n Num. Par. : " + numPar.ToString())

End If

For Each errPar As String In err.ErrorParameters

If String.Empty <> errPar Then

bld.Append("/n Err. Par. : " + errPar)

End If

Next errPar

MessageBox.Show(bld.ToString())

bld.Remove(0, bld.Length)

Next err

End Sub

Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click

connect()

Dim query As String = "select * from FlightData"

Dim dap As New SqlCeDataAdapter(query, cn)

Dim dataset1 As New DataSet

dap.Fill(dataset1, "FlightData")

Disconnect()

End Sub

Sub connect()

Synch()

Try

cn.Open()

Catch err As SqlCeException

ShowErrors(err)

End Try

End Sub

Sub Disconnect()

Try

cn.Close()

Catch err As SqlCeException

ShowErrors(err)

End Try

End Sub

Private Sub dgdResults_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgdResults.Click

End Sub

End Class

I want to synchronize the data from a SQLCE SERVER 2000

When i run the code above, the errors show:

Error Code:80045058
Message:Initializing SQL Server Reconciler has failed.

Error Code:80045058
Message:The course can't load "SSCE" Merge Replication program。please check the module wether enroll correctly.(probable description)

I do not know what's going wrong..=(
Who can help me?!!! HELP~~~~~~~~~
thanks..

Move to Sql Server Compact Edition forum.

Thanks!

|||

Please provide the exact error message, see here for instructions on how to get all error information:

http://msdn2.microsoft.com/en-us/library/ms174079.aspx

|||

I already used the error objectsuse and listed the error information above :

Error Code:80045058
Message:Initializing SQL Server Reconciler has failed.

Error Code:80045058
Message:The course can't load "SSCE" Merge Replication program。please check the module wether enroll correctly.(probable description)

and I do not know what's going wrong

help!

|||

You should also get a native error number (5 digits), please list this, and the proper english text for this:

The course can't load "SSCE" Merge Replication program。please check the module wether enroll correctly.(probable description)

|||

I use the SQL CE of Chinese Edition, so I don't know the proper english text for that.

the text list above is which I translate into English.

thanks

|||I still do not see the native error and the error source (although it appears to be in your ShowErrors code)|||

hi, eric zhang, I met a same problem with. and NATIVE_ERROR: 29045
I'm a chinese too.So, If you have any solution, please let me share with you, thx

Thursday, March 29, 2012

estimation the buffer cache hit ratio

Tuesday, March 27, 2012

Estimated cost shows 300% in query plan

Estimated cost shows 300% in query plan

Estimated cost shows 300% in query plan

Wednesday, March 21, 2012

Errors with SP4 upgrade... Please Help

Sunday, February 26, 2012

Error:Initializing SQL Server Reconciler has failed.

ERROR: Subreport

Blog Archive

About Me