Caching Set Up Problem

Hi Expert,

 Am I doing it right? Can you give me any suggestions please? Thank you!!
I followed the way for setting up the sql cache dependency fromhttp://quickstarts.asp.net/QuickStartv20/aspnet/doc/caching/SQLInvalidation.aspx.
However, I still have the following error:

When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.

IssueSummary.aspx
<%@.PageLanguage="VB"MasterPageFile="~/MasterPage.master"AutoEventWireup="true"CodeFile="IssueSummary.aspx.vb"Inherits="IssueSummary"title="Issue Summary" %>
<%@.RegisterAssembly="AtlasControlToolkit"Namespace="AtlasControlToolkit"TagPrefix="cc2" %>
<%@.RegisterAssembly="Microsoft.Web.Atlas"Namespace="Microsoft.Web.UI"TagPrefix="cc1" %>
<%@.OutputCacheDuration="999999"VaryByParam="None"SqlDependency="CommandNotification" %>

Global.asax
Sub Application_Start(ByVal senderAsObject,ByVal eAs EventArgs)
Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("ICTConnectionString").ConnectionString
Dim needToInstallAsBoolean =True
Try
Dim tables()AsString
tables = SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString)
If (Not tablesIsNothing)Then
Dim tblAsString
ForEach tblIn tables
If (tbl.ToLower().Equals("IssueDetails"))Then
needToInstall =False
EndIf
Next
EndIf
Catch exAs Exception
needToInstall =True
EndTry
If (needToInstall)Then
SqlCacheDependencyAdmin.EnableNotifications(connectionString)
SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString,"IssueDetails")
EndIf
EndSub

Web.config
<connectionStrings>
<addname="ICTConnectionString"connectionString="Data Source=jamesle-3;Initial Catalog=IssueCommunicationTool;Integrated Security=True"providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependencyenabled="true"pollTime="1000" >
<databases>
<addname="ICTDB"connectionStringName="ICTConnectionString" />
</databases>
</sqlCacheDependency>
</caching>

SQL Server 2005 - Database - IssueCommunicationTool
SELECT is_broker_enabledFROMsys.databasesWHEREname='IssueCommunicationTool'

Return = 1, it means the broker service is enabled. Also, the database has created the table ASPNET_SQLCacheTableForChangeNotification,
the trigger in IssueDetails table, and some other store procedures.

To use that the notification service nees to be enable, does not look like it is in your configuration.|||

Just thought that if loading them dynamicly you may want just to add the user control in the code behind

Cache["Myusercontrol"] = usercontrolname;

grab them

if ( Cache["Myusercontrol"] != null )

{

usercontrolname = (UserControl) Cache["Myusercontrol"];

}

|||

You are messing the both ways for SQL caching - polling and notifications. Polling is for both 2000 and 2005 version, whether notifications is only for 2005, because of the Service Broker.

In your case you have been usingSqlDependency="CommandNotification" in the outputcache directive. This means that you are using notification based invalidation. In such case you should call SqlDependency.Start(connectionString) in the Application_OnStart method (Global.asax file). In such case you did not need any settings in the web.config file. You need then only if you use polling based invalidation. This is all written down on the page that you point. Just read it more carefully.

Caching problem?

If I create a view and then a mining structure run it everything works fine BUT if I then alter the view (e.g change a filter setting in a named query) or the actual contents of the database, the OLD contents persist.

Using the view refresh says there are no changes (which is correct as I don't change any column names etc.). If I remove Keep Training Cases, this fixes it, but for Time Series, it then gives an error, so you have to go back and re-select it... is there a direct option to clear caches?

The dependency settings aren't sensitive to non-destructive changes in the DSV. You can simply do a ProcessFull on the mining structure to reprocess from the data.

There is an option ProcessClearStructureOnly (something like that), that clears the training cases from the cache.

TimeSeries requires the cache or the viewer will not work - you can likely drop the cache using the process method above, but you will get errors when trying to view the time series models (at least the chart part)

Caching of linked server data

Hi
I'm currently setting up a SQL Server 2000. My configuration is:
Instance MyCompany running on ServerA
Instance TheirCompany running on ServerB
TheirCompany is mapped as linked server into instance MyCompany
In my situation, the data in TheirCompany is only located on ServerB due to
legal restrictions. All data access happens through ServerA. Therefore, it
would be favourable if ServerA would keep the data of TheirCompany in its
cache and not query the remote server for every SELECT.
Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
not volatile, from cache or something).
Are there any other suggestions on how to optimize performance? I'm aware of
the query plans for joins on remote tables, I'm much more looking for a way
to keep the SQL Server from fetching the data every time, as it won't have
changed FOR SURE!
Thanks in advance
Simon
posted to: microsoft.public.sqlserver.setup;
microsoft.public.sqlserver.server; microsoft.public.sqlserver.clusteringSorry, but there is no way to cache remote server data on a local server...
If you wish the data to be local you'd have to make a copy in a local
database...
You might consider making a copy in tempdb, and sharing that among users. It
will automatically be deleted when the Server is re-booted.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Simon Niederberger" <dontreply@.hotmail.com> wrote in message
news:Ojd11uL8DHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm currently setting up a SQL Server 2000. My configuration is:
> Instance MyCompany running on ServerA
> Instance TheirCompany running on ServerB
> TheirCompany is mapped as linked server into instance MyCompany
> In my situation, the data in TheirCompany is only located on ServerB due
to
> legal restrictions. All data access happens through ServerA. Therefore, it
> would be favourable if ServerA would keep the data of TheirCompany in its
> cache and not query the remote server for every SELECT.
> Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
> not volatile, from cache or something).
> Are there any other suggestions on how to optimize performance? I'm aware
of
> the query plans for joins on remote tables, I'm much more looking for a
way
> to keep the SQL Server from fetching the data every time, as it won't have
> changed FOR SURE!
> Thanks in advance
> Simon
> posted to: microsoft.public.sqlserver.setup;
> microsoft.public.sqlserver.server; microsoft.public.sqlserver.clustering
>|||Could you replicate the data from your source to the target server. As long
as the data is treated as Read-Only on the target server, this could work.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Simon Niederberger" <dontreply@.hotmail.com> wrote in message
news:Ojd11uL8DHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm currently setting up a SQL Server 2000. My configuration is:
> Instance MyCompany running on ServerA
> Instance TheirCompany running on ServerB
> TheirCompany is mapped as linked server into instance MyCompany
> In my situation, the data in TheirCompany is only located on ServerB due
to
> legal restrictions. All data access happens through ServerA. Therefore, it
> would be favourable if ServerA would keep the data of TheirCompany in its
> cache and not query the remote server for every SELECT.
> Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
> not volatile, from cache or something).
> Are there any other suggestions on how to optimize performance? I'm aware
of
> the query plans for joins on remote tables, I'm much more looking for a
way
> to keep the SQL Server from fetching the data every time, as it won't have
> changed FOR SURE!
> Thanks in advance
> Simon
> posted to: microsoft.public.sqlserver.setup;
> microsoft.public.sqlserver.server; microsoft.public.sqlserver.clustering
>

Caching of linked server data

Hi
I'm currently setting up a SQL Server 2000. My configuration is:
Instance MyCompany running on ServerA
Instance TheirCompany running on ServerB
TheirCompany is mapped as linked server into instance MyCompany
In my situation, the data in TheirCompany is only located on ServerB due to
legal restrictions. All data access happens through ServerA. Therefore, it
would be favourable if ServerA would keep the data of TheirCompany in its
cache and not query the remote server for every SELECT.
Can this somehow be configured. (Like saying TheirCompany.DataAccessType = not volatile, from cache or something).
Are there any other suggestions on how to optimize performance? I'm aware of
the query plans for joins on remote tables, I'm much more looking for a way
to keep the SQL Server from fetching the data every time, as it won't have
changed FOR SURE!
Thanks in advance
Simon
posted to: microsoft.public.sqlserver.setup;
microsoft.public.sqlserver.server; microsoft.public.sqlserver.clusteringSorry, but there is no way to cache remote server data on a local server...
If you wish the data to be local you'd have to make a copy in a local
database...
You might consider making a copy in tempdb, and sharing that among users. It
will automatically be deleted when the Server is re-booted.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Simon Niederberger" <dontreply@.hotmail.com> wrote in message
news:Ojd11uL8DHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm currently setting up a SQL Server 2000. My configuration is:
> Instance MyCompany running on ServerA
> Instance TheirCompany running on ServerB
> TheirCompany is mapped as linked server into instance MyCompany
> In my situation, the data in TheirCompany is only located on ServerB due
to
> legal restrictions. All data access happens through ServerA. Therefore, it
> would be favourable if ServerA would keep the data of TheirCompany in its
> cache and not query the remote server for every SELECT.
> Can this somehow be configured. (Like saying TheirCompany.DataAccessType => not volatile, from cache or something).
> Are there any other suggestions on how to optimize performance? I'm aware
of
> the query plans for joins on remote tables, I'm much more looking for a
way
> to keep the SQL Server from fetching the data every time, as it won't have
> changed FOR SURE!
> Thanks in advance
> Simon
> posted to: microsoft.public.sqlserver.setup;
> microsoft.public.sqlserver.server; microsoft.public.sqlserver.clustering
>|||Could you replicate the data from your source to the target server. As long
as the data is treated as Read-Only on the target server, this could work.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Simon Niederberger" <dontreply@.hotmail.com> wrote in message
news:Ojd11uL8DHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm currently setting up a SQL Server 2000. My configuration is:
> Instance MyCompany running on ServerA
> Instance TheirCompany running on ServerB
> TheirCompany is mapped as linked server into instance MyCompany
> In my situation, the data in TheirCompany is only located on ServerB due
to
> legal restrictions. All data access happens through ServerA. Therefore, it
> would be favourable if ServerA would keep the data of TheirCompany in its
> cache and not query the remote server for every SELECT.
> Can this somehow be configured. (Like saying TheirCompany.DataAccessType => not volatile, from cache or something).
> Are there any other suggestions on how to optimize performance? I'm aware
of
> the query plans for joins on remote tables, I'm much more looking for a
way
> to keep the SQL Server from fetching the data every time, as it won't have
> changed FOR SURE!
> Thanks in advance
> Simon
> posted to: microsoft.public.sqlserver.setup;
> microsoft.public.sqlserver.server; microsoft.public.sqlserver.clustering
>

C# Master-Details (Separate Pages)

My question is setting up master detail pages using stored procedrues. In the tutorials C# Master-Details (Seperate Pages) example they use the following code for the master page for the navigation.

<asp:HyperLinkField HeaderText="View Details..." Text="View Details..." DataNavigateUrlFields="au_id"
DataNavigateUrlFormatString="DetailsView_cs.aspx?ID={0}" />

The call to the Details PageDetailsView_cs.aspxuses the following code

<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="SELECT dbo.authors.au_id, dbo.titles.title_id, dbo.titles.title, dbo.titles.type, dbo.titles.price, dbo.titles.notes FROM dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id WHERE (dbo.authors.au_id = @.au_id)"
ConnectionString="<%$ ConnectionStrings:Pubs %>">
<SelectParameters>
<asp:QueryStringParameter Name="au_id" DefaultValue="213-46-8915" QueryStringField="ID"/>
</SelectParameters>
</asp:SqlDataSource>

I can replicate the example calling the detail page but I am unable to make the detail page work when using a stored procedure as the asp:SQLDataSource. Using the above sql code as a stored procedure in the <SelectParameters> I am not able to return the data set using either asp:QueryStringParameter or asp:Parameter as I have built other forms using stored procedures and have tested the procedure and know that it works. Can someone point me in the right direction.

Thanks

The soulution works the same with stored procedures but you need to have

EnableSortingAndPagingCallbacks="false" not true

|||I spoke to soon, my previous post did not resole the issue of calling a stored procedure from the detail page. The master page passes the correct value to the detail but the procedure does not recognize it.

bytes per i/o

In SQL server 2000 it recommends configuring 64 kb stripes when setting
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?This is a multi-part message in MIME format.
--020609000407000503080500
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I'm not sure what recommendation you've been reading (I normally leave
the striping configuration up to the RAID controller; the RAID vendor
usually has the best idea about best practices for their RAID sets).
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only 1
byte on the page has changed, SQL Server has to write the entire 8K page
to disk (and also create one or more log records for the modification).
Similarly, when you read a single integer value, for example, from 1 row
in a table, even though that may only account for 1, 2, 4 or 8 bytes,
depending on the datatype, the entire page is read from disk and loaded
into memory (if it's not already cached). So each I/O ought to
represent 8K.
--
*mike hodgson*
http://sqlnerd.blogspot.com
tbone wrote:
>In SQL server 2000 it recommends configuring 64 kb stripes when setting
>up raid arrays. So technically each I/O is 64 kb. But when I am
>monitoring the I/O writes and I/O write bytes the numbers don't add up
>(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
>I/O write bytes) Is this most likely because each write doesn't fill up
>the 64 kb stripe? If so why not? Probably something to do with data
>pages being 8kb each and only filling up each page half way or
>something?
>
>
--020609000407000503080500
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not sure what recommendation you've been reading (I normally
leave the striping configuration up to the RAID controller; the RAID
vendor usually has the best idea about best practices for their RAID
sets).<br>
<br>
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only
1 byte on the page has changed, SQL Server has to write the entire 8K
page to disk (and also create one or more log records for the
modification). Similarly, when you read a single integer value, for
example, from 1 row in a table, even though that may only account for
1, 2, 4 or 8 bytes, depending on the datatype, the entire page is read
from disk and loaded into memory (if it's not already cached). So each
I/O ought to represent 8K.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
tbone wrote:
<blockquote
cite="mid1141683797.703495.277790@.i39g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">In SQL server 2000 it recommends configuring 64 kb stripes when setting
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?
</pre>
</blockquote>
</body>
</html>
--020609000407000503080500--|||SQL Server does more than just 8K I/Os. For transaction log writes, a write
could be as small as a sector (i.e. 256 bytes) or sa large as 64K. For
checkpoints, a write may be much larger than 8K (from 8K to 64K). For log
backups, you may yet see a different I/O size. For bulk inserts, a write can
be up to 128K. In addition, DBCC DBREINDEX and restore are often not 8K
writes.
On most systems, because of a multiplicity of activities, you can hardly
expect to see a single I/O size in terms of write bytes/sec, and the counter
values taken at the drive can fluctuate wildly. If you want to observe the
block sizes of SQL Server I/Os, you would need to carefully control the
environment and try to make sure only one type of SQL Server I/Os is taking
place, ideally on an isolated drive.
Linchi
"Mike Hodgson" wrote:
> I'm not sure what recommendation you've been reading (I normally leave
> the striping configuration up to the RAID controller; the RAID vendor
> usually has the best idea about best practices for their RAID sets).
> However, when SQL Server performs a single read or a single write it is
> always 1 single, whole, 8K page that is read or written. Even if only 1
> byte on the page has changed, SQL Server has to write the entire 8K page
> to disk (and also create one or more log records for the modification).
> Similarly, when you read a single integer value, for example, from 1 row
> in a table, even though that may only account for 1, 2, 4 or 8 bytes,
> depending on the datatype, the entire page is read from disk and loaded
> into memory (if it's not already cached). So each I/O ought to
> represent 8K.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> tbone wrote:
> >In SQL server 2000 it recommends configuring 64 kb stripes when setting
> >up raid arrays. So technically each I/O is 64 kb. But when I am
> >monitoring the I/O writes and I/O write bytes the numbers don't add up
> >(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
> >I/O write bytes) Is this most likely because each write doesn't fill up
> >the 64 kb stripe? If so why not? Probably something to do with data
> >pages being 8kb each and only filling up each page half way or
> >something?
> >
> >
> >
>