Performance tuning tips for database developers

The DBA is not going to have time to scrutinize every change made to a stored procedure. Learning to do basic tuning might save you from reworking code late in the game.

Performance tuning is not easy and there aren’t any silver bullets, but you can go a surprisingly long way with a few basic guidelines.

Continue reading “Performance tuning tips for database developers”

Advertisements

Deleting unused indexes from SQL server to free up space and improve performance

In SQL Server, indexes can be a double-edged sword. Sure, they can make queries run faster, but at the same time, their maintenance can have a negative impact. You can improve your server’s overall performance by only maintaining useful indexes – but finding the ones you don’t need can be quite a manual process.

If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index.

Remember that the data from these DMVs is reset when SQL Server is restarted, so make sure you have collected data for a long enough period of time to determine which indexes may be good candidates to be dropped.

Run this in SQL Server:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()

Here we can see seeks, scans, lookups and updates.

The seeks refer to how many times an index seek occurred for that index. A seek is the fastest way to access the data, so this is good.
The scans refers to how many times an index scan occurred for that index. A scan is when multiple rows of data had to be searched to find the data. Scans are something you want to try to avoid.
The lookups refer to how many times the query required data to be pulled from the clustered index or the heap (does not have a clustered index). Lookups are also something you want to try to avoid.
The updates refers to how many times the index was updated due to data changes which should correspond to the first query above.

To find the ones that can be safely removed, run this:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND OBJECT_NAME(S.[OBJECT_ID]) = '[your table name]'
       AND S.database_id = DB_ID() AND (USER_SEEKS = 0 AND USER_SCANS =0 AND USER_LOOKUPS=0)

You can then delete the unused indexes.

Get table size of the biggest tables in a database (SQL)

Following script will return a list of all the tables in your database with a size greater than 10MB.

SELECT 
    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
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
	HAVING SUM(a.total_pages) * 8 / 1024.00 > 10
ORDER BY 
   TotalSpaceMB Desc

tableSize

How to check if a constraint exists

In SQL Server, you can check if a constraint exists using the following script:

SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
        AND OBJECT_NAME(OBJECT_ID)='[constraint name here]'

or by running one of the following commands:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_Name = '[TableName]'

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

--Returns one row for each FOREIGN KEY constrain
SELECT *
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

--Returns one row for each CHECK constraint
SELECT *
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

Enable and disable all indexes in a database

Disabling indexes is a good idea when it comes to loading large quantities of data, but… the big problem is clustered indexes. If you disable a clustered index, you’ve disabled the entire table.

Several options suggest themselves, and none of them are simple.

1) Loop through the system views (sys.indexes), extract the table and index name, generate and execute dynamic SQL to disable the index. Have an “undo” routine to re-enable them. (Be wary–was it a unique index or a unique constraint?) This, alas, only works if you do not use clustered indexes. Good luck with that.

2) As for 1, but skip any clustered indexes. When you load data, make sure it gets loaded in (clustered index) sequential order, otherwise you’ll have poor load times and fragmented tables. (If you data providers are like mine, good luck with that one, too.)

3) Create tables in your database containing definitions of the indexes on your “loading” tables. Build a routine that loops through them and drops all the indexes (clustered indexes last). This will be fast if you truncate the tables first. Load your data, then loop through and recreate the indexes from scratch (clustered first). Use table partitioning to make less horrible on the rest of the system (e.g. do all the above on the “loading” tables, then use partition switching to move the loaded data into your “live” tables). It took me no little time to build such a system, but it can and will work.

Disable script:

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' DISABLE' 
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 0

 

Enable script:

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' REBUILD' 
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 1

How to install .NET Framework 3.5 on Windows Server 2012 and Windows Server 2012 R2

If you have an application that you want to run on Windows Server 2012 that requires the .NET Framework 3.5, you will most likely run in to a problem when trying to install it. If you are trying to install .NET Framework 3.5 from the Server Manager GUI, you will see this when installing the feature:

“Do you want to specify an alternate source path? One or more installation selections are missing source files…”

To solve this, you can either:

Powershell (As Admin)

Install-WindowsFeature Net-Framework-Core -source \\network\share\sxs

Old Fashion Command Line (As Admin)

DISM /Online /Enable-Feature /FeatureName:NetFx3 /All /LimitAccess /Source:d:\sources\sxs

Using Powershell you can verify the install by running Get-WindowsFeature from within PS, you will notice something similar to this;

[X] .NET Framework 3.5 Features NET-Framework-Features   Installed
[X] .NET Framework 3.5 (includes .NET 2.0 and 3.0)  NET-Framework-Core Installed

Note: Source should be the Windows installation disc. In my case, this was located on D:

Bug when adding .net framework 3.5 in Server 2012

2. Go down to “Specify an alternate source path” and enter “d:\sources\sxs” as the path.

Now you should see this under your Features list:

.NET Framework 3.5 feature installed on Windows Server 2012