17th January 20201st February 2020

Test your MAXDOP – SQL Server max degree of parallelism

The default value of your SQL server is MAXDOP=0. It means there is no limit for parallelism. Your query can use all of your CPU core.

Scenario:
If your SQL Server is not used, than your CPU utilization is low,
your query can use all of the server’s resources.
I run a cross join query in a table which has 40.000 rows.
The server has 8 core.
I set hint for the query to simulate the MAXDOP server setting.

The query and the result:

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 8) 
--  result 33520 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 6)  
-- result 43461 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 4) 
-- result 59837 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 3) 
-- result 80219 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 2) 
-- result 116805 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 1)  
-- result 232825

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 8)

-- result 33520 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 6)

-- result 43461 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 4)

-- result 59837 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 3)

-- result 80219 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 2)

-- result 116805 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 1)

-- result 232825

So if your query use only 1 thread it run longer ( in the example 232 seconds), if it use 8 thread it run much more quicker ( 33 seconds).
So 232/33 ~= 7 . Ok the speed is not 8x , but almost 8. The operating system and other processes use system resources.

Should we use the maxdop value without limit? In the real life a lot of queries run at the same time, so if your query get all of the resource the other waiting the resources. So you try to figure out what is ideal value?
If you have no idea, or don’t have experience with the new databases , you can set the recommended value:
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

Server Propertis - MAXDOP — Server Propertis – MAXDOP

You can reduce the parallelism in your server with increasing the Cost Threshold for Parallelims.

You can set it for individually for every database:

Database settings

Max DOP for you database and Max DOP For Secondary for your Always ON AG secondary replicas.

13th January 202013th January 2020

How to Check Index Fragmentation and Partitioned Table Index Fragmentation

I use two script. The firs one is for general purpose and run faster, the second one is good for partitioned table and get many details about partitioned index and table.

SELECT OBJECT_NAME(ips.OBJECT_ID)
 ,i.NAME
 ,ips.index_id
 ,ips.partition_number
 ,index_type_desc
 ,avg_fragmentation_in_percent
 ,avg_page_space_used_in_percent
 ,page_count
 ,ips.record_count
 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
-- where OBJECT_NAME(ips.OBJECT_ID) ='table_name'
ORDER BY index_id,ips.partition_number,avg_fragmentation_in_percent DESC

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SELECT OBJECT_NAME(ips.OBJECT_ID)

,i.NAME

,ips.index_id

,ips.partition_number

,index_type_desc

,avg_fragmentation_in_percent

,avg_page_space_used_in_percent

,page_count

,ips.record_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)

AND (ips.index_id = i.index_id)

-- where OBJECT_NAME(ips.OBJECT_ID) ='table_name'

ORDER BY index_id,ips.partition_number,avg_fragmentation_in_percent DESC

Detailed query for partitioned table:

/*
Script  : Index Fragmentation Status (includes Partitioned Tables/Indexes)
Version : 1.0 (April 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/
- Hide quoted text -
SELECT
             SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
     SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

/*

Script : Index Fragmentation Status (includes Partitioned Tables/Indexes)

Version : 1.0 (April 2010)

Author : Richard Doering

Web : http://sqlsolace.blogspot.com

*/

- Hide quoted text -

SELECT

SCHEMA_NAME(o.schema_id) AS SchemaName

,OBJECT_NAME(o.object_id) AS TableName

,i.name AS IndexName

,i.type_desc AS IndexType

,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned

,COALESCE(fg.name ,fgp.name) AS FileGroupName

,p.partition_number AS PartitionNumber

,p.rows AS PartitionRows

,dmv.Avg_Fragmentation_In_Percent

,dmv.Fragment_Count

,dmv.Avg_Fragment_Size_In_Pages

,dmv.Page_Count

,prv_left.value AS PartitionLowerBoundaryValue

,prv_right.value AS PartitionUpperBoundaryValue

,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange

,pf.name AS PartitionFunction

,ds.name AS PartitionScheme

FROM sys.partitions AS p WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON o.object_id = i.object_id

INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv

ON dmv.OBJECT_ID = i.object_id

AND dmv.index_id = i.index_id

AND dmv.partition_number = p.partition_number

LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)

ON ds.data_space_id = i.data_space_id

LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)

ON ps.data_space_id = ds.data_space_id

LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)

ON pf.function_id = ps.function_id

LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)

ON dds.partition_scheme_id = ps.data_space_id

AND dds.destination_id = p.partition_number

LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)

ON fg.data_space_id = i.data_space_id

LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)

ON fgp.data_space_id = dds.data_space_id

LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)

ON ps.function_id = prv_left.function_id

AND prv_left.boundary_id = p.partition_number - 1

LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)

ON ps.function_id = prv_right.function_id

AND prv_right.boundary_id = p.partition_number

WHERE

OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0

ORDER BY

SchemaName

,TableName

,IndexName

,PartitionNumber

25th December 201925th December 2019

SQL Server 2019 Administration Inside Out

SQL Server 2019 Administration Inside Out Paperback – release date: 2020 by Randolph West, Melody Zacharias, William Assaf, Sven Aelterman

Conquer SQL Server 2019 administration–from the inside out

Dive into SQL Server 2019 administration–and really put your SQL Server DBA expertise to work. This supremely organized reference packs hundreds of time-saving solutions, tips, and workarounds–all you need to plan, implement, manage, and secure SQL Server 2019 in any production environment: on-premises, cloud, or hybrid. SQL Server experts offer a complete tour of DBA capabilities available in SQL Server 2019 Database Engine, SQL Server Data Tools, SQL Server Management Studio, and via PowerShell. Discover how experts tackle today’s essential tasks–and challenge yourself to new levels of mastery.

Install, customize, and use SQL Server 2019’s key administration and development tools
Manage memory, storage, clustering, virtualization, and other components
Architect and implement database infrastructure, including IaaS, Azure SQL, and hybrid cloud configurations
Provision SQL Server and Azure SQL databases
Secure SQL Server via encryption, row-level security, and data masking
Safeguard Azure SQL databases using platform threat protection, firewalling, and auditing
Establish SQL Server IaaS network security groups and user-defined routes
Administer SQL Server user security and permissions
Efficiently design tables using keys, data types, columns, partitioning, and views
Utilize BLOBs and external, temporal, and memory-optimized tables
Master powerful optimization techniques involving concurrency, indexing, parallelism, and execution plans
Plan, deploy, and perform disaster recovery in traditional, cloud, and hybrid environments

For Experienced SQL Server Administrators and Other Database Professionals

10th January 201916th January 2019

Adatbázis audit

DevOps környezetben vagy éles környezetben érdemes auditot beállítani. Saját érdekünkben érdemes tudni, hogy ki nyúlt bele és mit csinált!

CREATE SERVER AUDIT [Audit-20190110]
TO FILE
( FILEPATH = N'H:\Audit\'
,MAXSIZE = 100 MB
,MAX_FILES = 500
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 3000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON)
GO

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110]
FOR SERVER AUDIT [Audit-20190110]
-- ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_MIRRORING_LOGIN_GROUP),
-- ADD (LOGOUT_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO

--Create the database Audit spec
CREATE DATABASE AUDIT SPECIFICATION [dbaudit]
FOR SERVER AUDIT [Audit-20190110]
ADD ( AUDIT_CHANGE_GROUP )
,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)
,ADD ( BACKUP_RESTORE_GROUP )
,ADD ( DATABASE_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )
,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
,ADD ( DBCC_GROUP )
,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )
,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )
,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )
-- 2012-newer allowed events
,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
   ,ADD ( USER_DEFINED_AUDIT_GROUP )
   --,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

WITH ( STATE = ON);

GO

SELECT event_time
,action_id
,session_server_principal_name AS UserName
,server_instance_name
,database_name
,schema_name
,object_name
,statement
FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT)
-- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

CREATE SERVER AUDIT [Audit-20190110]

TO FILE

( FILEPATH = N'H:\Audit\'

,MAXSIZE = 100 MB

,MAX_FILES = 500

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 3000

,ON_FAILURE = CONTINUE

)

ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON)

GO

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110]

FOR SERVER AUDIT [Audit-20190110]

-- ADD (DATABASE_OBJECT_ACCESS_GROUP),

ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

ADD (BACKUP_RESTORE_GROUP),

ADD (AUDIT_CHANGE_GROUP),

ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),

ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (FAILED_LOGIN_GROUP),

ADD (DATABASE_MIRRORING_LOGIN_GROUP),

-- ADD (LOGOUT_GROUP),

ADD (DATABASE_CHANGE_GROUP),

ADD (DATABASE_OBJECT_CHANGE_GROUP),

ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (SERVER_OBJECT_CHANGE_GROUP),

ADD (SERVER_OPERATION_GROUP),

ADD (LOGIN_CHANGE_PASSWORD_GROUP),

ADD (SERVER_STATE_CHANGE_GROUP),

ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),

ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),

ADD (USER_CHANGE_PASSWORD_GROUP)

WITH (STATE = ON)

GO

--Create the database Audit spec

CREATE DATABASE AUDIT SPECIFICATION [dbaudit]

FOR SERVER AUDIT [Audit-20190110]

ADD ( AUDIT_CHANGE_GROUP )

,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)

,ADD ( BACKUP_RESTORE_GROUP )

,ADD ( DATABASE_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )

,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )

,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )

,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )

,ADD ( DBCC_GROUP )

,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )

,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )

,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )

,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )

,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )

-- 2012-newer allowed events

,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )

,ADD ( USER_DEFINED_AUDIT_GROUP )

--,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

WITH ( STATE = ON);

GO

SELECT event_time

,action_id

,session_server_principal_name AS UserName

,server_instance_name

,database_name

,schema_name

,object_name

,statement

FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT)

-- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

14th November 2018

Nem használt oszlopok – Empty columns

Default Null értékű oszlopok , ahol minden Null értékű:

    -- Returns a list of all columns in current database
    -- where the column's value is null for all records.
    
	declare @tempTable TABLE
    (
        TableSchema nvarchar(256),
        TableName nvarchar(256),
        ColumnName sysname,
        NotNullCnt bigint
    );
     
    declare @sql nvarchar(4000);
    declare @tableSchema nvarchar(256);
    declare @tableName nvarchar(256);
    declare @columnName sysname;
    declare @cnt bigint;
     
    declare columnCursor cursor FOR
        SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 
		-- TABLE_NAME ='tablename' and
		IS_NULLABLE = 'YES';
     
    open columnCursor;
     
    fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
     
    while @@FETCH_STATUS = 0
    begin
        -- use dynamic sql to get count of records where column is not null
        SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
            '] where [' + @columnName + '] is not null';
        print @sql; --uncomment for debugging
        exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;
     
        INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;
     
        fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
    end
     
    close columnCursor;
    deallocate columnCursor;
     
    SELECT * FROM @tempTable WHERE NotNullCnt = 0;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

-- Returns a list of all columns in current database

-- where the column's value is null for all records.

declare @tempTable TABLE

(

TableSchema nvarchar(256),

TableName nvarchar(256),

ColumnName sysname,

NotNullCnt bigint

);

declare @sql nvarchar(4000);

declare @tableSchema nvarchar(256);

declare @tableName nvarchar(256);

declare @columnName sysname;

declare @cnt bigint;

declare columnCursor cursor FOR

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE

-- TABLE_NAME ='tablename' and

IS_NULLABLE = 'YES';

open columnCursor;

fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;

while @@FETCH_STATUS = 0

begin

-- use dynamic sql to get count of records where column is not null

SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +

'] where [' + @columnName + '] is not null';

print @sql; --uncomment for debugging

exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;

fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;

end

close columnCursor;

deallocate columnCursor;

SELECT * FROM @tempTable WHERE NotNullCnt = 0;

10th November 201810th November 2018

Table size

Tábla méretek , használt tartalommal:

-- table sizes , tábla méretek egy adatbázisban
SELECT 
    @@SERVERNAME  AS servername,
    DB_NAME ( )  AS dbname,
    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.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
	,format(getdate(), 'yyyy-MM-dd') as Datum
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 
    TotalSpaceMB desc , UsedSpaceKB desc, TableName desc

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

-- table sizes , tábla méretek egy adatbázisban

SELECT

@@SERVERNAME AS servername,

DB_NAME ( ) AS dbname,

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.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

,format(getdate(), 'yyyy-MM-dd') as Datum

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

TotalSpaceMB desc , UsedSpaceKB desc, TableName desc

10th September 201810th September 2018

SQL Server 2018? SQL Server 2019!

A következő SQL server verzió mégsem 2018 lesz ( az igényeknek megfelelően egyre gyakrabban adjuk ki az új verziókat ígéret), hanem SQL Server 2019 néven fog megjelenni. (hasonlóan Windwos Server 2019, Office 2019).

25th July 20182nd August 2018

HEAP table is fragmented

Általában nem ajánlott HEAP tábla létrehozása (Table without Clustered Index) , egy esetben jöhet jól, ha nagyon gyors adatbetöltés szükséges.
A Heap tábla azonban töredezik, ennek oka, hogy minden Insert a tábla végére kerül és a Deleted sorok sem kerülnek betöltésre.
Amíg a hagyományos indexeket egy index reorganizációval vagy újraépítéssel (rebuild) karbantarthajtuk és gyorssá tehetjük addig a Heap táblán nincs ilyen beépített karbantartás .
Teendő?

Continue reading “HEAP table is fragmented”

18th June 201821st June 2018

SQL Server Index Tuning

Egy több millió sorral rendelkező táblán már súlyos gondokat okozhat egy-egy index hiány.

5 Millió sor, paraméterezett select, a szerver túlterhelt:

CPU and Processor Queue Length - Performance Monitor — CPU and Processor Queue Length – Performance Monitor

11:20-kor 1 db index felrakása után látványosan csökken a CPU (kék) és CPU Queue (piros). Ellenpróba 13:35-kor az index letiltásakor ismét befullad a szerver (a határértéknek számító 80%-ról 95% átlagos cpu használatra ugrik). Ráadásul a szerveren több mint 100 adatbázis van és mégis egy darab eljárás jelentősen beterhel.
Persze a valóság nem ilyen egyszerű, egy -egy index felrakásának hatását többnyire nem lehet performance monitor-ból kimutatni, a fenti példa egy rosszul tervezett gyakori futású kódra vonatkozik.

Index méretek

Index size

--  USE <databasename>
SELECT i.[name] AS IndexName
                ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
                AND s.[index_id] = i.[index_id]
-- where i.[name] like 'mix%'
GROUP BY i.[name]
ORDER BY IndexSizeKB Desc, i.[name]
GO

1

2

3

4

5

6

7

8

9

10

-- USE <databasename>

SELECT i.[name] AS IndexName

,SUM(s.[used_page_count]) * 8 AS IndexSizeKB

FROM sys.dm_db_partition_stats AS s

INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]

AND s.[index_id] = i.[index_id]

-- where i.[name] like 'mix%'

GROUP BY i.[name]

ORDER BY IndexSizeKB Desc, i.[name]

GO

Az egyes indexek méretei. Az adott adatbázisra kell használni, szükség esetén szűrni.