SQL Performance Query Tips and Techniques

This was a very useful talk on Optimization of SQL queries and how to discover where bottlenecks are within a database. Some of the things picked up that I have listed below may be of some help across CIS to improve the performance of queries. There are more optimizations in the slides which I will bring back.



Grouping Sets (SQL 2008)

Group by grouping sets (a, (b), (a,b,c))

Avoids having to use Unions:-

Group by a
union all
...
Group by b
Union all
...
Group by a,b

When to Optimize T-SQL Code

The users point out when to optimize
"SQL Server is running slowly"
Or proactive optimization

What's running slowly (SQL 2005)

Select * from sys.dm_os_wait_stats
Select * from sys.dm_exec_query_stats

Can help deduce where the bottlenecks are and the most frequent run statements. Also got shown how to use Database Tuning Advisor which can tune multiple databases at once. Can be run without taking the system down which will bring back a reduced set of recommendations how to increase the performance of a database. A further set of recommendations can be found by taking the database offline. You can also get the application to apply the recommendations you approve at a scheduled time/date possibly out of office hours.

Auto Parameterisation of Queries

This is bad! When constructing your queries e.g.
...
Where price = 17.99
SQL Server will guess the format of this number as Decimal(4,2)
Changing any part of the query (i.e. Where price = 7.99 or adding a --comment at top of query) will cause SQL to use a different query plan every time you change the query.

If you always specify the format of your where e.g.
...
Where price = £17.99
You are telling SQL Server that this value is MONEY. This will now always use the same query plan. It stops guessing the format of your parameter as you are telling it what it is!





And a picture for this morning...

View of the exhibition hall. Lots of free goodies to be found here!