September 14, 2013 at 8:45 am
There are four questions nee all help, many thanks!
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?
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?
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?
4. when the database has deadlocks, how can we know which (one) processes cause these deadlocks?
September 15, 2013 at 4:17 pm
892717952 (9/14/2013)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?
That depends. If you have a live system, the missing_indexes DMVs can help you; if the candidate index is not in that DMV, the index may not help.
But if you are working on a new system, and do not have any production-like data, it can be difficult. You will have to rely to your skills and understanding of idnexes.
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?
Indexed views is fairly advanced feature, and not all views are indexable. Go with indexes for tables in the first place.
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?
Not that I know, but the Red Gate people here may have something up their sleeves. In any case, what is "better" is not always obvious. There are some bad things you should avoid, but there can be several different constructs that can be considered "good" - but still perform bad in some situation.
4. when the database has deadlocks, how can we know which (one) processes cause these deadlocks?
The system-health session tracks deadlocks. You can also get deadlock information in the SQL Server errorlog by enabling trace flag 1222. A deadlocks is rarely due to a single process, but rather the coincidence of two or more processes.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 19, 2013 at 3:48 pm
1) If you know that certain columns will often be used as search criteria (such as an employee ID or sales close date), those would be great candidates to include in indexes.
If certain columns will often be requried in query result sets but others will not be (such as if you're using a view, but for other situations like reporting as well) then a covering index including these columns is a great idea because the query can read only the index and skip the table itself, which may be much larger if there are many columns not needed for the query.
3) In SQL Management Studio you can highlight different versions of a query and view an Estimated Execution Plan to compare their relative cost.
May 10, 2019 at 2:28 am
Thanks for your kind help !
May 10, 2019 at 12:44 pm
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply