Need help on priorty to run query (urgent)

Question

Need help on priorty to run query (urgent)

jeyaprakash84

SSC Rookie

Points: 25
More actions
June 9, 2014 at 1:06 am

#294237

HI,
my table size is very big,i am loading data into the table using SSIS.same table some one ran select query,it keeps on running ,same time SSIS try to delete the table,its not happening bcas table got locked by select query ,once select query release the lock then only delete query will run.in this situation i want give priorty to delete ,how to break or stop select query and run delete query ...please help me on this.very urgent.loading is impartant .
Thanks in advance.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399381 More actions · Answer 1

SQL Server prevents changes from occurring at the same time. It's part of the Atomicity, Consistency, Isolation, and Durabality (ACID) properties of the database. You can't both SELECT a row from a table while simultaneously DELETE that same row from the table.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jeyaprakash84 SSC Rookie Points: 25 More actions · Answer 2

Thanks man ,i got it .i dont want simultaneous process.whenever delete query try to hit the table (waiting for SELECT complition)i want to quit the SELECT and run DELETE based on the time. suppose SELECT is running for more then 1hour then quit the select and run the DELETE.is it possible???...

Thanks.

Jeya.

jeyaprakash84 SSC Rookie Points: 25 More actions · Answer 3

example: 24 hours back SELECT is hit the particular table,but didn't give the result keep on executing ...so my delete query also waiting for SELECT complition..i want to avoid this problem..if SELECT runs more then 30 mins stop the SELECT,start the DELETE.

Grant Fritchey SSC Guru Points: 399381 More actions · Answer 4

You can try to kill the process that is running, but, if it's part of a transaction, then you'll still have to wait for the transaction to complete it's rollback process.

A select statement that's running for 30 minutes that's not part of a data load would, in most organizations I've worked, be considered broken. You could also try tuning that query so it doesn't run for 30 minutes.

Another way to mitigate this is to look at using read committed snapshot for your isolation level. That will allow you to modify data while the reads occur by versioning the data underneath the read. But I'm not 100% sure it's going to help in your situation.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004704 More actions · Answer 5

Grant Fritchey (6/9/2014)
SQL Server prevents changes from occurring at the same time. It's part of the Atomicity, Consistency, Isolation, and Durabality (ACID) properties of the database. You can't both SELECT a row from a table while simultaneously DELETE that same row from the table.

I wonder what's going to happen in Hekaton without the "D" of "ACID".

Sorry... just thinking out loud.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Jeff Moden (6/10/2014)
Grant Fritchey (6/9/2014)
SQL Server prevents changes from occurring at the same time. It's part of the Atomicity, Consistency, Isolation, and Durabality (ACID) properties of the database. You can't both SELECT a row from a table while simultaneously DELETE that same row from the table.
I wonder what's going to happen in Hekaton without the "D" of "ACID".

Nothing. Hekaton tables are fully durable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004704 More actions · Answer 7

GilaMonster (6/10/2014)
Jeff Moden (6/10/2014)
Grant Fritchey (6/9/2014)
SQL Server prevents changes from occurring at the same time. It's part of the Atomicity, Consistency, Isolation, and Durabality (ACID) properties of the database. You can't both SELECT a row from a table while simultaneously DELETE that same row from the table.
I wonder what's going to happen in Hekaton without the "D" of "ACID".
Nothing. Hekaton tables are fully durable.

Coming from you, that makes me feel much better about Hekaton. That's not what I've heard, though. I've heard terms such as "Delayed Durability" used in association with Hekaton. Any truth to such statements?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Jeff Moden (6/10/2014)
That's not what I've heard, though.

Maybe spend some time with Kalen's whitepaper? Lots of people have said lots of garbage about Hekaton.

I've heard terms such as "Delayed Durability" used in association with Hekaton. Any truth to such statements?

Delayed durability is an optional feature in SQL 2014 that is set database-wide (forced or optional). From Books Online:

SQL Server transaction commits can be either fully durable, the SQL Server default, or delayed durable (also known as lazy commit).
Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. Writing the transaction log entries to disk is required for a transaction to be durable. Delayed durable transactions become durable when the transaction log entries are flushed to disk.

http://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014

and Aaron's blog post: http://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014

The only relationships between delayed durability and Hekaton is that they both were introduced in SQL 2014, both require a lot of analysis and investigation before using and both are optional.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399381 More actions · Answer 9

Jeff Moden (6/10/2014)
GilaMonster (6/10/2014)
Jeff Moden (6/10/2014)
Grant Fritchey (6/9/2014)
SQL Server prevents changes from occurring at the same time. It's part of the Atomicity, Consistency, Isolation, and Durabality (ACID) properties of the database. You can't both SELECT a row from a table while simultaneously DELETE that same row from the table.
I wonder what's going to happen in Hekaton without the "D" of "ACID".
Nothing. Hekaton tables are fully durable.
Coming from you, that makes me feel much better about Hekaton. That's not what I've heard, though. I've heard terms such as "Delayed Durability" used in association with Hekaton. Any truth to such statements?

If you choose to go with the SCHEMA_ONLY durability, then on a restart (crash or not), you'll lose all data in the table. But, that's a conscious decision you're making that a given table doesn't need data reclaimed. It would be useful for say, data loads, where you want the schema back, but don't care about data. But again, as Gail has said, that's through a conscious decision to make that choice. It's not something just foisted on you by the technology.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004704 More actions · Answer 10

But that's what I'm talking about. That doesn't sound "fully durable" to me but I could be mistaken in what my definition of "fully durable" means.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

Jeff Moden (6/10/2014)
But that's what I'm talking about. That doesn't sound "fully durable" to me but I could be mistaken in what my definition of "fully durable" means.

Hang on, you're complaining because you can explicitly define a table to only have durable schema, a setting which is not default and has to be set on the table when you create it?

If you don't want a table to be 'durability = schema_only', then don't specify that option when you create that in-memory table and it will be fully durable. It's an option, not a requirement. Hekaton tables are fully-durable by default. If you want to have an in-memory table which has its contents discarded after a restart, you have to explicitly say so by specifying that option.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399381 More actions · Answer 12

GilaMonster (6/10/2014)
Jeff Moden (6/10/2014)
But that's what I'm talking about. That doesn't sound "fully durable" to me but I could be mistaken in what my definition of "fully durable" means.
Hang on, you're complaining because you can explicitly define a table to only have durable schema, a setting which is not default and has to be set on the table when you create it?
If you don't want a table to be 'durability = schema_only', then don't specify that option when you create that in-memory table and it will be fully durable. It's an option, not a requirement. Hekaton tables are fully-durable by default. If you want to have an in-memory table which has its contents discarded after a restart, you have to explicitly say so by specifying that option.

What she said.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning