.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

sql server (plan caching)

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server
 

what is the basis of a plan being included in a plan cache.?

suppose i want to know the number of times a query was executed, so i will get the entry in the plan cache for that query.

But i have read that many queries can use a single plan. So how will i know the number of times the query is executed using the plan because the same plan might have been used by some other query, so the plan will give the combined execution count of both the queries.

And, i have read that the query plans are based on the cost. So what is the basis for the cost of the plan. I mean, when does two different queries use the same plan?. can two queries fired on two different tables use the same plan?




View Complete Post


More Related Resource Links

sql server (plan caching)

  

what is the basis of a plan being included in a plan cache.?

suppose i want to know the number of times a query was executed, so i will get the entry in the plan cache for that query.

But i have read that many queries can use a single plan. So how will i know the number of times the query is executed using the plan because the same plan might have been used by some other query, so the plan will give the combined execution count of both the queries.

And, i have read that the query plans are based on the cost. So what is the basis for the cost of the plan. I mean, when does two different queries use the same plan?. can two queries fired on two different tables use the same plan?


proactive caching, sql server notification problem

  
I'll explain the problem with this simple example. I have a dimension and a fact table: CREATE TABLE [dbo].[Lead_Dim](   [Id] [int] NOT NULL,   [LeadSource] [nvarchar](50) NULL,   CONSTRAINT [PK_Lead_Dim] PRIMARY KEY ([Id]) ) GO CREATE TABLE [dbo].[Lead_Fact](   [Id] [int] IDENTITY(1,1) NOT NULL,   [LeadAmount] [float] NULL,   [Lead_Id] [int] NULL,   CONSTRAINT [PK_Lead_Fact] PRIMARY KEY ([Id]),   CONSTRAINT [FK_Lead_Fact_Lead_Dim] FOREIGN KEY([Lead_Id]) REFERENCES [dbo].[Lead_Dim] ([Id]) ) GO In the cube, I've enabled proactive caching for dimension and measure group partition. I use SQL Server notification (tracking table is 'Lead_Dim' for dimension and 'Lead_Fact' for measure group partition). Other properties are set to default (silence interval 10s,...). When I insert a record in the dimension and a record in fact like this: DECLARE @LeadId INT SET @LeadId = 1 INSERT INTO [Testing].[dbo].[Lead_Dim] ([Id], [LeadSource]) VALUES (@LeadId, N'Agent') INSERT INTO [Testing].[dbo].[Lead_Fact] ([LeadAmount], [Lead_Id]) VALUES (1.2, @LeadId) GO proactive caching processes both dimension and partition and cube measure is refreshed. But, when I run this: DECLARE @LeadId INT SET @LeadId =2 INSERT INTO [Testing].[dbo].[Lead_Dim] ([Id], [LeadSource]) VALUES (@LeadId, N'Agent') WAITFOR DELAY '00:00:02' INSERT INTO [Testing].[

BCS data caching in Search Server 2010

  
I am using BCS to pull Oracle data into a SharePoint external list via an .asmx web service so that it can be indexed and searched in Search Server 2010.  I created a .NET connector assembly which implements ReadItem() and ReadList() methods to accomplish this.  Both methods call web service methods to either retrieve a record by id or the entire list of data, respectively.  The data retrieved by the web service is done so using an Oracle stored procedure.  

The data retrieved by the web service is refreshed in the Oracle database on a nightly basis by perl scripts.  Because of this, search results muct also reflect any changes on a nightly b

SQL Server Maintenance Plan - Update Statistics fails if schema contains special chars

  

We have a database schema with a period in it's name.  This is valid as per http://msdn.microsoft.com/en-us/library/aa224033(SQL.80).aspx. When we create a maintenance plan to update statistics it will succeed if the object property is set to 'Tables and Views'. If this is set to 'Table' it will fail.

Steps to Reproduce

  • Create a new schema in a user database with a period in it's name, ie
    CREATE SCHEMA [Windows.EventLog] AUTHORIZATION [dbo]
  • Create a table with owner using schema created in #1, ie
    CREATE TABLE [Windows.EventLog].[Computer](
    
    	[ComputerId] [smallint] IDENTITY(1,1) NOT NULL,
    
    	[ComputerName] [varchar](255) NOT NULL,
    
     CONSTRAINT [PK_Computer] PRIMARY KEY CLUSTERED ([ComputerId] ASC)
    
    ) ON [PRIMARY]
    
    
  • Create a new maintenance plan, drag 'Update Statistics Task' to designer surface, edit properites, choose the user database, View T-SQL and test execution: it will work
  • Edit maintenance plan and modify 'Update Statistics Task', change Object to Table, change selection to all, execute task and it will fail. Also, if you now try to click 'View T-SQL' or change it back to 'Tables and Views', a error dialog with title 'Microsoft.SqlServer.MaintenancePlanTasksUI' shows will message 'Object

BCS data caching in Search Server 2010

  
I am using BCS to pull Oracle data into a SharePoint external list via an .asmx web service so that it can be indexed and searched in Search Server 2010.  I created a .NET connector assembly which implements ReadItem() and ReadList() methods to accomplish this.  Both methods call web service methods to either retrieve a record by id or the entire list of data, respectively.  The data retrieved by the web service is done so using an Oracle stored procedure.  

The data retrieved by the web service is refreshed in the Oracle database on a nightly basis by perl scripts.  Because of this, search results muct also reflect any changes on a nightly b

SQL Server 2008: Error in Creating Maintenance Plan

  

Hi,

We are trying to create and edit a Backup Script in Maintenance plans.  It is throwing the following error. Please help.

 

 

 

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.IO.FileNotFoundException: Could not load file or assembly 'msddsp, Version=9.0.0.0,

Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the

file specified.
File name: 'msddsp, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
   at Microsoft.DataTransformationServices.Design.DtrControlFlowDiagram.OnComponentChanged(Object

sender, ComponentChangedEventArgs chevent)
   at System.ComponentModel.Design.ComponentChangedEventHandler.Invoke(Object sender,

ComponentChangedEventArgs e)
   at

System.ComponentModel.Design.DesignerHost.System.ComponentModel.Design.IComponentChangeService.OnCompone

ntChanged(Object component, MemberDescriptor member, Object oldValue, Object newValue)
   at

Microsoft.DataWarehouse.VsIntegration.Designer.DataWarehouseScopeComponentChangeService.OnComponentChang

ed(Object component, MemberDescriptor member, Obje

sql server maintenance plan schedule time and execution time difference

  

Hi All,

One of my customer db there is one maintenance plan(full backup / Transaction log backup  ) schedule time for the full backup is 11:00:00 PM daily and for transaction log backup 12.30 pm Sunday to Friday ,I checked the job created by maintenance plan there also schedule time is same but when i check the history of the job it shows it happened at 7:00:00 pm every day. why there is time difference ?

Product version is 8.00.2055(SP4)

i checked the MSDB.dbo.sysjobschedules table and the next_run_date is not updated properly,it is showing yesterday 11:00:00 pm

 

please help me to resolve this issue

Thanks 

 

 


plan caching

  

what is the basis of a plan being included in a plan cache.?

suppose i want to know the number of times a query was executed, so i will get the entry in the plan cache for that query.

But i have read that many queries can use a single plan. So how will i know the number of times the query is executed using the plan because the same plan might have been used by some other query, so the plan will give the combined execution count of both the queries.

And, i have read that the query plans are based on the cost. So what is the basis for the cost of the plan. I mean, when does two different queries use the same plan?. can two queries fired on two different tables use the same plan?


i am using this dynamic view

sys.dm_exec_query_stats

please suggest me if there are any better views available.


Windows Server 2008 Cluster Disk write caching - SQL Server 2008

  
I was told by the windows admin that you cannot disable write caching in windows server 2008 when the drives are clustered. Can someone please tell me how to turn it off. I find it very hard to believe you cannot turn it off. i know on a normal server how to do it, by right clicking the drive, properties, hardware, click on the drive, click properties, uncheck write caching...However, this is disabled on my cluster. 
John M. Couch Insight Enterprises Inc. 6820 S. Harl Ave, Tempe, Az 85283 Email: john.couch@insight.com

Transfered ssis of type maintenance plan connect to wrong server

  
I transfered a maintenance plan using the method to transfer ssis with sql server management studio.

The sql task contained in my maintenance plan are set to use the "local server connection".  Somehow, when I run the maintenance plan on the target server, the sql task get executed on the source server from which I imported the maintenance plan.

In other words:
I transfer a maintenance plan from server A to server B.
The maintenance plan is set to use local server connection.
Running the maintenance plan on server B affects the server A instead of the server B.

Is there a way to refresh or repair the "local server connection" so that it actually connect to the current server instead of the one the ssis was imported from.

Here are some references on the method I used.

http://msdn.microsoft.com/en-us/library/ms141235.aspx

http://bloggingabout.net/blogs/mglaser/archive/2007/03/01/multiple

DR Plan, SP Producation, SP Development Server Setup

  

Hello,

We have setup two server 1) SPProd  2010 2)SPDev.  2010


Now, we are in process to test backup plan and DR plan.

I tool one of backup from SPProd and put it on SPDev. Now, when i was trying to restore that back up its refereing to SPProd database.

Now, the question is that if i restore backup at SPDev and then if i delete that SiteCollection from SPDev does it going to affect to my producation server?

I'm not good at DR. Please, advice me.

If some one knows other way to check DR plan then please do let me know.

 


Thanks, Sam

DR Plan, SP Producation, SP Development Server Setup

  

Hello,

We have setup two server 1) SPProd  2010 2)SPDev.  2010


Now, we are in process to test backup plan and DR plan.

I tool one of backup from SPProd and put it on SPDev. Now, when i was trying to restore that back up its refereing to SPProd database.

Now, the question is that if i restore backup at SPDev and then if i delete that SiteCollection from SPDev does it going to affect to my producation server?

I'm not good at DR. Please, advice me.

If some one knows other way to check DR plan then please do let me know.

 


Thanks, Sam

Backup Maintenance Plan Failure SQL Server 2005

  
Hi All,
I have three maintenance Plans Full, Differential and Transaction Log backup. Full and Transaction Log backup plans are working fine. In the all the maintenance plans I am taking backup of almost 60 databases.

The problem is with the differential backup plan. The backup plan is keep failing with the following errors:

FailedSad-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MY_Database' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'My_Database' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Giants_Security_4'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'E:\\MSSQL.1\\MSSQL\\Backup\\MY_Database\\MY_database_backup_200808240500.DIFF.BAK' WITH  FILE =

is server caching my page ?

  

Hi there 

I am making some modifcations on a ascx.cs page. When I upload the page to the server, it seems that the changes I made are not taken on board.

In fact if I put a response.redirect or if I remove the page altogether, nothing happens, my page appears on my browser as if nothing had happens. However if I make changes to the ascx page, I can see the changes straight away after uploading.

Even more bizarre, if I open my ascx page and if I remove CodeBehind="Page.ascx.cs" from the top line, I would expect the page to crash, but no, it still works. 

So it does look that the ascx.cs is cached and that whatever I do doesn't overwrite it.

What can I do ?

Cheers


Chris


Creating an Office Plan ECMAScript Mashup Drawing in Visio 2010 to Display in SharePoint Server 2010

  
Learn how to use the Visio Services ECMAScript API to create a floor plan drawing in Visio Premium 2010 in a Web Parts page in SharePoint 2010.

SQL Server Execution Plan

  

Can someone please tell me How I can check the execution plan of below script on SQL Server2005.

create table #Blocking_Check

(

        SPID int,

        Status varchar(100),

        [Login] varchar

SQL Server 2008 Maintenance plan will not delete bak and trn files

  

I have a sql 2008 maintenance plan to backup databases and to delete old bak files.  The db backup works but the delete does not.  I do not get any error messages, all steps marked successful.  So, I copied the sql statement EXECUTE master.dbo.xp_delete_file 0,N'Z:\SQL BACKUPS\',N'bak',N'2010-11-14T12:43:35',1 and tried to execute in a query window.  I have a successful completion message, BUT the files are not deleted.  I have ensured that the extension is bak and the folder is correct and the account has sysadmin on the server and is a sql sa.  I have tried many options and no success.  

We are version   Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM).

Any help would be appreciated.  Thanks!


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend