Snapshot And Cached Instances

Oct 23, 2007

Hi,

I'm trying to understand the cases where it's more interesting to use snapshot and when it's more interesting to use cached instances.

If I have 100 users trying to reach a report, is it better to use snaphsot or cache instance ? In both case, the 100 users will have the same report result. And what about the performance, are they similar ?

Thanks for your time and response,

See you,


Have a nice day!

regards,
sandy

View 1 Replies


ADVERTISEMENT

Snapshots Vs Cached Instances

Jun 19, 2007

Hello,

I would like to know what is the difference between a snapshot and a cached instance in SSRS?

Which one has the best performances and which one is the best for multiple users and reports containing parameters (the parameters are then passed in the where clause of the sql code; ex: WHERE IN(@param1))?

Thanks for your answers.
Zoz

View 4 Replies View Related

Images Not Displaying From Snapshot Or Cached Report

Apr 11, 2008

Hello,
This is my first post, and I'm hoping you all can help.

Using Reporting Services 2005, I have several reports that use embedded images.
All images render fine when the report execution is set to:
1) Always run this report with the most recent data
1a) Do not cache temporary copies of this report

However, when I change the execution to either a Cache or a Snapshot, the images and some charts render as red "X" placeholders. This is sometimes remedied when the user clicks the page refresh button, but not always.

Of course, I could just have all the concurrent users use the uncached report that hits the OLAP server, but that would be highly inefficient, and just plain slow.

Thanks for any help on this subject.

-michael

View 2 Replies View Related

SQL 2012 :: Select Statements And Ended Up Seeing Multiple Cached Instances Of Same Stored Procedure

Nov 24, 2014

I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?

SELECT o.name, o.object_id,
ps.last_execution_time ,
ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds,
ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds,
ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds

[code]...

View 4 Replies View Related

Generating User Instances In Sql Server Is Disabled. Use Sp_configure User Instances Enabled To Generate User Instances.

Sep 28, 2007

 When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance. 

View 4 Replies View Related

Problem When Applying A Snapshot When Tables Have Been Updated During Snapshot Generation

Jun 20, 2007

Hi

I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.



Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.



The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.



I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.



All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.

View 1 Replies View Related

Named Instances And Default Instances Of SQL Server 2000

Jul 18, 2001

I had a server with SQL Server 7.0
I installed a named instance of SQL Server 2000 and then i passed all my DB
of the 7.0 instance to the 2000 instance.
Then i removed the 7.0 instance, that was the default instance.
So at the moment there is only the 2000 version, but it isn't the default
instance
Can the 2000 instance become the default instance? (So that clients can
connect to it simply through computer name, and not creating an alias)

thanks

Fede

View 1 Replies View Related

Cached Datasets?

Aug 8, 2007

Hello all,
I have a report with a table and a chart. It uses dataset1 as the data source.
All works fine.
I create a new dataset called dataset2.
The queries are exactly the same. The only differences between the 2 datasets is the database server and the fact that one of the columns is a smallint (in dataset2) and an int(in Dataset1)
I change the datasetName property of both the table and the chart to use dataset2.
When I run the report I get a conversion error stating that there was an overflow of int2 while using dataset1. I have verified the report is not using dataset1 anywhere. If I delete dataset1 and run the report the error goes away. If I add it back, I get the error again. Why is the report looking at dataset1 if it is not referenced at all in the report? Does SQL RS cache the datasets and verify each when it compiles?

regards,
Bill

View 9 Replies View Related

View Result Cached

Aug 1, 2007

I am using SQL server 2005. I have a VIEW that joins several tables. One of the table's column can be added dynamically by the user from a GUI interface. However, after a column is added, it does not show up in the VIEW immediately. It will take a while (I haven't figured out exactly how long) before the extra column shows up as the execution result of the VIEW.
 So it seems like SQL server is caching that VIEW's schema. Is there anyway I can make this view always comes back with the latest schema?
Thanks a lot!
Penn

View 1 Replies View Related

Cached Search Of Top Terms

Mar 11, 2008

Hi, I have a search and I want to create a hyperlinked list of the top 5 search terms below it, what's the most efficient way to go about this?

View 20 Replies View Related

Cached Query Result

Sep 20, 2007

I want to check the performance of m query and i just want to remove cached query results. Is there any suggestion how can i do this.
I just want to check after each modificatin how much improvement in performance

View 1 Replies View Related

Non-cached Lookup Method

May 28, 2008

Phil, great links, really helpful and appreciated.

I just need to verify one thing on the lookup method:
--One of the lookup methods people were discussing is non-cached lookup -- which seem to be evaluated to be the fastest. Is the non-cached the default of LookUp transformation? and when I wanted the lookup method to be cached, I need to go into the Advance tab and set it to however %, right? thanks.

View 3 Replies View Related

Too Many Stored Procedures Or Cached Plans?

Aug 16, 2005

I was wondering if anyone had an concrete information about if there is a problem with having too many stored procedures or plans in the cache? Obviously there is an impact on memory but if we can ignore that for the time being, does SQL perform just as well with 100 query plans as it does with 10's millions of plans?

View 9 Replies View Related

Keeping A Cached Lookup In Memory

Dec 10, 2007

Is it possible to keep a Cached Lookup in memory when executing multiple Data Flows? Executing DFT€™s in parallel will cache and use the same LOOKUP statement. But what if I€™m executing the DFT sequentially, can I keep the LOOKUP from the first DFT in memory for the second DFT? For example, in my case, I€™m caching a lookup against the Customer dimension for invoices. The second DFT then processes credits and again does a lookup against the Customer dimension. I want to use the cached Customer records from the first DFT.

View 1 Replies View Related

Generating A Cached Copy Of The Report

May 27, 2005

Is there a way to programatically determine if a report should be generated from the cache or run against real-time data?

View 7 Replies View Related

Full Cached Lookup With Parameters

Jul 8, 2006

Parameterized queries are only allowed on partial or none cache style lookup transforms, not 'full' ones. Is there some "trick" to parameterizing a full cache lookup, or should the join simply be done at the source, obviating the need for a full cache lookup at all (other suggestion certainly welcome)

More particularly, I'd like to use the lookup transform in a surrogate key pipeline. However, the dimension is large (900 million rows), so its would be useful to restrict the lookup transform's cache by a join to the source.

For example:

Source query is: select a,b,c from t where z=@filter (20,000 rows)

Lookup transform query: select surrogate_key,business_key from dimension (900 M rows, not tenable)



Ideal Lookup transform query:

select distinct surrogate_key

,business_key

from dimension d inner join

t on d.business_key = t.c

where t.z = @filter

View 7 Replies View Related

Linked Servers And Cached Execution Path

May 1, 2006

I have a procedure that generates dynamic sql and then executes via the execute(strSQL) syntax. BOL states that if I use sp_executesql with hard-typed parameters passed in variables, the query optimizer will 'probably' match the sql statement with the cached execution path, thus avoiding recompilation and speeding up the results for heavily run procedures.

Can anyone tell me if this is also true if the sql references an object on a linked sql server 2000 database? Technically, the sql is exactly the same, but I'm unsure if there is some exception due to the way linked objects are processed.

Thanks!

View 1 Replies View Related

SQL 2012 :: Way To Invalidate Cached Query Plans?

Apr 29, 2014

Any way to invalidate cached query plans? I would rather target a specific query instead of invalidating all of them. Also any sql server setting that will cause cached query plans to invalidate even though only one character in the queries has changed?

exec sp_executesql N'select
cast(5 as int) as DisplaySequence,
mt.Description + '' '' + ct.Description as Source,
c.FirstName + '' '' + c.LastName as Name,
cus.CustomerNumber Code,
c.companyname as "Company Name",
a.Address1,
a.Address2,

[code]....

In this query we have seen (on some databases) simply changing ‘@CustomerId int',@CustomerId=1065’ too ‘@customerId int',@customerId=1065’ fixed the a speed problem….just changed the case on the Customer bind parameter. On other servers this has no effect.the server is using an old cached query plan, but don’t know for sure.

View 9 Replies View Related

Clear Cached Reports In Visual Studio

Jun 12, 2007

I am frequently going back and forth between the making changes to my reports and previewing those changes, all from within visual studio without publishing the reports each time.



