Below is a script to show the current, maximum and minimum sizes of the SGA components that can be dynamically resized.
SELECT component, current_size, min_size, max_size FROM v$sga_dynamic_components;
Execute the script below to Determine how much memory has been, and is currently, allocated to program global areas
SELECT
name,
value
FROM
v$pgastat
WHERE
name IN ('maximum PGA allocated','total PGA allocated');
Below is the T-SQL script to query the Report Server Execution Log. You may query either the dbo.ExecutionLog2 or dbo.ExecutionLog3 table (same as dbo.ExecutionLog2 with 2 fields renamed: ReportPath has been renamed to ItemPath and ReportAction has been renamed to ItemAction)
Below is the output of the query.
USE ReportServer GO SELECT * FROM dbo.ExecutionLog3 ORDER BY TimeStart DESC
There are many T-SQL scripts to get the index creation date but I always use this one.
DECLARE @filename VARCHAR(500) SELECT @filename = CAST(value AS VARCHAR(500)) FROM fn_trace_getinfo(DEFAULT) WHERE property = 2 AND value IS NOT NULL -- Go back 4 files since default trace only keeps the last 5 and start from there. SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc' SELECT gt.EventClass, gt.EventSubClass, te.Name AS EventName, gt.HostName, gt.StartTime, gt.DatabaseName, gt.ObjectName FROM fn_trace_gettable(@fileName, DEFAULT) gt JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id WHERE EventClass = 46 and ObjectType = 22601 and gt.DatabaseName <> 'tempdb' ORDER BY StartTime desc;
By default, your SSRS reports are generated in portrait format. The body of your report will be automatically resized when you add report items. When you deploy your reports, it will portray correctly in your browser. But when you export your report to PDF, you will notice that it is in portrait format. It is not presentable and it will have extra pages. You can prevent this from happening by exporting the PDF in landscape format. To do that, just follow the steps below.
2. When you export the report, you will see extra blank pages. To avoid that, the size of the body should be less or equal to the size of the report margins
Set the width of the body to 26.7 cm Set the height of the body to 18 cm
So now, when you export your report to PDF, it will be in landscape format.
The T-SQL query below can be used to check the properties of your databases.
USE master GO SELECT name AS 'Database_Name' , snapshot_isolation_state AS 'Allow Snapshot Isolation' , is_ansi_null_default_on AS 'ANSI NULL Default' , is_ansi_nulls_on AS 'ANSI NULLS Enabled' , is_ansi_padding_on AS 'ANSI Paddings Enabled' , is_ansi_warnings_on AS 'ANSI Warnings Enabled' , is_arithabort_on AS 'Arithmetic Abort Enabled' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , is_cursor_close_on_commit_on AS 'Close Cursor on Commit Enabled' , is_concat_null_yields_null_on AS 'Concatenate Null Yields Null' , is_db_chaining_on AS 'Cross-Database Ownership Chaining Enabled' , is_date_correlation_on AS 'Data Correlation Optimization Enabled' , is_read_only AS 'Database Read-Only' , is_local_cursor_default AS 'Default Cursor' , is_encrypted AS 'Encryption Enabled' , is_arithabort_on AS 'Numeric Round-Abort' , page_verify_option_desc AS 'Page Verify' , is_parameterization_forced AS 'Parameterization' , is_quoted_identifier_on AS 'Quoted Identifiers Enabled' , is_read_committed_snapshot_on AS 'Read Committed Snapshot' , is_recursive_triggers_on AS 'Recursive Triggers Enabled' , user_access_desc AS 'Restrict Access' , is_broker_enabled AS 'Service Broker Enabled' , is_trustworthy_on AS 'Trustworthy' FROM sys.databases ; GO
Below is a script that captures that top SQL queries consuming CPU.
SELECT TOP 50 [Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count, [Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000, [Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count, [Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000, qs.execution_count, [Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count, [Total I/O] = total_logical_reads + total_logical_writes, Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.[text]) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1 ), Batch = qt.[text], [DB] = DB_NAME(qt.[dbid]), qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp where qs.execution_count > 5 --more than 5 occurences ORDER BY [Total MultiCore/CPU time(sec)] DESC
Below is a script to check the database mirroring synchronization state.
SELECT @@SERVERNAME as Server_Name, DB_NAME(database_id) as Database_Name, mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc FROM sys.database_mirroring WHERE mirroring_role IS NOT NULL
The script below will retrieve the size of all your databases in MB and GB.
SELECT d.NAME ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 -- Skip system databases GROUP BY d.NAME ORDER BY d.NAME
SQL Server will look at each query and check if it has a plan cache already compiled for. If it has, then it will reuse the plan in the cache. If it does not have, then an execution plan is compiled and added to the plan cache.
When identical queries ran over many times, its plan will be reused. This will save resources from having to compile repeatedly.
Now what happens when you have hundreds or thousands of different queries that are only executed once? In this case there are no execution plan to be reused. This is what we call PLAN CACHE BLOAT.
Plan cache bloat is bad because it steals memory from the buffer pool that are better used for database pages. When you execute the script below, and if you see “Adhoc” Cache type to be on top, then that means that there are a lot of queries that are executed once and are causing plan cache bloat.
SELECT objtype AS [CacheType] , count_big(*) AS [Total Plans] , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs] , avg(usecounts) AS [Avg Use Count] , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1] , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1] FROM sys.dm_exec_cached_plans GROUP BY objtype ORDER BY [Total MBs - USE Count 1] DESC GO
Plan cache bloat can be prevented by enabling “Optimize for Adhoc Workloads” by only caching the full execution plan of queries that are known to be used more than once.
To enable the optimize for ad hoc workloads setting, use sp_configure, as shown below.
EXEC sp_configure 'show advanced options',1 RECONFIGURE EXEC sp_configure 'optimize for ad hoc workloads',1 RECONFIGURE
