Friday, March 30, 2012
Performance Tuning Class on SQL2005
which cover advanced performance tuning on SQL2005?
LitoI doubt such exists yet. You might want to check the Solid Quality Learning
class for SQL Server
2000 (see my signature). Vast majority applies for 2005 same way as for 2000
, where 2005 adds some
stuff to the picture.
Of course, if you already know your 2000 perf tuning stuff and are only look
ing for 2005 news, I
suggest you wait a while until things has matured.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lito D" <anonymous@.discussions.microsoft.com> wrote in message
news:136a01c5405d$16bb3170$a501280a@.phx.gbl...
> Does anyone know a class that concentrate or worth taking
> which cover advanced performance tuning on SQL2005?
> Lito
Performance Tuning Class on SQL2005
which cover advanced performance tuning on SQL2005?
Lito
I doubt such exists yet. You might want to check the Solid Quality Learning class for SQL Server
2000 (see my signature). Vast majority applies for 2005 same way as for 2000, where 2005 adds some
stuff to the picture.
Of course, if you already know your 2000 perf tuning stuff and are only looking for 2005 news, I
suggest you wait a while until things has matured.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lito D" <anonymous@.discussions.microsoft.com> wrote in message
news:136a01c5405d$16bb3170$a501280a@.phx.gbl...
> Does anyone know a class that concentrate or worth taking
> which cover advanced performance tuning on SQL2005?
> Lito
sql
Performance Tuning Class on SQL2005
which cover advanced performance tuning on SQL2005?
LitoI doubt such exists yet. You might want to check the Solid Quality Learning class for SQL Server
2000 (see my signature). Vast majority applies for 2005 same way as for 2000, where 2005 adds some
stuff to the picture.
Of course, if you already know your 2000 perf tuning stuff and are only looking for 2005 news, I
suggest you wait a while until things has matured.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lito D" <anonymous@.discussions.microsoft.com> wrote in message
news:136a01c5405d$16bb3170$a501280a@.phx.gbl...
> Does anyone know a class that concentrate or worth taking
> which cover advanced performance tuning on SQL2005?
> Lito
performance tuning as a DBA for ERP system
I have an ERP system running on SQL server 2000. How do i actually go abt
doing performance tuning as a DBA ?
i can check the no of ppl using the ERP and their activities. but come to
slow performance , how do i actually go abt doing performance tuning ?
for example , for this functional module of the ERP system i will not be
able to tell what stored procedures/queries that are being executed ? even if
i do know , i won't be able to change as that might cause further problem (do
i need to call the vendor support in this case ?) and my support agreement
will not cover any issues caused by changing the database ..
In the above scenario , how should a dba go abt perfoming a db performance
tunning ?
hope those experienced dbas can give me some ideas on how it works
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
You should be able to use profiler to watch for long running procs, or procs
which are doing a lot of reads or writes. These are candidates to be tuned.
Also watch for stored procedure recompilations, etc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:591855ccaf94c@.uwe...
> Hi,
> I have an ERP system running on SQL server 2000. How do i actually go abt
> doing performance tuning as a DBA ?
> i can check the no of ppl using the ERP and their activities. but come to
> slow performance , how do i actually go abt doing performance tuning ?
> for example , for this functional module of the ERP system i will not be
> able to tell what stored procedures/queries that are being executed ? even
> if
> i do know , i won't be able to change as that might cause further problem
> (do
> i need to call the vendor support in this case ?) and my support agreement
> will not cover any issues caused by changing the database ..
> In the above scenario , how should a dba go abt perfoming a db performance
> tunning ?
> hope those experienced dbas can give me some ideas on how it works
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Hi,
would you recommend using a 3rd party s/w to monitor the SPs/Queries being
acccessed as i read somewhere that profiler is cpu-intensive and might "hang"
the SQL Server if running for a long period of time ?
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>You should be able to use profiler to watch for long running procs, or procs
>which are doing a lot of reads or writes. These are candidates to be tuned.
>Also watch for stored procedure recompilations, etc.
>[quoted text clipped - 18 lines]
Message posted via http://www.droptable.com
Performance tuning and measure on MSSQL 2000
I am trying to design an IO subsystem for my SQL Server and for that I
need to try and predict IO activity on each table in my MSSQL
Database. My idea is to move the hottest tables into special disk
subsystem away from the less hotter tables. So far I have gathered
that we have three tables more hot than the others but I have no
feeling on ratio on how hot each is and how much activity is on the
less hotter tables. I need to predict how many disks I should assign
to each subsystem and so far...
I haven't found a reasonable way to do this.
The only way I found to see read/writes and physical read/writes is on
filelevel. but I've also managed to do a trace in sqlprofiler to get
the logical read and writes per query but since my queries are often
joins I have no way of spliting that IO between the tables included in
the join and no idea on which hit the buffer pool and which didn'nt.
Is there maybe a counter or some way that I have not found?
Any input would be greatly appriciated.
best regards & thanks
Arni Snorriarnie@.gormur.com (Arni Snorri Eggertsson) wrote in message news:<c8d15bfa.0404280125.6f1dadcf@.posting.google.com>...
> Hi
> I am trying to design an IO subsystem for my SQL Server and for that I
> need to try and predict IO activity on each table in my MSSQL
> Database. My idea is to move the hottest tables into special disk
> subsystem away from the less hotter tables. So far I have gathered
> that we have three tables more hot than the others but I have no
> feeling on ratio on how hot each is and how much activity is on the
> less hotter tables. I need to predict how many disks I should assign
> to each subsystem and so far...
> I haven't found a reasonable way to do this.
> The only way I found to see read/writes and physical read/writes is on
> filelevel. but I've also managed to do a trace in sqlprofiler to get
> the logical read and writes per query but since my queries are often
> joins I have no way of spliting that IO between the tables included in
> the join and no idea on which hit the buffer pool and which didn'nt.
> Is there maybe a counter or some way that I have not found?
> Any input would be greatly appriciated.
> best regards & thanks
> Arni Snorri
I'm not sure if it's possible to do exactly what you want - MSSQL will
probably cache a lot of the data from the 'hot' tables anyway, so the
issue is not so much the physical disk access as how much RAM you
have, and how well MSSQL uses the cache. There are a lot of
performance monitor counters for buffer and cache management you can
use to look at this.
As for the disks, I would start by identifying how much space is
required on disk, then try to use lots of smaller disks instead of
fewer bigger ones for the 'hot' filegroups. Placing the transaction
logs on separate disks would also help, of course.
Simon|||"Arni Snorri Eggertsson" <arnie@.gormur.com> wrote in message
news:c8d15bfa.0404280125.6f1dadcf@.posting.google.c om...
> Hi
> I am trying to design an IO subsystem for my SQL Server and for that I
> need to try and predict IO activity on each table in my MSSQL
> Database. My idea is to move the hottest tables into special disk
> subsystem away from the less hotter tables. So far I have gathered
> that we have three tables more hot than the others but I have no
> feeling on ratio on how hot each is and how much activity is on the
> less hotter tables. I need to predict how many disks I should assign
> to each subsystem and so far...
> I haven't found a reasonable way to do this.
If you don't have it, get the Microsoft Press book on SQL Server Performance
tuning. Lots of good help here.
> The only way I found to see read/writes and physical read/writes is on
> filelevel. but I've also managed to do a trace in sqlprofiler to get
> the logical read and writes per query but since my queries are often
> joins I have no way of spliting that IO between the tables included in
> the join and no idea on which hit the buffer pool and which didn'nt.
> Is there maybe a counter or some way that I have not found?
> Any input would be greatly appriciated.
> best regards & thanks
> Arni Snorri
Performance Tuning - MDX Query
Hi,
I have some performance troubles with queries of the type
MEDIAN(existing(DimensionX.Attributehierarchie.Attribute.Members),MeasureA/MeasureB)
[also min,max,stddev,variance, avg, etc....]
or similar with different functions... whereas this attriute has 50.000 to 100.000 Members and the related measures are of all types of aggregate functions (sum, count, distinct count, lastnonempty, ..)
While we have some of this measrues on the columns and analyze be an other hierarchy of the DimensionX on Rows it is incredible slow (up to minutes)
Do you have any ideas how to tune?
Best Regards, HANNES
It's hard to say... I assume you've done all the obvious things like partitioned your cube appropriately and made sure your queries are hitting aggregations?
Chris
|||As you said - partitiond by time ==> the queries are more or less on leave because the set is leave of this dimension - the lastnonempty forces the time dimension to leaves and therefore are only two other dimensions with possible aggregates...
The only thing I could think of is cache (Subcube) where I do a lot of this statements... Is cache already supported? - does it work at processing time only for the partition i want to cache?
Best Regards, HANNES
|||Well, it could be worth building the necessary aggregation (manually if that's the only way possible) even if there are only two other dimensions. The Cache statement isn't supported and I don't recommend you use it, but some form of cache warming could be useful because the results of your calculated members should be cached after the first time you've run your query.
What are you seeing in Profiler when you run a query? Are you seeing a bulk evaluation taking place, as Mosha describes in various blog entries (eg http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx)? Looking again at your original example, it might also be worth creating a hidden calculated measure defined as Measures.A/Measures.B, setting its Non_Empty_Behavior property as Mosha describes here:
http://sqlblog.com/blogs/mosha/archive/2007/03/06/averages-ratios-division-by-zero-and-non-empty-behavior.aspx
and then altering your existing expression to be Median(existing MyAttribute.Members, Measures.NewCalc) to see if that makes a difference.
Chris
|||Thanks for the input.
the hidden meausre - we used this approach already.
we have thought about some relational changes and redesign of the cube (to load more aggregated data) - maybe with this we could reach aceptable performance - we will see.
Thanks, HANNES
|||Can you give a full MDX query for exmaple?|||Thats an more representive example, but we are on the way to redesign the cube...
with member [Measures].[X] as [Measures].[Betriebskosten Original]/[Measures].[Immobilien Anzahl], visible=0, non_empty_behavior=[Measures].[Betriebskosten Original] member [Measures].[Median] asMedian(existing(Immobilien.Immobilie.Immobilie.Members),[Measures].[X]) select {[Measures].[X],[Measures].[Median]} oncolumns, { [Immobilien].[Geographie].[Alle Immobilien nach Geographie], [Immobilien].[Geographie].[Land].&[AUT], [Immobilien].[Geographie].[Land].&[AUT].children } onrows from [Angebotsanalyse] where [Zeit].[Zeit nach Monaten].[Monat].&[2007-02]The Backgrounds:
Betriebskosten Orginal: LastNonEmpty|||we have redesigned the cube to include additional measuregroups with the data relationaly pepared for month, quater, week and year level and useing on the measure groups the sum agg function. We do then a calc meausre dependent on the time level we use the appropriate physical member.
The cube become really complex because of this but we get out the results and performace we need.
HANNES
sqlPerformance Tuning - MDX Query
Hi,
I have some performance troubles with queries of the type
MEDIAN(existing(DimensionX.Attributehierarchie.Attribute.Members),MeasureA/MeasureB)
[also min,max,stddev,variance, avg, etc....]
or similar with different functions... whereas this attriute has 50.000 to 100.000 Members and the related measures are of all types of aggregate functions (sum, count, distinct count, lastnonempty, ..)
While we have some of this measrues on the columns and analyze be an other hierarchy of the DimensionX on Rows it is incredible slow (up to minutes)
Do you have any ideas how to tune?
Best Regards, HANNES
It's hard to say... I assume you've done all the obvious things like partitioned your cube appropriately and made sure your queries are hitting aggregations?
Chris
|||As you said - partitiond by time ==> the queries are more or less on leave because the set is leave of this dimension - the lastnonempty forces the time dimension to leaves and therefore are only two other dimensions with possible aggregates...
The only thing I could think of is cache (Subcube) where I do a lot of this statements... Is cache already supported? - does it work at processing time only for the partition i want to cache?
Best Regards, HANNES
|||Well, it could be worth building the necessary aggregation (manually if that's the only way possible) even if there are only two other dimensions. The Cache statement isn't supported and I don't recommend you use it, but some form of cache warming could be useful because the results of your calculated members should be cached after the first time you've run your query.
What are you seeing in Profiler when you run a query? Are you seeing a bulk evaluation taking place, as Mosha describes in various blog entries (eg http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx)? Looking again at your original example, it might also be worth creating a hidden calculated measure defined as Measures.A/Measures.B, setting its Non_Empty_Behavior property as Mosha describes here:
http://sqlblog.com/blogs/mosha/archive/2007/03/06/averages-ratios-division-by-zero-and-non-empty-behavior.aspx
and then altering your existing expression to be Median(existing MyAttribute.Members, Measures.NewCalc) to see if that makes a difference.
Chris
|||Thanks for the input.
the hidden meausre - we used this approach already.
we have thought about some relational changes and redesign of the cube (to load more aggregated data) - maybe with this we could reach aceptable performance - we will see.
Thanks, HANNES
|||Can you give a full MDX query for exmaple?|||Thats an more representive example, but we are on the way to redesign the cube...
with
member [Measures].[X] as [Measures].[Betriebskosten Original]/[Measures].[Immobilien Anzahl], visible=0, non_empty_behavior=[Measures].[Betriebskosten Original]
member [Measures].[Median] as Median(existing(Immobilien.Immobilie.Immobilie.Members),[Measures].[X])
select
{[Measures].[X],[Measures].[Median]} on columns,
{
[Immobilien].[Geographie].[Alle Immobilien nach Geographie],
[Immobilien].[Geographie].[Land].&[AUT],
[Immobilien].[Geographie].[Land].&[AUT].children
} on rows
from
[Angebotsanalyse]
where
[Zeit].[Zeit nach Monaten].[Monat].&[2007-02]
The Backgrounds:
Betriebskosten Orginal: LastNonEmpty|||
we have redesigned the cube to include additional measuregroups with the data relationaly pepared for month, quater, week and year level and useing on the measure groups the sum agg function. We do then a calc meausre dependent on the time level we use the appropriate physical member.
The cube become really complex because of this but we get out the results and performace we need.
HANNES