SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Brian Ellul / Tips to optimize your SQL statements / Latest Posts

RE: Tips to optimize your SQL statements

quackhandle1975 — Thu, 12 Apr 2012 11:18:13 GMT

Good article for Prod DBA's trying to sort out slow running queries created by junior developers. ;-)[b]qh[i][/i][/b]

RE: Tips to optimize your SQL statements

mtassin — Fri, 10 Feb 2012 14:44:18 GMT

[quote][b]Daniel Bowlin (2/10/2012)[/b][hr]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, IWBICHI would appreciate comments on the appropriateness of my approach.Thanks[/quote]That's typical from those DMVs, the next trick is to see if you either have an index that comes close to covering them all, and modify it to do so, or find an index or 2 that will cover all the missing ones.I like using those DMVs as a starting spot. I then have to work with the data they provide to figure out which indexes really belong there. I remember once when I created a recommended index, and it said that I still needed to make the same one again. Turns out there was a non-SARGable Where clause getting in the way. Changed the query and it picked up the index just fine.

RE: Tips to optimize your SQL statements

mmartin1 — Fri, 10 Feb 2012 14:33:52 GMT

I don't think the index is as bad as it sounds. Without a good index a process has to lock the whole table (on a table scan) instead of taking a more granular lock. On an insert not all the records need to be re-indexed, (I think just those in the page). But if we are talking about an operational database being heavily queried from end users, then a data warehouse database could be in order where, say, one day old data is acceptable.

RE: Tips to optimize your SQL statements

tabinsc — Fri, 10 Feb 2012 13:24:25 GMT

Sometimes you don't need a new index, but simply need to add an additional column or two to an existing index. I've gotten in the habit of looking at all existing indexes first to see if any of them should cover the query.And yes it is necessary to know your ratio of reads to inserts/updates/deletes. In my case I have one table with millions of rows, and there is only one process doing the inserts & updates, while hundreds of processes read from this table. For this table the numerous indexes are more beneficial, but I don't add one until I've ruled out every other possibility. With only one process doing inserts, I have better control of how the data gets in the table, and I can set & adjust thresholds every so often.And once in a while, you'll find that the only way to improve performance is to redesign the table layout. I did that just once, because I despise having to scan a table multiple times to get the data I need from it.

RE: Tips to optimize your SQL statements

sherifffruitfly — Fri, 10 Feb 2012 08:05:16 GMT

Oh welp there you go then! :)Now back to figuring out how to get the right passwords in my deployed ssis package's connection managers. #@$@#%@

RE: Tips to optimize your SQL statements

Jack Corbett — Fri, 10 Feb 2012 08:02:18 GMT

[quote][b]sherifffruitfly (2/10/2012)[/b][hr]Friend of mine told me to order my joins so that the greatest expected number of rows are eliminated soonest. Unfortunately, I've never been clear on just how to do that.[/quote]Your friend isn't exactly correct. The optimizer will re-arrange join order to get the best plan, it will also change LEFT JOIN's to RIGHT JOIN's if that is what it thinks is the fastest way to return the data. You supply the FORCE ORDER hint, but unless you are a superstar with the optimizer like Paul White, I wouldn't recommend it. Let the optimizer do it's job.I think what your friend means is that if you have a customerTypes table that has 10 rows, put that first and then JOIN to the customers table which has 100000 rows, especially if your WHERE clause has customerType = 'RETAIL' so you are only really looking for one customer type. As I said though, the optimizer will do that automatically.

RE: Tips to optimize your SQL statements

sherifffruitfly — Fri, 10 Feb 2012 07:50:36 GMT

Friend of mine told me to order my joins so that the greatest expected number of rows are eliminated soonest. Unfortunately, I've never been clear on just how to do that.

RE: Tips to optimize your SQL statements

Eric M Russell — Fri, 10 Feb 2012 07:46:34 GMT

[quote]1) Check if you're missing any required table joins. This can easily happen and the output will be a Cartesian join ...[/quote][quote]2) Check if you're missing any required WHERE clause. A missing WHERE clause will return more data than needed ...[/quote]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.

RE: Tips to optimize your SQL statements

Jack Corbett — Fri, 10 Feb 2012 07:41:41 GMT

[quote][b]Daniel Bowlin (2/10/2012)[/b][hr]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, IWBICHI would appreciate comments on the appropriateness of my approach.Thanks[/quote]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.

RE: Tips to optimize your SQL statements

Jack Corbett — Fri, 10 Feb 2012 07:35:06 GMT

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.

RE: Tips to optimize your SQL statements

mtassin — Fri, 10 Feb 2012 07:32:30 GMT

[quote]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: [/quote]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.

RE: Tips to optimize your SQL statements

Daniel Bowlin — Fri, 10 Feb 2012 07:23:49 GMT

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, IWBICHI would appreciate comments on the appropriateness of my approach.Thanks

RE: Tips to optimize your SQL statements

Lempster — Tue, 24 Aug 2010 05:15:44 GMT

[quote]It takes next to no time for developers to look at an execution plan and flag and possibly correct the obvious issues.[/quote]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 [i]how[/i] 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!RegardsLempster

RE: Tips to optimize your SQL statements

philcart — Tue, 24 Aug 2010 03:56:00 GMT

[b]Database[/b] 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.

RE: Tips to optimize your SQL statements

Lempster — Tue, 24 Aug 2010 01:51:25 GMT

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.RegardsLempster

RE: Tips to optimize your SQL statements

Nadrek — Tue, 03 Aug 2010 07:21:32 GMT

[quote][b]philcart (8/3/2010)[/b][hr]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.[/quote]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.

RE: Tips to optimize your SQL statements

vevoda.ulath — Tue, 03 Aug 2010 06:20:42 GMT

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 anything:)

RE: Tips to optimize your SQL statements

philcart — Tue, 03 Aug 2010 02:14:47 GMT

[quote][b]wbrianwhite (7/31/2010)[/b][hr]http://blogs.msdn.com/b/sqlazure/archive/2010/07/27/10043069.aspx[quote]Just a note, non-clustered indexes (what you make when you do a covered index) reduce the performance of your writes. This is because on insertion or updates, the indexes need to be updated. So you need to balance your covered index creation with the ratio of reads and writes to your database. Databases with a disproportion amount of reads to writes gain more performance from covered indexes.[/quote]Indexes DO affect your write performance. It is not theoretical. You have to seek a balance between reducing I/O by adding indexes and reducing insert efficiency by adding indexes. If you add a new covering index for every bad query it will have significant effects. If you can improve the query without changing the indexes, it is a win/win. In my shop the DBAs monitor poor-performing queries and make recommendations to the devs for re-writes and/or indexes. Some common problems in queries that can be fixed in the code: non-SARGable clauses, writing a where clause that tests where a or b instead of writing two selects with simple where clauses that are unioned together, cursor usage, not specifying a known column in the where clause when that would let you take advantage of a clustered index.[/quote]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.

RE: Tips to optimize your SQL statements

arun1_m1 — Tue, 03 Aug 2010 01:44:35 GMT

Thanks for the info

RE: Tips to optimize your SQL statements

wbrianwhite — Sat, 31 Jul 2010 18:44:33 GMT

http://blogs.msdn.com/b/sqlazure/archive/2010/07/27/10043069.aspx[quote]Just a note, non-clustered indexes (what you make when you do a covered index) reduce the performance of your writes. This is because on insertion or updates, the indexes need to be updated. So you need to balance your covered index creation with the ratio of reads and writes to your database. Databases with a disproportion amount of reads to writes gain more performance from covered indexes.[/quote]Indexes DO affect your write performance. It is not theoretical. You have to seek a balance between reducing I/O by adding indexes and reducing insert efficiency by adding indexes. If you add a new covering index for every bad query it will have significant effects. If you can improve the query without changing the indexes, it is a win/win. In my shop the DBAs monitor poor-performing queries and make recommendations to the devs for re-writes and/or indexes. Some common problems in queries that can be fixed in the code: non-SARGable clauses, writing a where clause that tests where a or b instead of writing two selects with simple where clauses that are unioned together, cursor usage, not specifying a known column in the where clause when that would let you take advantage of a clustered index.

RE: Tips to optimize your SQL statements

SQLRNNR — Fri, 30 Jul 2010 22:36:03 GMT

Thanks for the article.

RE: Tips to optimize your SQL statements

philcart — Fri, 30 Jul 2010 18:20:18 GMT

[quote][b]Nadrek (7/30/2010)[/b][hr]...When writing new SQL, why not take a little more time and do it well, instead of randomly? For existing bad SQL, most often I see that a few minutes rewriting is extremely beneficial, and has no bad side effects....[/quote]That may be fine if you're the one in control of the code being written. However, if you're like the majority of DBA's in the workplace, quite often that is not the case. You'll have to deal with development team and/or external vendors. Maybe even get managers approval for changing work priorities and any additional charges.Case in point, a vendor application we have running had a report that took 10-15 minutes to run. The end-user spoke directly to the vendor who quoted 4 hours of work to fix the report. This work was done and implemented and the report took just a few minutes. In the next invoicing cycle the extra 4 hour charge was rejected as it hadn't been approved. Naturally the vendor wasn't pleased about this and they reversed their change and left the user with the slow report. At this stage the user decided to speak with me and within 5 minutes I had identified a lack of indexes (there were none on the reporting tables). After creating some indexes the same report took 2-3 minutes to run. Later that week, when I had some free time, I decrypted the vendors procedure, pulled apart the mess and got the report to run in less than 30 secs. Naturally being vendor code I couldn't deploy the changes I'd made, but given I have a good relationship with them, I sent the changes through their developer.Three months later when we performed an upgrade on the application, the report that was running in 2-3 minutes started to run in less than 30 sec.[quote][b]Nadrek (7/30/2010)[/b][hr]...Essentially: Why add cost in one place to reduce cost in another before you attempt to reduce cost without adding any?[/quote]Easy answer, add the minimal (yes minimal, despite your assertions) cost in the short term (ie: getting the end-user a report they require), then see about change approval, testing, etc... of re-written code.

RE: Tips to optimize your SQL statements

Nadrek — Fri, 30 Jul 2010 14:23:23 GMT

[quote][b]philcart (7/30/2010)[/b][hr]@wbrianwhite & @NadrekThe cost of adding and updating the indexes is actually a lot smaller than it's made out to be. Only under extreme change (meaning insert/update/delete) activity will you notice performance degradation from index updates. I think it would be safe to say that a vast majority of SQL Server databases don't fit into the "extreme" category.For most businesses, the cost of adding the index is much less than the cost of re-developing a query/procedure. Especially when it's vendor supplied code and they have to pay for code changes ;)Yes, in some cases a re-write is warranted, but why not give the end users some benefit straight away and put any re-writes on the work queue?[/quote]The operational cost includes disk space to house the index, disk space and time to back it up, time to correctly figure out the right fillfactor, and maintenance window time to defragment the index regularly (however often or rare that happens to be - the default fillfactor of 100 tends to be pretty bad about requiring maintenance).When writing new SQL, why not take a little more time and do it well, instead of randomly? For existing bad SQL, most often I see that a few minutes rewriting is extremely beneficial, and has no bad side effects.In one nontrivial case, 45 minutes of rewriting resulted in four orders of magnitude of performance improvement. In many cases, 5 to 10 minutes of rewriting results in anywhere between double and an order of magnitude more performance.Essentially: Why add cost in one place to reduce cost in another before you attempt to reduce cost without adding any?

RE: Tips to optimize your SQL statements

philcart — Fri, 30 Jul 2010 13:44:05 GMT

@wbrianwhite & @NadrekThe cost of adding and updating the indexes is actually a lot smaller than it's made out to be. Only under extreme change (meaning insert/update/delete) activity will you notice performance degradation from index updates. I think it would be safe to say that a vast majority of SQL Server databases don't fit into the "extreme" category.For most businesses, the cost of adding the index is much less than the cost of re-developing a query/procedure. Especially when it's vendor supplied code and they have to pay for code changes ;)Yes, in some cases a re-write is warranted, but why not give the end users some benefit straight away and put any re-writes on the work queue?

RE: Tips to optimize your SQL statements

wbrianwhite — Fri, 30 Jul 2010 11:10:05 GMT

[quote]It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?[/quote]Indexes add a marginal performance hit as each row insert/update has to then go and update all the affected indexes. They are an essential tool, but shouldn't be used in place of sane queries.

RE: Tips to optimize your SQL statements

Nadrek — Fri, 30 Jul 2010 07:42:26 GMT

[quote][b]philcart (7/29/2010)[/b][hr]Would have thought that this is part of an investigation/monitoring phase. Once you've identified a query that needs attention, you run though the great list provided by Brian.It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?[/quote]Updated statistics, certainly; that's a normal operational cost. However, adding an index adds to the operational costs of writes to that table for the lifetime of the index; perhaps a simple, or even not so simple, rewrite would allow the query to improve performance without adding any additional costs?Perhaps more importantly, how will you learn what types of query implementations work better in which circumstances on your particular environment without testing them? This is critically important in some cases, particularly for writing new SQL in an environment that's heavily loaded for whatever reason; if there's little headroom, then using as little as possible of what's left is vital, in addition to freeing up more headroom.

