Transaction Isolation Levels

Feb 15, 2006

I am redesigning an application that distributes heldesk tickets to our
50 engineers automatically. When the engineer logs into their window a
stored procedure executes that searches through all open tickets and
assigns a predetermined amount of the open tickets to that engineer.The
problem I am running into is that if 2 or more engineers log in at the
same time the stored procedure will distribute the same set of tickets
multiple times.

Originally this was fixed by "reworking" the way SQL Server handles
transactions. The original developer wrote his code like this:

-----
DECLARE @RET_STAT INT
SELECT 'X' INTO #TEMP
BEGIN TRAN
UPDATE #TEMP SET 'X' = 'Y'
SELECT TOP 1 @TICKET_# =TICKET_NUMBER FROM TICKETS WHERE STATUS = 'O'
EXEC @RET_STAT = USP_MOVE2QUEUE @TICKET_#, @USERID
IF @RET_STAT <> 0
ROLLBACK TRAN
RETURN @RET_STAT
END
COMMIT TRAN
-----

The UPDATE of the #TEMP table forces the transaction to kick off and
locks the row in table TICKETS until the entire transaction has
completed.

I would like to get rid of the #TEMP table and start using isolation
levels, but I am unsure which isolation level would continue to lock
the selected data and not allow anyone else access. Do I need a
combination of isolation level and "WITH (ROWLOCK)"?

Additionally, the TICKETS table is used throughout the application and
I cannot exclusively lock the entire table just for the distribution
process. It is VERY high I/O!

Thanks for the help.

View 3 Replies


ADVERTISEMENT

Question About Transaction Locks And Isolation Levels

Oct 27, 2005

Have the need for going to a table to get an identity value. This is for updating an existing database, blah blah blah. Here is the schema of the table we are using:CREATE TABLE [TableIdentityValue] ( [TableName] [varchar] (50) , [NextNegativeIdentity] [int] NOT NULL , [NextPositiveIdentity] [int] NOT NULL , CONSTRAINT [PK_TableIdentityValue] PRIMARY KEY  CLUSTERED  (  [TableName] )  ON [PRIMARY] ) ON [PRIMARY]GO
Now, depending on the type of data we are inserting into a table, we need to get either a negative or positive number for the PK. There are two sprocs that control the obtaining of those values:CREATE PROCEDURE GetNegativeIdentity @tableName varchar(50)AS DECLARE @nextNegativeIdentityValue int
 BEGIN TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 SET @nextNegativeIdentityValue =  (  SELECT NextNegativeIdentity  FROM TableIdentityValue WITH (ROWLOCK)  WHERE TableName = @tableName )
 UPDATE TableIdentityValue SET NextNegativeIdentity = @nextNegativeIdentityValue - 1 WHERE TableName = @tableName
 COMMIT TRANSACTION RETURN @nextNegativeIdentityValueGOCREATE PROCEDURE GetPositiveIdentity @tableName varchar(50)AS DECLARE @nextPositiveIdentityValue int
 BEGIN TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 SET @nextPositiveIdentityValue =  (  SELECT NextPositiveIdentity  FROM TableIdentityValue  WHERE TableName = @tableName )
 UPDATE TableIdentityValue SET NextPositiveIdentity = @nextPositiveIdentityValue + 1 WHERE TableName = @tableName
 COMMIT TRANSACTION RETURN @nextPositiveIdentityValueGOSo, the thing is, we need the read and update of the value from the specific TableIdentityValue row to be atomic - we don't want anyone else reading or modifying that data. The problem is knowing which level of isolation to use and/or locking, and how to implement that. I have tried a few different things that seemed to make sense, like placing a ROWLOCK on the SELECT statement, but is that lock going to hold for the entire length of the transaction? Also, I read that using some of the lock hints can be accomplished in the sense that some isolation levels are the same as some lock hints (e.g. setting isolation level to SERIALIZABLE "has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction" according to SQL Books Online.Any help is appreciated!

View 5 Replies View Related

Locks - Isolation Levels

May 22, 2006

Good morning,

