Browse by Tags

  • Table Information including Index information (Usage, Blocks and Reads)

    The query below combines these three queries into one: Index Usage Query Recent Bocking History Table Information Query It can be used to just see the number of records in each table. But also by just changing "ORDER BY", it can be used to see which index cause most blocking / wait time / updates or locks. Or to compare Index Updates with Index Reads to get an idea of cost versus benefit...
    Filed under: ,

  • Index Usage Information Tool

    Attached at the end of this post is a set of NAV objects that collects index usage information, and displays it in a NAV client so that you can sort tables by no. of Indexes / Index Reads / (Index) Block wait time, etc.: The tool is using these three SQL Queries / DMVs: sys.indexes: Basic information about indexes sys.dm_db_index_usage_stats: No. of Index Reads, updates, etc sys.dm_db_index_operational_stats...
    Filed under:

  • How to read a SQL Profiler trace file into a SQL table

    Just a small tip that I use often when I have a large SQL Profiler trace. Rather than reading through trace files line by line, you can read it into a table and then query it. This syntax creates a new table in SQL Server and loads your trace file into it: SELECT * INTO MyTraceTemp FROM :: fn_trace_gettable ( 'c:\x\MyTrace.trc' , default ) The default parameter means that if the trace is across...
    Filed under:

  • SQL Query to show tables, their indexes and index usage

    The SQL query below shows a list of tables sorted by the largest tables first. For each table, the indexes in the table are shown. For each index it shows when the index was last used. The query is designed to collect various pieces of information in one place, and give an overview to help supporting decisions when doing index tuning. The left hand columns show data for the table (No. of receords,...
    Filed under:

  • Beware the SQL Index property on NAV 5.0 SP1

    The discussion of the changes starting with Microsoft Dynamics NAV version 5 regarding the use of Dynamic cursors has already been addressed in the article titled "Cursor Types" on the SE Blog - http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/ However, we are seeing more and more cases where the use of the SQL Index property in NAV 5.0 SP1 is causing performance issues...
    Filed under: ,