Script: To find the size of the tables in a database

SELECT
table_name = SCHEMA_NAME(o.[schema_id]) + '.' + o.name

, data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(8,4))

FROM sys.objects o

JOIN (




SELECT
p.[object_id]

, total_rows = SUM(p.[rows])

, total_pages = SUM(a.total_pages)

, usedpages = SUM(a.used_pages)

, pages = SUM(




CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0

WHEN a.[type]! = 1 AND p.index_id < 2 THEN a.used_pages

WHEN p.index_id < 2 THEN a.data_pages ELSE 0




END
)
FROM sys.partitions p

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]

GROUP BY p.[object_id]

) do ON o.[object_id] = do.[object_id]

WHERE o.[type] = 'U'

script: Change the owner of the sql jobs

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs 

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END 

CLOSE My_Cursor

DEALLOCATE My_Cursor

CPU consumption

--from SQLAuthority website
--SQL SERVER – Who is consuming CPU on my SQL Server?

WITH DB_CPU_Statistics




AS


(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY (SELECT CONVERT(INT, value) AS [DatabaseID]

FROM sys.dm_exec_plan_attributes(qs.plan_handle)

WHERE attribute = N'dbid') AS pa

GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking],

[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],

CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]

FROM DB_CPU_Statistics

WHERE DatabaseID <> 32767 -- ResourceDB

ORDER BY [CPU Ranking] OPTION (RECOMPILE);

script: How to find logical and physical file names of the database

SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf

Srcript: Analysing Performance monitor data

After capturing the data in  the tables  ( link here - http://sql-dba-online.blogspot.com/2015/02/performance-monitor-how-to-run-relog-to.html) The following script will help to analyze the data.

/*show data collected for specific collector set, ordered by counter and time */

SELECT * from counterData cd

JOIN DisplayTOId di

ON cd.[GUID] = di.[GUID]

JOIN counterdetails cdt

ON cdt.[counterID] = cd.[counterID]

WHERE di.[DisplayString] = 'ProdSample.blg'

ORDER BY cdt.[ObjectName],

cdt.[CounterName],cd.[RecordIndex]



/* List max,min,avg,stdev for each counter for a specific collector*/

SELECT CONVERT(VARCHAR(10),cd.counterDateTime, 101) AS "collection" ,

RTRIM(cdt.objectName) + '\' + rtrim(cdt.CounterName) +

Case

WHEN cdt.InstanceName IS NULL then ''

WHEN cdt.instanceName is not null then '_' + rtrim(cdt.InstanceName)

END as "Counter",

CAST (MIN(cd.CounterValue) AS Decimal(6,0)) AS "Minimum",

CAST (MAX(cd.CounterValue) AS Decimal(6,0)) AS "Maximum",

CAST (AVG(cd.CounterValue) AS Decimal(6,0)) AS "Average",

CAST (STDEV(cd.CounterValue) AS Decimal(6,0)) AS "StDev"

FROM counterData cd

JOIN DisplayTOId di

ON cd.[GUID] = di.[GUID]

JOIN counterdetails cdt

ON cdt.[counterID] = cd.[counterID]

WHERE di.[DisplayString] = 'ProdSample.blg'

GROUP BY CONVERT(VARCHAR(10),cd.counterDateTime, 101),cdt.objectName,cdt.CounterName,cdt.instanceName