The problem is that frequently changes that I make are not reflected in the preview window unless I either close out of visual studio completely or I wait some length of time, (I'm not sure how long exactly, but 1/2 an hour seems to always do the trick.)



Is there a way to clear any cache and force visual studio to completely reprocess a report?



At least when it is formatting changes I can identify whether the change has stuck, but when I'm fixing bugs in the code, I can't tell if I didn't fix it or if the change just hasn't taken effect.

View 1 Replies View Related

Storing Daily Cached Query Results In SQL Server

Mar 7, 2005

I'm working on a reporting tool that could bring back hundreds of thousands of results back at once. I need some way to run the actual query only once a day, and then the reporting tool would just pull back this cached results. To be short, I need to figure out how to do this using a minimum amount of resources. Would a DataView work with something like this? How would I have it update only once a day? I appreciate any advice!

View 2 Replies View Related

SQL 2012 :: Entity Framework Returning Cached Data

Mar 23, 2014

I have a datagridview bound to a table that is part of an Entity Framework model. A user can edit data in the datagridview and save the changes back to SQL. But, there is a stored procedure that can also change the data, in SQL, not in the datagridview. When I try to "refresh" the datagridview the linq query always returned the older cached data. Here's the code that I have tried using to force EF to pull retrieve new data:

// now refresh the maintenance datagridview data source
using (var context = new spdwEntities())
{
var maintData =
from o in spdwContext.MR_EquipmentCheck
where o.ProdDate == editDate
orderby o.Caster, o.Strand
select o;
mnt_DGV.DataSource = maintData;
}

When I debug, I can see that the SQL table has the updated data in it, but when this snippet of code runs, maintData has the old data in it.

View 0 Replies View Related

SQL Server 2012 :: Invalidate Cached Query Plans?

Apr 30, 2014

way to invalidate cached query plans? I would rather target a specific query instead of invalidating all of them.

Also do you know of any sql server setting that will cause cached query plans to invalidate even though only one character in the queries has changed?

exec sp_executesql N'select
cast(5 as int) as DisplaySequence,
mt.Description + '' '' + ct.Description as Source,

[Code].....

In this query we have seen (on some databases) simply changing ‘@CustomerId int',@CustomerId=1065’ too ‘@customerId int',@customerId=1065’ fixed the a speed problem….just changed the case on the Customer bind parameter. On other servers this has no effect. I’m thinking the server is using an old cached query plan, but don’t know for sure.

View 3 Replies View Related

Auto Stats Adversly Affect Cached Plans?

May 31, 2007

With SQL2005 SP2, we are seeing that when auto stats run on one or more indexes of a large table (1.5M rows), then immediately the stored proc using that table starts acting as if the query plan is no longer any good. This causes a drastic slowdown in response time and a corresponding increase of table reads to get the data. E.g, the next execution of the procedure after the auto stats kick in goes from 355 reads to 755000 reads (as depicted by Profiler). Generally, there are about 25 people using the DB at any one time. They connect through a mid-tier VB component.



I tried adding WITH RECOMPILE to the stored proc in question, but that caused almost all executions to run at the higher number. I thought that the WITH RECOMPILE hint would create a new query plan for each execution of the procedure and that plan would the the latest and greatest. Perhaps it did, but most users got stuck with the higher number of reads anyway. After taking the hint out, everyone went back to getting the 335 number and quick response times.



What we are wrestling with is that when those auto stats hit, it really messes up everyone until we manually recompile the procedure. Daily we delete all records in the table that are over 45 days old, so the table stays pretty much the same size. We also set the recompile flag to cause a new plan to be generated that will reflect the smaller amount of data. Should we also run a stats update before recompiling the procedure? Profiler has been very helpful in capturing what is going on, so I think I have a good handle on that. However, I don't understand why WITH RECOMPILE produced a messed up plan for everyone. The compile itself seems to take only 1 ms when done from the query screen.

View 11 Replies View Related

Reporting Services :: SSRS 2008 R2 BIS Deploy Does Not Always Clear Cached Reports

Jul 19, 2011

Is there a known problem with BIS Deploy not clearing cache.  I repeatly (but not always) have an issue where I will do a BIS Deploy of an RDL but when I run it, the old report runs.  I can't clear this out until I explicitly log into the Report Manager and explicitly delete the RDL and re-deploy from the BIS.

Is there a workaround, fix, process change, configuration change I can do to keep this from happening?

View 7 Replies View Related

Cached Reports REQUIRE That SQL Server Used SQL Server Authentication (or Mixed)?

May 16, 2007

I am trying to setup cached reports so that one of my larger reports doesn't have to be re-run every time someone wants to view it (the data source only updates every 24 hours).



Anyway I made a new data source, set the report to use that, and in that data source I said to use "SQLexampleUserName" as the stored credentials.



Now when I go to run the report it says: Login failed for user 'username'. The user is not associated with a trusted SQL Server connection.



This is referenced in the MSKB here:

http://support.microsoft.com/default.aspx/kb/555332



Which makes sense, but now my question is: If I want to used a cached report do I HAVE to allow SQL Server Credentials?



I was using Windows Authentication only up to this point.

View 7 Replies View Related

User-defined Stored Procedures InsertCustomerin Northwind Database That Is Cached In Database Explorer:No New Values Inserted?

Jan 14, 2008

Hi all,

I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:

--User-defined stored procedure 'InsertCustomer'--

ALTER PROCEDURE dbo.InsertCustomer

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30),

@ContactTitle nvarchar(30),

@Address nvarchar(60),

@City nvarchar(15),

@Region nvarchar(15),

@PostalCode nvarchar(10),

@Country nvarchar(15),

@Phone nvarchar(24),

@Fax nvarchar(24)

)

AS

INSERT INTO Customers

(

CustomerID,

CompanyName,

ContactName,

ContactTitle,

Address,

City,

Region,

PostalCode,

Country,

Phone,

Fax

)

VALUES

(

@CustomerID,

@CompanyName,

@ContactName,

@ContactTitle,

@Address,

@City,

@Region,

@PostalCode,

@Country,

@Phone,

@Fax

)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form_Kimmel


Public Sub InsertCustomer()

Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _

"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"

Dim connection As SqlConnection = New SqlConnection(connectionString)

connection.Open()

Try

Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)

command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@CustomerID", "PAULK")

command.Parameters.Add("@CompanyName", "Pauly's Bar")

command.Parameters.Add("@ContactName", "Paul Kimmel")

command.Parameters.Add("@ContactTitle", "The Fat Man")

command.Parameters.Add("@Address", "31025 La Jolla")

command.Parameters.Add("@City", "Inglewoog")

command.Parameters.Add("@Region", "CA")

command.Parameters.Add("@Counrty", "USA")

command.Parameters.Add("@PostalCode", "90425")

command.Parameters.Add("@Phone", "(415) 555-1234")

command.Parameters.Add("@Fax", "(415 555-1235")

Console.WriteLine("Row inserted: " + _

command.ExecuteNonQuery().ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

Throw

Finally

connection.Close()

End Try



End Sub

End Class
==============================================


I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.

Thanks in advance,
Scott Chang

View 10 Replies View Related

Cached SQL Plan Vs. Stored Proc Plan

Dec 12, 2002

We have a debate in our team about embedded SQL vs. Stored Procs.

The argument is why use SP's if you can embed the SQL in the code and SQL2K will cache it on the fly?

I can't find any definitive information on pros and cons between the two methods.

If there are no major performance issues, or gotchas, I guess it comes down to developer preference.

SP Pros:
- Great SQL support in VS.NET (dev, debug, integration)
- Seperation of database specific code from middle tier.
- Less lines of code in middle tier
- VS.NET support for .xsd dataset definitions.
- Logic closer to data for more demanding processes.

Embedded SQL Pros:
- Less artifacts for version control
- Better encapsulation of logic


Any info would be appreciated.

thanks

Kevin

View 4 Replies View Related

Report Viewer Is Printing A Cached Report

Mar 6, 2007

Hi All

I have a reporting viewer in a windows form that behaves very strange. When I open the form and run the report it shows up nicely in the report viewer. If I print the report it only prints one page. When i print the report a second time the whole report is printed. Next I'll change the report parameters and run the report, then it shows up nicely in the viewer, but when I print the report the first report is printed.

Does anyone know what to do with this problem?

Thanks in advance

View 9 Replies View Related

Get All Instances

Apr 23, 2008

Hi,

Can anybody tell me..which command should i use to get the list of all the server names, all the sql server instances in each server and all the databases in each sql server instance.

View 9 Replies View Related

Instances

Jan 12, 2006

Why do people have various instances of SQL Server running on 1 box , why cant we have various databases running for 1 instance

Whats the advantage of multiple instances


Thanks

View 1 Replies View Related

SQL Instances

Sep 29, 2006

I have a server running one instance of sql server enterprise edition. I want to install an additional application that requires sql enterprise edition. Can I install sql enterprise an create another instance for the new app. I know that I would have to give it's unique instance name.

Thanks in advance.

View 7 Replies View Related

Sql Instances

Jul 11, 2007

Hi every1. How can i know all the sql instances installed in my local machine?

Thanks.

RON
________________________________________________________________________________________________
"I won't last a day without SQL"

View 5 Replies View Related

2 Instances...

Feb 7, 2008

hi everyone.

i am working on 2 instances of sql. I want to do a queries and selects with this 2 instances in a single page. How could I do that?
For e.g. assuming the name of the instances are A and B...

thanks



RON
________________________________________________________________________________________________
"I won't last a day without SQL"

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved