View size of Clustered and Non Clustered index on a table

Hi Folks,

Happy New Year 2015,

Longtime I published my last post.Hope I will post regularly from now
onwards probably with some interesting post this year.

Today I am posting a query which most of developers/DBA’s often
require during optimization phase or when we have some limits on
our hardware constraints as far as space is concerned.

Open new query window under ssms and fire below query against
your respective database by providing appropriate table name.

DECLARE @TableName VARCHAR(200)
SET @TableName = ‘NAME_OF_YOUR_TABLE’

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS ‘Indexsize(KB)’,
(8 * SUM(a.used_pages)) / 1024 AS ‘Indexsize(MB)’
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = @TableName
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

Output:

When above query is executed against AdventureWorks DB
by providing Databaselog as table name produces below output,

image

Check which Query is currently running on SQL Server

Hi Folks,

Today I am sharing a pretty useful query often required when to many user’s are working on same server

and one query might be blocking another and we want to see which queries were the culprits,you can fire

the below and query and check the currently running queries on your server.

USE master
GO
SELECT DB_NAME(database_id) AS [Database], [text] AS [Query]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st
WHERE session_Id > 50           — Consider spids for users only, no system spids.
AND session_Id NOT IN (@@SPID)  — Don’t include request from current spid.

 

Sample output:

image

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Check data,log file size, space used & free space for all Databases on a server in SQL Server

Hi Folks,

Longtime I had posted on my blog,today I am sharing a pretty useful script which most of us require when we go out

of memory or space as far as hardware i.e hard drive space is choked by our data which if configured properly may help us to avoid such scenarios.

The following script will give you info regarding dbstatus,Recovery_Model,DBsize, file_Size_MB,    Space_Used_MB,Free_Space_MB,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB,    DB_Freespace.

Just run it on your Server by opening new query window.

------------------------------Data file size---------------------------- 
IF EXISTS (
		SELECT *
		FROM tempdb.sys.all_objects
		WHERE NAME LIKE '%#dbsize%'
		)
	DROP TABLE #dbsize

CREATE TABLE #dbsize (
	Dbname SYSNAME
	,dbstatus VARCHAR(50)
	,Recovery_Model VARCHAR(40) DEFAULT('NA')
	,file_Size_MB DECIMAL(30, 2) DEFAULT(0)
	,Space_Used_MB DECIMAL(30, 2) DEFAULT(0)
	,Free_Space_MB DECIMAL(30, 2) DEFAULT(0)
	)
GO

INSERT INTO #dbsize (
	Dbname
	,dbstatus
	,Recovery_Model
	,file_Size_MB
	,Space_Used_MB
	,Free_Space_MB
	)
EXEC sp_msforeachdb 'use [?]; 
  select DB_NAME() AS DbName, 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  
sum(size)/128.0 AS File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  
from sys.database_files  where type=0 group by type'
GO

-------------------log size-------------------------------------- 
IF EXISTS (
		SELECT *
		FROM tempdb.sys.all_objects
		WHERE NAME LIKE '#logsize%'
		)
	DROP TABLE #logsize

CREATE TABLE #logsize (
	Dbname SYSNAME
	,Log_File_Size_MB DECIMAL(38, 2) DEFAULT(0)
	,log_Space_Used_MB DECIMAL(30, 2) DEFAULT(0)
	,log_Free_Space_MB DECIMAL(30, 2) DEFAULT(0)
	)
GO

INSERT INTO #logsize (
	Dbname
	,Log_File_Size_MB
	,log_Space_Used_MB
	,log_Free_Space_MB
	)
EXEC sp_msforeachdb 'use [?]; 
  select DB_NAME() AS DbName, 
sum(size)/128.0 AS Log_File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  
from sys.database_files  where type=1 group by type'
GO

--------------------------------database free size 
IF EXISTS (
		SELECT *
		FROM tempdb.sys.all_objects
		WHERE NAME LIKE '%#dbfreesize%'
		)
	DROP TABLE #dbfreesize

CREATE TABLE #dbfreesize (
	NAME SYSNAME
	,database_size VARCHAR(50)
	,Freespace VARCHAR(50) DEFAULT(0.00)
	)

INSERT INTO #dbfreesize (
	NAME
	,database_size
	,Freespace
	)
EXEC sp_msforeachdb 
	'use [?];SELECT database_name = db_name() 
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') 
    ,''unallocated space'' = ltrim(str(( 
                CASE  
                    WHEN dbsize >= reservedpages 
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 
                    ELSE 0 
                    END 
                ), 15, 2) + '' MB'') 
FROM ( 
    SELECT dbsize = sum(convert(BIGINT, CASE  
                    WHEN type = 0 
                        THEN size 
                    ELSE 0 
                    END)) 
        ,logsize = sum(convert(BIGINT, CASE  
                    WHEN type <> 0 
                        THEN size 
                    ELSE 0 
                    END)) 
    FROM sys.database_files 
) AS files 
,( 
    SELECT reservedpages = sum(a.total_pages) 
        ,usedpages = sum(a.used_pages) 
        ,pages = sum(CASE  
                WHEN it.internal_type IN ( 
                        202 
                        ,204 
                        ,211 
                        ,212 
                        ,213 
                        ,214 
                        ,215 
                        ,216 
                        ) 
                    THEN 0 
                WHEN a.type <> 1 
                    THEN a.used_pages 
                WHEN p.index_id < 2 
                    THEN a.data_pages 
                ELSE 0 
                END) 
    FROM sys.partitions p 
    INNER JOIN sys.allocation_units a 
        ON p.partition_id = a.container_id 
    LEFT JOIN sys.internal_tables it 
        ON p.object_id = it.object_id 
) AS partitions'

----------------------------------- 
IF EXISTS (
		SELECT *
		FROM tempdb.sys.all_objects
		WHERE NAME LIKE '%#alldbstate%'
		)
	DROP TABLE #alldbstate

CREATE TABLE #alldbstate (
	dbname SYSNAME
	,DBstatus VARCHAR(55)
	,R_model VARCHAR(30)
	)

--select * from sys.master_files 
INSERT INTO #alldbstate (
	dbname
	,DBstatus
	,R_model
	)
SELECT NAME
	,CONVERT(VARCHAR(20), DATABASEPROPERTYEX(NAME, 'status'))
	,recovery_model_desc
FROM sys.databases

--select * from #dbsize 
INSERT INTO #dbsize (
	Dbname
	,dbstatus
	,Recovery_Model
	)
SELECT dbname
	,dbstatus
	,R_model
FROM #alldbstate
WHERE DBstatus <> 'online'

INSERT INTO #logsize (Dbname)
SELECT dbname
FROM #alldbstate
WHERE DBstatus <> 'online'

INSERT INTO #dbfreesize (NAME)
SELECT dbname
FROM #alldbstate
WHERE DBstatus <> 'online'

SELECT d.Dbname
	,d.dbstatus
	,d.Recovery_Model
	,(file_size_mb + log_file_size_mb) AS DBsize
	,d.file_Size_MB
	,d.Space_Used_MB
	,d.Free_Space_MB
	,l.Log_File_Size_MB
	,log_Space_Used_MB
	,l.log_Free_Space_MB
	,fs.Freespace AS DB_Freespace
FROM #dbsize d
INNER JOIN #logsize l ON d.Dbname = l.Dbname
INNER JOIN #dbfreesize fs ON d.Dbname = fs.NAME
ORDER BY Dbname

Sample output:

image

 

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

View fragmentation percentage of the indexes on a table in SQL Server

Hi Geeks,

Today I am sharing SQL query often required during performance tuning/Optimization phase of the project.The query makes use of system DMV’s provided by SQL server.Open new query window in SQL Server and fire the below query against the respective database.Just pass in the name of the table and the database for which you want to view the fragmentation.

SELECT i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(DB_ID(‘parisdev’),
OBJECT_ID(‘currency’), NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id

 

image

 

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Query to see the fill factor of the indexes on the tables in the current database in SQL Server

Hi Geeks,

Today I am sharing another useful query often required during performance tuning/Optimization phase of the project.The query makes use of system DMV’s provided by SQL server.Open new query window in SQL Server and fire the below query against the respective database..

SELECT DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, i.fill_factor
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
ORDER BY fill_factor DESC

 

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Get Index usage information in SQL Server

Hi Folks,

Today I am sharing another useful query often required during performance tuning/Optimization phase of the project.The query makes use of system DMV’s provided by SQL server.Open new query window in SQL Server and fire the below query  which will return information regarding the most used indexes on your server by SQL server.For this example,I have restricted the output to only 10 rows using the TOP clause.You can modify it as per your requirement.

 

SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage]
, s.user_updates
, i.fill_factor
INTO #TempUsage
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB ‘USE [?];
INSERT INTO #TempUsage
SELECT TOP 10
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage]
, s.user_updates
, i.fill_factor
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
ORDER BY [Usage] DESC’
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
DROP TABLE #TempUsage

 

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Follow my blog for regular updates related to DBA side on SQL Server.

Reads and Writes per DB in SQL Server

Hi Folks,

Today I am sharing another useful query often required during performance tuning/Optimization phase of the project.The query makes use of system DMV’s and DMF’s provided by SQL server.Open new query window in SQL Server and fire the below query by passing the respective database in the where clause.For this example,I have used Adventure Works as my database.You can modify it as per your requirement.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SUM(qs.total_logical_reads) AS [Total Reads]
, SUM(qs.total_logical_writes) AS [Total Writes]
, DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE DB_NAME(qt.dbid) = ‘AdventureWorks’
GROUP BY DB_NAME(qt.dbid)

image

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.