locked
Querying the most frequently referenced tables/columns RRS feed

  • Question

  • running MSSQL 2005.

    I'm on a performance tuning contract. Customer basically has about 600 DBs with identical design. In each DB there are several hundred very flat tables, with indexes on every single column (never using SPs--only dynamic SQL). Thats the performance problem right there. I can't do anything about the no-SPs issue because they're using PERL DBI in their architecture, so I'm focusing on making the tables efficient.

    Anyway, I'm trying to approach this systematically, and have run into a snag. I know I can run profiler, grab a trace, drop it into the tuning wizard to give me the most frequently accessed tables, but I'm wondering if there's a faster way to get that data, even in raw form. I'm dealing with 600 DBs and I really don't want to trace and analyze all that. I need the common threads.

    What I need is basically: tablename, num_of_statements...something I could just run against all DBs, dump to a table and sort.

     

    Is there anything? I've been doing this for 10 years and haven't ever needed it.

    Tuesday, January 18, 2011 5:22 PM

Answers

  • All DBs pretty much cause problems, but smaller ones less-so.

    I ended up writing something very similar to this one above about the wait-stats. I also scripted out the SQL for the standard report "Index Usage Statistics". I stuck all in a loop over all customer databases, and dumped it out to a table.

    Now I've got many hundreds of thousands of records to parse through, but it's yielding good results so far.

     

    Thanks all.

    • Marked as answer by Tom Li - MSFT Monday, January 31, 2011 4:52 AM
    Wednesday, January 19, 2011 9:21 AM

All replies

  • You have to create a loop and inside the loop pass all the procedures name to "sp_depends" and it will return table name and columns which are used. Then store those in a temp table and you have all the data you are looking for.

    sp_depends: http://msdn.microsoft.com/en-us/library/ms189487(SQL.90).aspx

     


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Wednesday, January 19, 2011 1:43 AM
  • How do you know what of 600 dbs cause problems? All of them?

    WITH DBIO AS
    (
     SELECT
       DB_NAME(IVFS.database_id) AS db,
       CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
       SUM(IVFS.num_of_bytes_read +IVFS.num_of_bytes_written) AS io,
       SUM(IVFS.io_stall) AS io_stall
     FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
       JOIN sys.master_files AS MF
         ON IVFS.database_id = MF.database_id
         AND IVFS.file_id = MF.file_id
     GROUP BY DB_NAME(IVFS.database_id), MF.type
    )
    SELECT db, file_type,
      CAST(1. *io/ (1024 *1024) AS DECIMAL(12, 2))AS io_mb,
      CAST(io_stall /1000. AS DECIMAL(12,2))AS io_stall_s,
      CAST(100.*io_stall / SUM(io_stall)OVER()
           AS DECIMAL(10,2))AS io_stall_pct,
      ROW_NUMBER()OVER(ORDER BY io_stall DESC) AS rn
    FROM DBIO
    ORDER BY io_stall DESC;

    What types of waits an instance has


    WITH Waits AS
    (
      SELECT
        wait_type,
        wait_time_ms  /1000. AS wait_time_s,
             100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
             ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
          FROM sys.dm_os_wait_stats
          WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
    ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
    ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
    ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
    ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN')
    )
          --filter out additional irrelevant waits

    SELECT
      W1.wait_type,
      CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12,2))AS pct,
      CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
    FROM Waits AS W1
      JOIN Waits AS W2
        ON W2.rn<= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
    ORDER BY W1.rn;

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 19, 2011 6:55 AM
  • All DBs pretty much cause problems, but smaller ones less-so.

    I ended up writing something very similar to this one above about the wait-stats. I also scripted out the SQL for the standard report "Index Usage Statistics". I stuck all in a loop over all customer databases, and dumped it out to a table.

    Now I've got many hundreds of thousands of records to parse through, but it's yielding good results so far.

     

    Thanks all.

    • Marked as answer by Tom Li - MSFT Monday, January 31, 2011 4:52 AM
    Wednesday, January 19, 2011 9:21 AM
  • If you are dealing with SQL Server 2005 and above


    Register all your servers and run query as first step

    Make use of DMVs-  Run sys.dm_db_missing_index_details and sys.dm_exec_query_requests on the query window of your registered server to capture missing index details and high resource consuming queries.

     

    Caution! the data gets reset on dmvs if the server is restarted.

     

    But the better option is always using SQL Server Profiler.

    • Proposed as answer by GenuineCyborg Wednesday, January 19, 2011 12:48 PM
    Wednesday, January 19, 2011 12:35 PM