jump to navigation

Who is Active ? October 25, 2010

Posted by furrukhbaig in DMV's, Execution Plan, Optimize, Queries running on the server, sp_who, sp_who2, sp_whoisactive, system monitoring, Tips, XML Execution Plan.
Tags: , , , , , ,
add a comment

Adam Machanic has released new build of system monitoring stored procedure SP_WhoIsActive. Its really useful and I would like to congratulate him for his efforts.

check out the link below for his original post and to download the code.

http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx

Enjoy !!

Advertisements

Find out whats running on SQL Server 2005 December 13, 2007

Posted by furrukhbaig in DMV's, Execution Plan, Optimize, Performance, Performance Tuning, Profiler, Queries running on the server, SQL Server 2005, SQL Server 2005 features, XML Execution Plan.
Tags: , , , ,
2 comments

Everyone wants to know whats running on the box. Specially if your job is to stabalise server you always concern what is killing the box.

A Friend of mine has published very usefull post with scripts to find out whats running on the SQL Server with their execution plan.

http://www.proteanit.com/b/2007/01/22/a-useful-script-to-analyse-current-activity-on-your-box/

The same can also be managed if you run profiler and capture XML execution plan which is not always possible due to security issue and overhead of profiler itself.

Is this Index useful ? August 17, 2007

Posted by furrukhbaig in DMV's, dm_db_index_usage_stats, Index tuning, Index usefulness, Indexes, Optimize, Performance, Performance Tuning, SQL Server 2005, SQL Server 2005 features, TSQL.
Tags: , , , , , , , , ,
1 comment so far

Creating indexes on tables always been a tricky question. Creating a index does not mean that optimizer will use that index to solve queries. SQL Server 2005 introduced a dynamic management view sys.dm_db_index_usage_stats to capture the use of indexes by SELECT, INSERT, UPDATE and DELETE. Thus any index or table which does not exists in this view is overhead to system and not useful.

Following query will return all the indexes and tables which never get used by SQL Server since the service started. The best way to check is to create index and run the data work load or regression test that should make use of all indexes and run following query.see full article on SQL Server Customer Advisory Team (CAT) How can SQL Server 2005 help me evaluate and manage indexes

SELECT  
            ObjectName = OBJECT_NAME(ind.object_id),
            IndexName = ind.name
FROM
            Sys.Indexes ind
            INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id
WHERE
            OBJECTPROPERTY(obj.object_id,‘IsUserTable’) = 1
            AND NOT EXISTS
            (
                  SELECT
                        1
                  FROM
                        Sys.dm_db_index_usage_stats usg
                  WHERE
                        usg.object_id = ind.object_id 
                        AND usg.index_id = ind.index_id
                        AND ind.index_id = usg.index_id
            )
ORDER BY
            ObjectName,
            IndexName
 
 

Worst Performing Queries August 17, 2007

Posted by furrukhbaig in DMV's, Indexes, Performance, SQL Server 2005, TSQL.
Tags: , , , , , ,
8 comments

I have been looking for a easy way to find worst performing queries without runing Profiler and I come across very interesting DMV’s dm_exec_query_stats and dm_exec_sql_text.Microsoft has introduced Dynamic Management Views (sys.dm_exec_query_stats, sys.dm_exec_sql_text) in SQL Server 2005 which provide a way to fetch information related to resources used by SQL Server for executing SQL statements.

Following query will return TOP 100 worst performing SQL Statement with the name of object that contain those statements e.g. Stored Procedure, Trigger and Function. Current database context will be used so change the database before executing query. The result will be sorted by CPU time in descending order.

SELECT  TOP 100
            [Object_Name] = object_name(st.objectid),
            creation_time,
            last_execution_time,
            total_cpu_time = total_worker_time / 1000,
            avg_cpu_time = (total_worker_time / execution_count) / 1000,
            min_cpu_time = min_worker_time / 1000,
            max_cpu_time = max_worker_time / 1000,
            last_cpu_time = last_worker_time / 1000,
            total_time_elapsed = total_elapsed_time / 1000 ,
            avg_time_elapsed = (total_elapsed_time / execution_count) / 1000,
            min_time_elapsed = min_elapsed_time / 1000,
            max_time_elapsed = max_elapsed_time / 1000,
            avg_physical_reads = total_physical_reads / execution_count,
            avg_logical_reads = total_logical_reads / execution_count,
            execution_count,
            SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                  (
                        (
                              CASE statement_end_offset
                                    WHEN 1 THEN DATALENGTH(st.text)
                                    ELSE qs.statement_end_offset
                              END
                              qs.statement_start_offset
                        ) /2
                  ) + 1
            ) as statement_text
FROM
            sys.dm_exec_query_stats qs
CROSS APPLY
            sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
            Object_Name(st.objectid) IS NOT NULL
            AND st.dbid = DB_ID()
ORDER BY
            db_name(st.dbid),
            total_worker_time / execution_count  DESC