SQL stuff

Here are SQL scripts you may find useful. These are all for SQL Server unless otherwise noted, and if a specific version of a database application is required, I will try to remember to call that out as well.


Reset identity for tables with zero records

Just on the off chance that you want to reset the identity back to 0 so that your new record start at 1 again, here is a query based off the Table information query below that will perform that task.

DECLARE @tableName NVARCHAR(255)

DECLARE db_cursor CURSOR FOR
SELECT
    t.NAME AS TableName
FROM
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
AND p.rows = 0
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TableName

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tableName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    DBCC CHECKIDENT (@tableName, RESEED, 0)
    FETCH NEXT FROM db_cursor INTO @tableName
END

CLOSE db_cursor  
DEALLOCATE db_cursor

If you have any tables in your database with 0 records, and the table schema does not include a primary key field with identity, you will get an error in the console on the DBCC CHECKIDENT line, but the script will just continue forward.


Stored procedure parameters

This query will return the parameters for a given stored procedure, just in case you do not have sufficient permissions to look at
and edit the stored procedure.

SELECT p.name AS ParameterName, t.name AS ParameterType, p.max_length AS ParameterLength
FROM sys.parameters AS p
JOIN sys.types AS t ON t.user_type_id = p.user_type_id
WHERE object_id = OBJECT_ID('YourProcedureName')

Table information

This query will show you the tables in your database, along with disk space information about each of the tables.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(SUM(a.total_pages) * 8 / 1024.0, 2) AS NUMERIC(36,2)) AS TotalSpaceMB, 
    CAST(ROUND(SUM(a.total_pages) * 8 / 1024.0 / 1024.0, 2) AS NUMERIC(36,2)) AS TotalSpaceGB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(SUM(a.used_pages) * 8 / 1024.0, 2) AS NUMERIC(36,2)) AS UsedSpaceMB, 
    CAST(ROUND(SUM(a.used_pages) * 8 / 1024.0 / 1024.0, 2) AS NUMERIC(36,2)) AS UsedSpaceGB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    UsedSpaceKB desc

Here is a simpler query to show the tables and the sizes in megabytes, with the largest tables at the top.

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as MB
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY MB DESC

Database information

This query will show you the databases that SQL Server knows about, along with their sizes.

SELECT 
    [TYPE] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' 
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END 
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
order by A.TYPE desc, A.NAME;

Monitoring connections

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID

Monitoring queries

SELECT TOP 5 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
    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 AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC

Monitoring query plans

SELECT
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
FROM
    (SELECT TOP 50
        qs.plan_handle,
        qs.total_worker_time
    FROM
        sys.dm_exec_query_stats qs
    ORDER BY qs.total_worker_time desc) AS highest_cpu_queries
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC

References

Monitoring Azure SQL Database using dynamic management views

Comments are closed.