Articles for the Month of June 2013

SQL Server – How to query all databases sizes?

Sometimes you need to get a quick glance of the size of all the databases in your instance. Following script would give you just that. It gives you the size of the data file as well as the log file.


SELECT
 DB_NAME(db.database_id) DatabaseName,
 (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
 (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
 (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
 (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
 FROM sys.databases db
 LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

And you should end up with something like the following:

DatabaseName  RowSizeMB LogSizeMB StreamSizeMB TextIndexSizeMB
------------- --------- --------- ------------ ---------------
master        4         1.25      NULL         NULL
model         2.25      0.75      NULL         NULL
msdb          14.75     8.1875    NULL         NULL
tempdb        8         0.5       NULL         NULL

SQL Server – Index Fragmentation Issue

A couple of days ago I came across an issue with our SharePoint administrator regarding the slow index performance on their databases. I had to find out what the current Index Fragmentation Factor is (on all the tables within their various databases) and then find out how this fragmentation issue could be resolved. After doing some search online and reading some articles, I was able to find the solution. So here is what I found:

Checking the Index Fragmentation Factor:

Checking the index fragmentation percentage on the tables of a database
USE DATABASENAME_HERE
 GO
 -- Find the average fragmentation percentage of all indexes

SELECT DB_NAME(database_id) as "Database", OBJECT_NAME(object_id) as "Table Name",avg_fragmentation_in_percent as "Avg Frag. Percent"
 FROM sys.dm_db_index_physical_stats (DB_ID(N'DATABASENAME_HERE), NULL, NULL, NULL, NULL)
 ORDER BY avg_fragmentation_in_percent DESC;
 GO
Returning information about a specific table
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');

IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

De-fragmenting one table based on fragmentation factor

-- between 5 and 30
 USE WSS_Content_1
 GO
 ALTER INDEX ALL ON StorageMetrics
 REORGANIZE
 GO

-- between 30 and 90, you need to rebuild in online mode
 USE WSS_Content_1
 GO
 ALTER INDEX ALL ON BuildDependencies
 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
 STATISTICS_NORECOMPUTE = ON)
 GO

NOTE: There is a Microsoft procedure that is supposed to automatically correct the fragmentation issue (proc_DefragmentIndices), but it’s broken and doesn’t do anything. So in my later posts, I’ll try to fix that procedure.