Cache Hit Ratio Issue

I have a customer using SQL Server 2000 Enterprise Edition on two clustered
servers (Windows 2003 SP1) with the following spec (times two):
4 x Intel Xeon Processors @. 2.7GHz
3.5GB RAM
36GB Local Hard Drives
175GB of SAN space for Data and log files
The database is around 2.5 GB.
I have a Cache Hit Ratio that is static at 70. I've never seen a Cache Hit
Ratio this low on any other customers let alone one that doesn't change.
It doesn't fluctuate at all. It has a minimum of 70, an average of 70, and
a maximum of 70 over a 24 hour period with more than 240 user connections.
This seems strange to me. Is there a setting that caps the cache hit ratio?
I would expect it to be > 90.
Any comments or suggestions would be great.Can you run this command and post it back here
DBCC MEMORYSTATUS
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:u9F8XcqqGHA.2068@.TK2MSFTNGP03.phx.gbl...
>I have a customer using SQL Server 2000 Enterprise Edition on two clustered
> servers (Windows 2003 SP1) with the following spec (times two):
> 4 x Intel Xeon Processors @. 2.7GHz
> 3.5GB RAM
> 36GB Local Hard Drives
> 175GB of SAN space for Data and log files
> The database is around 2.5 GB.
> I have a Cache Hit Ratio that is static at 70. I've never seen a Cache
> Hit
> Ratio this low on any other customers let alone one that doesn't change.
> It doesn't fluctuate at all. It has a minimum of 70, an average of 70,
> and
> a maximum of 70 over a 24 hour period with more than 240 user connections.
> This seems strange to me. Is there a setting that caps the cache hit
> ratio?
> I would expect it to be > 90.
> Any comments or suggestions would be great.
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uv0XKFsqGHA.928@.TK2MSFTNGP04.phx.gbl...
> Can you run this command and post it back here
> DBCC MEMORYSTATUS
> --
As requested...
Buffer Distribution Buffers
-- --
Stolen 5444
Free 936
Procedures 37946
Inram 0
Dirty 6306
Kept 0
I/O 0
Latched 700
Other 155172
Buffer Counts Buffers
-- --
Commited 206504
Target 206504
Hashed 162178
InternalReservation 1214
ExternalReservation 47
Min Free 536
Visible 206504
Procedure Cache Value
-- --
TotalProcs 10694
TotalPages 37946
InUsePages 14862
Dynamic Memory Manager Buffers
-- --
Stolen 43379
OS Reserved 976
OS Committed 954
OS In Use 948
General 4588
QueryPlan 37474
Optimizer 993
Utilities 17
Connection 995
Global Memory Objects Buffers
-- --
Resource 2398
Locks 135
XDES 73
SQLCache 1536
Replication 2
LockBytes 2
ServerGlobal 32
Query Memory Objects Value
-- --
Grants 1
Waiting 0
Available (Buffers) 139633
Maximum (Buffers) 139691
Optimization Queue Value
-- --
Optimizing 1
Waiting 0
Available 31
Maximum 32
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Thank you for your help so far.

Cache Dependency Problem

Hello.

I am having problems with SQL Cache dependency. I am using SQL 2005, ASP .net 2.0.

Every time i try to load data from cache, this is null. It acts like someone is constantly changing everything in the db.

Because of this my website makes hundreds of connections to the db instead of 5. This is a major issue that i cannot figure it out. PLEASE ADVISE.

On my local machine everything seems to work just fine. On the testing server the cache is always null.

Is cache dependency related

- to the platform used?

- to the number of IIS servers connected to the DB?

- to the sql user used?

Also a strange thing happens. When i change something in web.config the cache is working for about 1 min and after that it stops.

Here is the code i wrote:

public ManufacturerList GetAllManufacturers()
{
if (HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] != null)
return HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] as ManufacturerList;

SqlCacheDependency dep = new SqlCacheDependency(ConstantsManager.Instance.GetDefaultAsString("DB_NAME"), ConstantsManager.Instance.GetDefaultAsString("TABLE_MANUFACTURERS"));

_manufacturerList = new ManufacturerList();

DataReader reader = SqlHelper.ExecuteDataReader(WebContext.ConnectionString, CommandType.StoredProcedure, "dvx_web_MANUFACTURER_LoadAll", null);

while (reader.Read())
{
int manufacturerID = reader.GetInt("ID");

Manufacturer findManufacturer = _manufacturerList.FindByID(manufacturerID);

if (findManufacturer == null)
{
findManufacturer = new Manufacturer();
findManufacturer.LoadFromDataReader(reader);
_manufacturerList.Add(findManufacturer);
}
}

reader.Close();

HttpContext.Current.Cache.Insert(ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS"), _manufacturerList, dep);

return _manufacturerList;
}

Thanks a lot.

Hi,

Look at this article describing some ways of using SQL Dependency Cache.

http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html

Cache Database structure (How to detect if database-design has changed..)

Hello everyone,

I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.

I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.

I want to know if a column or table has changed any values, like datatype, name, properties, etc.

Any suggestions out there ?!Sure, use a DDL trigger. Anytime something changes that you need to know about insert a record into another table (tblDDLChanged?). Then use a SqlDependancy object on the tblDDLChanged table? Although you might be able to just use a set of SqlDependancy objects to monitor the base tables themselves, but I'm not sure if it detects DDL changes or not.|||I am not able to create any triggers only able to read the tables.|||After looking at the system tables I found the answer.

You retreive when last change was done to a column in a database by the following query:

SELECT TOP 1 sysobjects.refdate FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id ORDER BY sysobjects.refdate DESC

Cache calculated member

hi,

i have several calculated mebers in my cube that take a long time to be evaluated.
so i would like to evaluate the the calculation during processing my cube and store it directly in the cube.

So far i only found the "CACHE" statement that would fulfill my needs but i dont get it to work correctly

my calculated members looke like this;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Kaufpreis je Wohnflaeche m2 Max]
AS MAX({descendants([Immobilien].[Geographie].currentmember, [Immobilien].[Geographie].[Immobilie], self)},[Measures].[Kaufpreis]),
VISIBLE = 1;

the [Immobilien].[Geographie]-Hierachie looks like this:
Immobilien
Land
PLZ
Immobilien
Immobilie

what would the CACHE-Statement look like to cache this calculated member in the cube?
any other ideas how to solve this problem?

thanks,
Gerhard

Unfortunately Cache statement is not supported in AS2005.

Future versions of the product might give you ability to cache results of the calcualtions directly.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||thanks for your quick response

even though its not supported, i would like to test it out a bit

so what would the cache-statement look like for my calculated member?|||

Not supported, in part means I cannot encourage enyone using parts of the product that are not tested and might lead to unexpected results. Even for the test purposes. Sorry.

Closer to the release of the next version of the product you should be able to sign up for beta testing program to get to try new features.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Does anyone know if it will be supported in SP2? I would help us a lot too.

When is SP2 due, does it have a date yet?

Regards,

GJ

|||Not sure of the release date but a new December CTP is now available...|||CACHE statement is not supported in SP2 either. But by looking at your original question, the CACHE statement wouldn't have helped in this case anyway.|||

Is it going to be supported in the future?

Also i recall you once said ther were no known issues with it, but it was not supported becuase there was no time to have it tested enough. Is it in 2005 as an undocumented feature or has it been removed alltogether?

I tried cache (subcube) in a script, it doesn't throw an error but i couldn't find any canges in behaiviour either.

Regards GJ

Cache calculated member

hi,

i have several calculated mebers in my cube that take a long time to be evaluated.
so i would like to evaluate the the calculation during processing my cube and store it directly in the cube.

So far i only found the "CACHE" statement that would fulfill my needs but i dont get it to work correctly

my calculated members looke like this;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Kaufpreis je Wohnflaeche m2 Max]
AS MAX({descendants([Immobilien].[Geographie].currentmember, [Immobilien].[Geographie].[Immobilie], self)},[Measures].[Kaufpreis]),
VISIBLE = 1;

the [Immobilien].[Geographie]-Hierachie looks like this:
Immobilien
Land
PLZ
Immobilien
Immobilie

what would the CACHE-Statement look like to cache this calculated member in the cube?
any other ideas how to solve this problem?

thanks,
Gerhard

Unfortunately Cache statement is not supported in AS2005.

Future versions of the product might give you ability to cache results of the calcualtions directly.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||thanks for your quick response

even though its not supported, i would like to test it out a bit

so what would the cache-statement look like for my calculated member?|||

Not supported, in part means I cannot encourage enyone using parts of the product that are not tested and might lead to unexpected results. Even for the test purposes. Sorry.

Closer to the release of the next version of the product you should be able to sign up for beta testing program to get to try new features.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Does anyone know if it will be supported in SP2? I would help us a lot too.

When is SP2 due, does it have a date yet?

Regards,

GJ

|||Not sure of the release date but a new December CTP is now available...|||CACHE statement is not supported in SP2 either. But by looking at your original question, the CACHE statement wouldn't have helped in this case anyway.|||

Is it going to be supported in the future?

Also i recall you once said ther were no known issues with it, but it was not supported becuase there was no time to have it tested enough. Is it in 2005 as an undocumented feature or has it been removed alltogether?

I tried cache (subcube) in a script, it doesn't throw an error but i couldn't find any canges in behaiviour either.

Regards GJ

Cache a temporary copy of the report. How to set for multiple repo

Hi,
Is there a utility to set cache a temporary copy of the report for multiple
reports, instead of going to execution properties of each report? I have
close to a 100 reports to which I need to set this to expire after 2 hours
(120 min.).
Thanks,
Sam.You could use the RS.exe utility.
Create a script using the object model, and pass it in to the utility...
The object model is documented in Reporting Services Web Service Library In
Books on line.
There is also 2 samples which should be in the scripts subdirectory of your
RS install..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:B8254D24-B63A-4028-B91A-0B3F5C090DF4@.microsoft.com...
> Hi,
> Is there a utility to set cache a temporary copy of the report for
multiple
> reports, instead of going to execution properties of each report? I have
> close to a 100 reports to which I need to set this to expire after 2 hours
> (120 min.).
> Thanks,
> Sam.

cache

Sometimes we expect a report to run from cache and it doesnt. Scenario is we
subscribe to the report to run at 3 am and set execution to cache report and
expire at midnight. So this way when a user runs during the day, the report
is already cached
However I noticed in the executionlog table that when the report runs from a
subscription it runs as MHTML and when the user runs from browser it runs
from HTML 4.0 format and I think these differences in formats throw off the
cache execution status.
What is the difference in formats.
I also notice that sometimes a subscription executes once but the user gets
two emails. They try to export to excel and get a message that they do not
have sufficient permissions. My thought was that Installation of Microsoft
Publisher put Office in Read Only mode. We have had that issue with Office
Web Component in the past.
Assistance with these issues is appreciated
ThanksPlease answer my post
Thanks
"Will Byron" <WillB@.CEPSystems.com> wrote in message
news:%23G%23q9rBcEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Sometimes we expect a report to run from cache and it doesnt. Scenario is
we
> subscribe to the report to run at 3 am and set execution to cache report
and
> expire at midnight. So this way when a user runs during the day, the
report
> is already cached
> However I noticed in the executionlog table that when the report runs from
a
> subscription it runs as MHTML and when the user runs from browser it runs
> from HTML 4.0 format and I think these differences in formats throw off
the
> cache execution status.
> What is the difference in formats.
> I also notice that sometimes a subscription executes once but the user
gets
> two emails. They try to export to excel and get a message that they do not
> have sufficient permissions. My thought was that Installation of Microsoft
> Publisher put Office in Read Only mode. We have had that issue with Office
> Web Component in the past.
> Assistance with these issues is appreciated
> Thanks
>|||The format should not be a factor in the cachine decision, but different
parameters can be. The cache key is composed of the report and its
parameters. Are you running the report with the same parameters in the two
cases?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Will Byron" <will.byron@.maxqtech.com> wrote in message
news:OaIZOJOcEHA.1408@.TK2MSFTNGP12.phx.gbl...
> Please answer my post
> Thanks
> "Will Byron" <WillB@.CEPSystems.com> wrote in message
> news:%23G%23q9rBcEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Sometimes we expect a report to run from cache and it doesnt. Scenario
is
> we
> > subscribe to the report to run at 3 am and set execution to cache report
> and
> > expire at midnight. So this way when a user runs during the day, the
> report
> > is already cached
> >
> > However I noticed in the executionlog table that when the report runs
from
> a
> > subscription it runs as MHTML and when the user runs from browser it
runs
> > from HTML 4.0 format and I think these differences in formats throw off
> the
> > cache execution status.
> >
> > What is the difference in formats.
> >
> > I also notice that sometimes a subscription executes once but the user
> gets
> > two emails. They try to export to excel and get a message that they do
not
> > have sufficient permissions. My thought was that Installation of
Microsoft
> > Publisher put Office in Read Only mode. We have had that issue with
Office
> > Web Component in the past.
> > Assistance with these issues is appreciated
> > Thanks
> >
> >
>