SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #029 – Video

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Wed, 10 Oct 2012 01:30:38 +0000 Indexed on 2012/10/10 3:43 UTC
Read the original article Hit count: 500

There are a few questions I often get asked. I wonder how interesting is that in our daily life all of us have to often need the same kind of information at the same time. Here is the example of the similar questions:

  • How many user created tables are there in the database?
  • How many non clustered indexes each of the tables in the database have?
  • Is table Heap or has clustered index on it?
  • How many rows each of the tables is contained in the database?

I finally wrote down a very quick script (in less than sixty seconds when I originally wrote it) which can answer above questions. I also created a very quick video to explain the results and how to execute the script.

Here is the complete script which I have used in the SQL in Sixty Seconds Video.
SELECT [schema_name] = s.name, table_name = o.name,
MAX(i1.type_desc) ClusteredIndexorHeap,
COUNT(i.TYPE) NoOfNonClusteredIndex, p.rows
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
LEFT JOIN sys.partitions p ON p.OBJECT_ID = o.OBJECT_ID AND p.index_id IN (0,1)
LEFT JOIN sys.indexes i1 ON i.OBJECT_ID = i1.OBJECT_ID AND i1.TYPE IN (0,1)
WHERE o.TYPE IN ('U')
AND
i.TYPE = 2
GROUP BY s.name, o.name, p.rows
ORDER BY schema_name, table_name

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)


Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video Tagged: Excel

© SQL Authority or respective owner

Related posts about database

Related posts about Pinal Dave