Deleting Duplicate Rows

Frequent deletions to rows in a large table can be a fairly large IO load on SQL server. Not only do the rows get deleted, but indices get updated, foreign key constraints (might) get checked, and cluster indices get fragmented. This can especially be a tricky job in an ETL that regularly bulk loads data from an outside vendor.  I've seen this technique used before:

with v as
(select *, row_number() over (partition by DataField1,
DataField2, DataField3 Order by DataField1) AS dupRows
from myInputTable(nolock))
delete from v
where dupRows > 1

... but this is quite nasty to execute on a large table. A much better strategy is to add two additional columns to your bulk loaded table: a DulplicateFlag (bit) and a rowCheckSum (int). Also make sure your table has an Identity column cluster index. Now populate the rowCheckSum with the SQL checksum function over any fields that are logically significant (but not fields like the Identity column or modified-date columns). Create an index on the table to include the Identity, DulplicateFlag, rowCheckSum, and the natural key.

Now your "deletion" actually just sets the DulplicateFlag, thusly:

WITH a AS (SELECT  MIN(idColumn) AS MinClusterKey, rowCheckSum,
     NaturalKey, COUNT(*) AS howMany
     FROM  myInputTable AS v2
     WHERE  (DulplicateFlag IS NULL)
     GROUP BY rowCheckSum, NaturalKey
     HAVING (COUNT(*) > 1)) 
Update myInputTable
set v.DulplicateFlag = 1
FROM  myInputTable AS v INNER JOIN a
ON v.idColumn <> a.MinClusterKey AND v.NaturalKey = a.NaturalKey 
AND v.rowCheckSum = a.rowCheckSum

This is so much faster. Note that this example is saving the earliest instance of the duplicate; if you'd rather save the latest change the MIN to a MAX. You can then occasionally run a simple cleanup by deleting all rows with the DulplicateFlag set to 1 (if we're talking tons of rows it's faster to drop FK constraints and indices before the delete, then rebuild them after). Of course any queries that use this table downstream should select those with a NULL DulplicateFlag.

TempDB

One of the more important tasks to take care of when you settle into maintaining a new instance is the proper allocation of files to tempdb. How large to make them and the count of how many to create, really depends upon how your developers and their applications are using tempdb. I've seen various other posts that presume to have some sort of algorithm to help you determine this, but I've become somewhat wary of just relying on an outside wild arse guess as an authoritative one-shot setup.

You really face a handful of issues with tempdb. The first is it gets shared by all the processes touching all of the databases on any particular instance. This means even though you can access different files (even across different physical devices) to your databases to avoid contention, they will always unavoidably contend for tempdb. The second issue that pops up is tempdb only shrinks on a SQL service restart (or naturally on a server reboot). The third issue is various software packages (or developers) have their own way to use tempdb: OLTP is normally not too hectic on the thing, but some ETL processes and OLAP aggregation processes can by nasty. Then you always get the developer who joins ten tables with three of them doing full cluster index scans, and tempdb balloons to hold all the temporary data.

For say a terabyte of total databases on an instance, I like to initially set up three or four tempdb data files of ten to forty gigabytes size each. After the instance has been running for a few weeks see if you have any IO stalls on tempdb:

WITH IOFORDATABASE AS
(
SELECT
 DB_NAME(VFS.database_id) AS DatabaseName
,CASE WHEN smf.type = 1 THEN 'LOG_FILE' ELSE 'DATA_FILE' END AS DatabaseFile_Type
,SUM(VFS.num_of_bytes_written) AS IO_Write
,SUM(VFS.num_of_bytes_read) AS IO_Read
,SUM(VFS.num_of_bytes_read + VFS.num_of_bytes_written) AS Total_IO
,SUM(VFS.io_stall) AS IO_STALL
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
JOIN sys.master_files AS smf
 ON VFS.database_id = smf.database_id AND VFS.file_id = smf.file_id
GROUP BY DB_NAME(VFS.database_id), smf.type
)
SELECT
 ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS RowNumber
,DatabaseName, DatabaseFile_Type
,CAST(1.0 * IO_Read/ (1024 * 1024) AS DECIMAL(12, 2)) AS IO_Read_MB
,CAST(1.0 * IO_Write/ (1024 * 1024) AS DECIMAL(12, 2)) AS IO_Write_MB
,CAST(1. * Total_IO / (1024 * 1024) AS DECIMAL(12, 2)) AS IO_TOTAL_MB
,CAST(IO_STALL / 1000. AS DECIMAL(12, 2)) AS IO_STALL_Seconds
,CAST(100. * IO_STALL / SUM(IO_STALL) OVER() AS DECIMAL(10, 2)) AS IO_STALL_Pct
FROM IOFORDATABASE
ORDER BY IO_STALL_Seconds DESC;

If you have stalls higher on tempdb than your other databases, you need to increase the quantity of files preallocated to it. Also check how large it has grown (easiest to see by just opening windows explorer and looking at the file sizes). If the files are a whole lot larger than what you initially allocated then you should adjust your initial allocations up to something like the midpoint of the difference.

Every few months you should go back and review your tempdb sizes and stalls -- developers will have installed new software or even placed further strain on them with additional user databases. Also use a monitoring tool like RedGate to determine which queries are writing heavily to tempdb: you may be surprised to find they may have been constructed incorrectly.

Who's Got What

Every once in a while it helps to stand back and review the security privileges for everyone, down at the database level.  I've found this query to be exceedingly helpful in ferreting out the roles that have been assigned:

WITH RT AS
(SELECT name AS RoleType, principal_id
FROM sys.database_principals
WHERE (type_desc = 'DATABASE_ROLE'))
SELECT P.name, RT.RoleType FROM RT
INNER JOIN sys.database_role_members AS RM
ON RT.principal_id = RM.role_principal_id INNER JOIN
sys.database_principals AS P
ON P.principal_id = RM.member_principal_id
ORDER BY P.name, RT.RoleType

You need to run this either with a USE statement, or connected to a specific database (you should run it for each database you are reviewing). Sometimes it's necessary to perform a deep dive on a specific individual, in which case you'll want to run this little puppy:

execute as user = 'OURDOMAIN\chkusername'
select * from fn_my_permissions(null, 'DATABASE') -- Leave as the literal
order by subentity_name, permission_name
revert

All The Columns

It doesn't happen to me frequently, but once in a while a tool returns a bunch of SQL that I need to investigate, yet when I look I can't recognize what database it's running against. When your tool doesn't give you a clue, you can use this handy SQL statement to return a list of all of the columns, in all of the databases on your entire instance:

DECLARE @SQL varchar(max)
SET @SQL=''

SELECT @SQL=@SQL+'UNION
select
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.column_id
from '+d.name+'.sys.columns  c
    inner join sys.objects  o on c.object_id=o.object_id
    INNER JOIN sys.schemas  sh on o.schema_id=sh.schema_id
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'

EXEC (@SQL)

Pretty handy, eh?

Defrag It

I mentioned in earlier posts both the joys of indices and some strategies for maintaining them so they don't become too badly fragmented. Although these great high-level guidelines generally collect and resolve the major issues, occasionally you need a deeper inspection to make sure you aren't overlooking any mid-size tables that could be causing a few queries some consternation.  This method is more resource intensive, so I only run it every couple months or so.

You will first need to create a table to receive the results of the fragmentation analysis:

CREATE TABLE [dbo].[sampledIndexStats](
      [TableName] [nvarchar](128) NULL, [IndexName] [nvarchar](128) NULL,
      [Frag] [decimal](5, 2) NULL
) ON [PRIMARY]
GO

Next, run this:

INSERT INTO dbo.sampledIndexStats
SELECT
  OBJECT_NAME(i.[object_id]),
  i.name, s.avg_fragmentation_in_percent
FROM
  sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS s
INNER JOIN sys.indexes AS i
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id;

This takes a while to run as dm_db_index_physical_stats need to physically sample the index trees to see how out-of-sequence the leaf levels are. Once it's done though you can join the resulting sampledIndexStats table into the query that shows overall index usage to determine if any of the mid-sized heavily used indices suffer from fragmentation.

You're welcome.