Showing posts with label cache. Show all posts
Showing posts with label cache. Show all posts

Wednesday, March 28, 2012

performance differnce between 3027 and 3033

Hi,

I have found major differnces in performance between the two versions - specially with queries that should be answerd from cache if i execute multiple times...

Test Query - after the first execute it tooks

1 second with 9.0.3027 and 12 seconds at 9.0.3033.

Is this a bug in the last ctp - does this change back in the released SP2? or is the something bad in my models for the latest version

we used two equal machines - processed the db on 9.0.3033 and restored the backup on 9.0.3027

query looks like

select

{[4WochenSet]* [Zeit].[Tag].[Tag].Members} on axis(0),

Descendants([Immobilien].[Geographie].[Bundesland].&[Wien]&[AUT], [Immobilien].[Geographie].[PLZ],SELF) on axis(1)

from

Angebotsanalyse

where ([Measures].[Kaufpreis je Wohnflaeche MW], [Plattform].[Plattform].&[156],[Immobilien].[Immobilientyp].&[5])

LG, HANNES

Are there any calculations used by the query? How is the set [4WochenSet] defined? If you capture the performance counters under MSAS 2005:MDX during the query, do you see any significant difference in performance counter values between the two builds? For example, do you see a much larger increase in the value of the [Total calculation covers] performance counter in build 3033? If there is calculation involved in the query, such IIF, try connection string property "Cache Policy=9" to see if there is any difference in performance.

Jeffrey

|||

There are some calc, the measure [Kaufpreis je Wohnflaeche MW] is defined as

AVG(existing({[Immobilien].[Immobilie].[Immobilie].Members}),[Measures].[Kaufpreis je Wohnflaeche])

and the set is defined as

{Tail(except(

exists({[Zeit].[Zeit nach Wochen].[Woche].Members},[Zeit].[Abgeschlossen].&[True]),

exists({[Zeit].[Zeit nach Wochen].[Woche].Members},[Zeit].[Abgeschlossen].&[False])

),4)}

by this definition there are no if calculations, but the base measure [Measures].[Kaufpreis je Wohnflaeche] is defined as lastnonempty.

At the moment i test only using management studio - later i will test it with your sugestions.

Is this further optimized in the released version of SP2? so we do not need the setting?

LG, HANNES

|||

It's hard to tell what caused the performance degredation. But since there is no IIF and other calculation conditions, "Cache Policy=9" shouldn't make any difference.

Saturday, February 25, 2012

Perfmon SQL Server counters not appearing in list for saveds counter log.

Hi,
I set up a counter log to record SQL data in perfmon. This included a
few O/S counters and
SQL Server:Buffer Manager/Buffer Cache Hit Ratio
SQL Server:General Statistics/User Connections
I am now trying to look at the data through Perf Mon on the same
machine. However I can only add O/S counters to the view. The SQL
Server counters don't appear in the list.
Any ideas?
Cheers,
JamesHi James
"JimLad" wrote:
> Hi,
> I set up a counter log to record SQL data in perfmon. This included a
> few O/S counters and
> SQL Server:Buffer Manager/Buffer Cache Hit Ratio
> SQL Server:General Statistics/User Connections
> I am now trying to look at the data through Perf Mon on the same
> machine. However I can only add O/S counters to the view. The SQL
> Server counters don't appear in the list.
> Any ideas?
> Cheers,
> James
I assume this is opening the log file? In which case I suspect the counters
weren't collected. Are the counters available if you try to add them to the
system monitor?
If you are on a 64 bit server are you running the correct version of perfmon?
John|||On Jul 9, 1:28 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi James
>
>
> "JimLad" wrote:
> > Hi,
> > I set up a counter log to record SQL data in perfmon. This included a
> > few O/S counters and
> > SQL Server:Buffer Manager/Buffer Cache Hit Ratio
> > SQL Server:General Statistics/User Connections
> > I am now trying to look at the data through Perf Mon on the same
> > machine. However I can only add O/S counters to the view. The SQL
> > Server counters don't appear in the list.
> > Any ideas?
> > Cheers,
> > James
> I assume this is opening the log file? In which case I suspect the counters
> weren't collected. Are the counters available if you try to add them to the
> system monitor?
> If you are on a 64 bit server are you running the correct version of perfmon?
> John- Hide quoted text -
> - Show quoted text -
Hi John,
Yes, I am opening the log file. And yes they are available in Sys Mon.
It's a virtualised 32bit server I believe (Intel Xeon 3.2GHz).
As for perfmon version I'm just using the one in Administrative Tools.
So, I definitely put the counters in the log. Why haven't they been
recorded? I notice there is something about objects as well. I only
put the individual counters in. Do I need to put the whole SQL Server
object in?
Cheers,
James|||Hi James
"JimLad" wrote:
> On Jul 9, 1:28 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi James
> >
> >
> >
> >
> >
> > "JimLad" wrote:
> > > Hi,
> >
> > > I set up a counter log to record SQL data in perfmon. This included a
> > > few O/S counters and
> >
> > > SQL Server:Buffer Manager/Buffer Cache Hit Ratio
> > > SQL Server:General Statistics/User Connections
> >
> > > I am now trying to look at the data through Perf Mon on the same
> > > machine. However I can only add O/S counters to the view. The SQL
> > > Server counters don't appear in the list.
> >
> > > Any ideas?
> >
> > > Cheers,
> >
> > > James
> >
> > I assume this is opening the log file? In which case I suspect the counters
> > weren't collected. Are the counters available if you try to add them to the
> > system monitor?
> > If you are on a 64 bit server are you running the correct version of perfmon?
> >
> > John- Hide quoted text -
> >
> > - Show quoted text -
> Hi John,
> Yes, I am opening the log file. And yes they are available in Sys Mon.
> It's a virtualised 32bit server I believe (Intel Xeon 3.2GHz).
> As for perfmon version I'm just using the one in Administrative Tools.
> So, I definitely put the counters in the log. Why haven't they been
> recorded? I notice there is something about objects as well. I only
> put the individual counters in. Do I need to put the whole SQL Server
> object in?
> Cheers,
> James
If they are in the file it should be ok or the file is somehow corrupt. You
should not need to include anything other than the specific counters. Try
doing a new one with just one of the counters and see what happens.
You could try importing the file into SQL 2005 profiler it you have a
corresponding profile.
John|||On Jul 9, 12:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi James
>
>
> "JimLad" wrote:
> > On Jul 9, 1:28 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > > Hi James
> > > "JimLad" wrote:
> > > > Hi,
> > > > I set up a counter log to record SQL data in perfmon. This included a
> > > > few O/S counters and
> > > > SQL Server:Buffer Manager/Buffer Cache Hit Ratio
> > > > SQL Server:General Statistics/User Connections
> > > > I am now trying to look at the data through Perf Mon on the same
> > > > machine. However I can only add O/S counters to the view. The SQL
> > > > Server counters don't appear in the list.
> > > > Any ideas?
> > > > Cheers,
> > > > James
> > > I assume this is opening the log file? In which case I suspect the counters
> > > weren't collected. Are the counters available if you try to add them to the
> > > system monitor?
> > > If you are on a 64 bit server are you running the correct version of perfmon?
> > > John- Hide quoted text -
> > > - Show quoted text -
> > Hi John,
> > Yes, I am opening the log file. And yes they are available in Sys Mon.
> > It's a virtualised 32bit server I believe (Intel Xeon 3.2GHz).
> > As for perfmon version I'm just using the one in Administrative Tools.
> > So, I definitely put the counters in the log. Why haven't they been
> > recorded? I notice there is something about objects as well. I only
> > put the individual counters in. Do I need to put the whole SQL Server
> > object in?
> > Cheers,
> > James
> If they are in the file it should be ok or the file is somehow corrupt. You
> should not need to include anything other than the specific counters. Try
> doing a new one with just one of the counters and see what happens.
> You could try importing the file into SQL 2005 profiler it you have a
> corresponding profile.
> John- Hide quoted text -
> - Show quoted text -
James,
This just happened to me last week. In my case, I could see the all
counters except for SQL Server counters (Availible memory, buffer
etc). It was a permissions issue. You can "run as" and when I changed
to administrator of the box who has sa access, they appeared.
Hope this helps.
Kristina