1. when I create an index in a table or views, is there any good way to konw (or evaluate) the index is necessary and correct?
Yes. It involves precisely the same method you would use to determine if your code is working correctly. You need to build a shedload of test data and that's not really a difficult thing to do once you know how.
2. do we need to create one index in a view in which situation? if we create some indexs in the tables which is being used in a view, for this view, do we need to create a index for the view?
Possibly but, again, if you don't have data to test against, you'll need to build a shedload of it to know for sure. There's not much of a difference between writing an index for a view and writing an index for any other query with the exception being an "materialized" view, which is also known as an "indexed view".
3.when we use differenct T-SQL script to get same result, is there any tool to know which kind of T-SQL is better?
Yes... there are actually quite a few. SQL Profiler or Extended Events can help a whole lot here. You don't actually need either of those, though. The old tried and true method of simply capturing the date and time of when something starts and when it ends and then calculating the difference between those as a duration can be very effective. While that doesn't tell you the internal nuances of things like the number of Logical or Physical Reads or the amount of CPU time that was brought to bear in the runs, it's a really good first indication of which is better because the other things will frequently (usually, but there are exceptions) follow the duration. Again, you need a lot of test data to know for sure and it's just not that difficult to build such test data for the particular query you're working on once you know how.
The use of the ACTUAL Execution Plans also help a whole lot in this area BUT, there's an extreme caveat... the Execution Plans can seriously aid you in determining what the actual resources used where but even the ACTUAL Execution Plan is fraught with "cost-based estimates". The key word there is that they are estimates and you should NEVER use the Execution Plans to make the final decision as to which code is actually better for duration and resource usage. I've demonstrated many times how what it suggests is the best (especially when it comes to "Percent of Batch") can actually be the diametric opposite what actually occurs during the runs.
4. when the database has deadlocks, how can we know which (one) processes cause these deadlocks?
See Erland's post above.
is pronounced "ree-bar
" and is a "Modenism
" for R
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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)