Virtual Primary Key = Slow

Jul 23, 2005

I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.

Looking at Profiler, I see that when there is no virtual primary key, Access
sends a simple select command to SQL Server. However, when there is a
virtual primary key, Access gets a list of primary key values, and then uses
those values to get records from SQL Server in groups of 10.

This may account for why the scrolling is slow when there is a virtual
primary key. However, I wonder if there's any way to improve performance or
otherwise work around this apparent bottleneck.

The MS literature says that creating a virtual primary key will not affect
performance. However, since different methodologies for obtaining data are
used with and without the virtual primary key, it seems that it does affect
performance, and certainly does in this situation.

Thanks,

Neil

View 9 Replies


ADVERTISEMENT

SSIS Package Runs Slow On Virtual Server

May 24, 2008

I am using SSIS packages for data transfer, When i run the package on virtual server it takes more time as when run on a PC. After analysing i found that Package when run on Virtual server takes time in startup around (50 sec) or so.Could anyone help me with a little bit of detail description as to why it runs slow.

View 10 Replies View Related

Slow Query On Primary Key Using LEFT

Dec 19, 2001

My table consists of about 1.4 Million Records. The PK is a CHAR field and ranges in size from 3 - 25 characters. I need to pull a recordset using the LEFT function.

Example: SELECT blah WHERE LEFT(myPK, 8) = 'AIRBILLNU'

This query takes about 115927ms to run and the server is 100% CPU bound, it should only bring up 2 records. Seems like the index is not being used. I know the DB design is probably not the greatest, we probably should have had an INT PK and IX on the other field which is now the PK. I cannot do anything about that at this point.

Is there anything I can do to speed up this query.?

Thanks,Adrian

View 3 Replies View Related

Convert Composite Primary Key Into Simple Primary Key

Jan 11, 2007

Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.

may i convert Composite Primary key into simple primary key in thr table like this.



Thanks,
Uma"

View 1 Replies View Related

Virtual SQL Servers

Nov 5, 2002

Is virtual server the same as creating more than 1 instance of SQL Server?

Thanks!

View 1 Replies View Related

Out Of Virtual Memory

Dec 12, 2006

I have a SSIS package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.

I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.

Getting Error:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked

Any help would be greatly appreciated.

View 1 Replies View Related

Virtual Directory

Feb 5, 2008

Hi Guys,
Windows XP, IIS 5.1

I have written an installer that creates an MS SQL Database, creates the
necessary data tables and then imports the data into the data tables. All
this works fine.
I now need to copy the web pages to a directory on the users machine and
then make the chosen directory a virtual directory so the user can call the
pages in the usual way (http://localhost/mytestsite/index.html)

Would anyone know how to create a virtual directory using dos command line parameters. I have seen something using "IIsVdir.vbs" but I do not have this script file.

Any help would be really appreciated.

Best Regards,

Steve.

Steve

View 1 Replies View Related

Virtual Server

Mar 24, 2008

What is a virtual server?In my new job,my boss mentioned about moving the websites from a physical server to a virtual server.Why is this done or needed?
Also he insists that I buy and install SQL server 2000 enterprise edition in my laptop and use the enterprise manager to access their server.Can't I just download the client tools from their SQL package and be able to access their server?Do I need a CAL or is it just enough to have a permission in their network for my laptop?

Can anybody throw some light here please?Thanks!

View 1 Replies View Related

Out Of Virtual Memory

Dec 11, 2006

I have one package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.

I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.

Getting Error:
[DTS.Pipeline]
Information: The buffer manager detected that the system was low on
virtual memory, but was unable to swap out any buffers. 4 buffers were
considered and 4 were locked. Either not enough memory is available to
the pipeline because not enough is installed, other processes are using
it, or too many buffers are locked

Thank you for your assistance!

View 14 Replies View Related

Low Virtual Memory

Oct 21, 2007

Hello



I design package Merge 2 tables

Table one contains almost 25 million

Table 2 contains almost 30 million



using lookup component to split record if exist start update else insert as new record every time I start execute get error on lookup component low virtual memory



Kindly if anyone have suggest I will be appreciated



Thanks in advance

Note the 2 tables have PK & IX

View 2 Replies View Related

Low Virtual Memory

Dec 4, 2007

I have a sbs 2003 sp2 server running exchange 2003 sp2 it was running fine until one day I got this low virtual memory error, I checked the memory usage in the task manager and it is using 7 gigs of virtual memory, I increased the max size from 6 gigs to 8 gigs just to see what happens and now it is using 9 gigs of virtual memory. I don't have any clue as to what the problem is! Please Help!!!

View 1 Replies View Related

Adding Primary Key To A Table Which Has Already A Primary Key

Aug 28, 2002

Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.

Thanks,
Jeyam

View 9 Replies View Related

SQL Server Under A Virtual Machine

Aug 17, 2007

I really need some help. This is what I'm trying to do: first of all, i have a PC running Vista. Now on that PC, i have a virtual machine using VMWare Workstation 6, which has Win XP with SP2 installed on it. On the virtual machine, i have Visual Studio 2005 and SQL Server 2005 Express Edition with SP2; now, the thing is, i have a program in C#, which is supposed to connect to a network, but since I'm running on just one PC which is not connected to a network, i instead connect to 127.0.0.1 via port 1433. Now here's where my problems start. At first, i could not connect because i kept receiving an error which said "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provedier: TCP provider, error: 0 - No connection could be made because the target machine actively refused it.)". A friend of my mine helped, so i did the following: i went into SQL Server Surface Area Configuration (configuration for localhost) , changed the remote connections to allow for both local and remote, using both TCP/IP and named pipes, restarted the database engine, started the SQL Server Browser; then i went to administrator tools, Data Sources (ODBC), and from System DSN tried to add an SQL Server. I gave it a name, and on the server textbox i tried typing "localhost", but doing so, i could not get past the next screen, (i did specify the port as 1433) where i have to chose between win nt and sql server authentication, and neither would work (even though i know the sa password and can log on to SQL Management Studio using that password). Then i tried copying the server name from the management studio, as the server when trying to add the system DSN. That seems to have worked, and i finished ading the SQL Server to the System DSN, using the database which i wanted. But after all these steps, i still get the exact same error as before - "An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provedier: TCP provider, error: 0 - No connection could
be made because the target machine actively refused it.)".If someone can please help - i've tried everything i can think of - i can access the internet from the virtual machine, i even tried disabling the firewall, but no luck. Is it because of the virtual machine? If so, can anyone suggest a solution? I actually doubt it, because i've even tried to install it on my pc, not the virtual machine, so on vista - and also installed SP1 for VS 2005 and SP1 for Vista, but still the same thing. Sorry for making this post so long, but if anyone can help, i'd really appreciate it. Thanks!

View 5 Replies View Related

When Does SQL Use Virtual Memory In The System ?

Apr 22, 2006

I am trying to undertstand how does sql 2005 decide how to use the memory.

Does it use RAM first than the virtual memory or does it decide what to use conditionaly?

Anybody have an idea? Like if the table is really big does it put half
of the table to RAM and the other half to virtual memory ?

View 3 Replies View Related

SQL Clustering (virtual Server)

Apr 19, 2001

Say I have 2 servers clustered for SQL. xxxP1 and xxxP2.

Can my SQL virtual server name be xxxP1 or does it have to be something other than the names I called the 2 W2K boxes.

Thanks..

View 2 Replies View Related

Private Virtual Dtabase

Mar 22, 2000

One of our clients controls data for about 150 companies. Each company has the same schema and is running SQL Server 7. The maintenence task is horrible. He would like to put all the data in one large database, but needs to control the access, so a user at one company selecting data in a table cannot have access to another company's data in the same table. I thought about using views for each company but that is 150 companies times 100 tables. With Oracle8i you can make virtual private databases. Can this be done in SQL Server 7? Thanks in advance

