Wednesday 30 August 2017
Friday 4 August 2017
Understanding SQL Server Security
·
In order to allow the users to work with required
objects we need to
- Either grant the permissions to the users
- Or Add the user(s) to required ROLE
SQL Server
supports 3 types of permissions
- DATABASE Level
- SCHEMA Level
- OBJECT Level
To work with
permissions we need
·
GRANT ( To grant
permissions)
·
REVOKE (To take
back permission)
·
DENY (To
restrict other users should not grant permissions)
1. Database Level:
·
Below are the database level privileges
- Backup database
- Create table
- Show plan
- Alter table
- Select, insert, update e.t.c.,
Syntax: Grant ….. to
<UserName/RoleName> [WITH GRANT OPTION]
Ex : Grant select, backup database , insert to User_Name
2. Schema Level Permission:
· To group similar table in a database we can create
schemas
· By default every table is stored in ‘dbo’ schema
· Schema provides the following advantages
- To group similar tables
- To grant permission on more than one table at once.
- No need to change the owner of the table(s) if the user is deleted
Syntax to grant schema level
permission
GRANT ….. ON SCHEMA::[schemaName] to user/role
Ex: GRANT SELECT
ON schema::[sales] to user_Name
3. Object Level Permission:
· To Grant permissions on individual objects like tables,
views etc.
Syntax to grant object level
permission
GRANT ….. ON <objectName> to
<user/role>
Ex: GRANT SELECT, UPDATE, DELETE
on EMP(empid,ename) TO John
DATABASE ROLES:
· Role is used to group a set of privileges.
· We can reduce the process of granting and taking back
permission to large number of users with roles.
· SQL Server supports 3 types of roles
o Fixed Database Roles
o Custom Database Roles
o Application Roles
Fixed-Database Roles
The following
table shows the fixed-database roles and their capabilities. These roles exist
in all databases. The permissions assigned to the fixed-database roles cannot
be changed. The permissions assigned to the fixed-database roles cannot be
changed. The following figure shows the permissions assigned to the
fixed-database roles:
Fixed-Database role name
|
Description
|
db_owner
|
Members of the db_owner fixed database role
can perform all configuration and maintenance activities on the database, and
can also drop the database in SQL Server. (In SQL Database and SQL Data
Warehouse, some maintenance activities require server-level permissions and
cannot be performed by db_owners.)
|
db_securityadmin
|
Members of the db_securityadmin fixed
database role can modify role membership and manage permissions. Adding
principals to this role could enable unintended privilege escalation.
|
db_accessadmin
|
Members of the db_accessadmin fixed database
role can add or remove access to the database for Windows logins, Windows
groups, and SQL Server logins.
|
db_backupoperator
|
Members of the db_backupoperator fixed
database role can back up the database.
|
db_ddladmin
|
Members of the db_ddladmin fixed database
role can run any Data Definition Language (DDL) command in a database.
|
db_datawriter
|
Members of the db_datawriter fixed database
role can add, delete, or change data in all user tables.
|
db_datareader
|
Members of the db_datareader fixed database
role can read all data from all user tables.
|
db_denydatawriter
|
Members of the db_denydatawriter fixed
database role cannot add, modify, or delete any data in the user tables
within a database.
|
db_denydatareader
|
Members of the db_denydatareader fixed
database role cannot read any data in the user tables within a database.
|
Custom-Database Roles
We can create role with required privileges in order to
assign a group of people related privileges.
We can reduce
the process of granting and taking backup permissions to large number of users
with custom roles.
Steps :
1.
Creating Role
Syntax : Create Role
<RoleName>
2.
Granting
permissions to the role
Syntax : GRANT …. TO <RoleName>
3.
Adding users to
the role using sp_addrolemember
Example : Create role with the name CustomerCare_Role
Grant select, insert on emp table – Object Level
Grant select on library schema -
Schema Level
Grant backup database permission
– database level
1. Creating Role
Create Role
CustomerCare_Role
2. Granting permissions to the role
Grant select,
insert on emp to CustomerCare_Role
Grant select on
Schema::[Library] to CustomerCare_Role
Grant backup
database to CustomerCare_Role
3. Granting permissions to the role
Sp_addrolemember
@rolename=’CustomerCare_Role’, @membername=’<User_Name>’
Tuesday 11 April 2017
SQL Server DBA - Most useful script
----prep-----------------------
declare @tsql nvarchar(max)
declare @dbname sysname
declare @latencyThreshold int
declare @oldDate datetime
declare @ignoreheap int
declare @modcounter int
declare @tablename sysname
declare @inclSysTables int
declare @ProductVersion nvarchar(20)
declare @memThreshold int
declare @DrivePctThreshold numeric (16,2)
declare @ProductLevel table (partkey int identity(1,1),parts int)
declare @runMemoryCheck bit,@runBlockingCheck bit, @runParrallelCheck bit, @runErrorLogCheck bit,
@runDiskSpaceCheck bit, @runStatsLastUpdatedCheck bit, @runStatsModCounterCheck bit,
@runIndexFragCheck bit, @runCPUCheck bit, @runAvgCPUCheck bit,
@runAvgIOCheck bit, @runAvgLogicalIOReadCheck bit,@runAvgLogicalIOWriteCheck bit,
@runWaitTypesCheck bit, @runLatencyCheck bit, @runLongRunQueryCheck bit,
@runOpenTran1Check bit, @runOpenTran2Check bit, @runOpenTran3Check bit, @runLockCheck bit,
@runCurRunnableTopQueriesByCPU bit
--select @runMemoryCheck = 1,@runBlockingCheck = 1, @runParrallelCheck = 1, @runErrorLogCheck = 1,
-- @runDiskSpaceCheck = 1, @runStatsLastUpdatedCheck = 1, @runStatsModCounterCheck = 1,
-- @runIndexFragCheck = 1, @runCPUCheck = 1, @runAvgCPUCheck = 1,
-- @runAvgIOCheck = 1, @runAvgLogicalIOReadCheck = 1,@runAvgLogicalIOWriteCheck =1,
-- @runWaitTypesCheck = 1, @runLatencyCheck = 1, @runLongRunQueryCheck = 1,
-- @runOpenTran1Check = 1, @runOpenTran2Check = 1, @runOpenTran3Check = 1, @runLockCheck = 1,
-- @runCurRunnableTopQueriesByCPU = 1
select @runMemoryCheck = 1,@runBlockingCheck = 1, @runParrallelCheck = 1, @runErrorLogCheck = 0,
@runDiskSpaceCheck = 0, @runStatsLastUpdatedCheck = 0, @runStatsModCounterCheck = 0,
@runIndexFragCheck = 0, @runCPUCheck = 0, @runAVGCPUCheck = 0,
@runAvgIOCheck = 0, @runAvgLogicalIOReadCheck = 0,@runAvgLogicalIOWriteCheck =0,
@runWaitTypesCheck = 0, @runLatencyCheck = 0, @runLongRunQueryCheck = 0,
@runOpenTran1Check = 0, @runOpenTran2Check = 0, @runOpenTran3Check = 0, @runLockCheck = 0,
@runCurRunnableTopQueriesByCPU = 0
DECLARE
@CPU_BUSY int
, @IDLE int, @CPUBusyPct decimal(16,4), @CPUThreshold int
set @memThreshold = 1000.00 --- mb when get warning
set @DrivePctThreshold = 10.00 --- percent free on drive threshold
set @CPUThreshold = 90.00 -- Percentage cpu usage
set @latencyThreshold = 1 --- used for latency check returns where latency column value greater than this number
set @dbname = null -- 'master' ---- can be null or specify database name (for stats check)
set @oldDate = dateadd(day, -0, getdate())
set @ignoreheap = 1 -- ignore heap -- forstats check 1
set @modcounter = 10000 -- for stats check Total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated
set @tablename = null --- name of specific table -- for stats check
set @inclSysTables = 0 --- 0 for no, 1 for yes -- for stats check
select @ProductVersion= cast(serverproperty('ProductVersion') as nvarchar(20))
declare @StringList VARCHAR(MAX), @Delimiter CHAR(1)
set @StringList = @ProductVersion
set @Delimiter = '.'
DECLARE @XML xml
SET @XML = '<root><csv>'+replace(@StringList,@Delimiter,'</csv><csv>')+
'</csv></root>'
insert into @ProductLevel
(parts)
SELECT cast(rtrim(ltrim(replace(Word.value('.','nvarchar(128)'),char(10),''))) as int)
AS ListMember
FROM @XML.nodes('/root/csv') AS WordList(Word)
-------------------------------
--- check 0 - memlor
-------------------------------
if @runMemoryCheck = 1
begin
if exists(
select 1 from @ProductLevel PL2k12
where (partkey = 1 and parts >=11)
union all
select 1 from @ProductLevel t1
--cross apply @ProductLevel t2
--cross apply @ProductLevel t3
where (t1.partkey = 1 and t1.parts >=10)
--and t2.partkey = 2 and t2.parts >= 50
--and t3.partkey = 3 and t3.parts >=4000
)
begin
select 'Memory Check' as CheckDesc,
case when (available_physical_memory_kb/1024.0) < @memThreshold then
'Warning - Low Memory'
else
'None'
end as Warning,
total_physical_memory_kb /1024.0 as TotalPhysicalMB,
(total_physical_memory_kb /1024.0)/1024.0 as TotalPhysicalGB,
available_physical_memory_kb/1024.0 as AvailablePhysicalMB,
(available_physical_memory_kb/1024.0) /1024.0 as AvailablePhysicalGB,
total_physical_memory_kb /1024.0 -
available_physical_memory_kb/1024.0 as usedMB ,
(total_physical_memory_kb /1024.0 -
available_physical_memory_kb/1024.0) /1024.0 as usedGB
from sys.dm_os_sys_memory
end
else
begin
select 'Memory Check' as CheckDesc,
'Not Supported' as Warning,
0.00 TotalPhysicalMB,
0.00 as TotalPhysicalGB,
0.00 as AvailablePhysicalMB,
0.00 as AvailablePhysicalGB,
0.00 as usedMB ,
0.00 as usedGB
end
end
-------------------------------
--- check 1 -> blocking
-------------------------------
if @runBlockingCheck = 1
begin
select 'Blocking Processes Check' as CheckDesc,
DB_NAME(sp1.dbid) as DBName,
sp2.spid,st2.text as blockedtext,sp1.spid,
st.text,
SUBSTRING(st.text, (sp1.stmt_start/2)+1,
((CASE sp1.stmt_end
WHEN -1 THEN DATALENGTH(st.text)
ELSE sp1.stmt_end
END - sp1.stmt_start)/2) + 1) AS statement_text,
sp1.spid,sp1.lastwaittype, sp1.waitresource,sp1.dbid,
sp1.loginame
,(select s_eqp.query_plan from sys.dm_exec_requests [s_er] with(nolock)
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
where s_er.session_id = sp1.spid) as BlockingPlan
,(select s_eqp2.query_plan from sys.dm_exec_requests [s_er2] with(nolock)
OUTER APPLY
sys.dm_exec_query_plan ([s_er2].[plan_handle]) AS [s_eqp2]
where s_er2.session_id = sp2.spid) as BlockedPlan
--,(select [query_plan] AS [Last Plan] from sys.dm_exec_query_plan ([st2].[plan_handle]) AS [s_eqp] )
from master..sysprocesses sp1 with(nolock)
inner join master..sysprocesses sp2 with(nolock)
on sp2.blocked = sp1.spid
and sp1.spid <> sp2.spid
CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) AS st
CROSS APPLY sys.dm_exec_sql_text(sp2.sql_handle) AS st2
where sp1.blocked = 0
end
-------------------------------
--- check 2 -> Parrallelism
-------------------------------
if @runParrallelCheck = 1
begin
select 'Parrallel Processes Check' as CheckDesc,
db_name(sp1.dbid) as DBName,
(select COUNT(spid) from master..sysprocesses with(nolock) where spid = sp1.spid) as SPIDCount,
st.text,
SUBSTRING(st.text, (sp1.stmt_start/2)+1,
((CASE sp1.stmt_end
WHEN -1 THEN DATALENGTH(st.text)
ELSE sp1.stmt_end
END - sp1.stmt_start)/2) + 1) AS statement_text,
sp1.spid,sp1.lastwaittype, sp1.waitresource,sp1.dbid,
sp1.loginame
,(select s_eqp.query_plan from sys.dm_exec_requests [s_er] with(nolock)
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
where s_er.session_id = sp1.spid) as ParrallelQueryPlan
from master..sysprocesses sp1 with(nolock)
CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) AS st
where exists (select spid, COUNT(spid) as c from master..sysprocesses with(nolock) where spid = sp1.spid
group by spid having COUNT(spid) > 1)
end
-------------------------------
--- check 3 - errors in log
-------------------------------
if @runErrorLogCheck = 1
begin
declare @errLog table (LogDate datetime, ProcessInfo nvarchar(max), ErrorLogRawText nvarchar(max))
insert into @errLog
execute xp_readerrorlog
select 'ErrorLog Check' as CheckDesc, * from @errLog where ErrorLogRawText like '%error%' and ErrorLogRawText not like 'Logging SQL Server messages in file%'
and ErrorLogRawText not like 'The error log has been reinitialized.%'
end
-------------------------------
--- check 4 - disk space
-------------------------------
if @runDiskSpaceCheck = 1
begin
if OBJECT_ID('tempdb..#DriveSpace') is not null
begin
drop table #DriveSpace
end
if OBJECT_ID('tempdb..#DriveInfo') is not null
begin
drop table #DriveInfo
end
DECLARE
@FSOobjResult INT
,@DriveResult INT
,@objFSO INT
,@Drv INT
,@cDrive VARCHAR(13)
,@Size VARCHAR(50)
,@Free VARCHAR(50)
,@Label VARCHAR(50)
,@DriveLetter CHAR(1)
CREATE TABLE #DriveInfo
(
DriveLetter CHAR(1)
,VolumeName VARCHAR(50)
,TotalSpace BIGINT
,FreeSpace BIGINT
)
CREATE TABLE #DriveSpace
(
DriveLetter CHAR(1) NOT NULL
,FreeSpace VARCHAR(10) NOT NULL
)
INSERT INTO #DriveSpace
EXEC master..xp_FixedDrives
while exists (select top 1 1 from #DriveSpace)
begin
set @DriveLetter = (select top 1 DriveLetter from #DriveSpace)
SET @cDrive = 'GetDrive("' + @DriveLetter + '")'
EXEC @FSOobjResult = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT
IF @FSOobjResult = 0
BEGIN
EXEC @DriveResult = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT
IF @DriveResult = 0
BEGIN
EXEC sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT
EXEC sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT
EXEC sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT
SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
INSERT INTO #DriveInfo
VALUES (@DriveLetter, @Label, @Size, @Free)
EXEC sp_OADestroy @Drv
END
ELSE
BEGIN
PRINT 'Drive info not available for Drive ' + @DriveLetter
EXEC sp_OADestroy @Drv
END
EXEC sp_OADestroy @objFSO
END
ELSE
BEGIN
PRINT 'File System Object info not available for Drive ' + @DriveLetter
EXEC sp_OADestroy @objFSO
END
delete from #DriveSpace where DriveLetter = @DriveLetter
end
SELECT 'Drive Space Check' as CheckDesc,
UPPER(@@SERVERNAME) AS ServerName
,VolumeName
,DriveLetter
,FreeSpace AS [FreeSpaceMB]
,(TotalSpace - FreeSpace) AS [UsedSpaceMB]
,TotalSpace AS [TotalSpaceMB]
,((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [DrivePercentageFree]
,@DrivePctThreshold as DrivePctThreshold
,GETDATE() AS [DateTimeStamp_DTML]
,GETUTCDATE() AS [DateTimeStamp_DTMZ]
FROM #DriveInfo
where ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) < @DrivePctThreshold
ORDER BY [DriveLetter] ASC
drop table #DriveSpace
drop table #DriveInfo
end
-------------------------------
--- check 5.1 - statistics last update check 1
-------------------------------
if @runStatsLastUpdatedCheck = 1
begin
----declare @dbname sysname
----declare @oldDate datetime
----declare @ignoreheap int
----set @dbname = null -- 'master' ---- can be null or specify database name
----set @oldDate = dateadd(day, -1, getdate())
----set @ignoreheap = 1 -- ignore heap
if OBJECT_ID('tempdb..#temp') is not null
begin
drop table #temp
end
Create Table #temp
(
databasename sysname,
tablename sysname,
indexname sysname,
indexdesc nvarchar(100),
statsupdate datetime
)
Exec sp_msforeachdb '
Use [?];
Insert Into #temp (databasename,tablename, indexname, indexdesc, statsupdate)
select distinct ''[?]'',t.name as tablename
,isnull(i.name, ''Heap'') as indexname, i.type_desc as indexdesc,
stats_date(i.object_id,i.index_id) as statsupdate
from [?].sys.indexes i with(nolock)
inner join [?].sys.tables t with(nolock)
on i.object_id = t.object_id
inner join [?].sys.partitions p with(nolock)
on i.object_id = p.object_id
and p.rows > 0;
'
Select 'Stats Last Update Check' as CheckDesc, replace(replace(databasename, '[', ''), ']', ''),
tablename, indexname, indexdesc, statsupdate,
replace(replace(databasename, '[', ''), ']', '') + '..sp_updatestats ' as ResolutionTip1,
' use ' + replace(replace(databasename, '[', ''), ']', '') + '; UPDATE STATISTICS ' + tablename as ResolutionTip2
From #temp
where replace(replace(databasename, '[', ''), ']', '') = isnull(@dbname, replace(replace(databasename, '[', ''), ']', ''))
and isnull(statsupdate, '1/1/1999') <= isnull(@oldDate, '1/1/1999')
and( @ignoreheap = 0 or (@ignoreheap = 1 and indexname <> 'Heap'))
order by replace(replace(databasename, '[', ''), ']', ''), statsupdate desc
drop table #temp
end
-------------------------------
--- check 5.2 - statistics check 2
---- date/time and num of modifications
-------------------------------
if @runStatsModCounterCheck = 1
begin
--declare @dbname2 sysname
--declare @modcounter int
--declare @oldDate2 datetime
--declare @tablename sysname
--declare @inclSysTables int
--set @dbname2 = null -- 'master' ---- can be null or specify database name
--set @modcounter = 10000 -- Total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated
--set @oldDate2 = dateadd(day, -0, getdate()) --- find indexes updated previous to this time
--set @tablename = null --- name of specific table
--set @inclSysTables = 0 --- 0 for no, 1 for yes
if OBJECT_ID('tempdb..#temp2') is not null
begin
drop table #temp2
end
Create Table #temp2
(
databasename sysname,
tablename sysname,
tabletype varchar(5),
statsname sysname,
statsid int,
lastupdated datetime,
modification_counter bigint
)
if exists(
select 1 from @ProductLevel PL2k12
where (partkey = 1 and parts >=11)
union all
select 1 from @ProductLevel t1
cross apply @ProductLevel t2
cross apply @ProductLevel t3
where (t1.partkey = 1 and t1.parts >=10)
and t2.partkey = 2 and t2.parts >= 50
and t3.partkey = 3 and t3.parts >=4000
)
begin
Exec sp_msforeachdb '
Use [?];
Insert Into #temp2 (databasename,tablename, tabletype,statsname, statsid, lastupdated, modification_counter)
select distinct ''[?]'',obj.name, obj.type,stat.name, stat.stats_id, last_updated, modification_counter
FROM [?].sys.objects AS obj with(nolock)
JOIN [?].sys.stats stat with(nolock)
ON stat.object_id = obj.object_id
CROSS APPLY [?].sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
order by modification_counter desc;
'
end
else
begin
Insert Into #temp2 (databasename,tablename, tabletype,statsname, statsid, lastupdated, modification_counter)
select distinct 'NotSupported','NotSupported', '','',0,'1/1/1999',99999999
end
Select 'Stats ModCounter Check' as CheckDesc, replace(replace(databasename, '[', ''), ']', ''),
tablename, tabletype,statsname, statsid,modification_counter, lastupdated,
replace(replace(databasename, '[', ''), ']', '') + '..sp_updatestats ' as ResolutionTip1,
' use ' + replace(replace(databasename, '[', ''), ']', '') + '; UPDATE STATISTICS ' + tablename as ResolutionTip2
From #temp2
where replace(replace(databasename, '[', ''), ']', '') = isnull(@dbname, replace(replace(databasename, '[', ''), ']', ''))
and isnull(lastupdated, '1/1/1999') <= isnull(@oldDate, '1/1/1999')
and modification_counter >= @modcounter
and tablename = isnull(@tablename, tablename)
and (@inclSysTables = 1 or (@inclSysTables = 0 and tabletype <> 'S'))
order by replace(replace(databasename, '[', ''), ']', ''), lastupdated desc
drop table #temp2
end
-------------------------------
--- check 6 - index fragmentation
-------------------------------
if @runIndexFragCheck = 1
begin
select 'Index Fragmentation Check' as CheckDesc, 'Still need to write this part' as Notes
end
-------------------------------
--- check 7 - CPU
-------------------------------
if @runCPUCheck = 1
begin
SELECT
@CPU_BUSY = @@CPU_BUSY
, @IDLE = @@IDLE
WAITFOR DELAY '000:00:01'
set @CPUBusyPct = (
SELECT
(@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *
1.00) *100)-- AS CPUBusyPct
if @CPUBusyPct > @CPUThreshold--90.00
begin
select 'CPU Check' as CheckDesc, 'CPU Usage High' as CPUResponse, @CPUThreshold as Threshold, @CPUBusyPct as CPU
end
else
begin
select 'CPU Check' as CheckDesc, 'CPU Usage Ok' as CPUResponse, @CPUThreshold as Threshold, @CPUBusyPct as CPU
end
end
-------------------------------
--- check 8 - logical IO
-------------------------------
if @runAvgIOCheck = 1
begin
set @tsql = N'begin try
select top 100 ''Top 100 Avg IO Check UserDB(s)'' as CheckDesc
, rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, (total_logical_reads+0.0)/execution_count as [AvgLogicalReads]
, (total_logical_writes+0.0)/execution_count as [AvgLogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
, case when sql_handle IS NULL
then '' ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ))
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where (total_logical_reads+total_logical_writes ) > 0
and st.dbid > 4
'
if @dbname is not null
begin
set @tsql = @tsql + ' and db_name(st.dbid) = ''' + @dbname + ''''
end
set @tsql = @tsql + '
order by [AvgIO] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS AvgCPUTime, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch'
exec sp_executesql @stmt=@tsql,@params=N''
end
-------------------------------
--- check 9 - logical IO reads and writes
-------------------------------
if @runAvgLogicalIOReadCheck = 1
begin
set @tsql = N'begin try
select top 100 ''Top 100 Avg Logical IO Reads UserDB(s)'' as CheckDesc
, rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, (total_logical_reads+0.0)/execution_count as [AvgLogicalReads]
, (total_logical_writes+0.0)/execution_count as [AvgLogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
, case when sql_handle IS NULL
then '' ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ))
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where (total_logical_reads+total_logical_writes ) > 0
and st.dbid > 4
'
if @dbname is not null
begin
set @tsql = @tsql + ' and db_name(st.dbid) = ''' + @dbname + ''''
end
set @tsql = @tsql + '
order by [AvgLogicalReads] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS AvgCPUTime, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch'
exec sp_executesql @stmt=@tsql,@params=N''
end
if @runAvgLogicalIOWriteCheck = 1
begin
set @tsql = N'begin try
select top 100 ''Top 100 Avg Logical IO Writes UserDB(s)'' as CheckDesc
, rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, (total_logical_reads+0.0)/execution_count as [AvgLogicalReads]
, (total_logical_writes+0.0)/execution_count as [AvgLogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
, case when sql_handle IS NULL
then '' ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ))
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where (total_logical_reads+total_logical_writes ) > 0
and st.dbid > 4
'
if @dbname is not null
begin
set @tsql = @tsql + ' and db_name(st.dbid) = ''' + @dbname + ''''
end
set @tsql = @tsql + '
order by [AvgLogicalWrites] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS AvgCPUTime, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch'
exec sp_executesql @stmt=@tsql,@params=N''
end
-------------------------------
--- check 10 - wait types
-------------------------------
if @runWaitTypesCheck = 1
begin
--------------------------------------------------------------------------------------------
--- paul randall's query
----http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
--------------------------------------------------------------------------------------------
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
)
SELECT 'Wait Types Check' as CheckDesc,
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
[W1].[WaitCount] AS [WaitCount],
CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95 -- percentage threshold
end
-------------------------------
--- check 11 - latency
-------------------------------
if @runLatencyCheck = 1
begin
--------------------------------------------------------------------------------------------------------
--- Paul Randal's script
--- http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
--------------------------------------------------------------------------------------------------------
SELECT 'Latency Check' as CheckDesc,
--virtual file latency
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
--avg bytes per IOP
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [io_stall_write_ms] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
--[vfs].*,
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf] with(nolock)
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
where DB_NAME ([vfs].[database_id]) = isnull(@dbname, DB_NAME ([vfs].[database_id]))
and CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END > @latencyThreshold
ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
--ORDER BY [WriteLatency] DESC;
end
-------------------------------
--- check 12 - long running queries
-------------------------------
if @runLongRunQueryCheck = 1
begin
select
TOP 50 'Long Running Query Check' as CheckDesc,
SUBSTRING(t.text, (s.statement_start_offset/2)+1,
((CASE s.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE s.statement_end_offset
END - s.statement_start_offset)/2) + 1) AS statement_text,
s.max_elapsed_time AS MaxElapsedTime,
--s.max_rows,
p.query_plan,OBJECT_NAME(p.objectid) as objectname
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
OUTER APPLY
sys.dm_exec_query_plan (s.[plan_handle]) AS p
ORDER BY
s.max_elapsed_time DESC
end
-------------------------------
--- check 13 - open transactions
-------------------------------
if @runOpenTran1Check = 1
begin
----- version 1
select 'Open Transaction 1 Check' as CheckDesc,
db_name(sp1.dbid) as DBName,
st.text,
SUBSTRING(st.text, (sp1.stmt_start/2)+1,
((CASE sp1.stmt_end
WHEN -1 THEN DATALENGTH(st.text)
ELSE sp1.stmt_end
END - sp1.stmt_start)/2) + 1) AS statement_text,
sp1.spid,sp1.lastwaittype, sp1.waitresource,sp1.dbid,
sp1.loginame, sp1.open_tran, sp1.waittime, sp1.waitresource,
sp1.lastwaittype, sp1.login_time,sp1.last_batch
from master..sysprocesses sp1 with(nolock)
CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) AS st
where open_tran > 0
and db_name(sp1.dbid) = isnull(@dbname,db_name(sp1.dbid))
end
----- version 2
if @runOpenTran2Check = 1
begin
select 'Open Transaction 2 Check', 'Still need to write this part for dbcc opentran' as Notes --- dbcc opentran
end
----- version 3
if @runOpenTran3Check = 1
begin
-- from paul randal
---http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/
SELECT 'Open Transaction 3 Check' as CheckDesc,
[s_tst].[session_id],
s_es.host_name,
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM
sys.dm_tran_database_transactions [s_tdt] with(nolock)
JOIN
sys.dm_tran_session_transactions [s_tst] with(nolock)
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
sys.[dm_exec_sessions] [s_es] with(nolock)
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec] with(nolock)
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er] with(nolock)
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
where
DB_NAME (s_tdt.database_id) = isnull(@dbname,DB_NAME (s_tdt.database_id))
ORDER BY [s_tst].[session_id],
[Begin Time] ASC
end
-------------------------------
----- check 14 locks ----------
-------------------------------
if @runLockCheck = 1
begin
select request_session_id,db_name(resource_database_id),
case request_mode
when 'S' then 'Shared'
when 'U' then 'Update'
when 'X' then 'Exclusive'
when 'IS' then 'Intent Shared'
when 'IX' then 'Intent Exclusive'
when 'SIX' then 'Shared with Intent Exclusive'
when 'IU' then 'Intent Update'
when 'SIU' then 'Shared intent Update'
when 'UIX' then 'Update Intent Exclusive'
when 'Sch-M' then 'Schema Modification'
when 'Sch-S' then 'Schema Stability'
when 'BU' then 'Bulk Update'
else request_mode end as TypeofLock,
request_type,request_status,
case resource_type
when 'database' then 'Lock at database level'
when 'object' then OBJECT_NAME(resource_associated_entity_id, resource_database_id)
else resource_type
end as TheResource,
resource_subtype,
Request_Owner_Type
from sys.dm_tran_locks with(nolock)
where db_name(resource_database_id) = ISNULL(@dbname, db_name(resource_database_id))
order by request_session_id
--order by db_name(resource_database_id),request_session_id
end
-----------------------------------------------
----- check 15 top 100 avg cpu check ----------
-----------------------------------------------
if @runAVGCPUCheck = 1
begin
set @tsql = N'begin try
select top 100 ''Top 100 Avg CPU Check For UserDB(s)'' as CheckDesc
, rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ) as row_no
, (rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO]
, case when sql_handle IS NULL
then '' ''
else ( substring(st.text,(qs.statement_start_offset+2)/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) )
end as query_text
, db_name(st.dbid) as db_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_worker_time > 0
and st.dbid > 4 '
if @dbname is not null
begin
set @tsql = @tsql + ' and db_name(st.dbid) = ''' + @dbname + ''''
end
set @tsql = @tsql + ' order by [AvgCPUTime] desc
end try
begin catch
select -100 as row_no
, 1 as l1, 1 as create_time,1 as last_execution_time,1 as total_worker_time,1 as AvgCPUTime,1 as LogicalReads,1 as LogicalWrites
, ERROR_NUMBER() as execution_count
, ERROR_SEVERITY() as AggIO
, ERROR_STATE() as AvgIO
, ERROR_MESSAGE() as query_text
, 0 as db_name
, 0 as object_name
end catch'
exec sp_executesql @stmt=@tsql,@params=N''
end
-----------------------------------------------
----- check 16 runnable top queries ----------
-----------------------------------------------
if @runCurRunnableTopQueriesByCPU = 1
begin
select 'Runnable Queries by CPU' as CheckDesc,
sp1.spid,
DB_NAME(sp1.dbid) as DatabaseName,
st.text,
SUBSTRING(st.text, (sp1.stmt_start/2)+1,
((CASE sp1.stmt_end
WHEN -1 THEN DATALENGTH(st.text)
ELSE sp1.stmt_end
END - sp1.stmt_start)/2) + 1) AS statement_text,
sp1.spid,sp1.lastwaittype, sp1.waitresource,sp1.dbid,
sp1.loginame
from master..sysprocesses sp1
CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) AS st
where sp1.status = 'runnable'
and sp1.spid <> @@spid
and DB_NAME(sp1.dbid) = isnull(@dbname, DB_NAME(sp1.dbid))
order by cpu desc
end
Tuesday 21 March 2017
Partition in SQL Server
Hi All,
I would like to share my knowledge on partitioning in SQL Server,
You can follow me here if you like this post.
Creating table partition on Varchar Column(YEAR MONTH wise)
1. Create file groups equivalent to the ranges of
months we have in column YEARMONTH_SNAP(Example column name). (See below screen
shot)
For Example, If we are inserting data monthly wise and you have 12 months of total data then create 13 file groups.
Note: We have to create one extra
file group than range values we have in column
2. Create Partition function with all the ranges of
month we have. It will map all the column values to respective partitions. Make sure to same datatype in partition function as partition column data type, if you are partitioning on Int column then mention int() data type in PARTITION FUNCTION.
CREATE PARTITION FUNCTION [PF_YM2] (varchar(7)) AS RANGE RIGHT FOR VALUES
(N'2016-11', N'2016-12', N'2017-01', N'2017-02', N'2017-03', N'2017-04', N'2017- 05', N'2017-06', N'2017-07', N'2017-08', N'2017-09')
Note: The Partition function will
keep the above range values in respective partitions using below logic.
Example:
CREATE PARTITION
FUNCTION PF_YM2 (Varchar (7))
AS RANGE RIGHT FOR
VALUES (1, 100, 1000);
3. Create Partition Scheme using the partition function and file groups, it will map all the partitions to respective file groups.
CREATE PARTITION SCHEME
[PS_YM2] AS PARTITION
[PF_YM2] TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10], [FG11], [FG12])
Note: We have to add extra file
group in partition scheme than the partition function range. If we insert more date range values in future it will use this additional file group, it means right now we have 12 partitions to store 12 months of data in case in future if you insert data for 13th month then that data will store in this additional file group.
Example: If we have 11 ranges in
partition function we need to create partition scheme using 12 file groups.
4. Now create cluster index on partitioned column
using partition scheme, it will map all the range values to particular
partition
CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_YM] ON [dbo].[stgp_dcsinvb_cur_monthly]
(
[YEARMONTH_SNAP]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS_YM2] ([YEARMONTH_SNAP])
Validation:
We can
validate the partitions and mapped data on each partition using below script.
SELECT o.name
objectname,i.name
indexname, [partition_id], partition_number,
[rows]
FROM sys.partitions p
INNER JOIN
sys.objects o ON o.object_id=p.object_id
INNER JOIN
sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name
= 'stgp_dcsinvb_cur_monthly'
Subscribe to:
Posts (Atom)