RE: Tips to optimize your SQL statements

dfortier — Fri, 30 Jul 2010 07:35:39 GMT

Very good information, thanks for sharing.Don

RE: Tips to optimize your SQL statements

brian118 — Fri, 30 Jul 2010 00:33:09 GMT

[quote][b]cameron.wells (7/29/2010)[/b][hr]Thanks for the great check list, but i also want to take some of your other advice that this doesn't substitute reading books and white papers on SQL Performance. Can you please recommend several of the books you've read that you believe would be most beneficial to read for another DBA?e-mail is cameron.g.wells@gmail.comThanks,Cameron[/quote]Dissecting SQL Server Execution Plans (by Grant Fritchey)SQL Server 2008 Query Performance Tuning Distilled (by Grant Fritchey & Sajal Dam)

RE: Tips to optimize your SQL statements

brian118 — Fri, 30 Jul 2010 00:26:49 GMT

Thanks to all for your feedback!

RE: Tips to optimize your SQL statements

philcart — Thu, 29 Jul 2010 23:02:40 GMT

[quote][b]Nadrek (7/29/2010)[/b][hr]I didn't see a very important one:Pull up SQL Server Profiler, and watch the SQL:BatchCompleted and/or RPC:Completed numbers for CPU, Reads, Writes, and Duration. Know which is most important in your environment.Then re-write the statement a few different ways against the full dataset (always more than once), and see, in your actual environment, how each works. Derived tables vs temp tables vs table variables vs CTE vs straight joins, one large statement vs smaller statements, etc. etc.Know how much resources a given query "should" take; check if hints help, or hurt, and by how much, and decide if they're worth it or not. This is a reasonable way to find missing indexes or SQL Server generating bizzarely suboptimal query plans, by going "Hey! That's too much... why is that too much?"[/quote]Would have thought that this is part of an investigation/monitoring phase. Once you've identified a query that needs attention, you run though the great list provided by Brian.It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?

RE: Tips to optimize your SQL statements

cameron.wells — Thu, 29 Jul 2010 19:23:40 GMT

Thanks for the great check list, but i also want to take some of your other advice that this doesn't substitute reading books and white papers on SQL Performance. Can you please recommend several of the books you've read that you believe would be most beneficial to read for another DBA?e-mail is cameron.g.wells@gmail.comThanks,Cameron

RE: Tips to optimize your SQL statements

wbrianwhite — Thu, 29 Jul 2010 15:40:40 GMT

Make sure your where clause is SARG-able or indexes won't help:http://weblogs.sqlteam.com/dang/archive/2009/03/07/Low-Hanging-Fruit-of-Sargable-Expressions.aspx

RE: Tips to optimize your SQL statements

Nadrek — Thu, 29 Jul 2010 14:21:16 GMT

I didn't see a very important one:Pull up SQL Server Profiler, and watch the SQL:BatchCompleted and/or RPC:Completed numbers for CPU, Reads, Writes, and Duration. Know which is most important in your environment.Then re-write the statement a few different ways against the full dataset (always more than once), and see, in your actual environment, how each works. Derived tables vs temp tables vs table variables vs CTE vs straight joins, one large statement vs smaller statements, etc. etc.Know how much resources a given query "should" take; check if hints help, or hurt, and by how much, and decide if they're worth it or not. This is a reasonable way to find missing indexes or SQL Server generating bizzarely suboptimal query plans, by going "Hey! That's too much... why is that too much?"

RE: Tips to optimize your SQL statements

ehinrichsen — Thu, 29 Jul 2010 09:48:32 GMT

What perfect timing, the sp_updatestats stored procedure took a query that was taking 3 minutes to run down to 1 second.

RE: Tips to optimize your SQL statements

Daniel Bowlin — Thu, 29 Jul 2010 06:59:07 GMT

Great article. Lots of good things to be on the look out for. I am going to have to come back to this article a bunch of times. I wrote a query yesterday that could probably use a spin through the last half of this article. Thanks.

RE: Tips to optimize your SQL statements

bbop1322 — Thu, 29 Jul 2010 06:47:46 GMT

Thanks for the list, it's a great starting place!

Tips to optimize your SQL statements

brian118 — Wed, 28 Jul 2010 21:14:04 GMT

Comments posted to this topic are about the item [B]Tips to optimize your SQL statements[/B]