SQL Profiler and Tuning Advisor

SQL Profiler and Tuning Advisor

This script can be used to determine if you have choosen the right indexes. You need to look at how often the index is used for seek and compare it to how often the index is updated. Seek performance comes at the cost of update performance. And what is worse, when index is frequently updated you causes the index to be fragmented and the statistics to be out of date.

You should also compare the range_scan_count to singleton_lookup_count. Range scan is preferred before singleton lookup. A singleton lookup may be the cause of and index seek and a key lookup operation. That is, for every row found in the index seek, sql will lookup the datapage in the clustered index, and that is okay for lets say a couple of thousands, but not for millions of rows.

CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
    @dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))

set @sql=N'select ''object'' = object_name(iu.object_id, iu.database_id)
        , i.name
        ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
        ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
        ,''user writes'' = iu.user_updates
        ,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
where 
    iu.database_id = ' + @dbid + '
    and iu.index_id=i.index_id
    and iu.object_id=i.object_id
    and (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'

exec sp_executesql @sql

set @sql=N'SELECT
   ''object'' = object_name(o.object_id, o.database_id),
   o.index_id,
   ''usage_reads'' = user_seeks + user_scans + user_lookups,
   ''operational_reads'' = range_scan_count + singleton_lookup_count,
   range_scan_count,
   singleton_lookup_count,
   ''usage writes'' = user_updates,
   ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
   leaf_insert_count,
   leaf_update_count,
   leaf_delete_count,
   ''operational_leaf_page_splits'' = leaf_allocation_count,
   ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
   ''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
   ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
   ' + @dbname + '.sys.dm_db_index_usage_stats u
WHERE
   u.object_id = o.object_id
   AND u.index_id = o.index_id
ORDER BY
   operational_reads DESC,
   operational_leaf_writes,
   operational_nonleaf_writes'

exec sp_executesql @sql

GO

SQL Profiler and Tuning Advisor for Reporting Services - what events should be selected?

Here's what was happening, and how to work around it.

It appears that Reporting Server caches data in a temp database. Since most of our reports all used a common view, all of that data was being retrieved from cache.

After selected "Show All Events", under "Stored Procedures" I selected CacheHit, CacheInert, CacheMiss, Completed, StmtCompleted, and StmtStarting.

There was then enough information in the trace file for the profiler to evaluate and make recommendations.

How to measure the performance of the Azure SQL DB?

Database Engine Tuning Advisor does not support Azure SQL Database. It is also not possible to create a trace file from an Azure SQL Database using SQL Server Profiler.

SQL Azure automates the creation of indexes that may improve performance of your workload with a feature named automatic tuning. Automatic Tuning on Azure SQL also drops redundant indexes and uses the best execution plan for queries

Alternative to SQL Server Tuning Advisor?

I highly recommend the free Qure Analyzer from DB Sophic:

http://www.dbsophic.com/qure-analyzer.html

Disclaimer: I work for SQL Sentry, who partners with DB Sophic on a paid version of their tools which work with our Performance Advisor product. This is not an attempt to up-sell, I am merely pointing you to a free tool that seems to meet your requirements.

You may also want to look at ClearTrace - it's not a one-for-one replacement for DTA, but it is quite useful if you're already collecting traces:

http://www.sqlmag.com/article/performance/cleartrace

Does SQL Server's Database Tuning Advisor modify data?

I've used it multiple times on mission-critical database with millions of records, while there were users modifying data in the database and it works perfectly for me. It has never modified or corrupted data. The only thing it's done is slow things down while it applied the index changes.

I know for sure that it does not re-run the statements, because I've got some processes that do modify the data. I intentionally ran those while doing the profiler, and then used the tuning wizard on it, and I would have known if these particular statements were re-executed. The work being done is very obvious and would have resulted in obvious discrepancies.

All of that said, it is a good idea to have a backup of your DB just in case.

Can we totally depend on Database Engine Tuning Advisor?

The database tuning advisor can be useful for tuning specific queries or a multitude of queries.

But

Just be aware that throwing a bunch of indexes on a table can help the performance of one query, while harming the performance of another. Additionally, the more indexes a table has, the longer writes take due to having to write to multiple indexes.

If you have especially long running queries, you can reference the tuning advisor's suggestions (but try to understand why it's suggesting them before throwing them onto a table willy nilly).

One other (potentially) nice feature is providing a trace file for the advisor to parse. If you are able to get a "like production" trace, that could potentially give you beneficial indexes to throw on several tables. By "like production" i mean if you can get a trace that represents production like behavior over a long period of time. One option is to trace production (See why this can be a bad idea https://dba.stackexchange.com/questions/818/using-sql-profiler-on-a-database-thats-in-production)... but be careful this can be pretty heavy handed. Here's a tutorial on how to do a smaller footprint trace without utilizing the UI: http://tranpeter.blogspot.com/2013/10/sql-server-proffer-offline.html

SQL Server: Event does not reference any tables (Tuning Advisor warning)

I think the reason you're not getting recommendations is because you don't have 'SHOWPLAN' permissions on your database. Grant the user you're running the analyzer that access and try again.

Also, I see some "invalid object name" errors as well -- make sure the user you are running the analyzer as has the appropriate permissions to all of the tables involved.

SQL Tuning Advisor provides many suggested indexes that share many the same columns.. should I add all of them?

You are entering a very complex area of DBA work. When do I have to many indexes? and when can I use more coverng indexes?

If the table is written to a lot, you need to ensure that you do not have many indexes as this will degrade the insert/update/delete performance.

Also, you do not want over lapping indexes, as this will waste storage space.

What you do need to note though is that the order and columns selected for the index does matter.

I always use a telephone dictionary as an example.

If your index in the back was by firstname, lastname , a serch for surname = Smith would result in an entire index scan, if not a table scan.

So choosing your indexes is very important.

And also remember the use of included columns, this would greatly improve query performance where you find key lookups in the execution plans, but the trade off is that you use additional storage space.

Using an index advisor is great if you are starting to learn about query optimization, but I would recomend rather understanding the execution plans, and also narrowing down which type of queries you need the most for certain tables.

---

Related Topics