Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?

Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?

historical lookup query

I'm having a dickins of a time with a particular query and am hoping
someone here can help me.
Using the following example;
declare @.SearchDate datetime
set @.SearchDate = '30 Nov 2005'
declare @.t1 table (t1id int, t1desc varchar(10))
insert into @.t1 (t1id, t1desc) values (1, 'Ed')
insert into @.t1 (t1id, t1desc) values (2, 'Bill')
insert into @.t1 (t1id, t1desc) values (3, 'Bob')
insert into @.t1 (t1id, t1desc) values (4, 'Fred')
insert into @.t1 (t1id, t1desc) values (5, 'John')
declare @.t1history table (t1id int, t1desc varchar(10), created
datetime)
insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
May 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
Oct 2005')
declare @.lookup table (val varchar(10))
insert into @.lookup (val) values ('Ben')
insert into @.lookup (val) values ('Frank')
insert into @.lookup (val) values ('Bill')
--example query
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
I want to filter the results returned from @.t1 against those contained
in @.lookup
I also need to be able to filter the results based on what the value
for t1desc could have been in the past using @.t1history and @.SearchDate
For example, with the date of '30 Nov 2005' I would expect the
following;
t1id t1desc t1deschistory
----
2 'Bill' null
5 'John' 'Ben'
Changing the date to '15 Oct 2005' I would expect;
t1id t1desc t1deschistory
----
2 'Bill' null
and changing it again to '15 Jan 2005' I would expect;
t1id t1desc t1deschistory
----
1 'Ed' 'Frank'
2 'Bill' null
What I basically want to do is this;
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
This gives the following error as expected;
Server: Msg 207, Level 16, State 3, Line 28
Invalid column name 't1deschistory'.
Moving the sub-query into a join doesn't work either;
select *
from @.t1 as Table1
left join (select top 1 * from @.t1history as t1history where
t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) Table1History on Table1.t1id = Table1History.t1Id
where Table1.t1desc in (select val from @.lookup) or
Table1History.t1desc in (select val from @.lookup)
This gives the following error;
Server: Msg 107, Level 16, State 2, Line 28
The column prefix 'Table1' does not match with a table name or alias
name used in the query.
Can anyone help?
Many thanks in advance,
Edone way: make it a derived table before applying the where e.g.
select * from (
select *,
(select top 1 t1desc
from @.t1history as Table1History
where Table1History.t1id = Table1.t1Id
and created <= @.SearchDate +1
order by created desc) as t1deschistory
from @.t1 as Table1
) x
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
ThievingScouser wrote:
> I'm having a dickins of a time with a particular query and am hoping
> someone here can help me.
> Using the following example;
> declare @.SearchDate datetime
> set @.SearchDate = '30 Nov 2005'
> declare @.t1 table (t1id int, t1desc varchar(10))
> insert into @.t1 (t1id, t1desc) values (1, 'Ed')
> insert into @.t1 (t1id, t1desc) values (2, 'Bill')
> insert into @.t1 (t1id, t1desc) values (3, 'Bob')
> insert into @.t1 (t1id, t1desc) values (4, 'Fred')
> insert into @.t1 (t1id, t1desc) values (5, 'John')
> declare @.t1history table (t1id int, t1desc varchar(10), created
> datetime)
> insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
> May 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
> Oct 2005')
> declare @.lookup table (val varchar(10))
> insert into @.lookup (val) values ('Ben')
> insert into @.lookup (val) values ('Frank')
> insert into @.lookup (val) values ('Bill')
> --example query
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
>
> I want to filter the results returned from @.t1 against those contained
> in @.lookup
> I also need to be able to filter the results based on what the value
> for t1desc could have been in the past using @.t1history and @.SearchDate
> For example, with the date of '30 Nov 2005' I would expect the
> following;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> 5 'John' 'Ben'
> Changing the date to '15 Oct 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> and changing it again to '15 Jan 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 1 'Ed' 'Frank'
> 2 'Bill' null
>
> What I basically want to do is this;
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
> where t1desc in (select val from @.lookup) or t1deschistory in (select
> val from @.lookup)
> This gives the following error as expected;
> Server: Msg 207, Level 16, State 3, Line 28
> Invalid column name 't1deschistory'.
> Moving the sub-query into a join doesn't work either;
> select *
> from @.t1 as Table1
> left join (select top 1 * from @.t1history as t1history where
> t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) Table1History on Table1.t1id = Table1History.t1Id
> where Table1.t1desc in (select val from @.lookup) or
> Table1History.t1desc in (select val from @.lookup)
> This gives the following error;
> Server: Msg 107, Level 16, State 2, Line 28
> The column prefix 'Table1' does not match with a table name or alias
> name used in the query.
>
> Can anyone help?
> Many thanks in advance,
> Ed
>

Higher level of fragmentation and data corruption.

CAN maintaining higher level of fragmentation result in data corruption over
time? OR they are totally unrelated.
I would like to know because rebuilding indexes take resources and that is
an issue for a 24 x 7 environment. In other words, should we keep data device
safely fragmented for sometime if we have some hard disk space available that
allows us to maintain some abnormal growth, allowing us not to rebuild
indexes?
Can someone with SQL Server Internal Data Storage Knowledge please reply?
Also, please let me know if my question is not very clear enough and you
need further explanation.
--
Regards,
MZeeshanFragmentation in and of itself should not cause data corruption. Most
corruption these days is due to hardware issues and not the database itself.
I don't get what you are stating about the extra space and abnormal growth.
Are you staying you have lots of page splits and that is causing your data
size to increase? If so you do not have the clustered index on the right
column or you need to adjust your fill factor.
--
Andrew J. Kelly SQL MVP
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
> over
> time? OR they are totally unrelated.
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
> device
> safely fragmented for sometime if we have some hard disk space available
> that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan|||Inline...
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
over
> time? OR they are totally unrelated.
>
Absolutely not - they are unrelated. Corruption is caused by hardware
problems (and highly-infrequent bugs in various layers of software and
firmware, obviously)
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
device
> safely fragmented for sometime if we have some hard disk space available
that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
I'm your man. What are you trying to achieve? Minimal space usage, minimal
perf drop from disk/index fragmentation? Are you correlating a perf drop
with increasing fragmentation, or are you just worried about it?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan

High usage of RAM on SQL2k

Hi

I have an sql server with very high usage. The web which is connected to the sql server has approx. 1000 unique visitors every time.
The database is optimized and the site goes really fast now.

But a problem we've had since the beginning is that SQL Server constantly uses 1.8 GB of RAM. Never less, never more.

Do you have any idea on how to decrease the usage of RAM. I am sure that this high usage is necessary.check your logs,if they are more truncate them,
may be it helps you.|||But arent the log files truncated during the backup?|||Logs?

run sp_configure and tell me what the results are for min server memory.|||min server memory (MB) 0 2147483647 0 0

High number of Page Faults

Svein Terje Gaup wrote:
> Hi, I have a SQL Server 2000 with 14 different databases. I have been
> monitoring it for some time, and it seems there is a high number of
> page faults occurring. The number is constantly above 3000. The server
> has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> The Pages/sec counter is stabilised at a very low level (mostly it is
> at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> dedicated SQL Server.
> So what could be causing the high amount of Page Faults?
> Sincerely
> Svein Terje Gaup
>
Do you have something copying files off of this machine, perhaps a tape
backup or something copy files across the network? I see this sort of
thing when copying files from server to server. The page faults occur
as the files are read from disk.Hi, I have a SQL Server 2000 with 14 different databases. I have been
monitoring it for some time, and it seems there is a high number of
page faults occurring. The number is constantly above 3000. The server
has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
The Pages/sec counter is stabilised at a very low level (mostly it is
at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
dedicated SQL Server.
So what could be causing the high amount of Page Faults?
Sincerely
Svein Terje Gaup|||Svein Terje Gaup wrote:
> Hi, I have a SQL Server 2000 with 14 different databases. I have been
> monitoring it for some time, and it seems there is a high number of
> page faults occurring. The number is constantly above 3000. The server
> has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> The Pages/sec counter is stabilised at a very low level (mostly it is
> at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> dedicated SQL Server.
> So what could be causing the high amount of Page Faults?
> Sincerely
> Svein Terje Gaup
>
Do you have something copying files off of this machine, perhaps a tape
backup or something copy files across the network? I see this sort of
thing when copying files from server to server. The page faults occur
as the files are read from disk.|||Thank you for your answer. I will try to look into it, and see if any
jobs are running that copies files from the server.
The Page Faults are constantly over 3000 during the whole working day.
Maybe there's some fileshare that is accessed constantly by users (it's
a customer's server, and they said it was dedicated as a sql server,
but who knows...) ? Anyway, I will get back with any results.
Sincerely
Svein Terje Gaup
Tracy McKibben wrote:
> Svein Terje Gaup wrote:
> Do you have something copying files off of this machine, perhaps a tape
> backup or something copy files across the network? I see this sort of
> thing when copying files from server to server. The page faults occur
> as the files are read from disk.|||Thank you for your answer. I will try to look into it, and see if any
jobs are running that copies files from the server.
The Page Faults are constantly over 3000 during the whole working day.
Maybe there's some fileshare that is accessed constantly by users (it's
a customer's server, and they said it was dedicated as a sql server,
but who knows...) ? Anyway, I will get back with any results.
Sincerely
Svein Terje Gaup
Tracy McKibben wrote:
> Svein Terje Gaup wrote:
> Do you have something copying files off of this machine, perhaps a tape
> backup or something copy files across the network? I see this sort of
> thing when copying files from server to server. The page faults occur
> as the files are read from disk.|||I'd try to find out what process(es) is primarily responsible for these page
faults. Go to the Processes tab on Task Manager, and rank the output by page
Faults or PF delta. It's common to have page faults in hundreds without any
performance issue. But if it's constantly around 3000, I'd conisder that hig
h
(just from the numbers I've seen on some of the busy servers in my
environments).
Linchi
"Svein Terje Gaup" wrote:

> Thank you for your answer. I will try to look into it, and see if any
> jobs are running that copies files from the server.
> The Page Faults are constantly over 3000 during the whole working day.
> Maybe there's some fileshare that is accessed constantly by users (it's
> a customer's server, and they said it was dedicated as a sql server,
> but who knows...) ? Anyway, I will get back with any results.
> Sincerely
> Svein Terje Gaup
>
> Tracy McKibben wrote:
>|||I'd try to find out what process(es) is primarily responsible for these page
faults. Go to the Processes tab on Task Manager, and rank the output by page
Faults or PF delta. It's common to have page faults in hundreds without any
performance issue. But if it's constantly around 3000, I'd conisder that hig
h
(just from the numbers I've seen on some of the busy servers in my
environments).
Linchi
"Svein Terje Gaup" wrote:

> Thank you for your answer. I will try to look into it, and see if any
> jobs are running that copies files from the server.
> The Page Faults are constantly over 3000 during the whole working day.
> Maybe there's some fileshare that is accessed constantly by users (it's
> a customer's server, and they said it was dedicated as a sql server,
> but who knows...) ? Anyway, I will get back with any results.
> Sincerely
> Svein Terje Gaup
>
> Tracy McKibben wrote:
>|||Thank you. Thats a good idea. I'll try it next time I'm on-site.
Sincerely
Svein Terje Gaup
Linchi Shea wrote:[vbcol=seagreen]
> I'd try to find out what process(es) is primarily responsible for these pa
ge
> faults. Go to the Processes tab on Task Manager, and rank the output by pa
ge
> Faults or PF delta. It's common to have page faults in hundreds without an
y
> performance issue. But if it's constantly around 3000, I'd conisder that h
igh
> (just from the numbers I've seen on some of the busy servers in my
> environments).
> Linchi
> "Svein Terje Gaup" wrote:
>

High number of Page Faults

Hi, I have a SQL Server 2000 with 14 different databases. I have been
monitoring it for some time, and it seems there is a high number of
page faults occurring. The number is constantly above 3000. The server
has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
The Pages/sec counter is stabilised at a very low level (mostly it is
at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
dedicated SQL Server.
So what could be causing the high amount of Page Faults?
Sincerely
Svein Terje GaupSvein Terje Gaup wrote:
> Hi, I have a SQL Server 2000 with 14 different databases. I have been
> monitoring it for some time, and it seems there is a high number of
> page faults occurring. The number is constantly above 3000. The server
> has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> The Pages/sec counter is stabilised at a very low level (mostly it is
> at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> dedicated SQL Server.
> So what could be causing the high amount of Page Faults?
> Sincerely
> Svein Terje Gaup
>
Do you have something copying files off of this machine, perhaps a tape
backup or something copy files across the network? I see this sort of
thing when copying files from server to server. The page faults occur
as the files are read from disk.|||Thank you for your answer. I will try to look into it, and see if any
jobs are running that copies files from the server.
The Page Faults are constantly over 3000 during the whole working day.
Maybe there's some fileshare that is accessed constantly by users (it's
a customer's server, and they said it was dedicated as a sql server,
but who knows...) ? Anyway, I will get back with any results.
Sincerely
Svein Terje Gaup
Tracy McKibben wrote:
> Svein Terje Gaup wrote:
> > Hi, I have a SQL Server 2000 with 14 different databases. I have been
> > monitoring it for some time, and it seems there is a high number of
> > page faults occurring. The number is constantly above 3000. The server
> > has 3 Gigabytes of RAM, and there's about 600+ megs free at all times.
> > The Pages/sec counter is stabilised at a very low level (mostly it is
> > at 0). The SQL Server Cache hit ratio is mostly at 99%. The server is a
> > dedicated SQL Server.
> >
> > So what could be causing the high amount of Page Faults?
> >
> > Sincerely
> > Svein Terje Gaup
> >
> Do you have something copying files off of this machine, perhaps a tape
> backup or something copy files across the network? I see this sort of
> thing when copying files from server to server. The page faults occur
> as the files are read from disk.

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
Message posted via http://www.sqlmonster.com
You probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQLMonster.c om...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.sqlmonster.com

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
--
Message posted via http://www.sqlmonster.comYou probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQLMonster.com...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.sqlmonster.com

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
Message posted via http://www.droptable.comYou probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via droptable.com" <forum@.droptable.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQ
droptable.com...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.droptable.comsql

High CPU time per user as soon as they connect?

On a couple of our servers, users are currently scoring very
high on "CPU Time" as reported by Enterprise Manager and by
sp_who2, both as soon as they log in, and afterwards -
5,000,000 at login and 47,000,000 later on are the top figures.
Most of the fleet is on SQL Server 2000 Service Pack 2, with
between 2 and 8 hard-working processors. The hardest working
server is on Service Pack 3(a), clustered, and its top 5
current values in CPU time are between 382,847 and 705,879.
The servers are all on clients' sites and networks, with client's
choice of firewall. Usual application is a Java interface using
jTDS, but CPU time is also reported high for a new Query Analyzer
connection. SQL Server, not Windows, accounts are most often used.
Clients aren't complaining of poor performance, apparently, but
maybe they already gave up phoning for that.
So, should we worry? Reboot? Reindex? Patch? Disinfect?
Or ask around, "Who left SQL Profiler running"...i would run profiler to find out whats going on.
i would also run perfmon to find out what average cpu is
over the entire day
otherwise, you have nothing from which to make reasonable
assessments
>--Original Message--
>On a couple of our servers, users are currently scoring
very
>high on "CPU Time" as reported by Enterprise Manager and
by
>sp_who2, both as soon as they log in, and afterwards -
>5,000,000 at login and 47,000,000 later on are the top
figures.
>Most of the fleet is on SQL Server 2000 Service Pack 2,
with
>between 2 and 8 hard-working processors. The hardest
working
>server is on Service Pack 3(a), clustered, and its top 5
>current values in CPU time are between 382,847 and
705,879.
>The servers are all on clients' sites and networks, with
client's
>choice of firewall. Usual application is a Java
interface using
>jTDS, but CPU time is also reported high for a new Query
Analyzer
>connection. SQL Server, not Windows, accounts are most
often used.
>Clients aren't complaining of poor performance,
apparently, but
>maybe they already gave up phoning for that.
>So, should we worry? Reboot? Reindex? Patch?
Disinfect?
>Or ask around, "Who left SQL Profiler running"...
>.
>

high CPU time and sql 2000 getting slow performance

Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVThe first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle a
n
increasing load.
Let me know if this helps..
"PV" wrote:

> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and highe
r,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after som
e
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statement
s
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly
.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having sai
d
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, i
t
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could g
et
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS tak
es
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle
an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
>|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:

>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher
,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV

high CPU time and sql 2000 getting slow performance

Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVIt sounds to me that the server is overloaded.
Normally if it is a live environment, you have a dedicated server just
for MSSQL.
Normally it is not advisable to change the default memory setting. For
optimum performance, SQL Server should be allowed to take as much as
RAM as it wants for its own use without having to compete for RAM with
other applications.
You can turn on some performance monitors to see how busy your system
is and justify to the owner of the machine it is time to upgrade the
hardware.
Here are some monitors you can put on (for memory only):
Memory Object: Pages/Sec
Memory Object: Available Bytes
SQLServer: Memory Manager: Total Server Memory
SQLServer: Memory Manager: Target Server Memory
Alternatively, you can turn on SQL Profiler if you are more interested
what MSSQL doing all day longs. It has CPU, I/O and memory eventlog
you can trace.
Mel|||The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
> > Hi,
> >
> > I admin a server, dual 3,6 xeon with 4GB ram.
> >
> > Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> >
> > The server have around 800 dynamic sites.
> >
> > SQL 2000 have 1Gb memory for it.
> >
> >
> > the problem is:
> > the CPU time after a SQL restart of service keeps getting higher and higher,
> > i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> > wich is the reserved memory for SQL.
> >
> > Is it normal the CPU time keeping getting higher? or should refresh? like
> > dllhost.exe in the webserver.
> >
> > Is it normal the memory stays at 1Gb or also should refresh?
> >
> > The problem is with some hours/days sites start to open slow and when i
> > restart the SQL service, sites start to open in few seconds, but after some
> > day/days start to be slow again.
> >
> > Its a very busy server with some portals, a few, others sites dynamic also
> > but with few hits. But almost 100% of sites uses, except the portals, read
> > for one of the portals database.
> >
> > Im not the programmer of the sites, and also i dont make the SQL statements
> > need for each site, or design the databases.
> >
> > Tkx in advance,
> > PV|||You will need to use SQL Profiler to capture the CPU and memory usage.
As the monitor will also cause some system resources, so you normally
turn it on for a period of time that you think it is a typical business
workload.
After you have the trace, you can either just open in Profiler or even
better import into as a trace table (built-in feature of Profiler -
Save as trace table). From the trace you can then see what processes
have highest CPU/memory usage, using select sql.
As you quoted another server running just fine, it could well be some
poorly written sql scripts/stored procedures are running on the server.
With the trace, you can then identify the root of the problem (if
any). E.g. a stored procedure that keep re-compile for no good reason,
session no closed down properly.
Mel|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV

high CPU time and sql 2000 getting slow performance

Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PV
The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:

> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV
|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:

High CPU over extended time period

Hello,
We are running SQL 2000 sp4 with full text search, recently we are
getting 100% CPU over extended period of time and the only way to
resolve this is by killing the Mssearch.exe process. (we tried to
restart the service but it hang) the Microsoft search service is
running under local system account.
our system topology is active/passive cluster with NAS disks, 4GB RAM
and 4 CPU for each server.
May be some one can help me?
The issue happen randomly with no specific job running at that time or
any thing, and when it happen the SQL queries are running very very
slow.
Thank you
Orna Kahil
Orna,
Does the CPU usage drop at some point or does it stay high at all or most
times? If the latter, please re-confirm that the MSSearch service is using
the "system account" (LocalSystem) and if not, change it and restart the
MSSearch service.
If the former, you can set the MSSearch service's to use one CPU and then
use sp_configure to set the affinity of SQL Server to that cpu to avoid cpu
contention during heavy MSSearch CPU usage via the following method - launch
the Tskmgr.exe from the AT command on the multi-proc server where sql server
resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"or" <orna@.panam.co.il> wrote in message
news:1131446707.073884.89790@.f14g2000cwb.googlegro ups.com...
> Hello,
> We are running SQL 2000 sp4 with full text search, recently we are
> getting 100% CPU over extended period of time and the only way to
> resolve this is by killing the Mssearch.exe process. (we tried to
> restart the service but it hang) the Microsoft search service is
> running under local system account.
> our system topology is active/passive cluster with NAS disks, 4GB RAM
> and 4 CPU for each server.
> May be some one can help me?
> The issue happen randomly with no specific job running at that time or
> any thing, and when it happen the SQL queries are running very very
> slow.
> Thank you
> Orna Kahil
>
|||Hello John and thank you for your quick response.
The CPU usage does not drop it stack on 100%. and do not release till
we kill the process.
we can change the configuration so the MSsearch will use only 1 CPU but
I think the problem is different since it hang in the same position and
just using lots of CPU usage.
The MSSearch service is using the "system account" (LocalSystem). I
looked at the Resources you point at is there any thing specific there?
I forgot to mention that it all worked fine and suddenly on day (we did
not change a thing) it started to happen.
|||You're welcome, Orna,
What is the exact version of SQL Server that you are using? Could you post
the full output of SELECT @.@.version ?
Depending upon your answer, you may need to upgrade to a new/higher Service
Pack (SP) level to get a fix for a possible known bug related to MSSearch &
high CPU usage. The @.@.version info will provide that information.
Additionally, please review your server's Application event log for
"Microsoft Search" and MssCi source events. Especially, the latter (MssCi)
for ALL informational, warnings and error messages as this source event may
indicate what the true problem is and hopefully provide more detail.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"or" <orna@.panam.co.il> wrote in message
news:1131523025.784794.210480@.g44g2000cwa.googlegr oups.com...
> Hello John and thank you for your quick response.
> The CPU usage does not drop it stack on 100%. and do not release till
> we kill the process.
> we can change the configuration so the MSsearch will use only 1 CPU but
> I think the problem is different since it hang in the same position and
> just using lots of CPU usage.
> The MSSearch service is using the "system account" (LocalSystem). I
> looked at the Resources you point at is there any thing specific there?
> I forgot to mention that it all worked fine and suddenly on day (we did
> not change a thing) it started to happen.
>
|||Hello John,
Sorry I did not reply sooner .
The data from the SQL @.@.version is:
"Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
"
and there are no error from the MssCi only information about merging
once a day.
from the Microsoft search we get all sort of this:"Error: Unable to
complete the requested operation because of either a catastrophic media
failure or a data structure corruption on the disk" and some of "An
error occurred in the resource status monitor for instance <SQL Server
Fulltext>: 800706ba - The RPC server is unavailable. "
The SQL drive is located on NAS disks and recently we have problem with
the Snapshot drive (low disk space) the Snapshot drive is separated
from the SQL drive (S:/ sql and I:/ snapshot)
do you think there is any thing to do with the Mssearch problem?
|||No problem, Or,
The errors "Unable to complete the requested operation because of either a
catastrophic media failure or a data structure corruption on the disk" and
"An error occurred in the resource status monitor for instance <SQL Server>
Fulltext>: 800706ba - The RPC server is unavailable" indicate some serious
problem with the Full Text Catalog.
Is the FT Catalog located on the "Snapshot"drive that you are having low
disk space problems? If so, then either free up disk space on this drive or
drop and re-locate the FT Catalog to another local disk drive, but separate
from your SQL drive (S:\ or I:\) as the MSSearch service requires at least
15% of free disk space be available at all times. Additionally, the above
errors indicate serious problems with the existing FT Catalog, so even if
the FT Catalog is not on the "Snapshot" drive, I'd still recommend dropping
and recreating it.
I'd also recommend that you review the FT Deployment white paper and other
FTS related Kb articles at "SQL Server 2000 Full-Text Search Resources and
Links"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"or" <orna@.panam.co.il> wrote in message
news:1131874652.316687.171850@.g14g2000cwa.googlegr oups.com...
> Hello John,
> Sorry I did not reply sooner .
> The data from the SQL @.@.version is:
> "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
> "
> and there are no error from the MssCi only information about merging
> once a day.
> from the Microsoft search we get all sort of this:"Error: Unable to
> complete the requested operation because of either a catastrophic media
> failure or a data structure corruption on the disk" and some of "An
> error occurred in the resource status monitor for instance <SQL Server
> Fulltext>: 800706ba - The RPC server is unavailable. "
> The SQL drive is located on NAS disks and recently we have problem with
> the Snapshot drive (low disk space) the Snapshot drive is separated
> from the SQL drive (S:/ sql and I:/ snapshot)
> do you think there is any thing to do with the Mssearch problem?
>
|||Hi John and Thanks again,
The Catalogs located in the SQL drive which mean there is no space
problem, But I think we will follow your advise and rebuild the
catalog, however this will cause down time to our site so maybe you can
help me with estimating the demage.
We have catalog of about 57,000 rows and each row is 33K (max). how
long will it take to rebuild this catalog?
Thanks
Orna
|||You're welcome, Orna,
Depending upon your server's configuration and if you have the FT Catalog
located on a separate drive from the database files, it should take between
1 and 1.5 hours to run to completion...
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"or" <orna@.panam.co.il> wrote in message
news:1131966255.893904.253210@.o13g2000cwo.googlegr oups.com...
> Hi John and Thanks again,
> The Catalogs located in the SQL drive which mean there is no space
> problem, But I think we will follow your advise and rebuild the
> catalog, however this will cause down time to our site so maybe you can
> help me with estimating the demage.
> We have catalog of about 57,000 rows and each row is 33K (max). how
> long will it take to rebuild this catalog?
> Thanks
> Orna
>
|||Thank you John for all your help we will rebuild the catalogs and
continue increase our knowledge via the KB you pointed at.
appreciate it
Orna Kahil
|||Hello John,
Well after 3 weeks of quite it happen again, I will summaries our actions
1. Rebuild all catalogs
2. Changed population mode to be change tracking with background index
3. Separate the resources SQL is working on 1 CPU and Mssearch on the other.
4. Fixed the Netapp configuration
5. Looked for errors in all the available logs
And nothing helped suddenly one day , again nothing was done by us the CPU
stabilized and was low this situation continued for aprox 3 week and on this
Sunday again it went high to 100% it did not harm the site functioning
because it is on different CPU but it require manual interference .
the occurrences are not on the same time each day. and we have no claw what
is going on here do you have any suggestion
Thank you
Orna
"John Kane" wrote:

> You're welcome, Orna,
> Depending upon your server's configuration and if you have the FT Catalog
> located on a separate drive from the database files, it should take between
> 1 and 1.5 hours to run to completion...
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "or" <orna@.panam.co.il> wrote in message
> news:1131966255.893904.253210@.o13g2000cwo.googlegr oups.com...
>
>

High %privileged and interrupt time

Had a high CPU on my SQL Servers and on further investigation, it appears
that the % privileged and interrupt time were high and not user time.
What should I account for such behavior especially if it occurs on many SQL
servers at the same time ?
Cannot find anything odd in the event or SQL Logs.Hi
"Hassan" wrote:

> Had a high CPU on my SQL Servers and on further investigation, it appears
> that the % privileged and interrupt time were high and not user time.
> What should I account for such behavior especially if it occurs on many SQ
L
> servers at the same time ?
> Cannot find anything odd in the event or SQL Logs.
You can also look at interupts/sec. If you are getting excessive number of
interupts it may be a disc issue so also look at Physical Disk counters.
John

High %privileged and interrupt time

Had a high CPU on my SQL Servers and on further investigation, it appears
that the % privileged and interrupt time were high and not user time.
What should I account for such behavior especially if it occurs on many SQL
servers at the same time ?
Cannot find anything odd in the event or SQL Logs.
Hi
"Hassan" wrote:

> Had a high CPU on my SQL Servers and on further investigation, it appears
> that the % privileged and interrupt time were high and not user time.
> What should I account for such behavior especially if it occurs on many SQL
> servers at the same time ?
> Cannot find anything odd in the event or SQL Logs.
You can also look at interupts/sec. If you are getting excessive number of
interupts it may be a disc issue so also look at Physical Disk counters.
John

Hierarchy Problem after processing - Help!

Hi,

I'm using AS2005 and have time dimension with hierarchy as follows:

[yr_dt] --> [yr] --> [hyr] --> [qtr] --> [mon] --> [dt]

Normally, it show full hierarchy when I browse the hierarchy :
e.g. 2006 --> 1 half --> 1 quarter --> Jan --> 2006-01-01T00:00:00

However, after I change the data source view to include more data in specified data range and reprocess the cube, The hierarchy only shows [yr] nothing under [yr].
Only correct year is the last year (i.e. 2006). It show full hierarchy as above.

May I know what's wrong with AS2005. Or is it a bug ? Any method to solve ?
Thanks in advanceHi,

my time dimension has the following relationship:

[yr]
|-- [hyr]
cardinality : many
relationship type: rigid

[hyr]

|-- [qtr]

cardinality : many

relationship type: rigid

[mon]

|-- [qtr]

cardinality : many

relationship type: rigid

[dt]

|-- [mon]

cardinality : many

relationship type: rigid

When I remove all the above relationship and reprocess the cube. It works fine and
the full hierarchy show correctly.

May I know what's wrong with the above hierarchy relationship setting ?
thanks a lot .|||

Are your attributes unique?

i.e.

Year -> 2007

Quarters

Q1, 2007

Q2, 2007

Q3, 2007

Month

January, 2007

February, 2007

Day

1/1/2007

1/2/2007

etc

If they are not unique (i.e. A must imply B in your relationships, you can't have just January since January does not imply January in a specific year) then the aggregations don't work properly.

|||Hi mccpres,

thanks for your reply.
Is that you mean the KeyColumn unique ? or the NameColumn Unique ?

My settings for [Mon] attribute
NameColumn : abbreviation of month (i.e. Jan, Feb ...etc)
KeyColumn : integer value of month (i.e. 1 to 12)

My settings for [Qtr] attribute

NameColumn : 'Q'mm + yyyy (i.e. Q1 2002, Q2 2002 ....etc)
KeyColumn : integer value of year (i.e. 2002, 2003 etc)

Here, NameColumn is unique to corresponding year. But the KeyColumn is not.
So, seems make the NameColumn unique would solve the issue.

Also, there's ValueColumn properties for each attribute. It seems that the ValueColumn should be same as KeyColumn, right ? Then why we need ValueColumn ?

Above maybe just simple questions, but just want to clarify my understanding as I new to ssas. Thanks a lot !|||hi mccpres,

thanks for your reply.
Is that you mean Keycolumn unique or NameColumn unique ?

I change my setting for [qtr] attribute:
NameColumn: 'Q'mm+yyyy (ie. Q1 2002, Q2 2002 ...etc)
KeyColumn: integer value of quarter (i.e. 1, 2, 3, 4 )

for [yr] attribute:
NameColumn : 'Y'+yyyy (ie Y2002, Y2003 ...etc)
KeyColumn: integer value of year (ie 2002, 2003 ...etc)

Here NameColumn for [qtr] is unique to corresponding NameColumn of [yr]. But the KeyColumn is not.
I tried the above settings and seems make the NameColumn unique would solve the issue. right?

Also, is that we should set KeyColumn same as the ValueColumn ?

Above maybe just simple questions, appreciate if anyone can help as I'm new to ssas. thanks!|||Hi,

anyone can help ?

Hierarchy Member MDX Name

I created a 'Budgeting Time' hierarchy from a dimension table that looks like:

IDYearMonth08C5DC29-6E61-4625-BA20-CFDFE5D3232D2006Jan

KeyColumns is binded to Year/Month and so is NameColumn. The MDX representation of the year 2006 is now [Budgeting Time].[Budgeting Time].[Year].&[2006] but is there anyway to have the ID as the member identifier so the MDX looks like [Budgeting Time].[Budgeting Time].[Year].&[08C5DC29-6E61-4625-BA20-CFDFE5D3232D]?

If your entity can equally be uniquely identified either by ID or { Year, Month } pair you can choose ID to be the key column of your attribute. MDX code refers to unique names, which often use the key columns of attributes. If you choose ID to be the key column of the attribute then you will have the desired representation of the unique name.

When you browse your dimension the user interface will show captions, which will be resolved through the NameColumn binding. I do not know how you succeeded to bind Name to Year/Month since NameColumn can only be one and not a collection like KeyColumns. Probably you created a calculated column on DSV and provided a formula joining the name of the month and year together. This can stay if you choose ID to be the key column of the attribute.

I am not sure about your goal to have specific unique names, but key columns are chosen by the relationships with a fact table or a dimension table. If we are talking about the granularity attribute then it will participate in some relationship with either a fact table (regular relationship) or another dimension table (referenced relationship). If it is ID, which participates in that relationship then you would better choose ID for the key column. If your fact table or referenced dimension table contains Year/Month pair then you would better choose Year/Month to be the key columns.

Hierarchy Member MDX Name

I created a 'Budgeting Time' hierarchy from a dimension table that looks like:

ID

Year

Month

08C5DC29-6E61-4625-BA20-CFDFE5D3232D

2006

Jan

KeyColumns is binded to Year/Month and so is NameColumn. The MDX representation of the year 2006 is now [Budgeting Time].[Budgeting Time].[Year].&[2006] but is there anyway to have the ID as the member identifier so the MDX looks like [Budgeting Time].[Budgeting Time].[Year].&[08C5DC29-6E61-4625-BA20-CFDFE5D3232D]?

If your

entity can equally be uniquely identified either by ID or { Year, Month } pair

you can choose ID to be the key column of your attribute. MDX code refers to

unique names, which often use the key columns of attributes. If you choose ID to

be the key column of the attribute then you will have the desired

representation of the unique name.

When you

browse your dimension the user interface will show captions, which will be

resolved through the NameColumn binding. I do not know how you succeeded to

bind Name to Year/Month since NameColumn can only be one and not a collection

like KeyColumns. Probably you created a calculated column on DSV and provided a

formula joining the name of the month and year together. This can stay if you

choose ID to be the key column of the attribute.

I am not

sure about your goal to have specific unique names, but key columns are chosen

by the relationships with a fact table or a dimension table. If we are talking

about the granularity attribute then it will participate in some relationship

with either a fact table (regular relationship) or another dimension table

(referenced relationship). If it is ID, which participates in that relationship

then you would better choose ID for the key column. If your fact table or

referenced dimension table contains Year/Month pair then you would better

choose Year/Month to be the key columns.