Common queries on a SQL Server environment for a DBA

This blog will be for queries we use for minor everyday problems. This blog will address issues related to disk space, SID for SQL login, and index fragmentation..

For this blog, you will only need SSMS and sysadmin rights on your environment.

Disk Space problem

If you have a full disk.
Instead of connecting to the server.
You can use this query, which will show all the disks associated with the instance.

-- Volume info for all LUNS that have database files on the current instance (Query 26) (Volume Info)
SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name,
CONVERT(DECIMAL(18,2), vs.total_bytes/1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],
CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],
vs.supports_compression, vs.is_compressed,
vs.supports_sparse_files, vs.supports_alternate_streams
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs

If you want to see which database caused the disk to fill up, you can use this query.

DECLARE @tracepath nvarchar(260)

SELECT @tracepath = path 
FROM sys.traces 
WHERE is_default = 1

SELECT g.DatabaseName AS DBName
, mf.physical_name AS DBFileName
, CASE mf.type WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN 'FILESTREAM' WHEN 4 THEN 'Full-text' END AS FileType
, te.name AS EventName
,  convert(decimal(19,2),g.IntegerData*8/1024.)  AS AutoGrowSize
, g.StartTime
, convert(decimal(19,2),g.Duration/1000./1000.) AS EventDuration -- Length of time necessary to extend the file.
, CASE WHEN mf.is_percent_growth = 1 THEN CONVERT(char(2), mf.growth) + '%'
ELSE CONVERT(varchar(30), convert(decimal(19,2), mf.growth*8./1024.)) + 'MB' END AutoGrowSizeSetting
, convert(decimal(19,2),mf.size* 8./1024.) AS fileSize
, CASE WHEN mf.max_size = -1 THEN 'Unlimited' ELSE convert(varchar(30), convert(decimal(19,2),mf.max_size*8./1024.)) END AS maxFileSize

FROM fn_trace_gettable(@tracepath, default) g
cross apply sys.trace_events te 
inner join sys.master_files mf
on mf.database_id = g.DatabaseID
and g.FileName = mf.name
WHERE g.eventclass = te.trace_event_id
and te.name in ('Data File Auto Grow','Log File Auto Grow')
--GROUP BY StartTime,Databaseid, Filename, IntegerData, Duration
order by 6 desc 

SID problem with an SQL login in an Always On environment

As you know, when a login is created in an Always On environment, it must be created on both sides. Therefore, when it comes to an SQL login, the SID must be the same on both sides. This ensures that both logins map to the user. Pour ceci nous pouvons utiliser cette requete pour qui va donner l’exact commande a exécuter pour crée le login souhaitée

SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+
    CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+
    N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';'
FROM master.sys.server_principals AS sp
INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.name='LoginName'

Control of index fragmentation and statistics updates

When performance issues arise, the first step is often to check whether all indexes have been created and are not fragmented. To do this, you can use this query to determine the level of index fragmentation.

USE DatabaseName;
GO
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

as well as this query, which indicates the last time the database statistics were updated.

USE DatabaseName;
GO
SELECT 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.object_id, i.index_id)FROM sys.objects o JOIN sys.indexes i ON o.object_id = i.object_id;
GO

Conclusion

This is just a small part of our toolkit for getting started with debugging. In the future, other blogs more focused on everyday utility may be created. If you have any further questions or other topics you would like to discuss, please do not hesitate to contact us.