Tag Info

Hot answers tagged

7

how can I utilise SQL Server tools to identify query optimisations, missing indexes etc? No tool is perfect and you should not rely implementing the suggestions without proper testing your entire business life cycle. Native tools like DTA are OK, but I have seen many problems with using it. I would not rely on it. That being said, SQL Server exposes a ...


4

{ This is a little lengthy, perhaps, but your actual problem(s) cannot be solved by looking at execution plans. There are two main issues, and both are architectural. } Distractions Let's start with the items that are not your major problem areas. These are things that should be looked into as it definitely helps improve performance to use the datatypes ...


4

In what situations is varchar(max) preferred Commenters have addressed this point in detail already. I'd say that VARCHAR(MAX) generally makes sense if you are 100% sure that the column will never need non-ASCII characters and the column's maximum length is either unknown or greater than 8,000 characters. You can read ...


4

I have a broad question regarding SQL Server... Depending on which server version you have access to, the tools vary. Microsoft has an entire documentation section on optimization tools for each of the different server versions. Despite the differences, the fundamentals are the same across most versions. Missing indexes, for example, are addressed in ...


1

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on ...


1

For it worth : I"ve ran optimize table on the main tables of a database who works under heavy load. The main tables have been under great load of inserts/updates/deletes. The optimize reduced the table sizes by 90% and improved the system performance by more than twice !


1

Since #1 requires copying data from the master to the child while it is in an active production environment, I personally went with #2 (creating a new master). This prevents disruptions to the original table while it is actively in use and if there are any issues, I can easily delete the new master without issue and continue using the original table. Here ...


1

Learn about composite indexes; use them where appropriate. For example: ON cpr.product_id = cp.id AND cpr.branch_id = 3 begs for either of these: INDEX(product_id, branch_id) INDEX(branch_id, product_id) Indexing flags (such as active) rarely useful. Don't use LEFT unless the "right" table has optional data. What do you expect the ORDER BY to do if ...


1

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need ...



Only top voted, non community-wiki answers of a minimum length are eligible