In this post I decided to add the scripts that occasionally helped me to manage Microsoft SQL servers. Those are small scripts that I gathered searching the WEB (ok, google), some are as is and some are edited to cover the current needs.
Script #1 | Get the waste space of a database
With this script we can get the Total Plan Cache Size In MBs, the Total Single Used Plans In MBs and the % of Wasted space from single used plans.
declare @TotalPlanCacheSizeInMBs decimal(18,2), @TotalSingleUsedPlansInMBs decimal(18,2) select @TotalPlanCacheSizeInMBs = (sum(cast(size_in_bytes as decimal(18,2)))/1024)/1024 , @TotalSingleUsedPlansInMBs = (sum(cast((case when usecounts = 1 and objtype in ('Adhoc','Prepared') then size_in_bytes else 0 end) as decimal(12,2)))/1024)/1024 from sys.dm_exec_cached_plans select @TotalPlanCacheSizeInMBs AS [Total Plan Cache Size In MBs] , @TotalSingleUsedPlansInMBs AS [Total Single Used Plans In MBs] , cast((@TotalSingleUsedPlansInMBs * 100 ) / @TotalPlanCacheSizeInMBs as decimal(18,2)) AS [% of Wasted space from single used plans] go
Script #2 | Get the Current Backup-Restore Progress
With this script we can see if currently there is a Backup or restore progress running on the server, for all databases. Also if you have started a backup or restore process from the GUI and accidentally closed the progress window then with this script you can monitor the progress.
USE master SELECT session_id as SPID, CONVERT(VARCHAR(50),start_time,100) AS start_time, percent_complete, CONVERT(VARCHAR(50),dateadd(second,estimated_completion_time/1000, getdate()),100) as estimated_completion_time, command, a.text AS Query FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command LIKE 'BACKUP%' OR r.command LIKE 'RESTORE%'
Script #3 | Get who is connected
With this script we can see who is currently connected and to what database.
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame ;
Script #4 | Get the Index Fragmentation
With this script we can get the Index Fragmentation of a database. It will return the Table, the Index, the Average Fragmentation in % and the Page Count.
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.page_count desc
That’s all until now, I will try to keep updating this post.
Pantelis Apostolidis is a Cloud Solutions Architect at Microsoft and an ex Microsoft Azure MVP. For the last 15 years, Pantelis has been involved to major cloud projects in Greece and abroad, helping companies to adopt and deploy cloud technologies, driving business value. He is entitled to a lot of Microsoft Expert Certifications, demonstrating his proven experience in delivering high quality solutions. He is an author, blogger and he is acting as a spokesperson for conferences, workshops and webinars. He is also an active member of several communities as a moderator in azureheads.gr and autoexec.gr. Follow him on Twitter @papostolidis.