I am trying to get my head around locking (row, table) and Isolation Levels. We have written a large .NET/SQL application and one day last week we had about two dozen people in our company do some semi "stress/load" testing of the app.

On quite a few occassions, a few of the users would receive the following error:

"Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

We are handling this on two fronts, the app and the database. The error handling in the app is being modified to capture this specific error and to retry the transaction.

However, from the database side, I am trying to find the most affective and efficient change to make regarding locking. I have been doing a lot of reading online and in BOL to get a better grasp of locking, but what I would really like is feedback from the community (forum) and get your thoughts on what changes I should make, if any, on the db side.

Thanks...

Scott

View 5 Replies View Related

SQL 2012 :: Blocking In Report Server Database And Changing Isolation Levels?

Oct 12, 2015

We are getting frequently blocking in Report server database.

Is it ok to change isolation level to RCSI for report server database?

View 1 Replies View Related

TRANSACTION ISOLATION

Sep 16, 2007

Hi all. I have a question. I 've read already about isolation lavels, but I don't understand how in practic set proper isolation if I have say 100 transactions..what is the algoriphm?

View 4 Replies View Related

Transaction Isolation Level

Sep 10, 2002

Hello all,

What is the TRANSACTION ISOLATION LEVEL settings for MSSQL like the default setting in Oracle. In Oracle the default setting allows one session to read consistent data without waiting for the other sessions to commit/rollback the data.

For eg: In Mssql, if I update table A in the first session, and in another session (second session) if I select from table A, the second session waits till the first session completes the updates and commit or rollbacks.

But in Oracle , if I update table A in the first session, and in another session (second session) if I select from table A, the second session will perform a read from the ROLLBACK SEGS and give a read consistent data without waiting for the first session to commit or rollback the transaction.

Is this type of behaviour is possible is MSSQL. And If YES how can I do it?

Thanks for any help
Suresh

View 10 Replies View Related

Transaction Isolation Level

Jul 24, 2007

Not sure if this is more a .Net question or SQL Server, but I think it belongs here.

I have a small .Net app that reads records from a bunch of files from disk and inserts them into a database table. There could be several hundred files resulting in 100,000 records or more each time its run. Since it's a large table there are of course a few indexes on it so the insert takes a while. For larger sessions it could run as long as an hour. I need it to run in a transaction so that if anything happens while it's running the records from that run were committed on an all or nothing basis. However, I don't want to lock the table at all while the insert is happening. These aren't transaction records or anything like that, and the batches are separated by client so there will be no conflicts (no need to lock the table).

Unfortunately, no matter what I use for the isolation level of the transaction the table always ends up locked for reads. Data from previous runs is live at this point and we can't allow that. I have the choice of the following isolation levels when I create the transaction, but none seems to work:
Chaos
ReadCommitted
ReadUncommitted
RepeatableRead
Serializable
Snapshot
Unspecified

I would expect Chaos, ReadUncommitted, or Snapshot be okay here, but I can't seem to get it working. Any thoughts?

View 4 Replies View Related

Transaction Isolation Level

Nov 3, 2007

Hi,I have 1 SQL statement selecting data from various tables and updating othertables.The question then is how do I prevent other applications from modifying thetables that I'm working on (that is while my transaction is being executed)?I know that the isolation level should be either REPEATABLE READ orSERIALIZABLE. But I need confirmation on if one of these actually solve myissue - prevents other applications/threads from modifying/inserting datainto the same tables that I'm working on.Thanks in advance,Daniel

View 5 Replies View Related

What Is Transaction Isolation Level In MS SQL?

Mar 20, 2008

What are the different kinds of Transaction Isolation Level? How they useful in day to day activity as SQL Developer ?

View 2 Replies View Related

TRANSACTION ISOLATION To SQL Server From Access

Apr 25, 2005

Hi,

I currently have a requirement for access to a SQL Server 2000 box using Access 2003. The queries will sometimes be quite demanding which in turn might affect the rest of the SQL users on the system.

Does anyone know of any setting in Access so that I can achieve the same result as setting the TRANSACTION ISOLATION level using T-SQL?

Any ideas would be much appreciated.

Regards,
Paul.

View 4 Replies View Related

Setting Transaction Isolation Level

May 6, 2015

By setting the TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; is this automatically sets all the joined tables to NOLOCK?

Or, in order this statement to work right, this needs to be only done inside BEGIN TRAN > COMMIT (ROLLBACK) statement?

View 7 Replies View Related

Isolation Level In NON-Transaction Queries

May 19, 2008

I need to set the Isolation Level (in ADO) for the Non-transaction queries to SNAPSHOT.

Both the ADO.Connection.IsolationLevel Property and the SQL Server SET TRANSACTION ISOLATION LEVEL command set the Isolation Level for the Transaction queries but no for the non-transaction queries.

I cannot use the READ_COMMITTED_SNAPSHOT database option, becaus when I am in a transaction I need the READ COMMITTED Isolation Level not the SNAPSHOT Isolation Level.

I don't want to rewrite the entire code of my existing application to add (NOLOCK).

Thanks,

View 10 Replies View Related

What Is The Default Transaction Isolation Level For SQL Server?

Dec 7, 2000

What is the default transaction isolation level for SQL Server?
and Advantages of having multiple filegroups ?

View 1 Replies View Related

Question About Transaction Isolation Level=readCommitted

May 23, 2007

I have a question about the "readCommitted" transaction isolation level.I have a client that is updating a record on a table.I suspend the execution after the UPDATE but before the commit statement.Than another client is trying to read the same record.As transaction isolation is set to "readCommited" I expected that the secondclient will read the old version of the record (before the update).Instead, the second client hangs and wait until the first client do thecommit.I expect this behavior if transaction isolation is set to "serializable"Is this behavior correct?Thanks,D.

View 3 Replies View Related

SQL 2012 :: Transaction Isolation Level And Distributed Transactions

Mar 5, 2015

I vaguely remember reading somewhere that all distributed transactions are executed at Serializable Isolation Level "under the covers."

1. Is this true?
2. What does "under the covers" mean in this case; i.e. will I not see the isolation level represented accurately in requests?

View 9 Replies View Related

Transaction Reading From Linked Server Without DTC? (Isolation Level?)

Feb 9, 2006

Is there a way to read data from a linked server,within a transaction, without using DTC?The data on the linked server is static, thereforethere is no need for two-phase commit. There isno need for locking data on the linked server, becauseit is not being updated (either from the remote server,or from the local server).I don't want to run DTC because:1.) there have been security-related flaws with DTC inthe past2.) the application doesn't do distributed updates, andbecause the data on on the remote server is static,there is really no data integrity exposure withoutDTC.I cannot specify "WITH (NOLOCK)" on the select fromthe linked server:Server: Msg 7377, Level 16, State 1, Line 6Cannot specify an index or locking hint for a remote data source.I tried setting the isolation level:SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDbut that seems to have no effect on the requirement to use DTC.I still get the message:MSDTC on server 'LOCALSERVER' is unavailable.Is there some other way around this? Is it possible to provide someconnection string parameter, in the linked server setup, that wouldspecify the "READ UNCOMMITTED" isolation level for the linked server,so that DTC wouldn't be necessary.(In other words, can I tell SQL Server, "trust me, this won't hurt"?)Environment: SQL Server 2000 sp4The SQL does something like:declare @x char(4), @k int, @rc1 int, @rc2 intset @k=123BEGIN TRANselect @x=xfrom remoteserver.remotedatabase.dbo.tablewhere k=@kupdate localdatabase.dbo.table1set x=@xwhere k=@kset @rc1=@@errorupdate localdatabase.dbo.table2set x=@xwhere k=@kset @rc2=@@errorif (@rc1 = 0 AND @rc2=0) COMMIT TRANelse ROLLBACK TRAN

View 2 Replies View Related

Exclusives Locks With Transaction Isolation Level Readuncommitted

Mar 12, 2008

Hello,

I have some locks issues on production database (win 2k3 SP1, sql server 2k5).
In fact, I have an asynchronous process that makes SELECT TOP 1 in a table and UPDATE the selected row. The transaction isolation level for doing this action is READUNCOMMITTED.
The isolation level readuncommitted is ignored for the update if I'm not wrong.
On the other hand, I have some transactional activities with the isolation level read uncommitted too.

But when I control the database activity, I find very often locks between the asynchronous part and the transactional part. This is the transactional activity that is locking the asynchronous activity.
The transactional activity is a simple SELECT and this type of query, in spite of the isolation level readuncommitted, makes exclusives locks when the asynchronous makes LCK_M_U.

I tried to modify the strored procedure for the SELECT/UPDATE of the asynchronous process with a "UPDATE my_table ... FROM my_table" query in order to reduce the transaction time. But the problem is always present.

Can someone help me to understand how a select query with the isolation level readuncommtted can make exclusives locks ?

Thanks in advance.

View 2 Replies View Related

DELETE Transaction With SNAPSHOT Isolation Level - Conflicts Another Table

Nov 29, 2007

 



Hi,we are executing the following query in a stored procedure using snapshot isolation level:DELETE FROM tBackgroundProcessProgressReportFROM         tBackgroundProcessProgressReport LEFT OUTER JOIN                      tBackgroundProcess ON                      
tBackgroundProcess.BackgroundProcessProgressReportID =
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFT
OUTER JOIN                      tBackgroundProcessProgressReportItem ON                      
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID =
tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportIDWHERE     (tBackgroundProcess.BackgroundProcessID IS NULL) AND                       (tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.However, for some reasone we get the following exception:System.Data.SqlClient.SqlException:
Snapshot isolation transaction aborted due to update conflict. You
cannot use snapshot isolation to access table 'dbo.tBackgroundProcess'
directly or indirectly in database 'RHSS_PRD_PT_Engine' to update,
delete, or insert the row that has been modified or deleted by another
transaction. Retry the transaction or change the isolation level for
the update/delete statement.The exception specifies that we are
not allowed to update/delete/insert records in tBackgroundProcess, but
the query indeed deletes records from tBackgroundProcessProgressReport,
not from the table in the exception.Is the exception raised because of the join?Has someone encountered this issue before?Thanks,Yani

View 1 Replies View Related

SQL Server 2014 :: Transaction Isolation Level And Implicit Transactions

Oct 23, 2015

I'm investigating a poorly performing procedure that I have never seen before. The procedure sets the transaction isolation level, and I suspect it might be doing so incorrectly, but I can't be sure. I'm pasting a bastardized version of the proc below, with all the names changed and the SQL mucked up enough to get through the corporate web filters.

The transaction isolation level is set, but there is no explicit transaction. Am I right that there are two implicit transactions in this procedure and each of them uses snapshot isolation?

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
DECLARE @l_some_type varchar(20),
@some_type_code varchar(3),
@error int,
@error_msg varchar(50);

[Code] .....

View 4 Replies View Related

Transact SQL :: Left Outer Join And Transaction Isolation Level

Nov 30, 2015

We have a service that inserts some rows into a parent table (P) and child table (C). This operation is atomic and performed within a transaction.

We also have a service that queries these tables such that rows are (should only be) returned from P where there are no children for that parent.

The SQL that performs this is simplified below:

SELECT P.SomeCol
FROM P
LEFT OUTER JOIN C ON P.PKofP_Value = C.PkofP_Value
WHERE
C.PkofPValue IS NULL
AND P.SomeOtherCol=0

Our expectation is that the query service should only return rows from P where there are no rows in C.

However, this seems not to be the case, and occasionally we find that rows from P are returned where there are matching rows in C.

We are sure that the process that inserts rows into P and C does so within a single transaction.

We have traced this with SQLTrace and can see the txn stag and committing and all operations using the same transactionid within the transaction.

We are running the default isolation level committed.

In SQLTrace we can see the query process start, the inserter process start and complete and then the query process continue (after presumably being blocked).

So how can the query process "miss" the child rows and return the parent from the above query?

Is it possible that, in this isolation level, the inserter process can block the query process such that when the inserter process commits and when the query process continues it does not see the child rows inserted because they were inserted in the table/index "behind" where the query process has already read - some kind of phantom phenomenon?

View 3 Replies View Related

Levels In A Cube

Feb 24, 2004

I have just started working the 2047 OLAP and came arcross the Analysis Service Limits. It states that the levels in a cube has a limit of 256 and the leves per dimension is 64. I am confused.

What is the definition of (levels in a cube)! and how are the levels in a cube different from (levels per dimension)

View 1 Replies View Related

FOR XML Query With More Than 2 Levels Of Data

Mar 30, 2006

I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.

In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.

I'm using the Bush family in this example, these are the relationships:
- George SR
--- George JR
------ Jenna
------ Barbara
--- Jeb
------ Jeb JR
------ Noelle

These statements will create and populate the tables for the example with the above relationships:

SET NOCOUNT ON
DROP TABLE KK_Kids, DD_Dads, GG_Grandpas
CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20))
CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20))
CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))

ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Dad_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT PK_KK PRIMARY KEY (KK_Kid_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT FK_KK FOREIGN KEY (KK_Dad_Key) REFERENCES DD_Dads (DD_Dad_Key)

INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR')
INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR')
INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' )
INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' )
INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' )
INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )


So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads.
I am getting this, which is not what I want:

- George SR
--- George JR
--- Jeb
------ Jenna
------ Barbara
------ Jeb JR
------ Noelle


SELECT 1 as Tag,
NULL as Parent,
GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName],
GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id],
NULL as [DD_Dads!2!DD_DadName],
NULL as [DD_Dads!2!DD_Dad_Key!id],
NULL as [DD_Dads!2!DD_Grandpa_Key!idref],
NULL as [KK_Kids!3!KK_KidName],
NULL as [KK_Kids!3!KK_Dad_Key!idref]
FROM GG_Grandpas
UNION ALL
SELECT 2 ,
1 ,
NULL ,
GG_Grandpa_Key ,
DD_DadName ,
DD_Dad_Key ,
DD_Grandpa_Key ,
NULL ,
NULL
FROM GG_Grandpas, DD_Dads
WHERE GG_Grandpa_Key = DD_Grandpa_Key
UNION ALL
SELECT 3 ,
2 ,
NULL ,
GG_Grandpa_Key ,
NULL ,
DD_Dad_Key ,
NULL ,
KK_KidName ,
KK_Dad_Key
FROM GG_Grandpas, DD_Dads , KK_Kids
WHERE GG_Grandpa_Key = DD_Grandpa_Key
AND DD_Dad_Key = KK_Dad_Key

FOR XML EXPLICIT


I've tried it all different ways, but no luck so far.
Any ideas?

View 5 Replies View Related

Results Are Returning On Different Row Levels?

Feb 26, 2014

How do I get my data to show starting at the first row instead of skipping down?

Refer to the attachment.

Code:
CREATE PROCEDURE [dbo].[uspReportData]
-- Add the parameters for the stored procedure here
@Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL,
@Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,

[code].....

View 1 Replies View Related

Get The Names For Different Levels In A Table

Aug 20, 2007

hi

I've a table with coln names

ID
Name
ParentID
Level


I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4



How to write the query for getting the Names for different levels for corresponding ParentID....

Output should be like:-
Leaf Branch Trunk Root
Stem Branch Trunk Root

View 1 Replies View Related

Multiple Levels Of Partitioning In EE

Jun 4, 2007

Hi All,

we are building a DW for a company that operates in 10 countries with the home country being the major portion of the data......



Previous efforts have always had the data separated by schemas and so to ask a question about a specific country required the schema number to be provided.



I am proposing that the 10 schemas, and therefore 10x the number of tables, indexes etc, be removed in favour of using partitioning.



However, we want to partition by country and by periods...that is we would like to create monthly partitions as normal.



No matter how I read the documentation and test this out, it seems to me that this multiple levels of partitioning can only be achieved if I create a field on the table that is some manipultion of the key for the company reporting structure and the period. I think I can take the first, add 10M and then add the period key.



But I am unsure if the optimiser is going to do it's partition elimination properly on such a calculated field.



Has anyone attempted such a multi-level partitioning scheme in SQL Server? I am thinking people must have as one level of partitioning was seen to be too restrictive many years ago.....



Thanks in Advance for your comments.



Best Regards

Peter Nolan

View 9 Replies View Related

Dynamically Set Logging Levels?

Sep 19, 2007

Hello All,

I suspect I know the answer but I'll ask away. We currently have our SSIS packages set up to log to SQL Server. Currently they log OnError, OnInformation and OnTaskFailed. If I'd like to have it log OnPipeLineRowsSent, is there anyway I can get that done without opening up the package and editing it? I know the change is trivial from the IDE but the deployment process at my current engagement is quite lengthy. If something breaks in production, I'd like to know if it'll be possible to turn up the chattiness of logging without going through a full deploy scenario.

I was looking at the parameters for dtexec/dtexecui and I see that you can configure where something logs but nothing about the verbosity of the logs generated. Is it something I'm missing with that or is that all you can set there?


The only other option that jumps out at me is to develop a custom script or component that sets the logging level based on a parameter. Anyone have a thought as to how much effort that would be---something easily tackled or probably more trouble that it's worth?


Thanks for the help

View 1 Replies View Related

Mirroring And Build Levels

Nov 30, 2007

All,

Is anyone aware if the database engine build levels will affect the mirroring process. we're in the process of upgrading a PROD environment to a new build however like to delay onto the disaster recovery (DR) server in case of issues. The DR is the mirror in the setup and so would have a differnet build level.

Is this likely to affect anything? All info seems to point to only versions differences causing a problem but not the build.

Is someone able to confirm this.

Thanks......

View 5 Replies View Related

Differing RAID Levels And Their Performance...

Jan 18, 2001

Hi fellas,

I have to spec out a new server, and I have the option of using RAID-5 or RAID-1 drive sets. I am limited to 24 drives in groups of 6,
and I have to have one hotspare per group, so up to 5 usable drives per channel.

I need 80-100GB of space total.

Okay, you're waiting for the question....I have heard many differing opinions on which is better, RAID 1 or RAID 5. If I have 2 Large disks (say 36GB)
on a Raid-1, I assume having 4 smaller 9GB drives on a RAID-5 will be faster, but I am not sure due to overhead and the like.

Does anyone know where I can get more information on RAID performance and how it is going to affect me? The database is going to be Read-Write, with
a ton of small transactions, and the occasional (usually on a weekend) aggregation.

Any help would be much appreciated,

Joe

View 2 Replies View Related

Sp_executesql And Database Compatibility Levels

Apr 15, 2003

I have captured some trace output for performance evaluation for an application which has just been upgraded. Originally, this application can only run with database compatible 70.

So, after we have switched this level from 70 to 80, I noticed that all T-SQL statements which executed thru the use of "sp_executesql" have much higher IO usuage. The usage increased from approximately 50 I/O to approximately 12000 I/O. When I reviewed the profiler output, I noticed that all select statements which executed thru this "sp_executesql" statements performed "index scan".

When I switch back to run with database compatibility level 70, my profiler output shows that all these "sp_executesql" statements performed "index seek".

All these statements use the same unique non-clustered index.

Is it a SQL Server bug? Does anyone know which service pack or hot fix address this problem?

Thanks...byyu :)

View 7 Replies View Related

AS Question: How To Hide Certain Levels Of A Dimension

Apr 22, 2004

Hi,

I have a Star schema based dimension called Customer which has these levels:

ALL Customers
Level1: Customer Type
Level2: Customer Sub Type
Level3: Customer Name


When a user is browsing the cube, is it possible to hide the the 1st level (and all it's sub-levels)? For example, If the Customer Type = "Low Ranked" then I do not want it to be
displayed to the user while (s)he is selecting from the dimension. HOWEVER I only want it to be hidden from being displayed but it's effect should always reflect e.g. Suppose:
[list=1]
Sales (measure count) for Customers with Type "High Ranked" = 100
Sales (measure count) for Customers with Type "Medium Ranked" = 50
Sales (measure count) for Customers with Type "Low Ranked" = 10
[/list=1]
Now if the user selects 'ALL Customer Type' in the dimension he/she should get a total Sale (measure count) of 160 (i.e. 100+50+10).

However when the user expands the Customers Dimension (i.e. ALL Customers), the resulting child nodes should only list 2 nodes i.e. High Ranked and Medium Ranked.

I went to the cube editor --> Advanced Properties and looked at the 'Hide Member If' property but amongst the 5 options there is none which allows me to specify the criteria.

Maybe the solution already is in one of those 5 options and thus please help me.

Many thanks in advance.

View 2 Replies View Related

Permission Levels For MSDE, ASAP

Dec 17, 2004

We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.

I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.

Any ideas how how to make MSDE let anyone connect? Thanks in advance!

View 10 Replies View Related

Contents Sort 3 Levels Of Data

Dec 21, 2007

I Have a table of Data (WikiData)

WikiIDint
ParentIDint
sTitlevarchar(50)
sDescriptionvarchar(MAX)

There will be three levels of data imposed at the Application Layer

Level 1: ParentID = 0
An Item Like Geography
Level 2: ParentID = a Level 1 WikiID
A sub Topic like Volcanoes
Level 3: ParentID = Level 2 WikiID
A bottom Topic like Pyroclastic Flows

I Need a SQL statement that Will Produce the Output where The output will be produced like this:
Level 1
Level 2
Level 2
Level 2
Level 1
Level 2
Level 2

I Built this but its wrong and has no order by Group by Statements
Select * from WikiData where ParentID = 0 or ParentID IN (Select * from WikiData where ParentID = 0)

View 12 Replies View Related

Find Table Reference Levels

Oct 3, 2006

The script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.

This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.

Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).

Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.

Tested on SQL 2000 only. Please post any errors found.

Edit 2006/10/10:
Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.


-- Start of Script - Find_Table_Reference_Levels.sql
/*
Find Table Reference Levels

This script finds table references and ranks them by level in order
to be able to load tables with FK references in the correct order.
Tables can then be loaded one level at a time from lower to higher.
This script also shows all the relationships for each table
by tables it references and by tables that reference it.

Level 0 is tables which have no FK relationships.

Level 1 is tables which reference no other tables, except
themselves, and are only referenced by higher level tables
or themselves.

Levels 2 and above are tables which reference lower levels
and may be referenced by higher levels or themselves.

*/

declare @r table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))

declare @rs table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))

declare @t table (
REF_LEVEL int,
TABLE_NAME nvarchar(200) not null primary key clustered )

declare @table table (
TABLE_NAME nvarchar(200) not null primary key clustered )
set nocount off

print 'Load tables for database '+db_name()

insert into @table
select
TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME
from
INFORMATION_SCHEMA.TABLES a
where
a.TABLE_TYPE = 'BASE TABLE'and
a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties'
order by
1

print 'Load PK/FK references'
insert into @r
selectdistinct
PK_TABLE =
b.TABLE_SCHEMA+'.'+b.TABLE_NAME,
FK_TABLE =
c.TABLE_SCHEMA+'.'+c.TABLE_NAME
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on
a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on
a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
order by
1,2

print 'Make copy of PK/FK references'
insert into @rs
select
*
from
@r
order by
1,2

print 'Load un-referenced tables as level 0'
insert into @t
select
REF_LEVEL = 0,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
(
select PK_TABLE from @r union all
select FK_TABLE from @r
)
order by
1

-- select * from @r
print 'Remove self references'
delete from @r
where
PK_TABLE = FK_TABLE

declare @level int
set @level = 0

while @level < 100
begin
set @level = @level + 1

print 'Delete lower level references'
delete from @r
where
PK_TABLE in
( select TABLE_NAME from @t )
or
FK_TABLE in
( select TABLE_NAME from @t )

print 'Load level '+convert(varchar(20),@level)+' tables'

insert into @t
select
REF_LEVEL =@level,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
( select FK_TABLE from @r )
and
a.TABLE_NAME not in
( select TABLE_NAME from @t )
order by
1

if not exists (select * from @r )
begin
print 'Done loading table levels'
print ''
break
end

end


print 'Count of Tables by level'
print ''

select
REF_LEVEL,
TABLE_COUNT = count(*)
from
@t
group by
REF_LEVEL
order by
REF_LEVEL

print 'Tables in order by level and table name'
print 'Note: Null REF_LEVEL nay indicate possible circular reference'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
order by
b.REF_LEVEL,
a.TABLE_NAME

print 'Tables and Referencing Tables'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.PK_TABLE
order by
a.TABLE_NAME,
c.FK_TABLE


print 'Tables and Tables Referenced'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.FK_TABLE
order by
a.TABLE_NAME,
c.PK_TABLE


-- End of Script



Results from Northwind database:

Load tables for database Northwind

(13 row(s) affected)

Load PK/FK references

(13 row(s) affected)

Make copy of PK/FK references

(13 row(s) affected)

Load un-referenced tables as level 0

(0 row(s) affected)

Remove self references

(1 row(s) affected)

Delete lower level references

(0 row(s) affected)

Load level 1 tables

(7 row(s) affected)

Delete lower level references

(9 row(s) affected)

Load level 2 tables

(4 row(s) affected)

Delete lower level references

(3 row(s) affected)

Load level 3 tables

(2 row(s) affected)

Done loading table levels

Count of Tables by level

REF_LEVEL TABLE_COUNT
----------- -----------
1 7
2 4
3 2

(3 row(s) affected)

Tables in order by level and table name
Note: Null REF_LEVEL nay indicate possible circular reference

REF_LEVEL TABLE_NAME
----------- ----------------------------------------
1 dbo.Categories
1 dbo.CustomerDemographics
1 dbo.Customers
1 dbo.Employees
1 dbo.Region
1 dbo.Shippers
1 dbo.Suppliers
2 dbo.CustomerCustomerDemo
2 dbo.Orders
2 dbo.Products
2 dbo.Territories
3 dbo.EmployeeTerritories
3 dbo.Order Details

(13 row(s) affected)

Tables and Referencing Tables

REF_LEVEL TABLE_NAME REFERENCING_TABLE
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories dbo.Products
2 dbo.CustomerCustomerDemo NULL
1 dbo.CustomerDemographics dbo.CustomerCustomerDemo
1 dbo.Customers dbo.CustomerCustomerDemo
1 dbo.Customers dbo.Orders
1 dbo.Employees dbo.Employees
1 dbo.Employees dbo.EmployeeTerritories
1 dbo.Employees dbo.Orders
3 dbo.EmployeeTerritories NULL
3 dbo.Order Details NULL
2 dbo.Orders dbo.Order Details
2 dbo.Products dbo.Order Details
1 dbo.Region dbo.Territories
1 dbo.Shippers dbo.Orders
1 dbo.Suppliers dbo.Products
2 dbo.Territories dbo.EmployeeTerritories

(16 row(s) affected)

Tables and Tables Referenced

REF_LEVEL TABLE_NAME TABLE_REFERENCED
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories NULL
2 dbo.CustomerCustomerDemo dbo.CustomerDemographics
2 dbo.CustomerCustomerDemo dbo.Customers
1 dbo.CustomerDemographics NULL
1 dbo.Customers NULL
1 dbo.Employees dbo.Employees
3 dbo.EmployeeTerritories dbo.Employees
3 dbo.EmployeeTerritories dbo.Territories
3 dbo.Order Details dbo.Orders
3 dbo.Order Details dbo.Products
2 dbo.Orders dbo.Customers
2 dbo.Orders dbo.Employees
2 dbo.Orders dbo.Shippers
2 dbo.Products dbo.Categories
2 dbo.Products dbo.Suppliers
1 dbo.Region NULL
1 dbo.Shippers NULL
1 dbo.Suppliers NULL
2 dbo.Territories dbo.Region

(19 row(s) affected)







CODO ERGO SUM

View 20 Replies View Related







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