Skip to content

Commit db8e87f

Browse files
AlexTitenkoAlexTitenko
committed
Add 4 SQL script in Scripts
1. DetermineSpaceWithinSQLDatabaseFiles 2. FindingImplicitColumnConversionsInPlanCache 3. GenerateTSQLTimeSlices 4. MisleadingSQLServerPerformanceCounters
1 parent 7c25b5e commit db8e87f

4 files changed

Lines changed: 134 additions & 0 deletions

‎Scripts/DetermineSpaceWithinSQLDatabaseFiles.sql‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
/*
2+
Author: BradC
3+
Original link: https://dba.stackexchange.com/questions/7917/how-to-determine-used-free-space-within-sql-database-files
4+
Desctiption: Determine used/free space within SQL database files
5+
*/
6+
7+
8+
--This one works for me and seems to be consistent on SQL 2000 to SQL Server 2012 CTP3:
9+
SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
10+
CAST(size/128.0 AS DECIMAL(10,2)) AS [Allocated Size in MB],
11+
CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used in MB],
12+
CAST([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max in MB],
13+
CAST([maxsize]/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB],
14+
CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST([maxsize]/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
15+
FROM sysfiles
16+
ORDER BY groupid DESC
17+
18+
19+
--An alternative (not compatible with SQL Server 200) that provides more information, suggested by Tri Effendi SS:
20+
USE [database name]
21+
GO
22+
SELECT
23+
[TYPE] = A.TYPE_DESC
24+
,[FILE_Name] = A.name
25+
,[FILEGROUP_NAME] = fg.name
26+
,[File_Location] = A.PHYSICAL_NAME
27+
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
28+
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
29+
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
30+
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
31+
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
32+
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
33+
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
34+
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
35+
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
36+
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
37+
order by A.TYPE desc, A.NAME;

‎Scripts/FindingImplicitColumnConversionsInPlanCache.sql‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
/*
2+
Author: Jonathan Kehayias
3+
Original link: https://www.sqlskills.com/blogs/jonathan/finding-implicit-column-conversions-in-the-plan-cache
4+
Desctiption: Finding Implicit Column Conversions in the Plan Cache
5+
*/
6+
7+
8+
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
9+
10+
DECLARE @dbname SYSNAME
11+
SET @dbname = QUOTENAME(DB_NAME());
12+
13+
WITH XMLNAMESPACES
14+
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
15+
SELECT
16+
stmt.value('(@StatementText)[1]', 'varchar(max)'),
17+
t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
18+
t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
19+
t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
20+
ic.DATA_TYPE AS ConvertFrom,
21+
ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
22+
t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
23+
t.value('(@Length)[1]', 'int') AS ConvertToLength,
24+
query_plan
25+
FROM sys.dm_exec_cached_plans AS cp
26+
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
27+
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
28+
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
29+
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
30+
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
31+
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
32+
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
33+
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

‎Scripts/GenerateTSQLTimeSlices.sql‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
/*
2+
Author: Atom
3+
Original link: http://billfellows.blogspot.ru/2017/07/generate-tsql-time-slices.html
4+
Desctiption: Generate TSQL time slices
5+
*/
6+
7+
8+
SELECT
9+
D.Slice AS SliceStart
10+
, LEAD
11+
(
12+
D.Slice
13+
, 1
14+
-- Default to midnight
15+
, TIMEFROMPARTS(0,0,0,0,0)
16+
)
17+
OVER (ORDER BY D.Slice) AS SliceStop
18+
, ROW_NUMBER() OVER (ORDER BY D.Slice) AS SliceLabel
19+
FROM
20+
(
21+
-- Generate 15 second time slices
22+
SELECT
23+
TIMEFROMPARTS(A.rn, B.rn, C.rn, 0, 0) AS Slice
24+
FROM
25+
(SELECT TOP (24) -1 + ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) FROM sys.all_objects AS AO) AS A(rn)
26+
CROSS APPLY (SELECT TOP (60) (-1 + ROW_NUMBER() OVER (ORDER BY(SELECT NULL))) FROM sys.all_objects AS AO) AS B(rn)
27+
-- 4 values since we'll aggregate to 15 seconds
28+
CROSS APPLY (SELECT TOP (4) (-1 + ROW_NUMBER() OVER (ORDER BY(SELECT NULL))) * 15 FROM sys.all_objects AS AO) AS C(rn)
29+
) D
30+

‎Scripts/MisleadingSQLServerPerformanceCounters.sql‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
/*
2+
Author: Kendra Little
3+
Original link: https://sqlworkbooks.com/2017/06/top-5-misleading-sql-server-performance-counters
4+
Desctiption: Top 5 Misleading SQL Server Performance Counters
5+
*/
6+
7+
8+
SELECT TOP 20
9+
(SELECT CAST(SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
10+
((CASE qs.statement_end_offset
11+
WHEN -1 THEN DATALENGTH(st.text)
12+
ELSE qs.statement_end_offset
13+
END
14+
- qs.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''),TYPE) AS [TSQL],
15+
qs.execution_count AS [#],
16+
qs.total_logical_reads as [logical reads],
17+
CASE WHEN execution_count = 0 THEN 0 ELSE
18+
CAST(qs.total_logical_reads / execution_count AS numeric(30,1))
19+
END AS [avg logical reads],
20+
CAST(qs.total_worker_time/1000./1000. AS numeric(30,1)) AS [cpu sec],
21+
CASE WHEN execution_count = 0 THEN 0 ELSE
22+
CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,1))
23+
END AS [avg cpu sec],
24+
CAST(qs.total_elapsed_time/1000./1000. AS numeric(30,1)) AS [elapsed sec],
25+
CASE WHEN execution_count = 0 THEN 0 ELSE
26+
CAST(qs.total_elapsed_time / execution_count / 1000. / 1000. AS numeric(30,1))
27+
END AS [avg elapsed sec],
28+
qp.query_plan AS [query execution plan]
29+
FROM sys.dm_exec_query_stats AS qs
30+
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
31+
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
32+
ORDER BY qs.total_logical_reads DESC
33+
OPTION (RECOMPILE);
34+
GO

0 commit comments

Comments
 (0)