SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tips to optimize your SQL statements


Tips to optimize your SQL statements

Author
Message
vevoda.ulath
vevoda.ulath
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 396
Nice article, thx !! Althought all tips are pretty well known, it's nice have them at one place and check if one didn't forget anythingSmile
Nadrek
Nadrek
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2481 Visits: 2733
philcart (8/3/2010)

As previously stated, the cost of adding and updating the indexes is actually a lot smaller than it's made out to be. This is the same sort of thing spouted by people that say you should always rebuild the indexes when generally updating statistics is all thats required.

More often than not, the problem with a poorly performing query are the cases you've specified, but what if you're dealing with a vendor application where code changes could take months, if they ever happen at all?

Better to wear the impact of applying an index rather than leaving the query to perform poorly.



I agree that it's all a matter of the precise situation one is in at the time, and the likely future.

I would argue that all other things being equal, tune the query before adding an index. When all other things are not equal (lousy vendor SQL), make the best compromise within your constraints as possible, whether that be adding indexes or buying SSD's or moving tables around filegroups or changing configuration options or whatever else is within your power.

For indexes, I still stand by the premise that in at least some situations, the additional index maintenance, fill factor tuning, drive space required, and backup/restore time can be significant. I would agree that in most cases the added time for updates and inserts is minimal; but a large index created with the default fillfactor of 100 on key columns that are often changed or added to essentially randomly (date of birth, SSN, address and name fields are good examples here) can grow very quickly due to fragmentation; that's one cost to be aware of - either the fragmentation and space taken up, or the time the regular maintenance of the index takes, or the time taken to correctly set the fillfactor, or more likely some combination of the those.

In all cases indexes take space - if you have a bad vendor database with lousy SQL and a poor design where everything is a CHAR(too many) and there are millions of rows, then indexes on that will take a fair bit of space, too.
Lempster
Lempster
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3667 Visits: 1657
Interesting that you think Developers should be the people responsible for performing the very useful tips you recommend. Everywhere I've worked it has always fallen to DBAs to check execution plans, create suitable indexes, review stored procs etc.
I think there is a divide between Developers and DBAs which both sides need to work to close; we can both learn from each other.

Regards
Lempster
philcart
philcart
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5421 Visits: 1441
Database Developers and those developing on databases, should know enough to code efficiently without having it "...always fall to the DBA."

If you're in the position of being the sole database person fighting against a slew of developers, then educate them. It takes next to no time for developers to look at an execution plan and flag and possibly correct the obvious issues.

One developer I worked with actually found it easier to work through the execution plan than try to create the best query from scratch. From his point of view, the execution plan explained the query procedurally, just like his .Net code.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Lempster
Lempster
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3667 Visits: 1657
It takes next to no time for developers to look at an execution plan and flag and possibly correct the obvious issues.

I don't disagree with the general sentiment that developers should be encouraged and educated to think about execution plans, indexes, SARGable clauses etc., but I do take issue with your statement above; they would need to know how to read an execution plan and look for areas of improvement and that's a skill that takes time to accquire.
It can be very difficult to initiate a change in the division of tasks between developers and DBAs if working practices have 'always been done this way'. I'm not saying it's right, it's just the way it is sometimes!

Regards
Lempster
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4844 Visits: 2629
So I read the article and ran the query for missing indexes ordered by performance impact.

The first 4 missing indexes were for the same table. Take a look at the attachment. The recommendation is for 4 amazing similar and mostly overlapping indexes. In this case I would be inclined to create an index on IWNROV, IWSVCD, IWNBFY, and include columns IWCNBR, IWCEK6, IWSPY8, IWDRAT, IWAKBN, IWBICH

I would appreciate comments on the appropriateness of my approach.

Thanks
Attachments
missingindex.JPG (13 views, 31.00 KB)
mtassin
mtassin
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5370 Visits: 72521

5) Check for any table or Index scans. Using the execution plan, it's easy to identify if the SQL statement is performing any table or index scans. In the majority of cases (assuming that statistics are up to date), this indicates that an index is missing. These three DMVs might prove useful to identify missing indexes:


This should be reworded to Check for any table or Clustered Index Scans. Regular non-clustered index scans can happen for many other reasons, including pulling a wide enough range of data from the table that an index scan is warranted, somebody using a non-deterministic function in a where clause, etc.

Creating a new index in an attempt to cover an index that an existing index scan is likely serving won't necessarily generate an index seek afterwards.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24362 Visits: 14905
Good article. I would only mention that in order to fix ordering issues you don't necessarily need to change the clustered index. ALL indexes are sorted, so a correct index may remove the sort and changing the query may remove the sort operation as well. For instance if you have an index on LastName, FirstName but then you sort on FirstName, LastName, the index may still be used but you'd have to have the sort operation in the query, where an index on FirstName, LastName would likely serve the results up in ordered fashion so the sort operation would be unnecessary.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24362 Visits: 14905
Daniel Bowlin (2/10/2012)
So I read the article and ran the query for missing indexes ordered by performance impact.

The first 4 missing indexes were for the same table. Take a look at the attachment. The recommendation is for 4 amazing similar and mostly overlapping indexes. In this case I would be inclined to create an index on IWNROV, IWSVCD, IWNBFY, and include columns IWCNBR, IWCEK6, IWSPY8, IWDRAT, IWAKBN, IWBICH

I would appreciate comments on the appropriateness of my approach.

Thanks


First, I'd recommend creating a separate thread for this conversation as I foresee it being possible lengthy.

Second, before I'd add an index I'd evaluate the existing indexes to see if there is one I could alter to meet this need. I've been planning on working on some code that does a comparison between the missing index information and existing indexes. I've seen many times where there is an existing index with a different order or just missing one of the columns. I'd also evaluate the queries that are causing the missing index recommendations. It may be that you don't really need all the included columns for the majority of the columns. While eliminating bookmark lookups is often a quick performance enhancer, it may not always be the best thing.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Eric M Russell
Eric M Russell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16878 Visits: 10949
1) Check if you're missing any required table joins. This can easily happen and the output will be a Cartesian join ...

2) Check if you're missing any required WHERE clause. A missing WHERE clause will return more data than needed ...

It's interesting that your first two reccomendations would be to confirm that the query has the required JOIN and WHERE clause criteria. This type of thing is so obvious that most performance tutorials jump right into analyzing wait states, query execution plans, and indexing. In other words, often times we start with the assumption that the SQL is logically correct, and our optimization is focussed just on returning the same resultset in a more efficient way.
However, having worked for a company that completed mergers with several other companies in the industry, a big part of my job has been refactoring legacy databases. I've seen a lot of SQL code from different teams; and much of it poorly written. The scenario, where a developer basically wraps a DISTINCT clause around a SELECT statement performing what amount to a cartesian join; I see that a lot.
Another scenario is where the developer includes un-needed table joins and returns additional columns that are never referenced by the application or reporting tool. I've even seen procedures that query an interim result into a temp table, which is then never used. This is often the result of a developer wrting a new stored procedure by copy/pasting from an existing stored procedure, and then failing to remove those table joins, columns, and code that arn't needed.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search