View 1 Replies View Related

Low Virtual Memory In SQLServer

Jan 19, 1999

We have a Server that is set to use 80mb of a 128mb machine. The machine is also set to use up to 300mb of Virtual memory as needed.

After running for 10 days, we got the message "Low on Virtual memory". We did some looking and found SQLServer was using 300mb between real and virtual memory.

So the question: Is there a manual method to get SQLServer to release extra memory with out stopping and restarting the service?

View 1 Replies View Related

Number Of Virtual Log Files

Nov 8, 2012

We are using sql server 2008 r2 standard with sp2. I have a 12 GB database in production server, log file was set to 150MB with increment of 10 percent, in last 4 years database size has gone from 2gb to 12 gb. I ran following command

DBCC LOGINFO

And found I have 150 plus rows(which means 150 plus virtual log files)

I increased the size of log file to 25 percent of data file which comes to be approx 3gb and also set auto growth to 20 percent ...

Additional info: we have a log shipping environment in production, i am taking log backups every 15 mins.. still number of virtual log files is same , why is that , how to bring them between number of 25-50 as thats the recommended.

View 5 Replies View Related

ODBC Virtual Driver

Mar 16, 2004

Hi,
I have the following requirement :

I need to have a way to intercept the SQL queries from an application written in VB and using a ODBC driver and modify the SQL queries before it goes through the ODBC driver and then to the database. This I need to do without modifying the original application.

The solution I have in mind is to write a ODBC virtual driver and configure my application to use my virtual ODBC driver. The ODBC virtual driver in turn will use the actual ODBC driver to the database. The virtual driver will basically intercept the SQL queries, modify it and then give it to the real ODBC driver.

My question is

1. Is this a feasible solution?
2. What should I do in order to implement the vitrtual ODBC driver.
3. Any pointer will be appreciated.

Thanks
Jake.

View 3 Replies View Related

Virtual To Static Table

May 26, 2004

:confused: Please forgive this elementary question. I have database which has a view that produces the desired records and fields from multiple tables. As I understand it a view is a Virtual Table. My problem is I need to export these results periodically to deliver to a customer. I am running this database on SQL Server 2000 if I right click on a table I have the option to dts the data to a text file but when I right click on the view I do not have this option. How can I make this virtual table an actual table. PLEASE Help Thank You, Ed

View 1 Replies View Related

Creating A Database In Virtual IP

Jan 25, 2014

I am using internet through a dongle connection and every time I connect to internet, the ip gets changed.

1. How to create a database when the ip is not constant?
2. Can I use DNS server ip as database engine?
3. Is there any way to get the system ip?

View 4 Replies View Related

SQL Server & Virtual Machines

Jan 25, 2007

Good morning,

I am looking for some first-hand experiences from fellow DBA's where they had SQL Server running on a Win2K3 VM. What sort of issues (or successes) did you find re: resource sharing, swap files, etc? Are there any experiences where using a VM negatively affected your environment?

Thanks so much. What a great forum!

View 5 Replies View Related

Why Is SQL Server Using So Much Virtual Memory?

Jul 23, 2005

I have two instances of SQL Server running on my Development machine.I am having some performance problems and while investigating theproblem I saw with the Process Explorer form Sysinternals that bothinstances consume each 800 Mbytes of memory!I experimented with sp_configure and by giving both instances a fixedmemory size. Both methods do not seem to have any effect.Can anybody explain me why SQL Server is using so much memory?Thanks for any information.Evert WiesenekkerPSBesides the northwind database I only have one simple extra database(70 Mb in size) installed.

View 2 Replies View Related

Can I Rename A Virtual Sqlserver ?

Jan 17, 2008

I have 2 clusters I am reducing to 1 cluster with 2 instances. In order to save programming time I need to rename the sql instances to use the old names after the old servers are removed from the network. I plan on changing the IP address to match the old servers. Can this be done ?

View 1 Replies View Related

Virtual Links In Database

Aug 13, 2007



Not sure where to place this question...

We have a bunch of SQL databases that are used for a similar number of IIS sites (we are talking of close to 500 sites), dealing with a .NET e-learning application

While we manage to create a virtual folder for the e-learning content, we do have one database for each site. So, when it comes to update the whole platform, we are talking about running one script per site, which may take about 5 seconds per user per site. With a total of 300.000 users and sites, it may come to more than 17 days running the update script.

Is there a way similar to that on IIS to create a symbolic link from one table in one database to another table in a diferent database in a diferent or same server?

Thanks,

AG



View 5 Replies View Related

Keep Getting Virtual Memory Errors

Mar 14, 2006

Hello,

I am wondering if there is a way to solve a virtual memory error? We randomly get the following error when trying to run sycn over the http websync. Some clients have 512 MB running sql express, others are full instances that have 1.5 GB.



The merge process could not allocate memory for an operation; your system may be running low on virtual memory. Restart the Merge Agent.

View 35 Replies View Related

HOW TO CREATE VIRTUAL CUBE?

May 20, 2008

Can anybody tell me how to create a Virtual Cube from two or more diffrent SSAS 2005 Cubes?

View 7 Replies View Related

Low Virtual Memory Shutdown

Jul 6, 2006

hi,

for the last six months or so my pc has been shutting down all applications for no apparent reason when a 'low virtual memory' bubble appears. I have removed dozens of items, such as games, image editos; all programmes that require a lot of memory but it is no good. Every 40mins or so the pc decides to shut everything down and where it is impossible to start any further applications, unless I log off and on or shut down the pc myself. I really am fed up with this, its so annoying. Is it because of a virus or do I still have too much on my pc?



insaneolly

View 1 Replies View Related

Urgent - Problem Due To Virtual Dimesions Or??

Jun 14, 2004

Hi,

I am using dimensions: Product, Customer, Customer Geogrpahy (This is a virtual Dimension).The DDLs are posted below in case they might help....

The problem that I am running into is that when I see the "ALL" Sales or Zone Level sales, I get the correct results. However, when I go to State or County level, I get different results. This is driving me nutes and have already spend good 48 hours at least but am unable to solve this :(

1) Could this problem be caused due to use of Virtual Dimensions?

2) Is it better to use SnowFlake Schema based dimension instead? ( Though these are not shown in the DDLs but I also have the fields for ZoneCode, StateCode, CountyCode, & CityCode and can link them with my Geography table/dimesion --- If that's what it will take to solve the problem)

3) Some where in the Wizard steps, there is an option for "Uniqueness". Exactly what is this option and can this solve my problem.

I'll be gratefull for your help. Many thanks.


DDLs:
-------

CREATE TABLE [tblCustomers] (
[CustCode] [varchar] (20) ,
[CustName] [varchar] (64),
[CustZone] [char] (10),
[CustState] [varchar] (64),
[CustCounty] [varchar] (64),
[CustCity] [varchar] (64),
)




CREATE TABLE [tblSalesFACT] (
[SaleDate] datetime,
[CustCode] varchar (20),
[ProdCode] varchar (20),
[SaleAmount] DECIMAL (12,2),
[ProdQty] INTEGER
)


CREATE TABLE [tblProducts] (
[ProdCode] [varchar] (20),
[ProdName] [varchar] (50) ,
[ProdFamily] [varchar] (20) ,
)


Thanks.

View 2 Replies View Related

Problem With Virtual Table As Parameter

Jul 23, 2006

Hi all,

here is what i have so far

set @fld_name = 'sales_vol'
set @tbl_name = 'tbl_sales'

create @tbl table (val int, rank int identity(1,1))
---
I want to insert values of colum 'sales_vol' into the 'val' of the table 'tbl_sales'

i tried this

execute sp_executesql
n'insert into @t(value) select @fld_name from @tbl_name'

---
sql analyser keeps telling me : "Must declare the variable '@t'."

Can I do this? If not, is there another way?

Thank you.

---
here is a sample data for 'sales_vol'
sales_vol
38
217
10
95
34
7
186
39
81
80
31
67
60
5
41
97
259
14
116
-----

View 1 Replies View Related

Error : 806 -- Could Not Find Virtual Page...

May 19, 1999

at SQL Server startup, in error log:
Error : 806, Severity: 21, State: 1
Could not find virtual page for logical page <number> in database 'EMGTEST'

Error : 3414, Severity: 21, State: 1
Database 'EMGTEST' (dbid 7): Recovery failed. Please contact Technical Support for further instructions.

Database 'EMGTEST' cannot be opened - it has been marked SUSPECT by recovery. The SA can drop the database with DBCC.


.....

I reset status to Emergency mode (sysdatabases.status = -32768),
run dbcc checkdb, dbcc newalloc, dbcc textall...it all looks just fine.
I also changed database status to 12 (sysdatabases.status = 12 - select into/bulkcopy and trunc. log on chkpt.) and then run dbcc checkdb, dbcc newalloc, dbcc textall again. looked just fine.
but, if I restarted server , database will be marked 'suspect' again, because of Error 806.

any ideas?

Thanks.

View 3 Replies View Related

Virtual T-SQL Problem With Emtpy Spaces

Jan 12, 2005

Trying to finish a stored proc that's based off the classic search and replacers, except I want it to change any blank fields to actual nulls. Can't get the virtual sql to accept '' to do the update though. Here's the proc:

Create PROC DBUpdateBlankToNull
AS
BEGIN
SET NOCOUNT ON
--SET QUOTED_IDENTIFIER ON - Was using in testing various ways of identifying the empty field


DECLARE @Blank nvarchar(256), @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @Blank = ''
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL='UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = Null WHERE ' + @ColumnName + ' = ' + '''' -- Also tried using @Blank which would be preferable
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
--SET QUOTED_IDENTIFIER OFF - Was using in testing various ways of identifying the empty field
END


Any ideas? This is driving me nuts.

Thanks in advance!
Mark

View 3 Replies View Related

Urgent - Problem Due To Virtual Dimesions Or??

Jun 15, 2004

Hi,

I am using dimensions: Product, Customer, Customer Geogrpahy (This is a virtual Dimension).The DDLs are posted below in case they might help....

The problem that I am running into is that when I see the "ALL" Sales or Zone Level sales, I get the correct results. However, when I go to State or County level, I get different results. This is driving me nutes and have already spend good 48 hours at least but am unable to solve this :(


1) Could this problem be caused due to use of Virtual Dimensions?

2) Is it better to use SnowFlake Schema based dimension instead? ( Though these are not shown in the DDLs but I also have the fields for ZoneCode, StateCode, CountyCode, & CityCode and can link them with my Geography table/dimesion --- If that's what it will take to solve the problem)

3) Some where in the Wizard steps, there is an option for "Uniqueness". Exactly what is this option and can this solve my problem.

DDLs:-------

CREATE TABLE [tblCustomers] (
[CustCode] [varchar] (20) ,
[CustName] [varchar] (64),
[CustZone] [char] (10),
[CustState] [varchar] (64),
[CustCounty] [varchar] (64),
[CustCity] [varchar] (64),
)


CREATE TABLE [tblSalesFACT] (
[SaleDate] datetime,
[CustCode] varchar (20),
[ProdCode] varchar (20),
[SaleAmount] DECIMAL (12,2),
[ProdQty] INTEGER
)


CREATE TABLE [tblProducts] (
[ProdCode] [varchar] (20),
[ProdName] [varchar] (50) ,
[ProdFamily] [varchar] (20) ,
)
I'll be gratefull for your help. Many thanks.

View 4 Replies View Related







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