SQL Questions

nwcomer

Hi All,

I was asked couple of questions yesterday and I thought I had to still learn a lot. Questions sound very normal but as for as I am concerned, don't know the answers hence this forum.

Please advise.

Here the questions asked to me

1) A simple insert into the table (could be from .net application or query analyzer) takes 10 minutes and times out, does not do any thing. What could be the reason and how would you identify and resolve the problem.

2) A simple select from table taking 10 minutes( assuming there are only there columns in the table,those are,

id, name, description)

id being identity column and has got index as well. What could be the reason and how would you identify and resolve the problem

3) How would you change the identity column value or can we change the identity value and how

4) How would you use the shared, update, exclusive locks in the sql statement.

Please let me know.

Regards,

nw

Hi Its Me

I dont know about the last question..

But for the first 2 question you can use SQL Profiler.

You need to check whether the query being locked by some other procedure..

or what happens to the query after it reach to the SQL..

I am new to it so cant help you much.

But i am sure someone else will help both of us to learn Profiler.

Good Luck.

Louis Davidson

Interview

1) A simple insert into the table (could be from .net application or query analyzer) takes 10 minutes and times out, does not do any thing. What could be the reason and how would you identify and resolve the problem.

Many many reasons. Probably something like (in this order of likelihood) Locks, lots of data, minimal hardware, a waitfor statement in trigger (don't say that if you are being interviewed unless you are feeling really confident!). etc

2) A simple select from table taking 10 minutes( assuming there are only there columns in the table,those are,

id, name, description)

id being identity column and has got index as well. What could be the reason and how would you identify and resolve the problem

See #1, other than the snarky trigger comment for the trivial reasons.

3) How would you change the identity column value or can we change the identity value and how

You cannot. You can insert a new row and pick your own identity, but the values are not updateable.

4) How would you use the shared, update, exclusive locks in the sql statement.

Look up locking hints and isolation levels. They control how you customize locks and length that locks are being held.

nwcomer

Thanks but how would you identify and resolve these issue.

What you mentioned I knew it could be one of those but how actually I come to know that this is the issue and fixing it.

Regards

nwcomer

How can I identify the problems based on reasons specified above

Regards,

Manivannan.D.Sekaran

Ok..

Use the following sps to identify which process is locking your resource. And try to kill them using KILL statement.

Code Snippet

Exec sp_lock

Exec sp_who

Go

Kill <spid which locks your db>

Go

Kill <spid which locks your db>

..

..

Kill <spid which locks your db>

If you are not sure who locks your resource, you can simply execute the below query, it might bring down your database offline for few seconds & again it will become online. Then try to execute the query.

It might fix if the database status affects your query.

Recommended to do it from the Server & you should have a SYSADMIN access to perform these operations.

Code Snippet

Use Master;

Alter database <database>

SET OFFLINE

WITH ROLLBACK IMMEDIATE;

GO

Alter database <database>

SET ONLINE

WITH ROLLBACK IMMEDIATE;

Louis Davidson

Sorry, I din't answer completely, but frankly the answer is so very broad in nature.

You need to get a book (one of the Inside books from Kalen Delaney for technical details), or read a lot of the stuff on the http://www.sql-server-performance.com/ website and others.

The starting tools are profiler (looking for high durations, CPU, and IO), as well as perfmon, looking at the hardware characteristics.

The dynamic management views have a wealth of information, if you get digging into them in 2005.

The problem is that there is no simple answer for your question. Frankly a lot of people make their living solving these sorts of problems. To say there could be a book written on the subject is not a joke: http://www.amazon.com/Server-Performance-Tuning-Distilled-Second/dp/1590594215/ref=sr_1_1/104-9469620-3506312 ie=UTF8&s=books&qid=1187313394&sr=1-1

My book on DB design is largely about how to avoid these situations, other than hardware, of course. Good design principles with an understanding of SQL Server coding and concurrency principles are all major requirements. Using SQL Server as it is designed to be used is the key to most every database problem. In essence, the goal is:

1. Do things in as few SQL Statements as possible

2. Reduce round trips to the server (1 call, either procedure or ad-hoc sql to do as much as reasonably possible.

There are many many contra-examples to these, but it is generally where to start.