Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
 Posted - 2009-02-25 : 08:54:48
|
| Dear All,i've faced a question in an interview that "When will you tune your database and How"?dear experts please shre your experience....--------------your experience is someone's life.-----------ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-25 : 09:16:25
|
| When there numerous poorly written code Which results in Blocking,Locking/Deadlocking and doesn't have indexes. --Efficient use of index--Efficient use of maintenance jobs--Efficient use of smartly written code. |
 |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2009-02-25 : 09:24:55
|
| blocking we can resolve some what by running the DML operations like batches.locking we can prevent byusing nolock hintdead locks SQL server itself will take care. we need not to act on it...am i correct?and regarding index, defragmentation, rebuild, creating indexes on required columns... is enough------------i'm not sure of maintenance jobs-----------and smartly written code means, queryies should use 'exists' inplace of 'in' and instead of select count(*) from table_name, we can use SELECT rows FROM sysindexes WHERE id = OBJECT_ID('<table_name>') AND indid < 2 ----------like thisam i correct? please correct me where ever i'm wrongArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-25 : 09:47:40
|
quote:
Originally posted by sunsanvin
blocking we can resolve some what by running the DML operations like batches.locking we can prevent byusing nolock hintdead locks SQL server itself will take care. we need not to act on it...am i correct?and regarding index, defragmentation, rebuild, creating indexes on required columns... is enough------------i'm not sure of maintenance jobs-----------and smartly written code means, queryies should use 'exists' inplace of 'in' and instead of select count(*) from table_name, we can use SELECT rows FROM sysindexes WHERE id = OBJECT_ID('<table_name>') AND indid < 2 ----------like thisam i correct? please correct me where ever i'm wrongArnavEven you learn 1%, Learn it with 100% confidence.
Well the question you are asking is real broad.If it was single topic I would explain it deeply.I would look at this area to answer your question.-- How to resolve blocking/locking and Deadlocking?-- How to read execution plan and know what to do?-- How to implement maintenance jobs?-- How to resolve statistics issue and fragmentation issues?-- Difference between IN vs Exists Or Joins?-- How to read Standard Reports?-- How to accurately resolve performance issues of STORED PROCEDURES,UDF?-- Isolation Level and lock hints?-- Use of all kinds of index..... and more. |
|
|
|
|
|
|
|
|
Whar are the Performance tuning steps you follow